How to get data from BigQuery to Google Sheets automatically

bigquery to google sheets featured image

Written by Valentine Schelstraete

May 6, 2021

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. With Sheetgo

Here at Sheetgo, we are all about automation and we love providing you with solutions—so we integrated with BigQuery to automatically update BigQuery data in your spreadsheets.

Benefits of Sheetgo
  • Unlimited rows import (Google Sheets data connector has a limit of 16,000 rows)
  • Google’s data connector is only available for Google Workspace Enterprise Plus, Education Plus, Enterprise Standard, or Enterprise Essentials account users
  • Sheetgo connects BigQuery to Google Sheets, Excel, and CSV files
  • Sheetgo’s BigQuery import returns a native spreadsheet, instead of a layer on top of your file

Read on for a tutorial on how to quickly set up the script so you can get started on more important tasks!

Step 1: Install Sheetgo

Click below to start connecting BigQuery to Google Sheets. Login with your Google, Microsoft, or Dropbox account.

On the next screen, click Connect files to start building your BigQuery to Google Sheets workflow.

Step 2: BigQuery as your data source

The first step in building a Sheetgo workflow, is selecting your data source, where you want to import the data from.

This can either be a spreadsheet (Google Sheets, Excel, CSV, or TSV file) or a database.

Click on BigQuery to select it as your data source.

BigQuery to Google Sheets data source

Step 3: BigQuery opens inside Sheetgo

Select your BigQuery Project, Dataset, and Table.

Once you’ve done that, the BigQuery query editor opens automatically inside Sheetgo.

Here, you can simply write queries the exact same way you would do inside BigQuery directly.

BigQuery to Google Sheets 2 query editor

Use our Validate query button to check whether or not you’ve written a correct query.

Step 4: Select your data destination

After writing your query and telling Sheetgo exactly what data to import, select the destination you want to send it to.

This can either be a Google Sheets, Excel, or CSV file.

Click on Google Sheets.

You can either choose to send the data to an already existing file from in your Drive storage, or let Sheetgo create a new file for you.

BigQuery to Google Sheets 4 send data

Finally, click Finish and save to send your BigQuery data to Google Sheets.

Step 5: Automate your workflow

After saving your workflow, you visually see how it’s build in the workflow view.

Here, click on Automate to schedule automatic updates.

In doing so, Sheetgo will automatically update your BigQuery data to Google Sheets at the frequency of your choice.

BigQuery to Google Sheets 6 workflow view

2. Use Google’s connected sheets

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.

Inside your sheet, go to Data > Data connectors > Connect to BigQuery.

Next, choose a cloud project from the list, or search for it in the search bar. You can select a specific project and dataset, or write a custom query directly.

Once you’ve clicked Connect, Google automatically fetches the data in a new tab in your sheet.

BigQuery to Google Sheets connected sheets

That’s how you connect BigQuery to Google Sheets automatically. Lear more about BigQuery in our 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

Looking for more automation and spreadsheet tools?

Feel free to take a look at Sheetgo’s large range of automated business templates for Google Sheet and Excel.

You may also like…