Google Apps Script

Tutorial: Update Spreadsheets with data from Google BigQuery

Michael Manoochehri, Google BigQuery Team
July 2012

Goal

This tutorial shows how to run, display, and visualize query results from Google BigQuery using Google Sheets.

Time to Complete

Approximately 20 minutes.

Overview

You can access the Google BigQuery API from Apps Script to do things like:

  • Run queries over BigQuery datasets and display the results in Google Sheets
  • Visualize query results in a spreadsheet using the Google Charts API.
  • Build interactive or automated data analysis tools.

The complete list of functions can be found in the reference documentation.

In this tutorial we are going to see how to:

Section 1: Activate the Google BigQuery service in Apps Script

  1. Create a new spreadsheet.
  2. Click Tools > Script Editor... to open the script editor.
  3. Click Resources > Advanced Google services....
  4. In the dialog that appears, click the on/off switch next to the BigQuery API (v2).
  5. At the bottom of the dialog, click the link for the Google Developers Console.
  6. In the new console, again click the on/off switch next to the BigQuery API.
  7. Click Overview in the console's left-side navigation. Note the project number shown at the top of the Overview page. We will use this project number to make calls to the BigQuery API.
  8. Return to the script editor and click OK in the dialog. BigQuery will now be available in autocomplete.

Section 2: Insert a BigQuery query job and retrieve query results

Running queries with BigQuery is an asynchronous operation. First, a query job is inserted, returning a job id. The job id can then be used to poll for the status of the job. If complete, the query results can be retrieved using the job id.

As a convenience, the BigQuery.Jobs.query method will combine all of these steps into a single API call. Once the query job is complete, the first page of results will be included in the queryResults object. An optional timeoutMs parameter may be included in the call to BigQuery.Jobs.query. If the timeout is reached before the query is complete, the job id can be used to poll manually for the result.

In the code below, the onOpen function adds a BigQuery->Run Query option to the Sheets menu bar. Clicking this option calls the runQuery function, which starts a query job and retrieves the result upon completion. If the query has not completed before the optional timeout is reached, the script enters a polling loop which returns the query result once the job is complete.

  1. Copy and paste the following code into the Script Editor.
  2. // Replace the following with your Google Developer Console project number
    var projectNumber = 'XXXXXXXX';
    
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    
    function onOpen() {
      var menuEntries = [ {name: 'Run Query', functionName: 'runQuery'} ];
      ss.addMenu('BigQuery', menuEntries);
    }
    
    function runQuery() {
      var sheet = SpreadsheetApp.getActiveSheet();
    
      var sql = 'select TOP(word, 10), COUNT(*) as word_count from publicdata:samples.shakespeare WHERE LENGTH(word) > 12;';
    
      var queryResults;
    
      // Inserts a Query Job with an optional timeoutMs parameter.
      try {
        queryResults = BigQuery.Jobs.query(projectNumber, sql, {'timeoutMs':10000});
      }
      catch (err) {
        Logger.log(err);
        Browser.msgBox(err);
        return;
      }
    
      // If the query results are not complete when the optional timeout is reached,
      // periodically poll the BigQuery API for the results manually using the query job id.
      while (queryResults.getJobComplete() == false) {
        try {
          queryResults = BigQuery.Jobs.getQueryResults(projectNumber, queryResults.getJobReference().getJobId());
          // If the Job is still not complete, sleep script for
          // 3 seconds before checking result status again
          if (queryResults.getJobComplete() == false) {
            Utilities.sleep(3000);
          }
        }
        catch (err) {
          Logger.log(err);
          Browser.msgBox(err);
          return;
        }
      }
    
      // Update the amount of results
      var resultCount = queryResults.getTotalRows();
      var resultSchema = queryResults.getSchema();
    }
    
  3. Click the Save icon.
  4. Reload your current spreadsheet. A new menu item called BigQuery will be available.
  5. Click on BigQuery > Run Query to run the query.

