cloud, JavaScript

Big Query And Google Spreadsheet Integration

big queryThere are many options to extend the powerful spreadsheets that Google offers. One of the cool, new ways to leverage its power is by using a spreadsheet as your ‘front-end’ to a big data processing power (=Big Query). In our world, there is a need to get results as fast as possible and since our data sources grow fast. It’s nice to have a tool that let us ‘see’ (and share) results quickly and easily.

What is BigQuery?

Google BigQuery is a web service that lets you do interactive analysis of massive datasets. When we saying massive we are talking here on billions of rows (or more). It is a scalable and easy to use tool that gives developers and businesses an easy way into powerful data analytics on demand.

As for Google Docs and their powerful sharing capabilities – I guess we don’t need to elaborate here. So, let’s see what are the steps that will let us get data from BigQuery into our Google spreadsheet.

Integrate BigQuery To Google Spreadsheet

  1. Go to: drive.google.com and click on ‘Create’ a new Spreadsheet.
  2. Go to: Tools -> Script Editor
  3. Choose Create script for Spreadsheet.
  4. Insert this script or copy this example I’ve published in the past.
  5. Click Resources -> Use Google APIs
  6. Enter a project name. For example: “Testing BQ with G-Docs”.
  7. Toggle BigQuery API v2 from off to on But WAIT! on the bottom of that dialog you have a link that these services need to be also enabled in Google Developer Console. Click that link.
  8. Find Big Query API in the console launched in the previous step and toggle it to on.
  9. Go back to Script Editor and click OK on the dialog.
  10. Go back to your spreadsheet and reload the page so our ‘example’ menu item will be added.
  11. You should see BigQuery menu in the spreadsheet with ‘Run Query’ sub-menu.
  12. Choose ‘Run Query’.
  13. On the first run, you should see a dialog Authorization Required. Click Continue and then Accept
  14. Hold you fingers cross and look at the spreadsheet. You should see it with results from BigQuery.

The Apps Script


/**
* Fetching data from BigQuery and present it in our sheet
* Author: Ido Green
* Date: 14/12/2013
*
* See: https://greenido.wordpress.com/2013/12/16/big-query-and-google-spreadsheet-intergration/
* Misc: https://developers.google.com/bigquery/
*/
//
// Build and run the query: Get the top 30 longest works of Shakespeare
//
function runQuery() {
// Replace this value with your Google Developer project number (It is really a number.
// Don't confuse it with an alphanumeric project id)
var projectNumber = 'Put your project number here';
if (projectNumber.length < 1) {
var errMsg = "You forgot to set a project number – So no BQ for you!";
Logger.log(errMsg);
Browser.msgBox(errMsg);
return;
}
var sheet = SpreadsheetApp.getActiveSheet();
var sql = 'select TOP(word, 30), COUNT(*) as word_count from publicdata:samples.shakespeare WHERE LENGTH(word) > 10;';
var queryResults;
// Inserts a Query Job
try {
var queryRequest = BigQuery.newQueryRequest();
queryRequest.setQuery(sql).setTimeoutMs(100000);
queryResults = BigQuery.Jobs.query(queryRequest, projectNumber);
}
catch (err) {
Logger.log(err);
Browser.msgBox(err);
return;
}
// Check on status of the Query Job
while (queryResults.getJobComplete() == false) {
try {
queryResults = BigQuery.Jobs.getQueryResults(projectNumber, queryJob.getJobReference().getJobId());
}
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, starting from cell A1
sheet.getRange(1, 1, resultCount, tableRows[0].getF().length).setValues(resultValues);
Browser.msgBox("Yo yo! We are done with updating the results");
}
//
// Insert our customize menu item
//
function onOpen() {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var menuEntries = [ {name: 'Run Query', functionName: 'runQuery'} ];
sheet.addMenu('BigQuery Example', menuEntries);
};

view raw

BigQuery2GSheets.js

hosted with ❤ by GitHub

This is the results you should get on your Sheet

BQ results in Google Spreadsheet

Happy hacking.

Standard

3 thoughts on “Big Query And Google Spreadsheet Integration”

  2. Ace says:

    Hey, this is awesome! Tried doing it, but getting an Apps Script error 😦 –
    Exception: Invalid project ID ‘Put your project number here’. Project IDs must contain 6-63 lowercase letters, digits, or dashes. IDs must start with a letter and may not end with a dash.

    Any idea what could have gone wrong?

    • greenido says:

      Yes… You should replace the place holder that I’ve put in the code with your project ID. See line 15 – and set a real value to the variable over there.
      The apps script should run with your specific project-ID and not this demo one.

Comments are closed.