I'm using the following Google Apps Script to pull in data using the Google Ads API. The data appears to be pulling in fine, but parsing it seems to be an issue. Here is the script:
function getGoogleAdsData() { const fileId = 'fileID'; const campaignSheetName = 'Campaigns'; const campaignMetrics = ['segments.date', 'campaign.id', 'metrics.cost_micros']; // Calculate the dates for the previous week (Monday to Sunday) const today = new Date(); const dayOfWeek = today.getDay(); // 0 (Sunday) to 6 (Saturday) // Adjust today to get the last Sunday const lastSunday = new Date(today); lastSunday.setDate(today.getDate() - dayOfWeek - 0); // Calculate the previous Monday (6 days before last Sunday) const lastMonday = new Date(lastSunday); lastMonday.setDate(lastSunday.getDate() - 6); const formattedStartDate = Utilities.formatDate(lastMonday, Session.getScriptTimeZone(), 'yyyy-MM-dd'); const formattedEndDate = Utilities.formatDate(lastSunday, Session.getScriptTimeZone(), 'yyyy-MM-dd'); Logger.log(`Fetching data from ${formattedStartDate} to ${formattedEndDate}`); // Fetch Campaign Data const campaignData = fetchGoogleAdsData(formattedStartDate, formattedEndDate, campaignMetrics); const formattedCampaignData = campaignData.map(row => [ formatDate(row[0]), // Date row[1], // Campaign ID row[2] // Cost in currency units ]); // Write to Sheets if there's data if (formattedCampaignData.length > 0) { writeToSheet(fileId, campaignSheetName, formattedCampaignData, ['Date', 'Campaign ID', 'Cost']); } else { Logger.log('No data available to write to the sheet.'); } } function fetchGoogleAdsData(startDate, endDate, metrics) { const query = ` SELECT ${metrics.join(', ')} FROM campaign WHERE segments.date BETWEEN '${startDate}' AND '${endDate}' `; const apiUrl = 'https://googleads.googleapis.com/v17/customers/xxxxxxxx/googleAds:searchStream'; const options = { method: 'POST', headers: { 'Authorization': `Bearer ${ScriptApp.getOAuthToken()}`, 'developer-token': 'OUR-TOKEN', 'login-customer-id': 'xxxxxxxxxx' }, contentType: 'application/json', payload: JSON.stringify({ query: query }), muteHttpExceptions: true }; const response = UrlFetchApp.fetch(apiUrl, options); const responseData = JSON.parse(response.getContentText()); Logger.log(`Full API Response: ${JSON.stringify(responseData)}`); if (responseData.length > 0 && responseData[0].results) { Logger.log(`Found ${responseData[0].results.length} result(s).`); return responseData[0].results.map(result => { Logger.log(`Metrics object: ${JSON.stringify(result.metrics)}`); const row = []; metrics.forEach(metric => { let value = getNestedValue(result, metric.split('.')); Logger.log(`Metric: ${metric}, Value: ${value}`); // Log each metric's value if (metric === 'metrics.cost_micros') { value = value / 10; // Convert micros to currency units Logger.log(`Converted cost_micros: ${value}`); } row.push(value); }); return row; }); } else { Logger.log('No results found in response or response structure is different.'); return []; } } function getNestedValue(obj, keys) { return keys.reduce((acc, key) => (acc && acc[key] !== undefined) ? acc[key] : null, obj); } function writeToSheet(fileId, sheetName, data, headers) { const sheet = SpreadsheetApp.openById(fileId).getSheetByName(sheetName); sheet.clear(); // Clear existing data sheet.appendRow(headers); // Add headers if (data.length > 0 && data[0].length > 0) { sheet.getRange(2, 1, data.length, data[0].length).setValues(data); // Add data } else { Logger.log('No data to write to the sheet.'); } } function formatDate(dateString) { const date = new Date(dateString); return Utilities.formatDate(date, Session.getScriptTimeZone(), 'dd/MM/yyyy'); }
The full API response looks something like this:
Full API Response: [{"results":[{"campaign":{"resourceName":"customers/xxxxx/campaigns/3509213","id":"3509213"},"metrics":{"costMicros":"41060000"},"segments":{"date":"2024-08-26"}},{"campaign":{"resourceName":"customers/xxxxx/campaigns/3509213","id":"3509213"},"metrics":{"costMicros":"54460000"},"segments":{"date":"2024-08-27"}},{"campaign":{"resourceName":"customers/xxxxx/campaigns/3509213","id":"3509213"},"metrics":{"costMicros":"33690000"},"segments":{"date":"2024-08-28"}}.....
And further logging shows this:
8:05:38 AM Info Found 232 result(s). 8:05:38 AM Info Metrics object: {"costMicros":"41060000"} 8:05:38 AM Info Metric: segments.date, Value: 2024-08-26 8:05:38 AM Info Metric: campaign.id, Value: 3509213 8:05:38 AM Info Metric: metrics.cost_micros, Value: null 8:05:38 AM Info Converted cost_micros: 0
I've tried changing the metrics.cost_micros to metrics.costMicros, but this doesn't work and returns an unrecognised field.
Why is 'Metric: metrics.cost_micros, Value: null'?
