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

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