Section 3: Update the spreadsheet with query result data

Using the dataTable object returned from the function in the previous section, we can now populate cells in the spreadsheet with query result data.

  1. Copy and paste the following code into the Script Editor, at the end of the runQuery function.
  2.   var resultValues = new Array(resultCount);
      var tableRows = queryResults.getRows();
    
      // Iterate through query results
      for (var i = 0; i < tableRows.length; i++) {
        var cols = tableRows[i].getF();
        resultValues[i] = new Array(cols.length);
        // For each column, add values to the result array
        for (var j = 0; j < cols.length; j++) {
          resultValues[i][j] = cols[j].getV();
        }
      }
    
      // Update the spreadsheet with data from the resultValues array
      sheet.getRange(1, 1, resultCount, tableRows[0].getF().length).setValues(resultValues);
    
  3. Click the Save icon.
  4. In your spreadsheet, click on BigQuery > Run Query to run the query.
  5. Results from the query will populate the current spreadsheet beginning from cell A1.

Section 4: Visualize BigQuery results in a chart

Depending on the query, result data from BigQuery may contain columns of different data types. As of the date this tutorial was published, BigQuery supports four data types: Integer, Float, Boolean, and String. In order to populate spreadsheets with visualizations from the Apps Script Chart Services, we must represent query result data as Data Table objects, which require one of three Charts.ColumnType data types - "STRING," "NUMBER," or "DATE."

First, we will create a helper function called buildDataTable which builds an Apps Script Data Table from a BigQuery query result data and schema. This function will place numeric query result data (Integer and Float) into a ColumnType of "NUMBER." Boolean or String data will be placed in a ColumnType of "STRING." Next, we will use the resulting Data Table to populate and display a Bar Chart.

  1. Click Tools > Script Editor... to open the script editor.
  2. Copy and paste the following code into the editor.
  3. // Returns a Data Table from a query's result values and schema
    function buildDataTable(resultValues, resultSchema) {
    
      var dataTable = Charts.newDataTable();
      for (var i = 0; i < resultSchema.getFields().length; i++) {
        column = resultSchema.getFields()[i];
        columnName = column.getName();
        columnType = (column.getType() == 'INTEGER' || column.getType() == 'FLOAT')
                      ? Charts.ColumnType.NUMBER : Charts.ColumnType.STRING;
    
        dataTable.addColumn(columnType, columnName);
      }
    
      for (var j = 0; j < resultValues.length; j++) {
       dataTable.addRow(resultValues[j]);
      }
    
      dataTable.build();
      return dataTable;
    }
    
  4. Click the Save icon.

Now that we have defined our buildDataTable function, we can use it to build a Bar Chart.

  1. Click Tools > Script Editor... to open the script editor.
  2. Copy and paste the following code into the editor, at the end of the runQuery function.
  3.   var bigqueryDataTable = buildDataTable(resultValues, resultSchema);
      var chart = Charts.newBarChart()
        .setDataTable(bigqueryDataTable)
        .setColors(['green'])
        .setDimensions(500, 500)
        .setYAxisTitle('Word')
        .setXAxisTitle('Count')
        .setLegendPosition(Charts.Position.BOTTOM)
        .build();
    
      var chart_panel = UiApp.createApplication()
        .setTitle('BigQuery Results')
        .setWidth('600')
        .setHeight('600');
      chart_panel.add(chart)
      ss.show(chart_panel);
    
  4. Click the Save icon.
  5. In your spreadsheet, click on BigQuery > Run Query to run the query.
  6. After the query is run, a bar chart of your query results will appear.

Section 5: Schedule daily query result updates

A common use of BigQuery is to generate automatically daily reports from query data. Apps Script provides time-based triggers, which can be used to automatically run functions at defined intervals. The following steps will set the runQuery function to run once per day, automatically updating the spreadsheet with the latest query results.

  1. From the Script Editor, choose Resources > Current project's triggers.
  2. Click the link that says Add a new trigger.
  3. Under Run, select the runQuery function.
  4. Under Events, select Time-driven.
  5. On the first drop-down list that appears, select Day timer.
  6. Select the hour interval during which the script will be executed.
  7. Click Save.

