Automate a Gsheet Bigquery extract

Question

I was looking for a way to automate the process of creating a extract from a big query table using a query. Would I use the gsheet app script or a python library? Is this possible?

Answer 1

Based on the comments and what could be accomplished, app script has an option to add "Advance Services" including the BigQuery API, you can review the guide and how it works over this BigQuery Service reference.

Sample code from the documentation:

/**
 * Runs a BigQuery query and logs the results in a spreadsheet.
 */
function runQuery() {
  // Replace this value with the project ID listed in the Google
  // Cloud Platform project.
  const projectId = 'XXXXXXXX';

  const request = {
    // TODO (developer) - Replace query with yours
    query: 'SELECT TOP(word, 300) AS word, COUNT(*) AS word_count ' +
      'FROM `publicdata.samples.shakespeare` WHERE LENGTH(word) > 10;',
    useLegacySql: false
  };
  let queryResults = BigQuery.Jobs.query(request, projectId);
  const jobId = queryResults.jobReference.jobId;

  // Check on status of the Query Job.
  let sleepTimeMs = 500;
  while (!queryResults.jobComplete) {
    Utilities.sleep(sleepTimeMs);
    sleepTimeMs *= 2;
    queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId);
  }

  // Get all the rows of results.
  let rows = queryResults.rows;
  while (queryResults.pageToken) {
    queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId, {
      pageToken: queryResults.pageToken
    });
    rows = rows.concat(queryResults.rows);
  }

  if (!rows) {
    Logger.log('No rows returned.');
    return;
  }
  const spreadsheet = SpreadsheetApp.create('BiqQuery Results');
  const sheet = spreadsheet.getActiveSheet();

  // Append the headers.
  const headers = queryResults.schema.fields.map(function(field) {
    return field.name;
  });
  sheet.appendRow(headers);

  // Append the results.
  var data = new Array(rows.length);
  for (let i = 0; i < rows.length; i++) {
    const cols = rows[i].f;
    data[i] = new Array(cols.length);
    for (let j = 0; j < cols.length; j++) {
      data[i][j] = cols[j].v;
    }
  }
  sheet.getRange(2, 1, rows.length, headers.length).setValues(data);

  Logger.log('Results spreadsheet created: %s',
      spreadsheet.getUrl());
}

There is also a way to utilize the feature from the comments, Connected Sheets. There is a step by guide on how to implement it. It should give you an insight or idea, on how to utilize the Big Query API or the Sheets with App Scripts.