How to easily use BigQuery's Querys & Charts with Google Apps Script! (and our FREE library)

by +Cesar Anton 

@reicek

BigQuery's ability to digest billions of rows, trillions of records stored in a 1Tb Database in seconds sounds really appealing for massive data-analysis! Using it you can, for example, digest all the sales in the last 20 years of a super market and make a chart with the evolution of the sales of the different brands of cereal inside such period... all of them or just a single one... Put it all inside some graphs and send them attached as a PDF report via Gmail....now that kind of power screams "Google" right? and hopefully might draw a smile on some of your client's face.

Google Apps Script certainly facilitates the use of Big Query! but still we need to get (and wait) for the results and iterate through them, so we (the team at MyWorkers) decided to create a library to facilitate working with BigQuery. So far it helps you with the 3 basic things you need to get started:

• Sends your query, wait for and retrieve the results. 
• Iterate through the results. 
• Build a data table that can be drawn by any appropriate Google Chart. 

This functions are called and works independently, so you can make reuse them many times in your codes to save you a lot of time coding and debugging on the data handing so you can focus more on your charts and the rest of your super cool WebApp or automatic report.

This makes using Big Query VERY easy and straight forward! But before you can start using it, you need to make sure you have the following:



First you need to have (or create) a project in the Google APIS console and take note of the project number.



Also (if you haven't already) you need to enable the Big Query service in the Services tag of the Google APIS console.

To use BigQuery, you also need, well... a query. BigQuery queries are written using a variation of the standard SQL SELECT statement, you can read more details about how to make them in this Query Reference and/or using this Query Cook-book. We will be using one of the example query's (and free public tables) that Google provides:

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

Now you are ready to start using BigQuery via Google Apps Script! Let's check it out:



* Create a new script at script.google.com (let's call it bigQueryToCharts) and go to the Resources menu and click the Use Google APIs tag. From there, turn on the Big Query API.



* Then go to Manage libraries (also in the Resources menu) and:

1. Go to "Find library:" and type: ME6_s9CBqVzsOfhZryNM0vzfIqMXwq4Jm .
2. Click Select.
3. In Version click  8 getQueryResults function added.
4. Turn on development mode for setupBigQueryResults.
   

It's time to start putting some code, first lets put what we know inside some variables:

var projectNumber = '<ID>'; // The ID of your project.
var sql = 'select TOP(word, 10), COUNT(*) as word_count from publicdata:samples.shakespeare;';

IMPORTANT NOTE: I suggest using the same names for the variables, it's not necessary  but  this way the auto-complete of Apps Script will fill the names of the variables needed by the library when we call it.

Now let's use the library (setupBigQueryResults) to send our SQL to BigQuery, wait for the results and deliver them back, it will offer you options to autocomplete, choose getQueryResults and the line should look as follow:

 var queryResults = setupBigQueryResults.getQueryResults(projectNumber, sql);

To continue, we need to know 2 things: the resultCount (to iterate results) and the resultSchema to build our charts data table, both values are passed by setupBigQueryResults inside queryResults, to get them type as follows:

var resultCount = queryResults.getTotalRows();
var resultSquema = queryResults.getSchema();

Now lets hand those values back to our library, it's done this way to give more flexibility on the use of the library, (E.g. when processing multiple instances.

var resultValues = setupBigQueryResults.iterateResults(queryResults, resultCount);
var bigQueryDataTable = setupBigQueryResults.buildChartsDataTable(resultValues, resultSchema);

And that's it! We are ready to pass our data to any (appropriate) chart! Let's see our full example (with the required code to build a basic UI):

function doGet(e) {
  var projectNumber = '<YourProjectsNumber>'; 
  var sql = 'select TOP(word, 30), COUNT(*) as word_count from publicdata:samples.shakespeare WHERE LENGTH(word) > 10;';
  var queryResults = setupBigQueryResults.getQueryResults(projectNumber, sql);
  var resultCount = queryResults.getTotalRows();
  var resultSchema = queryResults.getSchema(); 
  var resultValues = setupBigQueryResults.iterateResults(queryResults, resultCount); 
  var bigQueryDataTable = setupBigQueryResults.buildChartsDataTable(resultValues, resultSchema);
  var chart = Charts.newBarChart()
    .setDataTable(bigQueryDataTable)
    .setDimensions(500, 700)
    .setYAxisTitle('Word')
    .setXAxisTitle('Count')
    .setLegendPosition(Charts.Position.BOTTOM)
    .build();
  var uiApp = UiApp.createApplication();
  uiApp.add(chart);
  return uiApp;
}

You can read all the details about what you can do with BigQuery, along with the original version of this example (without our library) in the Big Query class documentation.

Feel free to try this example, already published as a WebApp and to post your ideas, suggestions, requests, etc at the bottom of this article.

________________________________________________________________________
Important Note: The library provided in this example is setup to run as Me (César Antón Dorantes) and only gives access to the free datasets for Google BigQuery, to use it to process YOUR information (with the Billing activated on your account) you must use a library that runs as YOU. To do so, use the following library:

 "Project key" to import the library from "Resources" -> "Manage Libraries":

MsZBZDQxT88E-QS2OFguwejfIqMXwq4Jm
It will require you to authorize the script to use your BigQuery access, this is only done ONE TIME, to do it, simply run it from the following address:

https://script.google.com/macros/s/AKfycbyTD13yqIya75jntPkdubgq0f3DRsL-eTUDI8ZC9uGx5lYjHiM/exec

The FIRST time you run it, it SHOULD pop up an alert asking you to authorize the library. Once authorized, it should give this output (when run directly from the link):

"Unknown macro doGet".
________________________________________________________________________
Update: Version 3 of the library now available! It now includes Documentation and GNU License. Please Update your library to Version 3 so that your code get's the License for this Library. You can read the Library Documentation in the following link:
https://script.google.com/macros/library/d/MsZBZDQxT88E-QS2OFguwejfIqMXwq4Jm/3
________________________________________________________________________
Update: Version 4 of the library now available! It now incorporates the new method changes described on this article. Please Update your library to Version 4 so that your code works again with the new methods.
________________________________________________________________________