Query BigQuery conveniently with an Apps Script library

If you use Google Apps Script a lot, and particularly for working with BigQuery, then you might be interested in this library: BigQuery Helper. Just include the library's key "MjmmwOcqTeIFoRwuA8KZV5MtILCH4r83t" in your Apps Script script and you're ready to go!

Why use my library?

What this library does is it saves you from writing all of the "plumbing" code to interact with BigQuery. You can then concentrate on writing your query and any other code for manipulating the queried data. Increase your productivity!

The Apps Script way – not productive!

Apps Script's developer documentation for BigQuery has some sample code for working with BigQuery. But it forces you to write this code:

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

No, no, no! That is just wrong! Why should you bother with:

• Figuring out how to construct a proper request to BigQuery,
• Polling BigQuery for the results, and
• Looping through page tokens to build the full results set?

BigQuery Helper library – one line of code

That's where my library comes in. All of the above is reduced to a single line:

var result = BigQueryHelper.runQuery(query, projectId);

BOOM! Done!

Your queried data is then available as an array of objects in result.rows, e.g.

[
  {
    title: 'File talk:Replace this image male.svg',
    id: 12437607,
    language: '',
    wp_namespace: 7,
    ...
  }
]

That's so much cleaner! You can now manipulate with the data as you wish.

Also, by using one key-value object per row, column fields and their values are kept together contextually. No need to worry about row headers and such!

What about Google Sheets add-ons?

While there are add-ons for Google Sheets that let you interact with BigQuery without writing any code, sometimes, you need to write your own custom script without needing a spreadsheet intermediary.

For example, I have a script to insert new rows into a table every day from a third-party data source. I don't need a Google Sheet to do that, so any add-ons there are useless to me.

Recipe for using BigQuery Helper in Apps Script

1. Include the library: MjmmwOcqTeIFoRwuA8KZV5MtILCH4r83t
2. Write your query.
3. Get your query's results with BigQueryHelper.runQuery(query, projectId).

For more information on BigQuery Helper, visit my Github page.