BigQuery Tutorial Code Snip 1

// Replace the following with your Google Developer Console project number
var projectNumber = 'xxxxxxxxxxxx';

var ss = SpreadsheetApp.getActiveSpreadsheet();

function onOpen() {
  var menuEntries = [ {name: 'Run Query', functionName: 'runQuery'} ];
  ss.addMenu('BigQuery', menuEntries);
}

function runQuery() {
  var sheet = SpreadsheetApp.getActiveSheet();

  var sql = 'select TOP(word, 10), COUNT(*) as word_count from publicdata:samples.shakespeare WHERE LENGTH(word) > 12;';

  var queryResults;

  // Inserts a Query Job with an optional timeoutMs parameter.
  try {
    queryResults = BigQuery.Jobs.query(projectNumber, sql, {'timeoutMs':10000});
  }
  catch (err) {
    Logger.log(err);
    Browser.msgBox(err);
    return;
  }

  // If the query results are not complete when the optional timeout is reached,
  // periodically poll the BigQuery API for the results manually using the query job id.
  while (queryResults.getJobComplete() == false) {
    try {
      queryResults = BigQuery.Jobs.getQueryResults(projectNumber, queryResults.getJobReference().getJobId());
      // If the Job is still not complete, sleep script for
      // 3 seconds before checking result status again
      if (queryResults.getJobComplete() == false) {
        Utilities.sleep(3000);
      }
    }
    catch (err) {
      Logger.log(err);
      Browser.msgBox(err);
      return;
    }
  }

  // Update the amount of results
  var resultCount = queryResults.getTotalRows();
  var resultSchema = queryResults.getSchema();
}