Last active
September 2, 2016 10:04
-
-
Save peihsinsu/20142b912b931295075eaddc8dacafdf to your computer and use it in GitHub Desktop.
BigQuery sample for Apps Script
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() { | |
| var projectId = 'sunny-573'; | |
| var request = { | |
| query: | |
| "SELECT FORMAT_UTC_USEC(metadata.timestamp) as time, structPayload.rpitemp as pi_temp " + | |
| "FROM [sunny-573:linkerlab.rpitemp_simon_project_LYDvUZWJJtOPxRERAAAC_20160902@-3600] " + | |
| "WHERE metadata.labels.value = 'linker-log-gateway' ORDER BY structPayload.ctime desc LIMIT 3000" | |
| }; | |
| 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); | |
| } | |
| if (rows) { | |
| var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); | |
| var sheet = spreadsheet.getActiveSheet(); | |
| // 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', | |
| spreadsheet.getUrl()); | |
| } else { | |
| Logger.log('No rows returned.'); | |
| } | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment