Skip to content

Instantly share code, notes, and snippets.

@greenido
Last active May 28, 2022 22:48
Show Gist options
  • Save greenido/7984116 to your computer and use it in GitHub Desktop.
Save greenido/7984116 to your computer and use it in GitHub Desktop.
/**
* Fetching data from BigQuery and present it in our sheet
* Author: Ido Green
* Date: 14/12/2013
*
* See: https://greenido.wordpress.com/2013/12/16/big-query-and-google-spreadsheet-intergration/
* Misc: https://developers.google.com/bigquery/
*/
//
// Build and run the query: Get the top 30 longest works of Shakespeare
//
function runQuery() {
// Replace this value with your Google Developer project number (It is really a number.
// Don't confuse it with an alphanumeric project id)
var projectNumber = 'Put your project number here';
if (projectNumber.length < 1) {
var errMsg = "You forgot to set a project number - So no BQ for you!";
Logger.log(errMsg);
Browser.msgBox(errMsg);
return;
}
var sheet = SpreadsheetApp.getActiveSheet();
var sql = 'select TOP(word, 30), COUNT(*) as word_count from publicdata:samples.shakespeare WHERE LENGTH(word) > 10;';
var queryResults;
// Inserts a Query Job
try {
var queryRequest = BigQuery.newQueryRequest();
queryRequest.setQuery(sql).setTimeoutMs(100000);
queryResults = BigQuery.Jobs.query(queryRequest, projectNumber);
}
catch (err) {
Logger.log(err);
Browser.msgBox(err);
return;
}
// Check on status of the Query Job
while (queryResults.getJobComplete() == false) {
try {
queryResults = BigQuery.Jobs.getQueryResults(projectNumber, queryJob.getJobReference().getJobId());
}
catch (err) {
Logger.log(err);
Browser.msgBox(err);
return;
}
}
// Update the amount of results
var resultCount = queryResults.getTotalRows();
var resultSchema = queryResults.getSchema();
var resultValues = new Array(resultCount);
var tableRows = queryResults.getRows();
// Iterate through query results
for (var i = 0; i < tableRows.length; i++) {
var cols = tableRows[i].getF();
resultValues[i] = new Array(cols.length);
// For each column, add values to the result array
for (var j = 0; j < cols.length; j++) {
resultValues[i][j] = cols[j].getV();
}
}
// Update the Spreadsheet with data from the resultValues array, starting from cell A1
sheet.getRange(1, 1, resultCount, tableRows[0].getF().length).setValues(resultValues);
Browser.msgBox("Yo yo! We are done with updating the results");
}
//
// Insert our customize menu item
//
function onOpen() {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var menuEntries = [ {name: 'Run Query', functionName: 'runQuery'} ];
sheet.addMenu('BigQuery Example', menuEntries);
};
@tpetrzilkaCD
Copy link

@greenido: You used BigQuery.Jobs.getQueryResults directly without setting up the service account credentials. How was it possible? I mean, in order to give access to the Apps Script project to the BigQuery, there must be a service account configuration, right?

I had to add Bigquery as extra service in Appscript editor - after adding it will ask you for your credentials to use when accessing Bigquery data.. after that the SQL is results are returned without issues..
I was following the script in this article> https://developers.google.com/apps-script/advanced/bigquery

@tpetrzilkaCD
Copy link

How to get header, can anyone please help. I am not getting header though record is ready

// Append the headers.
const headers = queryResults.schema.fields.map(function(field) {
return field.name;
});
sheet.appendRow(headers);

see> https://developers.google.com/apps-script/advanced/bigquery

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment