Why has data loads via Google Apps script to Bigquery limited to 513 rows of data only?

Question

I load data into google spreadsheets from our google bigquery database via google apps scripts. Until recently, I have managed to load over 3k rows of data... however it has now been limited to just 513 rows. When I run the same query in BigQuery, i'm able to get the actual 3000 rows of data.

My code:

function Job(){

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheetName = "RAW";
var sheet = ss.getSheetByName(sheetName);
ss.setActiveSheet(sheet);
var sql = ' BigQuery code';
var results = GSReport.runQueryAsync(sql);
var resultsValues = GSReport.parseBigQueryAPIResponse(results); 
sheet.clear();

var data = [];
data.push([ "InstallWeek","Event","Users"]);
for ( var i = 0 ; i < resultsValues.length ; i++ ) {
data.push(resultsValues[i]);

}

var range = sheet.getRange(1,1,data.length,data[0].length); range.setValues(data);

}

Jordan Tigani Jordan Tigani · Accepted Answer · 2014-02-19T00:15:54

There is a lot of magic that you've left out in your question (as in what does "runQueryAsync" do?). That said, what is likely happening is that you're only reading one page of data. You should be able to set the max result size when you issue the query. See the maxResults field documented here: https://developers.google.com/bigquery/docs/reference/v2/jobs/query.

The reason that this changed recently is because we accidentally reduced the default number of query results returned in one page to 512 to bring in line with other APIs. However, we should be increasing that number again soon.

Why has data loads via Google Apps script to Bigquery limited to 513 rows of data only?

1 Answers