Google Ads script not showing cost, Avg. CPC ($) and Cost/Conv ($) Data

[Jiahui Sun]

Hi, can anyone helps to fix the Google ads script below? Why are cost, Avg. CPC ($) and Cost/Conv ($) Data remain 0 in the excel after I ran the Google Ads Script:

function main() {
  const SPREADSHEET_URL = 'https://docs.google.com/spreadsheets/d/1cnx7GO3UCxmgurb3GU_dDK1w9nIZ2_INznp-C5iv1Uw/edit';
  const sheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL).getSheetByName('Sheet1');
 
  // Clear existing content
  sheet.clear();

  const timeZone = AdsApp.currentAccount().getTimeZone();
  const today = new Date();
  const formattedDate = Utilities.formatDate(today, timeZone, "yyyy-MM-dd");

  // Add debugging log
  Logger.log("Starting report generation for date: " + formattedDate);

  const report = AdsApp.report(
    'SELECT CampaignName, Impressions, Clicks, Ctr, Cost, AverageCpc, Conversions, ' +
    'ConversionRate, CostPerConversion, ConversionValue ' +
    'FROM CAMPAIGN_PERFORMANCE_REPORT ' +
    'DURING LAST_7_DAYS'
  );

  const rows = report.rows();
 
  // Add debugging log
  Logger.log("Report query executed, processing rows...");

  sheet.appendRow(['']);
  sheet.appendRow(['Report Date', formattedDate]);
  sheet.appendRow([
    'Campaign Name', 'Impressions', 'Clicks', 'CTR (%)', 'Cost ($)', 'Avg. CPC ($)',
    'Conversions', 'Conversion Rate (%)', 'Cost/Conv ($)', 'Conv. Value ($)'
  ]);

  let rowCount = 0;
 
  while (rows.hasNext()) {
    const row = rows.next();
    rowCount++;

    // Debug log for each row
    Logger.log("Processing row " + rowCount + ": " + row['CampaignName']);
   
    // Debug specific fields
    Logger.log("Raw Cost value: " + row['Cost']);
    Logger.log("Raw ConversionValue: " + row['ConversionValue']);

    const campaignName = row['CampaignName'] ? row['CampaignName'].toLowerCase() : '';
    const impressions = parseInt(row['Impressions']) || 0;
    const clicks = parseInt(row['Clicks']) || 0;

    // Skip irrelevant campaigns
    if (
      (impressions === 0 && clicks === 0) ||
      campaignName.includes('test') ||
      campaignName.includes('remarketing') ||
      campaignName.includes('rm') ||
      campaignName.includes('xx')
    ) {
      Logger.log("Skipping campaign: " + campaignName);
      continue;
    }

    // Improved value handling functions
    function safeNumber(value) {
      if (value === undefined || value === null || value === '--') return '';
      return parseFloat(value) || 0;
    }
   
    function formatCurrency(value, isMicro = false) {
      if (value === '' || isNaN(value)) return '';
      const num = isMicro ? (parseFloat(value) / 1000000) : parseFloat(value);
      return num.toFixed(2);
    }
   
    function safePercent(value) {
      if (!value || value === '--') return '';
      // Handle percentage values with or without % symbol
      const cleanValue = value.toString().replace('%', '');
      return parseFloat(cleanValue).toFixed(2);
    }

    // Get raw values first
    const rawCost = row['Cost'];
    const rawCpc = row['AverageCpc'];
    const rawCostPerConv = row['CostPerConversion'];
    const rawConvValue = row['ConversionValue'];
   
    // Process values
    const cost = formatCurrency(safeNumber(rawCost), true);
    const cpc = formatCurrency(safeNumber(rawCpc), true);
    const costPerConv = formatCurrency(safeNumber(rawCostPerConv), true);
   
    // ConversionValue might be in micro-units or regular units depending on account settings
    // Try both ways and use the one that makes more sense
    const convValueRegular = formatCurrency(safeNumber(rawConvValue), false);
    const convValueMicro = formatCurrency(safeNumber(rawConvValue), true);
   
    // Choose the most likely correct value (non-zero if possible)
    const convValue = (parseFloat(convValueRegular) > 0) ? convValueRegular :
                     (parseFloat(convValueMicro) > 0) ? convValueMicro :
                     rawConvValue ? formatCurrency(safeNumber(rawConvValue), true) : '';
   
    // Debug processed values
    Logger.log("Processed Cost: " + cost);
    Logger.log("Processed ConversionValue: " + convValue);

    sheet.appendRow([
      row['CampaignName'],
      impressions,
      clicks,
      safePercent(row['Ctr']),
      cost,
      cpc,
      formatCurrency(safeNumber(row['Conversions']), false),
      safePercent(row['ConversionRate']),
      costPerConv,
      convValue
    ]);
  }
 
  // Add summary log
  Logger.log("Report generation complete. Processed " + rowCount + " rows.");
 
  // Add a note about debugging
  sheet.appendRow(['']);
  sheet.appendRow(['Note:', 'If data is still missing, check the Logs in Google Ads Scripts editor for debugging information.']);
}

[Jiahui Sun]

Hi 

Thanks for helping. May I know if the script should be used under our MCC account or applied to individual ad accounts? 

Thank you 

Warm Regards

SUN JIAHUI DIGITAL MARKETING DIRECTOR t +65 6817 4571 | m +65 8751 8311  56 Kallang Pudding Road, #07-05/06 HH@Kallang, Singapore 349328

4.6 AVERAGE CLIENT RATING Trusted by over 3,000 businesses in Singapore How's our service so far? https://verzdesign.com/cip

This message is intended for the designated recipient only and may contain private & confidential information. If you have received it in error, please notify the sender immediately and delete the original. We appreciate your support and understanding.

--
-- 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 the Google Groups "Google Ads Scripts Forum" group.
To unsubscribe from this group and stop receiving emails from it, send an email to adwords-scrip...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/adwords-scripts/05df5bb6-e615-4d98-82cc-13baf268ea8dn%40googlegroups.com.

[Google Ads Scripts Forum Advisor]

Hi,

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

I would suggest that you use the below query instead of the query you are using in the “AdsApp.report()” method.

SELECT campaign.name, metrics.impressions, metrics.clicks, metrics.ctr, metrics.cost_micros, metrics.average_cpc, metrics.conversions, metrics.cost_per_conversion, metrics.conversions_value, metrics.conversions_from_interactions_rate FROM campaign WHERE segments.date DURING LAST_7_DAYS

Please be noted that the field “metrics.cost_micros” in the above query returns the value as 1000000 times to that of actual cost. To retrieve the actual cost, divide it with 10^6.

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.

If you are still facing the same issue after using the above query, kindly get back to us with the following details.

• Google Ads account ID/CID
• Name of the affected script
• Shareable spreadsheet link that you are using in the script. You may follow this article to share a file publicly

You can share the requested details via Reply privately to the author option or a direct private reply to this email.

Thanks,
 

Google Ads Scripts Team

Feedback
How was our support today?

               
[2025-06-03 22:06:55Z GMT] This message is in relation to case "ref:!00D1U01174p.!500Ht01re6ZS:ref" (ADR-00309768)