Skip to content

Instantly share code, notes, and snippets.

@goodpic
Last active January 22, 2019 14:52
Show Gist options
  • Save goodpic/2fbfc795fff1e1180e90ea5811bb22b6 to your computer and use it in GitHub Desktop.
Save goodpic/2fbfc795fff1e1180e90ea5811bb22b6 to your computer and use it in GitHub Desktop.
// [START apps_script_bigquery_update_sheet]
/**
* Runs a BigQuery query and replace the existing sheet
*/
/**
* Add a custom menu to the spreadsheet when it is opened.
*/
function onOpen() {
var spreadsheet = SpreadsheetApp.getActive();
var menuItems = [
{name: 'Update data', functionName: 'updateData'}
];
spreadsheet.addMenu('BigQuery', menuItems);
}
function updateData() {
// Need to provoke a drive dialog
// DriveApp.getFiles()
// Replace this value with your project ID and the name of the sheet to update.
var projectId = 'jcstockprd';
var sheetName = 'latest';
// Use standard SQL to query BigQuery
var request = {
query: 'SELECT * FROM jcstockprd.views.current_stock_and_sales_by_jan ORDER BY net_last_30_days DESC;',
useLegacySql: false
};
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);
}
console.info('%d rows found.', rows.length);
if (rows) {
// 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;
}
}
var ss = SpreadsheetApp.getActiveSpreadsheet();
var currentSheet = ss.getSheetByName(sheetName);
if (currentSheet === null) {
currentSheet = ss.insertSheet(99);
ss.renameActiveSheet(sheetName);
} else {
currentSheet.clear();
}
currentSheet.getRange(1, 1, rows.length, data[0].length).setValues(data);
console.info('%d rows inserted.', rows.length);
} else {
console.info('No results found in BigQuery');
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment