cloud, JavaScript

Big Query And Google Spreadsheet Intergration

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

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 Intergration

  1. 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.