Fetch data from BigQuery to Google Sheets. More: https://greenido.wordpress.com/2013/12/16/big-query-and-google-spreadsheet-intergration/

BigQuery2GSheets.js

/**
* 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);
};

Hi there, Thanks for posting this script. I'm keen to get it up and running but I'm getting this error message when I run it: "TypeError: Cannot read property "length" from undefined." - any ideas?

This is because you didn't change the projectID. See on lines 14/15 the explanation and then replace my string with your projectID on line 16.

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?

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

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.

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.

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.

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!

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 :)

Thank you for posting this script!

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.

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

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

you are a star

@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

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