Script does not export TopImpressionPercentage and AbsoluteTopImpressionPercentage as digits in Google Sheets

Question

I’ve created a script that exports campaign results to a Google Sheets file. When I implemented the script on September 29th, the data was exported perfectly. See example below (notice that the last four numbers are right-aligned because they're digits).

Month	AccountDescriptiveName	CampaignName	Impressions	SearchImpressionShare	TopImpressionPercentage	AbsoluteTopImpressionPercentage
2019-03-01	Account name	Campaign name	2800	90.82%	0.73	0.73

However, something changed in the output two weeks ago. The columns TopImpressionPercentage and AbsoluteTopImpressionPercentage were exported differently.

Month	AccountDescriptiveName	CampaignName	Impressions	SearchImpressionShare	TopImpressionPercentage	AbsoluteTopImpressionPercentage
2019-03-01	Account name	Campaign name	2800	90.82%	0.73	0.73

The issue here is that TopImpressionPercentage and AbsoluteTopImpressionPercentage have different cell properties. Google Sheets doesn’t see these cells as digits but as text (I guess). Now the goal of this export is that we automatically make calculations in a different sheet based on these number. These automatic calculations fail now because these two columns don’t work anymore. Manually changing the cell properties in Google Sheets is not a solution.

I have no idea how this has changed. I haven’t made any changes to the script. I’ve tried to run the script to a different Google Sheets file but without any difference.

Here's the script. My apologies for the incorrect indenting, but the focus here is on the elements that the script contains.

function main(){
var sheetURL = 'URL';
var tabName = 'Tabname';

var QUERIES = [{'query' : 'SELECT Month, AccountDescriptiveName, CampaignName, Labels, '
            + 'Impressions, Clicks, Cost, Conversions, SearchImpressionShare, TopImpressionPercentage, AbsoluteTopImpressionPercentage '
            + 'FROM CAMPAIGN_PERFORMANCE_REPORT '
            + 'WHERE Impressions > 0 '
            + 'DURING 20190101,20220101', 
              'spreadsheetUrl' : sheetURL,
              'tabName' : tabName,
              'reportVersion' : 'v201809'
       }            
      ];

  var query;
  var spreadsheetUrl;
  var tabName;
  var reportVersion;

 for(var i in QUERIES) {
  var queryObject = QUERIES[i];
  query = queryObject.query;
  spreadsheetUrl = queryObject.spreadsheetUrl;
  tabName = queryObject.tabName;
  reportVersion = queryObject.reportVersion;
 }

  var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);
  var sheet = spreadsheet.getSheetByName(tabName);
  var report = AdWordsApp.report(query, {apiVersion: reportVersion});
  report.exportToSheet(sheet);
}

I was forwarded from the Google Ads Scrips Direct Support as everything works as it should in the Campaign Performance Report. Because "the Google Ads Scripts utilizes Google Apps Script's Spreadsheet to actually extract the report to your spreadsheet", they recommended me reaching out to Apps Script team.

I hope that you are able to help me out.

Thank you in advance.

Answer 1

You can easily parse the values such that they end up matching the type you need for your calculations.

The thing with Apps Script is that when using getValue, the method will end up returning a value of type Number, Boolean, Date, or String, depending on the value of the cell. Therefore, if the cell is indeed formatted to store values of type text, the value returned in the script will be of type String.

To fix this, simply do the following:

var topImpressionPercentage = Number(rangeTop.getValue());
var absoluteTopImpressionPercentage = Number(rangeAbs.getValue());

Reference

• Apps Script Range Class - getValue().