Accessing BigQuery with Google Spreadsheet

google-apps-script, google-bigquery

Solution

UPDATE: We just added a new BigQuery + Apps Script Tutorial that should walk you through the answer to this question here: https://developers.google.com/apps-script/articles/bigquery_tutorial

@GQuery: We've very recently updated AppsScript to have access to the latest BigQuery API version (v2). Here's a simple example to get started, will display results in the AppScript log. We are working on an update to the AppScript/BigQuery documentation.

function runQuery() {
  var projectId = 'YOUR PROJECT';
  var sql = 'select word, word_count from publicdata:samples.shakespeare limit 100';
  var queryResults;

  // 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;
    }
  }

  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++) {
      rowString += cols[j].getV() + '\t';
    }
    Logger.log(rowString);

Problem

I look on the internet to see how can I import bigquery data inside google spreadsheet. I found this appscript sample, but it doesn'twork API are not at the same level, and I do not find how to query with API2 or API@beta1 in appscript. ``` function runQuery() { var ss = SpreadsheetApp.getActive(); var range = ss.getRangeByName('query'); var query = range.getCell(1, 1).getValue(); //var results = bigquery.query(query); var header = ss.getRangeByName('header'); header.clearContent(); var output = ss.getRangeByName('output'); output.clearContent(); for (var i = 0; i < results.fields.length; i++) { var field = results.fields[i]; header.getCell(1, 1 + i).setValue(field.id); } for (var i = 0; i < results.rows.length; i++) { var row = results.rows[i].f; for (var j = 0; j < row.length; ++j) { output.getCell(1 + i, 1 + j).setValue(row[j].v); } } } ``` Thanks in advance for your ideas, GQ

Original source