How to get data from BigQuery to Google Sheets automatically

by Jan 17, 2019

What is the BigQuery Data Connector for Google Sheets?

BigQuery connectors allow users to integrate BigQuery with other platforms—either data sources or analytics tools. Google recently launched a BigQuery connector for Google Sheets which allows greater accessibility to your data. Large datasets can be intimidating for non-experts within your organization, and not all users need access to your full range of data. For big data practitioners, it is better to free up smaller amounts of data and distribute it to the members of an organization who need it, rather than requiring those who are less tech-savvy to query the system themselves. Google Sheets is intuitive and familiar for any users who are used to working with spreadsheets. Learn more about the BiqQuery Data Connector in our first post in this series.

How to connect BigQuery to Google Sheets?

1. Use Sheetgo’s Google Apps Script

Here at Sheetgo, we are all about automation and we love providing you with solutions—so we created a tool ourselves to automatically update the BigQuery data in your Sheets! We made a script for you to install in your Google Sheets so that you can get data from BigQuery to Google Sheets automatically! Read on for a tutorial on how to quickly set up the script so you can get started on more important tasks!

Step 1: Make a copy of the script

Open the template by clicking here. Click on Make a Copy to get your own copy of this script, it will automatically save to your Google Drive.

BigQuery to Google Sheets: Navigation to Make a Copy of the File

Step 2: Set up the script

Your copy of the script will open automatically in a new tab. Click Tools →  Script Editor. A new tab will open in your browser. You then need to complete two steps: enable the BigQuery API (2.1) and get the Project ID (2.2).

BigQuery to Google Sheets: How to Set up The Script

Step 2.1: Enable the BigQuery API

To enable the Big Query API, select Resources → Advanced Google Services. A new window will pop up, make sure the Big Query API is turned On.

Bigquery to Google Sheets: Step 2

Step 2.2: Get the Project ID

To get the Project ID, follow these steps. The screenshots below show detailed steps.

  • Click the hyperlink Google Cloud Platform API Dashboard.
  • Click the ‘ENABLE APIS AND SERVICES’ button.
  • Type BigQuery into the search bar and click BigQuery API.
  • Click Enable
  • In the browser URL copy the text project-id-##################
  • Go back to the BigQuery Queries tab in your browser and paste that text into the script code after Project ID. (See screenshot below)
  • You can close the tab
Bigquery to Google Sheets: Enable APIs
Bigquery Queries in Google Cloud Platform
Enabling API Library
Bigquery to Google Sheets: Project ID in URL
Bigquery to Google Sheets: Code in Google Cloud

Step 3: Automate the data transfer

To automate, inside the script, click on the clock icon.

Bigquery to Google Sheets: Step 3

Then, click on > Add Trigger.

Bigquery to Google Sheets: Adding Trigger

In the following screen, you can set it up the way you prefer.

Bigquery to Google Sheets

Step 4: Run the script

Go back to your spreadsheet. Click Sheetgo → Run Query → Continue → Login.

That’s it! The script has been created! Now you can automatically import data from BigQuery into your Google Sheets spreadsheets!

Bigquery to Google Sheets: Step 4

2. Follow Google’s approach

This Google Sheets feature only recently updated to schedule automatic updates within Sheets to the connected BigQuery data. Here is a breakdown on how to automatically import large datasets into Google Sheets and stay on top of the latest and critical company data.

First, record a macro to refresh your data. Open the spreadsheet that contains the data connected to the BigQuery. Then record a macro from a different tab than the one you want to refresh. After starting to record, switch to the tab you want to refresh. At the bottom left, click Refresh and after that, click Save.

Second, schedule your macro. Go to the Menu Tools > Script Editor. Inside the Script Editor, go to Edit > Current project’s triggers. On the bottom right, click on Add triggers and you can select your desired options. To finish, click on Save. That’s it, you are all set! Make sure to learn more about Google´s BigQuery in the following blog posts:

BigQuery Data Connector for Google Sheets: How to Get More Than 10,000 Rows

How to Analyze BigQuery Data Within Google Sheets

Share This