How to Add Another Column of Data onto Google Sheet Using Google Script

[Liyan Liang]

Hi Google Ads Scripts Support Team, 

I find a script to  filter out products, whose conversion is 0 and cost is over 10€ in the less 7 days. But the products on the result list did not fit the criteria. 

Therefore, I would like to have the cost and conversion data also exported to the result list.

Since my knowledge on Google Script is limited, I would appreciate it if you can help me. 

Script: 

// Copy the link of the new sheet and paste it below -
var SPREADSHEET_URL =
"(for security reason the url cannot be displayed)";


// Enter your filters below, for multiple filters use AND clause. E.g. Impressions > 100 AND
// Currently default filter is Clicks < 1 i.e. Zero Clicks
var FILTERS = "Conversions = 0 AND Cost > 10";
// Enter time duration below. Possibilities:
// TODAY | YESTERDAY | LAST_7_DAYS | LAST_WEEK | LAST_BUSINESS_WEEK |
//THIS_MONTH | LAST_MONTH |
// LAST_14_DAYS | LAST_30_DAYS | THIS_WEEK_SUN_TODAY |
//THIS_WEEK_MON_TODAY | LAST_WEEK_SUN_SAT
// Currently default time duration is set to: LAST_30_DAYS
var TIME_DURATION = "LAST_7_DAYS";
var COUNT_LIMIT = 999999;
function main(){
var products = getFilteredShoppingProducts();
products.sort(function(a,b){return a[0] > b[0];});
products = products.slice(0, COUNT_LIMIT);
pushToSpreadsheet(products);
}
function getFilteredShoppingProducts(){
var query = "SELECT OfferId FROM SHOPPING_PERFORMANCE_REPORT WHERE " +
FILTERS + " DURING "+ TIME_DURATION;
var products = [];
var count = 0;
var report = AdWordsApp.report(query);
var rows = report.rows();
while (rows.hasNext()){
var row = rows.next();
var offer_id = row['OfferId'].toString();
products.push([offer_id]);
count+= 1;
}
Logger.log(count);
return products;
}
function pushToSpreadsheet(data){
var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
var sheet = spreadsheet.getSheetByName('Custom_Label');
var lastRow = sheet.getMaxRows();
sheet.getRange('A2:A'+lastRow).clearContent();
var start_row=2;
var endRow=start_row+data.length-1;
var range = sheet.getRange('A'+start_row+':'+'A'+endRow);
if (data.length>0){range.setValues(data);}
return;
}

Thank you very much ahead! 

Liyan

[Google Ads Scripts Forum Advisor]

Hello Liyan,

I’m James from the Google Ads scripts support team. Thank you for reaching out to us.

Can you please provide us with the following details below so that we can further investigate and can guide you accordingly?

• Google Ads account ID / CID
• Name of the script wherein the given code is implemented
• Shareable link of the involved spreadsheet

Kindly send the requested details above via ‘Reply privately to author’ option, if the private option is not available on your end, then please send it over through this email <googleadsscr...@google.com> instead.

Regards,

James Howell Google Ads Scripts Team

ref:_00D1U1174p._5004Q2aNL5S:ref

[Google Ads Scripts Forum Advisor]

Hello Liyan,

I made some adjustments to your script so that you will be able to also export those data(cost and conversion) on separate columns within your spreadsheet. Having said that, can you please try to implement the attached script file and let me know how it goes?

Regards,

[Liyan Liang]

Hi James, 

thank your for the adjustment of the script. 

I tried to implement it but there is an error. 

The log was: 

29.4.2022 08:14:59       1810.0
29.4.2022 08:14:59      TypeError: Cannot read property 'getMaxRows' of null at pushToSpreadsheet (Code:52:23) at main (Code:24:1)

There is also no input on the Google Sheet. 

It could be that we have now 3 rows and the script does not know which column it should get the max row with "  var lastRow = sheet.getMaxRows();".

Could you please take a look for us? 

Thanks ahead. 

Regards,

Liyan

[Google Ads Scripts Forum Advisor]

Hello,

I work along with James. Allow me to assist you in this.

I've checked the updated script and can confirm that its implementation is fine now. One thing I notice is that your FILTERS were configured as "Conversions = 1 AND Cost > 10" which will result to INVALID_PREDICATE_OPERATOR error. This error occurs because the Conversions field is of type, Double. As a measure to ensure accuracy of calculations, this type only supports the use of the two operators, ' > ' and ' < '. With that said, you may declare your fiter like this var FILTERS = "Conversions < 1 AND Cost > 10"; instead.

I was able to extract the result after doing the changes mentioned above (please see attached file). In addition, kindly ensure that the new experience is toggled off before previewing your script.

Regards,

Teejay Wennie Google Ads Scripts Team

ref:_00D1U1174p._5004Q2aNL5S:ref

[Liyan Liang]

Hi Teejay, 

thanks for the correction on the FILTER. 

I toggleed off the new experience and previewed  the script. The error of getMaxRow(). Attached is the screenshot. 

Could it be that your code is different than mine, or setting of the script? 

Could you please share your code with me, which has successfully been previewed and implemented. 

This is the code that I have implemented and got the error.

// Copy the link of the new sheet and paste it below -

var SPREADSHEET_URL ="the url is not shown";

// Enter your filters below, for multiple filters use AND clause. E.g. Impressions > 100 AND