Complete source code for this tutorial

// Replace the following with your Google Developer Console project number
var projectNumber = 'XXXXXXXX';

var ss = SpreadsheetApp.getActiveSpreadsheet();

function onOpen() {
  var menuEntries = [ {name: 'Run Query', functionName: 'runQuery'} ];
  ss.addMenu('BigQuery', menuEntries);
}

function runQuery() {
  var sheet = SpreadsheetApp.getActiveSheet();

  var sql = 'select TOP(word, 10), COUNT(*) as word_count from publicdata:samples.shakespeare WHERE LENGTH(word) > 12;';

  var queryResults;

  // Inserts a Query Job with an optional timeoutMs parameter.
  try {
    queryResults = BigQuery.Jobs.query(projectNumber, sql, {'timeoutMs':10000});
  }
  catch (err) {
    Logger.log(err);
    Browser.msgBox(err);
    return;
  }

  // If the query results are not complete when the optional timeout is reached,
  // periodically poll the BigQuery API for the results manually using the query job id.
  while (queryResults.getJobComplete() == false) {
    try {
      queryResults = BigQuery.Jobs.getQueryResults(projectNumber, queryResults.getJobReference().getJobId());
      // If the Job is still not complete, sleep script for
      // 3 seconds before checking result status again
      if (queryResults.getJobComplete() == false) {
        Utilities.sleep(3000);
      }
    }
    catch (err) {
      Logger.log(err);
      Browser.msgBox(err);
      return;
    }
  }

  // Update the amount of results
  var resultCount = queryResults.getTotalRows();
  var resultSchema = queryResults.getSchema();

  var resultValues = new Array(resultCount);
  var tableRows = queryResults.getRows();

  // Iterate through query results
  for (var i = 0; i < tableRows.length; i++) {
    var cols = tableRows[i].getF();
    resultValues[i] = new Array(cols.length);
    // For each column, add values to the result array
    for (var j = 0; j < cols.length; j++) {
      resultValues[i][j] = cols[j].getV();
    }
  }

  // Update the spreadsheet with data from the resultValues array
  sheet.getRange(1, 1, resultCount, tableRows[0].getF().length).setValues(resultValues);

  // Create a new Bar Chart object from BigQuery query data, add to current spreadsheet
  var bigqueryDataTable = buildDataTable(resultValues, resultSchema);
  var chart = Charts.newBarChart()
    .setDataTable(bigqueryDataTable)
    .setColors(['green'])
    .setDimensions(500, 500)
    .setYAxisTitle('Word')
    .setXAxisTitle('Count')
    .setLegendPosition(Charts.Position.BOTTOM)
    .build();

  var chart_panel = UiApp.createApplication()
    .setTitle('Frequency of words longer than 12 characters in the works of Shakespeare')
    .setWidth('600')
    .setHeight('600');
  chart_panel.add(chart);
  ss.show(chart_panel);
}

// Returns a Data Table created from a BigQuery query result data and schema
function buildDataTable(resultValues, resultSchema) {

  var dataTable = Charts.newDataTable();
  for (var i = 0; i < resultSchema.getFields().length; i++) {
    column = resultSchema.getFields()[i];
    columnName = column.getName();
    columnType = (column.getType() == 'INTEGER' || column.getType() == 'FLOAT')
                  ? Charts.ColumnType.NUMBER : Charts.ColumnType.STRING;
    dataTable.addColumn(columnType, columnName);
  }

  for (var j = 0; j < resultValues.length; j++) {
   dataTable.addRow(resultValues[j]);
  }

  dataTable.build();
  return dataTable;
}

Authentication required

You need to be signed in with Google+ to do that.

Signing you in...

Google Developers needs your permission to do that.