CODEWITHSUNDEEP
google-bigquerygoogle-sheets-api
Andy Leung
Andy Leung

Reputation: 11

BigQuery Result save as google sheets through api

In Google BigQuery WebUI, it shows query result screen after executing a query, and it shows the button of "Save as Google Sheets". I like this feature but would like to automate this, is there such function through the REST API that I could do?

Upvotes: 1

Views: 1107

Answers (1)

VictorGGl
VictorGGl

Reputation: 1916

It doesn’t seem like there is a straightforward way to do this directly with the BigQuery API. There are few workarounds for this though:

  1. You can use the BigQuery API to query your data and then the GoogleSheets API to upload it to Google Sheets.

  2. You can use Google Apps Script. If you go to this link, you click on “New Script”, you can run the code below. You can adapt this to your needs. You can also add a trigger to run the script every hour/minute …

Here the code snippet from this link:

function runQuery() {
  // Replace this value with the project ID listed in the Google
  // Cloud Platform project.
  var projectId = 'XXXXXXXX';

  var request = {
    query: 'SELECT TOP(word, 300) AS word, COUNT(*) AS word_count ' +
      'FROM publicdata:samples.shakespeare WHERE LENGTH(word) > 10;'
  };
  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);
  }

  if (rows) {
    var spreadsheet = SpreadsheetApp.create('BiqQuery Results');
    var sheet = spreadsheet.getActiveSheet();

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

    // 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;
      }
    }
    sheet.getRange(2, 1, rows.length, headers.length).setValues(data);

    Logger.log('Results spreadsheet created: %s',
        spreadsheet.getUrl());
  } else {
    Logger.log('No rows returned.');
  }
}

Upvotes: 3

Related Questions