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'?

Tag:google-ads-api, google-apps-script, google-cloud-platform

3 comments.

  1. Tanaike

    Although, unfortunately, I cannot understand I've tried changing the metrics.cost_micros to metrics.costMicros, but this doesn't work and returns an unrecognised field., when I saw your The full API response, the property cost_micros cannot be found. Is this related to your current issue Why is 'Metric: metrics.cost_micros, Value: null'??

    If my understanding is correct, how about modifying as follows?

    From:const campaignMetrics = ['segments.date', 'campaign.id', 'metrics.cost_micros']; To:const campaignMetrics = ['segments.date', 'campaign.id', 'metrics.costMicros']; Another approach:

    I cannot know your entire response value from the API. But, as another approach, if your values have the properties both cost_micros and costMicros, how about the following modification?

    From:function getNestedValue(obj, keys) { return keys.reduce((acc, key) => (acc && acc[key] !== undefined) ? acc[key] : null, obj); } To:function getNestedValue(obj, keys) { return keys.reduce((acc, key) => (acc && acc[key] !== undefined) ? acc[key] : (key.includes("_") ? (acc[key.replace(/_./g, ([, a]) => a.toUpperCase())] || null) : null), obj); }

    By this modification, when you use const campaignMetrics = ['segments.date', 'campaign.id', 'metrics.cost_micros'];, the properties of both the snake case and the camel case are used like cost_micros and costMicros.

    Reply
    1. user8358204

      Thank you, the second approach did indeed work!

      Reply
    2. Tanaike

      @user8358204 Thank you for replying and testing it. I'm glad your issue was resolved. Thank you, too.

      Reply

Add a new comment.