BigQuery_to_Google_sheet.js

// [START apps_script_bigquery_update_sheet]
/**
 * Runs a BigQuery query and replace the existing sheet
 */

/**
 * Add a custom menu to the spreadsheet when it is opened.
 */
function onOpen() {
  var spreadsheet = SpreadsheetApp.getActive();
  var menuItems = [
    {name: 'Update data', functionName: 'updateData'}
  ];
  spreadsheet.addMenu('BigQuery', menuItems);
}

function updateData() {
  // Need to provoke a drive dialog
  // DriveApp.getFiles()
  
  // Replace this value with your project ID and the name of the sheet to update.
  var projectId = 'jcstockprd';
  var sheetName = 'latest';

  // Use standard SQL to query BigQuery
  var request = {
    query: 'SELECT * FROM jcstockprd.views.current_stock_and_sales_by_jan ORDER BY net_last_30_days DESC;',
    useLegacySql: false
  };
  var queryResults = BigQuery.Jobs.query(request, projectId);
  var jobId = queryResults.jobReference.jobId;

  // Check on status of the Query Job.
  var sleepTimeMs = 500;
  while (!queryResults.jobComplete) {
    Utilities.sleep(sleepTimeMs);
    sleepTimeMs *= 2;
    queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId);
  }
  
  // Get all the rows of results.
  var rows = queryResults.rows;
  while (queryResults.pageToken) {
    queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId, {
      pageToken: queryResults.pageToken
    });
    rows = rows.concat(queryResults.rows);
  }
  console.info('%d rows found.', rows.length);
  
  if (rows) {
    // Append the results.
    var data = new Array(rows.length);
    for (var i = 0; i < rows.length; i++) {
      var cols = rows[i].f;
      data[i] = new Array(cols.length);
      for (var j = 0; j < cols.length; j++) {
        data[i][j] = cols[j].v;
      }
    }
    
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var currentSheet = ss.getSheetByName(sheetName);
    if (currentSheet === null) {
      currentSheet = ss.insertSheet(99);
      ss.renameActiveSheet(sheetName);
    } else {
      currentSheet.clear();
    }
    currentSheet.getRange(1, 1, rows.length, data[0].length).setValues(data);
    
    console.info('%d rows inserted.', rows.length);
  } else {
    console.info('No results found in BigQuery');
  }
}