how to insert data from js object array to a google sheet in bulk instead of row by row

[Jay Wilner]

does anyone know the way to publish/write data from an array of js objects from an adwords script to a google sheet?

right now i am doing it one row at a time and its really slow. you can sit there and watch the rows being inserted one by one. i know this is not the most efficient or even preferred way google wants this to happen.

here is my code i am currently using (the snippet in question anyway). I have an array named "queries" that is an array of query objects and then i use the setValue sheet method to write the values in each cell of each row. I am thinking there is some method in the sheet class that will do this all at once.

 for (i=0; i < queries.length; i++) {

      var row = i+2;

      sheet.getRange("A" + row).setValue(queries[i].AdGroupName);

      sheet.getRange("B" + row).setValue(queries[i].CampaignName);

      sheet.getRange("C" + row).setValue(queries[i].Keyword);

      sheet.getRange("D" + row).setValue(queries[i].MatchType);

      sheet.getRange("E" + row).setValue(queries[i].Query);

      sheet.getRange("F" + row).setValue(queries[i].Impressions);

      sheet.getRange("G" + row).setValue(queries[i].Clicks);

      sheet.getRange("H" + row).setValue(queries[i].Cost);

      sheet.getRange("I" + row).setValue(queries[i].Conversions);

      sheet.getRange("J" + row).setValue(queries[i].CostPerConversion);

      sheet.getRange("K" + row).setValue(myDate);

    }

[Alexander Wang]

Hi Jay,

Sorry for the delay. Most of the team was out for the holidays.

As for your question, I'm not completely certain as it's more a question for Google Apps Script than AdWords scripts (they maintain SpreadsheetApp, not us), but I believe it's faster to set entire ranges. I think this is what report.exportToSheet does. For example, I'd update your code like:

var range = sheet.getRange("A" + row + ":K" + row);
range.setValues([[queries[i].AdGroupName,queries[i].CampaignName,queries[i].Keyword,...]]);

There is some documentation about this here:

https://developers.google.com/apps-script/best_practices#batchOperations

http://googleappsscript.blogspot.com/2010/06/optimizing-spreadsheet-operations.html

You might even be able to get an even bigger boost by first converting 'queries' into a 2-dimensional array. Then you wouldn't need to make 'queries.length' number of setValues calls, you could make a single call like:

sheet.getRange("A1:K" + queries.length).setValues(queriesAs2dArray);

(Where queriesAs2dArray is something like [[queries[0].AdGroupName,queries[0].CampaignName,...],[queries[1].AdGroupName,queries[1].CampaignName,...],...])

Anyways, give those a shot and see if it helps. You could also look into Google Apps Script Support for a definitive answer.

Cheers,

Alex