Getting Started with Google Cloud, App Script and BigQuery

The humble spreadsheet is one of the most versatile digital tools that any tester has in their toolkit. I’m working on a data analytics project at InsightTimer where we are doing event tracking across our android and iOS apps using MParticle and BigQuery. I want to do some data analyst on the events coming through for new android builds. “Basic Sanity” testing of events is my goal. My first step towards this is to create a Google Sheet dashboard that integrates with BiqQuery via a javascript-esk language called App Scripts. App scripts is an amazing extension of the humble spreadsheet, it really adds a lot of extra functionality. I’m going to walk you through how you can get started with a similar project of your own.

1. Sign up for a google cloud trial

I used a test account; to explore this for you and I collected screenshots along the way.

Navigate to and sign up for all that cloud magic.


You will get $300 worth of credit to explore the cloud


You will be asked for credit card details 🙁 but at least there is no auto charge if you forget


Woot, the default project name is “My First Project”


Go to to test out your big query set up. BigQuery should be enabled for new projects by default.

There’s a whole bunch of public data sets for you to explore


We are going to explore the hacker_news datasets in this example. This is actually a collection of the data (comments and articles) from this Hacker News website.

Previewing the table is a handy feature for getting an idea of the structure of the data

The preview gives you some sample data, an idea of columns and a way to explore


If you wanted to find the top ranked comments for author ‘4ad’ you can run a query like the following (remove the \ characters if you are copy pasting this query):

author = '4ad'
AND deleted IS NULL
ranking desc,


2. Setting up Google Sheets with App Script

Create a new sheet in your drive folder, I’ve named mine BigQueryTest. Open up the tools > Script Editor:


You will see a blank script editor. Copy & paste the code from 

Save, the first time you save it will ask you to name your project


I ended up calling mine BigQueryAppScriptIntegration, original I know


Run the onOpen function. The first time you run this app script it will ask you to set up permissions


Review the permissions


select your account


More Permissions


Whew, done. Now if you navigate back to your spreadsheet you should see some added menu items:


What happens when you click on the Prepare sheet… function?

Huh, fancy that. Our spreadsheet is updated


Then how about the Generate step-by-step function?


That’s pretty cool a new tab is added


You don’t need to understand google’s code to explore this. I find it’s better to learn by starting from scratch. This code gives you a guideline for what you can achieve but you will have a better understanding of how app script works by building out your own.

3. Enable App Script APIs in your google cloud project

Navigate to your google cloud console: and click on enable APIs and services


search for App Script and click enable


4. Enable BigQuery APIs in your App Script

Navigate back to you App Script and go to Resources > Advanced Google settings


Enable BigQuery APIs


Delete your old app scrip code, copy and paste the first code sample from

Replace the XXXXX string with your project id, you get this from your google cloud console



Replace the query with 

'SELECT COUNT (DISTINCT id) FROM [bigquery-public-data:hacker_news.comments]'


Save and run the runQuery function. You will also need to set up permissions again the first time you run this.


Huh, that’s a new screen. I clicked on “Advanced” to see a similar permission screen I had previously encountered


Dam. I got another error message:

When I click on the details it gave me a handy url to navigate to


I went to the suggested URL and it turned out I need to enable another API somewhere else. That makes me thing I did it in the wrong spot in step 3, maybe that step wasn’t needed?


I wait a few moments and go back to my app script. I click on “Run Query”. No error messages this time. If you go to your drive folder you should see a new sheet called “Big Query Results” with one row of data:


You can test out this result is roughly correct by running the query directly in your google cloud query editor:


Now you are all set up with your API integrations. Next lesson will be building out your own dashboard that puts this set up into practice.

1 comment

Leave a Reply