Created
September 27, 2017 02:59
-
-
Save hlecuanda/d34895d1c356617d913c5259d900572f to your computer and use it in GitHub Desktop.
Get data from BigQuery into Google Sheets. Google Apps Scrpt
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 runQuery(querySelector) { | |
// Replace this value with the project ID listed in the Google | |
// Cloud Platform project. | |
var projectId = 'your-project-id-not-project-name'; | |
if (querySelector){ // be creative, construct your own dynamic query with parameters. or something | |
var request = { | |
query: 'SELECT * FROM project.dataset.table', | |
useLegacySql: false // set to true to use strange sql from the past | |
}; | |
} else { | |
var request = { | |
query: 'SELECT * FROM project.dataset.othertable', | |
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); | |
console.log('waiting...'); | |
} | |
// 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.log('getting results...'); | |
} | |
if (rows) { | |
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); | |
if (withCosts){ | |
var sheet = spreadsheet.getSheetByName("SheetWhereDataFromQuery1Goes"); | |
} else { | |
var sheet = spreadsheet.getSheetByName("SheetWhereDataFromQuery2Goes"); | |
}; | |
// 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, 2, rows.length, headers.length).setValues(data); | |
console.log('Results spreadsheet updated: %s', | |
spreadsheet.getUrl()); | |
} else { | |
console.log('No rows returned.'); | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment