Using sheets via Bigquery from Apps Script

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.
function getAllData() {
  
  var ag = App.globals.bigQuery;
  Logger.log(doQuery_ ( 'SELECT * FROM' + 
    ' [' + ag.dataStore + '.' + ag.table + '] LIMIT 10'));
}

function getSomeQuery() {
  
  var ag = App.globals.bigQuery;
  Logger.log(doQuery_ ( 'SELECT * FROM' + 
    ' [' + ag.dataStore + '.' + ag.table + '] WHERE iso_country="CA"'));
}

They use this simple pattern
function doQuery_ (sqlString) {
  
  App.init();
  var ag = App.globals.bigQuery;
  return QueryUtils.query (
    App.goa.getProperty("apiKey"), 
    App.goa.getToken(),
    ag.projectId,
    sqlString);

}

and here's snippet of the results 

[{
"name": "Port Moresby Jacksons International Airport",
"latitude_deg": "-9.443380356",
"longitude_deg": "147.2200012",
"elevation_ft": "146",
"iso_country": "PG",
"iso_region": "PG-NCD",
"municipality": "Port Moresby",
"iata_code": "POM"
}, {
"name": "Keflavik International Airport",
"latitude_deg": "63.98500061",
"longitude_deg": "-22.60560036",
"elevation_ft": "171",
"iso_country": "IS",
"iso_region": "IS-2",
"municipality": "Reykjavik",
"iata_code": "KEF"
}, {
.......etc
[{
"name": "Port Moresby Jacksons International Airport",
"latitude_deg": "-9.443380356",
"longitude_deg": "147.2200012",
"elevation_ft": "146",
"iso_country": "PG",
"iso_region": "PG-NCD",
"municipality": "Port Moresby",
"iata_code": "POM"
}, {
"name": "Keflavik International Airport",
"latitude_deg": "63.98500061",
"longitude_deg": "-22.60560036",
"elevation_ft": "171",
"iso_country": "IS",
"iso_region": "IS-2",
"municipality": "Reykjavik",
"iata_code": "KEF"
}, .... etc


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.
/**
* used to control the App functions
* and hold globals values
* @namespace App
*/
var App = (function (ns) {
  
  ns.globals = {
    bigQuery: {
      projectId:'lateral-command-416',
      dataStore:'airports', 
      table:'details' 
    },
    oauth: {
      packageName:'bigquerysheets',
      props:PropertiesService.getScriptProperties()
    }
  };
  
  ns.init = function () {
    ns.goa = cGoa.GoaApp.createGoa(
      ns.globals.oauth.packageName, 
      ns.globals.oauth.props
    ).execute();
    return ns;
  };
  
  return ns;
}) (App || {});

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.


// use  a service account to access my bigquery
function oneOffSetting() { 
  // provoke drive auth DriveApp.getFiles()
  cGoa.GoaApp.setPackage (App.globals.oauth.props , 
   cGoa.GoaApp.createServiceAccount (DriveApp , {
     packageName: App.globals.oauth.packageName,
     fileId:'0B92ExLh4POiZSnRPNmkwX0N6SGM',
     scopes : cGoa.GoaApp.scopesGoogleExpand (['cloud-platform.read-only','bigquery','drive']),
     service:'google_service',
     apiKey:'AIzcxxxxxxxxxxxxxxxxHB8'
   }));
}

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
QueryUtils.query (apiKey , accessToken , preojectId , sqlString);

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.
    var queryJob = cUseful.Utils.expBackoff ( function () {
      return bigQueryFetch_ ( apiKey, accessToken,  projectId, sqlString);
    }, {
      lookahead:function(response , attempt) {
        return !response.jobComplete;
      }
    });

The code for QueryUtils.

/**
*@namespace QueryUtils
*runs server side and handles all database querying
*/
var QueryUtils = (function (ns) {
  
  /**
  * do a query
  * @param {string} apiKey for billing - apikey should belong to owner fo bigquery project
  * @param {string} accessToken this is a service token to allow this execution api to run as owner of bigquery project
  * @param {string} projectId the project id
  * @param {string} sqlString the string
  * @return {[object]} the results as array of javacript objects
  */
  ns.query = function (apiKey, accessToken, projectId , sqlString) {
    
    
    // we can use exp backoff to get results of job
    var queryJob = cUseful.Utils.expBackoff ( function () {
      return bigQueryFetch_ ( apiKey, accessToken,  projectId, sqlString);
    }, {
      lookahead:function(response , attempt) {
        return !response.jobComplete;
      }
    });
    
    // now retrieve the result
    var result = nextQueryResults_(apiKey , accessToken , queryJob);
    
    // Get all the rows of results.
    var rows = result.rows;
    while (result.pageToken) {
      result = nextQueryResults_ (apiKey, accessToken, result);
      Array.protoype.push.apply (rows,result.rows);
    }
    
    // the headers
    var headers = result.schema.fields.map(function(field) {
      return field.name;
    });
    
    // the rows to an array of obs
    return (rows || []).map(function (d) {
      var idx = 0;
      return headers.reduce(function (p,c) {
        p[c] = d.f[idx].v;
        idx++;
        return p;
      },{});
    });
    
  };
  
  /**
  * take care of returning the pageifed data frm a query
  * @param {string} apiKey for billing - apikey should belong to owner fo bigquery project
  * @param {string} accessToken this is a service token to allow this execution api to run as owner of bigquery project
  * @param {object} queryResult the query result resource
  * @param {object} the next set of results
  */
  function nextQueryResults_ (apiKey , accessToken,  queryResult ) {
    
    var response = cUseful.Utils.expBackoff ( function () {
      return UrlFetchApp.fetch (
        "https://www.googleapis.com/bigquery/v2/projects/" + 
        queryResult.jobReference.projectId + 
        "/queries/" + 
        queryResult.jobReference.jobId + 
        "?apiKey=" + apiKey + (queryResult.pageToken ? 
        ("&pageToken=" + queryResult.pageToken) : ''),  {
        headers:{
          Authorization:"Bearer " + accessToken
        },
          method:"GET"  
      });
    });
    return JSON.parse(response.getContentText());
  }; 
  
  /**
  * do initial query
  * @param {string} apiKey for billing - apikey should belong to owner fo bigquery project
  * @param {string} accessToken this is a service token to allow this execution api to run as owner of bigquery project
  * @param {string} projectId the project id
  * @param {string} sqlString the string
  */
  function bigQueryFetch_ (apiKey, accessToken, projectId, sqlString) {
    
    var body = {
      "kind": "bigquery#queryRequest",
      "query": sqlString
    };
    
    var result = cUseful.Utils.expBackoff (function () {
      return UrlFetchApp.fetch ( 
        "https://www.googleapis.com/bigquery/v2/projects/" + projectId + "/queries?apiKey=" + apiKey , {
          method:"POST",
          headers:{
            Authorization:"Bearer " + accessToken
          },
          payload:JSON.stringify(body),
          contentType:"application/json"
       });
    });
  
    return JSON.parse(result.getContentText());
  
   };
                  
   return ns;
}) (QueryUtils || {});

And that's it. You'll find the source code on Github as well as reproduced above.
The gadget spec URL could not be found

You want to learn Google Apps Script?

Learning Apps Script, (and transitioning from VBA) are covered comprehensively in my my book, Going Gas - from VBA to Apps script, available All formats are available now from O'Reilly,Amazon and all good bookshops. You can also read a preview on O'Reilly

If you prefer Video style learning I also have two courses available. also published by O'Reilly.
Google Apps Script for Developers and Google Apps Script for Beginners.





The code

You'll find the code on github 


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




Comments