The Apps Script team released this post today about integrating BigQuery and sheets. So this means you can use SQL to access any sheets that you’ve linked to bigQuery. You can follow the instructions in the post for how to do that, but also see this for some cautions on this initial version.

I’m using the JSON API for BigQuery rather than the Apps Script advanced service, since I have centralized all my BigQuery datasets in one project, and want to use a Service Account for authorization – but more about that later.

How to access sheets via BigQuery.

Here’s a couple of sample queries to get you started.

They use this simple pattern

and here’s snippet of the results

So that’s all you need to do to open up the wonderful world of BigQuery SQL to your Sheets. Of course there is some setup needed, so lets look at that.

App settings

I always keep settings for an app in one namespace. Here’s what mine looks like. You’ll need to change the project name, datastore and table name to match your bigquery project.

Authorization.

As mentioned, my BigQuery project is a standalone project I use for all BigQuery work, so I need to do some simple Oauth2 preparation. I first need to create a function to run once using goa to manage a service account. This will create my credentials, which I’ve downloaded from the console project that holds my bigquery data. I’ve also created an API key in that project for billing. You don’t need to enable any special services in your Apps Script project, but of course you do need the Drive and BigQuery APIs enabled in your BigQuery project. Then you can run this to establish Goa in your Apps Script project. You’ll need to set the fileid of your service account credentials file and the API key you’ve created.

You’ll also need the Goa library.

Goa library is available with this key.

MZx5DzNPsYjVyZaR67xXJQai_d-phDA33

QueryUtils namespace

Next up I needed a couple of reusable functions through which to access BigQuery. I decided not to use the Apps Script advanced service, but instead to use the JSON REST API. I actually find it easier than the advanced service, it allows me better control over exponential backoff, and more importantly, I can use a service account pointing to a different project where I have all my cloud billing centralized.

There is actually only one exposed method in this namespace

It takes care of normalizing the paging, and turns the data returned from BigQuery into an array of JavaScript objects. Note in particular the use of exponential backoff while waiting for the query data to complete loading (this happens asynchronously). This avoids the unnecessary sleeping recommended in the developers guide. The lookahead function tells expBackoff about things that might happen that it’s worth retrying for. Aside from that, the code is fairly self explanatory.

The code for QueryUtils.

And that’s it. You’ll find the source code on Github as well as reproduced above.

For more like this see Google Apps Scripts Snippets
Why not join our forum, follow the blog or follow me on twitter to ensure you get updates when they are available.