Google Ads API issue with Metric: metrics.cost_micros, Value: null

Question

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

Answer 1

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.