Last active
July 24, 2016 06:44
-
-
Save huschdie/c4313d27df4ce0fb2160777cf97b2835 to your computer and use it in GitHub Desktop.
Running BigQuery-queries (sql, projectId, output_sheet)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
function runQ(sql,projectId,output_sheet) { | |
var request = { | |
query: sql | |
}; | |
var queryResults = BigQuery.Jobs.query(request, projectId); | |
var jobId = queryResults.jobReference.jobId; | |
// Check on status of the Query Job. | |
var sleepTimeMs = 500; | |
while (!queryResults.jobComplete) { | |
Utilities.sleep(sleepTimeMs); | |
sleepTimeMs *= 2; | |
queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId); | |
} | |
// Get all the rows of results. | |
var rows = queryResults.rows; | |
while (queryResults.pageToken) { | |
queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId, { | |
pageToken: queryResults.pageToken | |
}); | |
rows = rows.concat(queryResults.rows); | |
} | |
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(output_sheet); | |
sheet.clear(); | |
if (rows) { | |
// Append the headers. | |
var headers = queryResults.schema.fields.map(function(field) { | |
return field.name; | |
}); | |
sheet.appendRow(headers); | |
// Append the results. | |
var data = new Array(rows.length); | |
for (var i = 0; i < rows.length; i++) { | |
var cols = rows[i].f; | |
data[i] = new Array(cols.length); | |
for (var j = 0; j < cols.length; j++) { | |
data[i][j] = cols[j].v; | |
} | |
} | |
sheet.getRange(2, 1, rows.length, headers.length).setValues(data); | |
Logger.log('Results spreadsheet created: %s', | |
SpreadsheetApp.getActiveSpreadsheet().getUrl()); | |
} else { | |
Browser.msgBox('No data found for your request. Maybe you specified to many parameters.'); | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment