Exceeded maximum execution time on Google App script with Google Big Query

Question

How can I extend the execution time within my code below. Essentially, I use Google App scripts to query data from our big query data base and export data on to Google spreadsheets.

The following is my code:

function Weekly_Metric(){

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheetName = "Budget";
var sheet = ss.getSheetByName(sheetName);
ss.setActiveSheet(sheet);
var sql = ' bigqueryscript ';
var results = GSReport.runQueryAsync(sql);
var resultsValues = GSReport.parseBigQueryAPIResponse(results); 
sheet.clear();
ss.appendRow(["Label1", "Label2", "Label3"]);

 for ( var i = 0 ; i < resultsValues.length ; i++ ) {
  ss.appendRow(resultsValues[i]);
 }
}

score 2 · Accepted Answer · answered Dec 09 '13 at 18:32

Always reduce the number of calls to Google Apps Script services as much as you can.

In this case, the loop containing appendRow() can be replaced with javascript array operations and a single call to setValues().

...
sheet.clear();

var data = [];
data.push(["Label1", "Label2", "Label3"]);
for ( var i = 0 ; i < resultsValues.length ; i++ ) {
  data.push(resultsValues[i]);
}
ss.getRange(1,1,data.length,data[0].length).setValues(data);
...

Alternatively, if resultsValues is an array of rows already, you only need to add the labels:

...
sheet.clear();

resultsValues.unshift(["Label1", "Label2", "Label3"]);
ss.getRange(1,1,resultsValues.length,resultsValues[0].length).setValues(resultsValues);
...

If that doesn't do the trick, then you should look at your GSReport object's methods.

Exceeded maximum execution time on Google App script with Google Big Query

1 Answers1