Google SpreadSheets Apps Scripts >>> run BigQuery to Sheet

runBigQuery.js

function runBigQuery(projectId, sql, sheetName) {
  var ss = SpreadsheetApp.getActive();
  var sheetData = ss.getSheetByName(sheetName);
  sheetData.clearContents();
  
  // Run the query
  try {
    queryResults = BigQuery.Jobs.query(projectId, sql);
  }
  catch (err) {
    Logger.log(err);
    return;
  }
  
  // Loop until successful job completion
  while (queryResults.getJobComplete() == false) {
    try {
      queryResults = BigQuery.Jobs.getQueryResults(projectId, queryResults.getJobReference().getJobId());
    }
    catch (err) {
      Logger.log(err);
      return;
    }
  }
  
  // Create Table Header
  var tableHeader = queryResults.getSchema();
  for (var header in tableHeader.getFields()) {
    sheetData.getRange(1, parseInt(header)+1).setValue(tableHeader.getFields()[header].getName());   
  }
  
  // Fill Table with Data
  var tableRows = queryResults.getRows();
  for (var i = 0; i < tableRows.length; i++) {
    var rowString = '';
    var cols = tableRows[i].getF();
    for (var j = 0; j < cols.length; j++) {
      sheetData.getRange(i+2, j+1).setValue(cols[j].getV());
    }
  }
}

@mgenov