Script pulls incorrect cost data

[Chad Pagtulingan]

Hello, I have an account-level script which pulls daily campaign cost data and pastes to a google sheet. While it largely works, it also pulls a $1.00 cost for some days when it should be $500-1000 when pulling through the report editor. This problem is only occurring in one account. 

Could I get troubleshooting assistance to see if this erroneous data can be resolved (barring a tracking issue on Google's end)? Why would some campaigns (1.8% of rows) randomly pull $1.00 for daily cost when the UI and report editor are showing much higher values? Script below:

function main() {
  const SPREADSHEET_ID = ' ';
  const SHEET_NAME = 'Google Data';
  const START_DATE = '20230101';

  const sheet = connectToSheet(SPREADSHEET_ID, SHEET_NAME);
  clearSheet(sheet);
  const rows = fetchCampaignCostData(START_DATE);
  writeToSheet(sheet, rows);
  formatCostColumnAsCurrency(sheet, rows.length);
}

function connectToSheet(spreadsheetId, sheetName) {
  const spreadsheet = SpreadsheetApp.openById(spreadsheetId);
  const sheet = spreadsheet.getSheetByName(sheetName);
  if (!sheet) throw new Error('Sheet named "' + sheetName + '" not found');
  return sheet;
}

function clearSheet(sheet) {
  sheet.clearContents();
  sheet.appendRow(['Date', 'Campaign Name', 'Currency Code', 'Cost']);
}

function fetchCampaignCostData(startDate) {
  const timeZone = AdsApp.currentAccount().getTimeZone();
  const yesterday = Utilities.formatDate(new Date(Date.now() - 24 * 60 * 60 * 1000), timeZone, 'yyyyMMdd');
  const currencyCode = AdsApp.currentAccount().getCurrencyCode();

  const query = `
    SELECT
      CampaignName,
      Cost,
      Date
    FROM
      CAMPAIGN_PERFORMANCE_REPORT
    WHERE
      Cost > 0
    DURING ${startDate},${yesterday}
  `;

  const report = AdsApp.report(query);
  const rows = [];
  const iterator = report.rows();

  while (iterator.hasNext()) {
    const row = iterator.next();
    rows.push([
      row['Date'],
      row['CampaignName'],
      currencyCode,
      parseFloat(row['Cost'])
    ]);
  }

  // Sort rows by date ascending (index 0 = 'Date')
  rows.sort(function(a, b) {
    return a[0].localeCompare(b[0]);
  });

  return rows;
}

function writeToSheet(sheet, data) {
  if (data.length === 0) return;
  sheet.getRange(2, 1, data.length, data[0].length).setValues(data);
}

function formatCostColumnAsCurrency(sheet, numRows) {
  if (numRows === 0) return;
  sheet.getRange(2, 4, numRows, 1).setNumberFormat("$#,##0.00");
}

[Google Ads Scripts Forum]

Hi,

Thank you for reaching out to the Google Ads Scripts support team.

To help us analyze the issue further, please provide the following details:

• Google Ads account ID / CID
• Script name
• Spreadsheet URL, refer to the shareable link on how to share the spreadsheet publicly
• Uncropped screenshot of the Google Ads UI showing the higher cost value

You can send the details via Reply privately to the author option, or direct private reply to this email.

Thanks,

Google Ads Scripts Team

[Chad Pagtulingan]

• Google Ads account ID / CID: 548-951-4410
• Script name: CP Test Script - Google Cost Weekly Pull
• Spreadsheet URL: https://docs.google.com/spreadsheets/d/15F4s2J5x7jUSoShB9wHo1nq5_E-gun_smhQ2DHpqk6M/edit?usp=sharing
• Uncropped screenshot of the Google Ads UI showing the higher cost value: See screenshot below which shows the UI with $1218.30 cost, while the spreadsheet pulls $1 for the same day (June 16 2025).
• 
  

--
-- You received this message because you are subscribed to the Google Groups AdWords Scripts Forum group. Please do not reply to this email. To post to this group or unsubscribe please visit https://developers.google.com/adwords/scripts/community.
---
You received this message because you are subscribed to a topic in the Google Groups "Google Ads Scripts Forum" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/adwords-scripts/3YTrdeRkV_c/unsubscribe.
To unsubscribe from this group and all its topics, send an email to adwords-scrip...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/adwords-scripts/148cac48-f8e6-484b-bad5-5095d7491670n%40googlegroups.com.

[Google Ads Scripts Forum Advisor]

Hi,

Thank you for reaching out to the Google Ads Scripts support team.

I would like to inform you that the resource “CAMPAIGN_PERFORMANCE_REPORT” you are using in the query at line 36 is outdated and it won’t work as intended. I would recommend that you replace your current query with the following query so that the data will be retrieved correctly.

const query = `SELECT campaign.name, metrics.cost_micros, segments.date FROM campaign WHERE metrics.cost_micros > 0 AND segments.date BETWEEN ${startDate} AND ${yesterday};`

Please be noted that the metric “metrics.cost_micros” will retrieve the data 1000000 times to that of the actual value. So, I would request that you use “parseFloat(row['metrics.cost_micros']/1000000)” while exporting the data to the spreadsheet.

I hope this helps! Feel free to get back to us if you still face any issues.

Thanks,
 

Google Ads Scripts Team

Feedback
How was our support today?

               
[2025-06-18 20:25:28Z GMT] This message is in relation to case "ref:!00D1U01174p.!500Ht01rfi1K:ref" (ADR-00314877)

[Chad Pagtulingan]

Hello, I've applied the change, but now all my cost values are pulling  #NUM! or zero values. What could be causing this and how to troubleshoot?

[Google Ads Scripts Forum Advisor]

Hi,

I could see that you are using a different field “metrics.costMicros” in the query. As mentioned earlier, kindly replace it with the “metrics.cost_micros” at the SELECT and WHERE fields in the query. You can also go through the code available in the script “CP Test Script - Google Cost Weekly Pull (copy)” for your reference. Additionally, you can utilize the Google Ads Query Builder and Query Validator to build and validate the queries for retrieving the data from the Google Ads API.

I hope this helps! Feel free to get back to us for further issues.

Thanks,
 

Google Ads Scripts Team

Feedback
How was our support today?

               

[2025-06-18 22:34:53Z GMT] This message is in relation to case "ref:!00D1U01174p.!500Ht01rfi1K:ref" (ADR-00314877)

[Chad Pagtulingan]

Hello, with the help of ChatGPT I was able to get the correct cost values showing. The issue is related to cost being denoted as cost_micros or costMicros; there is a lack of consistency in naming convention that could be improved here. Script below for reference. Nothing further is needed, thank you!

function main() {
  const SPREADSHEET_ID = '15F4s2J5x7jUSoShB9wHo1nq5_E-gun_smhQ2DHpqk6M';

  const SHEET_NAME = 'Google Data';
  const START_DATE = '20230101';

  const sheet = connectToSheet(SPREADSHEET_ID, SHEET_NAME);
  clearSheet(sheet);
  const rows = fetchCampaignCostData(START_DATE);
  writeToSheet(sheet, rows);
  formatCostColumnAsCurrency(sheet, rows.length);
}

function connectToSheet(spreadsheetId, sheetName) {
  const spreadsheet = SpreadsheetApp.openById(spreadsheetId);
  const sheet = spreadsheet.getSheetByName(sheetName);

  if (!sheet) throw new Error('Sheet "' + sheetName + '" not found');

  return sheet;
}

function clearSheet(sheet) {
  sheet.clearContents();
  sheet.appendRow(['Date', 'Campaign Name', 'Currency Code', 'Cost']);
}

function fetchCampaignCostData(startDate) {
  const timeZone = AdsApp.currentAccount().getTimeZone();

  const currencyCode = AdsApp.currentAccount().getCurrencyCode();
  const yesterday = new Date(Date.now() - 24 * 60 * 60 * 1000);
  const endDate = Utilities.formatDate(yesterday, timeZone, 'yyyyMMdd');

  const query = `
    SELECT
      campaign.name,
      metrics.cost_micros,
      segments.date
    FROM campaign

    WHERE segments.date BETWEEN '${startDate}' AND '${endDate}'
  `;

  Logger.log('📤 Executing GAQL query');

  const rows = [];
  const results = AdsApp.search(query);
  let count = 0;

  while (results.hasNext()) {
    const row = results.next();
    const campaign = row.campaign.name;
    const date = row.segments.date;
    const costMicros = row.metrics.costMicros;
    const cost = parseFloat(costMicros) / 1000000;

    if (!isNaN(cost)) {
      rows.push([date, campaign, currencyCode, cost]);
      count++;
    }
  }

  Logger.log(`✅ ${count} rows to write`);
  rows.sort((a, b) => a[0].localeCompare(b[0]));

  return rows;
}

function writeToSheet(sheet, data) {
  if (data.length === 0) return;
  sheet.getRange(2, 1, data.length, data[0].length).setValues(data);
}

function formatCostColumnAsCurrency(sheet, numRows) {
  if (numRows === 0) return;
  sheet.getRange(2, 4, numRows, 1).setNumberFormat("$#,##0.00");
}

[Sigurd Fabrin]

"The issue is related to cost being denoted as cost_micros or costMicros; there is a lack of consistency in naming convention that could be improved here"

It is actually consistant, although in a super annoying way.

 In the GAQL query you must use snake_case but in the response, the fields are in camelCase.

...I was eventually so annoyed by this that I wrote a workaround (code below).  You're welcome to use it:

*  It will query any API report and export the data to a Google sheet of your choice. You only need to provide the query, the spreadsheet URL and a sheet name.

Just copy/paste the query from this tool https://developers.google.com/google-ads/api/fields/v19/campaign_query_builder into the settings variable

Btw: it'll also convert cost_micros to account currency and make headlines pretty

const settings = {
  url : 'https://docs.google.com/spreadsheets/d/etc,
  sheet : 'Sheet1',
  query : `
    SELECT  ...  FROM   ...  WHERE  .. ORDER BY  ..

`
}

// Code below is generic for all API reports - so only change stuff in the settings variable above

function main() {
  const select = settings.query.replace(/\s+/g, '') // Remove spaces
    .replace(/(select)(.*)(from.*)/i, '$2') // Only keep what's in the SELECT clause
    .replace(/(_)(\w{1})/g, (match, p1, p2) => p2.toUpperCase()); // Convert snake_case to camelCase
  const fields = select.split(',');
  const values = [];
  fields.forEach((field, index) => {
    values.push(field.trim());
  });
  const response = AdsApp.search(settings.query);
  const data = [];
  while (response.hasNext()) {
    const row = response.next();
    const rowData = values.map(field => {
      const parts = field.split('.'); 
      if (parts[0] === 'segments') {
        return row.segments ? row.segments[parts[1]] : null;
      } else if (parts[0] === 'metrics') {
        if (parts[1] === 'costMicros' && row.metrics) {
          // cost_micros to account currency
          const costMicros = row.metrics[parts[1]];
          return costMicros ? parseFloat(costMicros) / 1000000 : null;
        }
        return row.metrics ? row.metrics[parts[1]] : null;
      } else if (row[parts[0]]) {
        return row[parts[0]] ? row[parts[0]][parts[1]] : null;  // top-level properties like customer, campaign
      }
      return null;
    });
    data.push(rowData);
  }
  const prettyHeaders = values.map(header =>
    header
      .replace(/^(segments|metrics|customer)\./, '')
      .replace(/([a-z])([A-Z])/g, '$1 $2')
      .replace(/\./g, ' ')                            
      .toLowerCase()
      .replace(/cost micros/g, 'cost')
  );
  data.unshift(prettyHeaders);
  const sheet = SpreadsheetApp.openByUrl(settings.url).getSheetByName(settings.sheet);
  sheet.clearContents(); // delete any old data first
  sheet.getRange(1, 1, data.length, data[0].length).setValues(data);
}

Sigurd

[Chad Pagtulingan]

Thank you!

--

-- You received this message because you are subscribed to the Google Groups AdWords Scripts Forum group. Please do not reply to this email. To post to this group or unsubscribe please visit https://developers.google.com/adwords/scripts/community.
---
You received this message because you are subscribed to a topic in the Google Groups "Google Ads Scripts Forum" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/adwords-scripts/3YTrdeRkV_c/unsubscribe.
To unsubscribe from this group and all its topics, send an email to adwords-scrip...@googlegroups.com.

To view this discussion visit https://groups.google.com/d/msgid/adwords-scripts/1cecd302-a8a0-434e-879a-d61f0d29fd00n%40googlegroups.com.