// Currently default filter is Clicks < 1 i.e. Zero Clicks

var FILTERS = "Conversions < 1 AND Cost > 10";

// Enter time duration below. Possibilities:
// TODAY | YESTERDAY | LAST_7_DAYS | LAST_WEEK | LAST_BUSINESS_WEEK |
//THIS_MONTH | LAST_MONTH |
// LAST_14_DAYS | LAST_30_DAYS | THIS_WEEK_SUN_TODAY |
//THIS_WEEK_MON_TODAY | LAST_WEEK_SUN_SAT
// Currently default time duration is set to: LAST_30_DAYS
var TIME_DURATION = "LAST_7_DAYS";
var COUNT_LIMIT = 999999;


function main(){
var products = getFilteredShoppingProducts();
products.sort(function(a,b){return a[0] > b[0];});
products = products.slice(0, COUNT_LIMIT);
pushToSpreadsheet(products);
}

function getFilteredShoppingProducts(){

  var query = "SELECT OfferId, Cost, Conversions FROM SHOPPING_PERFORMANCE_REPORT WHERE " +

  FILTERS + " DURING "+ TIME_DURATION;
  var products = [];
  var count = 0;
 

  var report = AdsApp.report(query);

  var rows = report.rows();
  while (rows.hasNext()){
  var row = rows.next();
   
  var offer_id = row['OfferId'].toString();

  var cost = row['Cost'];
  var conversion = row['Conversions'];
 
  products.push([offer_id, cost, conversion]);

  count+= 1;
  }
  Logger.log(count);
  return products;
}

function pushToSpreadsheet(data){
  var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var sheet = spreadsheet.getSheetByName('Custom_Label');
  var lastRow = sheet.getMaxRows();

  sheet.getRange('A2:C'+lastRow).clearContent();
  var start_row=2;
  var endRow=start_row+data.length-1;
  var range = sheet.getRange('A'+start_row+':'+'C'+endRow);

  if (data.length>0){range.setValues(data);}
  return;
}

Thank you very much ahead. 

Regards,

Liyan

[Google Ads Scripts Forum Advisor]

Hello Liyan,

Can you please confirm if the value that you have set to line number 51 under getSheetByName method is exactly matching the sheet name of your spreadsheet? I asked this because I wasn't able to access the given spreadsheet link.

Regards,

James Howell Google Ads Scripts Team

ref:_00D1U1174p._5004Q2aNL5S:ref

[Liyan Liang]

Hi James, 

yes, getSheetByName is identical as the name of sheet of the link, as screenshot below. 

I think that is not the problem for this script. 

I ran the script for preview again. It shows always the same error of 'getMaxRows':  

Could you please take a closer look at this error? 

Thank you very much ahead. 

Regards,

Liyan

[Дмитро Булах]

names are not identical:
 'Custom_label' vs 

 'Customer_label'

[Liyan Liang]

Hi James, 

thanks for the correction. 

I have corrected the code. Now we also have the data on the sheet but they did not quite fit the criteria of the codes. 

On the codes, we have FILTER: "Conversions < 1 AND Cost > 10"

But on the result of sheet, we see, e.g. at row 33. ID: 16511 with conversion = 7.13  > 1 and cost = 0

Do you have any idea, what the problem could be? 

Thank you very much. 

Liyan

[Google Ads Scripts Forum Advisor]

Hello,

I've tested once again your script, but didn't encounter the reported behavior. Instead, the script provides the right data based on the condition set. With this, could you kindly try the said script once again? Also, could you try to add this code after var conversion = row['Conversions'];
 

  Logger.log("offer_id: " + offer_id);
  Logger.log("cost: " + cost);
  Logger.log("conversion: " + conversion);
  Logger.log("-------");

Doing this will enable us to check your script history, and compare your result when testing it.

Regards,

Teejay Wennie Google Ads Scripts Team

 
 

ref:_00D1U1174p._5004Q2aNL5S:ref

[Liyan Liang]

Hi, 

hmm, that's strange that we didn't have the same output, eventhough the we have the same script. I have copied the script onto our Google Ads Account, which is provided by James on  29.04.2022 on this thread. 

I have added the addtional codes on the script. Please take a look. 

Thanks and regards,

Liyan

[Google Ads Scripts Forum Advisor]

Hi Liyan,

Thank you for getting back to us. 

I've scrutinized your script once again and noticed that the new experience is toggled on which causes an issue. In order for the script to work on new experience, you will need to migrate it from AdWords API report to Google Ads API report. I've updated this for you. Please see attached script for your reference.

[Liyan Liang]

Hi Teejay,

thanks for checking and adjusting the script for us. 

It works! I noticed that the number format for cost on the excel sheet is a bit multiplied by 100,000. Therefore, I have also changed my filter accordingly so that I have result needed. 

A small question: would it be possible to have campaign information? I have tried with "metrics.campaign" or "metrics.campaign_micros" and adjusted the script accordingly. But the information cannot be found on the GoogleAdsService.Search. It would be great to have the campaign information. 

Best regards,

Liyan

[Google Ads Scripts Forum Advisor]

Hi Liyan,

Thank you for updating us and I'm glad that the script works on your end.

The script that I've provided uses shopping_performance_view report which you can find here. If you're looking to add the campaign name on the report, then you will need to add campaign.name field into your SELECT clause.