There 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
- Go to: drive.google.com and click on ‘Create’ a new Spreadsheet.
- Go to: Tools -> Script Editor
- Choose Create script for Spreadsheet.
- Insert this script or copy this example I’ve published in the past.
- Click Resources -> Use Google APIs
- Enter a project name. For example: “Testing BQ with G-Docs”.
- 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.
- Find Big Query API in the console launched in the previous step and toggle it to on.
- Go back to Script Editor and click OK on the dialog.
- Go back to your spreadsheet and reload the page so our ‘example’ menu item will be added.
- You should see BigQuery menu in the spreadsheet with ‘Run Query’ sub-menu.
- Choose ‘Run Query’.
- On the first run, you should see a dialog Authorization Required. Click Continue and then Accept
- 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