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);
};
@victorbergelin
Copy link

When I run the code, it says:
"Exception: Access Not Configured. The API (BigQuery API) is not enabled for your project. Please use the Google Developers Console to update your configuration."

This is enabled for the project specified with projectNumber. Any ideas what could be causing this?

@micheleorsi
Copy link

Regarding the last problem I think you should enable BigQuery API in your Google Developers Console.
https://console.cloud.google.com/apis/api/bigquery/overview

@dudeanurag
Copy link

Hi, The BigQuery API is enabled in my developer console. But still I am getting the same problem "Access not Configured. BigQuery API has not been used in project 266315118773 before or it is disabled". Any idea what is the reason I am getting this.

@ashleythomas1988
Copy link

ashleythomas1988 commented Oct 26, 2017

Hi, everyone,
In the line :
sheet.getRange(1, 1, resultCount, tableRows[0].getF().length).setValues(resultValues);

why are we using resultCount instead of tableRows.length ? I have a code which is giving me different values for resultCount and tableRows.length.

@ashleythomas1988
Copy link

I think I figured the difference from here

public java.util.List getRows()
An object with as many results as can be contained within the maximum permitted reply size. To get any additional rows, you can call GetQueryResults and specify the jobReference returned above.

@tcfredericgilbert
Copy link

tcfredericgilbert commented Oct 30, 2017

Great script indeed once I figured how to enable permissions between BQ projects...
Question: How to programmatically display the table's schema as column's headers in Row[0] above the data (starting at Row[1])?
Thx for sharing this script!

@alig114
Copy link

alig114 commented Mar 16, 2018

Hey, I know this was posted a while ago but it's exactly what i need to do, and its not working for me. For some reason, every time i run this script its telling me that the project doesn't exist but its' referencing a project that isn't even in my script. Has anyone ever had this issue? I changed the project ID in this script (the beginning) to my project ID (which i know is correct), but again, everytime i run it it says :

GoogleJsonResponseException: Project XXXXXXXXXX is not found and cannot be used for API calls. If it is recently created, enable BigQuery API by visiting https://console.developers.google.com/apis/api/bigquery-json.googleapis.com/overview?project=**XXXXXXXXXXX** then retry. If you enabled this API recently, wait a few minutes for the action to propagate to our systems and retry.

But this isnt even the project number in the script. Any ideas guys? Would be greatly appreciated :)

@houn
Copy link

houn commented Apr 4, 2018

Thank you for posting this script!

@LysianeC
Copy link

LysianeC commented May 10, 2018

Thanks for posting this! How would you modify this script to use 'Standard SQL' instead of 'Legacy' ?

Edit: I found the answer elsewhere. I simply added .setUseLegacySql(false) after the setTimeoutMs.

@dixitshubhashish
Copy link

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

@piavgh
Copy link

piavgh commented Sep 23, 2019

@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?

@voeller1984-zz
Copy link

you are a star

@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