Don’t forget to share this post

Google offers a great variety of tools and services for your data, most of which are free to use. Google Sheets is a very popular example, used by many to analyze, visualize, and even store data.

However, with time, the spreadsheets start piling up, and they become unmanageable. Google’s BigQuery is designed to accommodate big data with better storage and data analysis services. Connected Sheets is the integration of Sheets and BigQuery, which allows you to analyze big data through the Sheets interface.

In this article, you will learn how to connect Google Sheets and BigQuery, as well as the ways in which this can benefit you. First, you will learn how to export data from BigQuery to Sheets using two different methods, one manually and one automatically with Sheetgo. Finally, you will learn how to migrate data from Sheets to BigQuery.

What is BigQuery?

Google’s BigQuery is a serverless, scalable, multi-cloud data warehouse. Google has integrated BigQuery with Sheets to create Connected Sheets, allowing you to store massive amounts of data that you can analyze from Google Sheets.

BigQuery offers very flexible pricing structures, so you can ensure you only pay for what you use. If you’re working with big data, you can now do so from the familiar environment of Google Sheets without sacrificing performance.

Why Connect BigQuery to Google Sheets?

Both Sheets and BigQuery are excellent tools for their respective purposes. Connected Sheets allows you to take advantage of the best of both. On the one hand, BigQuery’s data warehousing and user-friendly query interface; on the other hand, Sheets’ familiar data analysis and visualization capabilities.

Before you can connect Sheets to BigQuery, you need a Google Cloud Platform account. You can try BigQuery for free. You will be asked for a credit card number to verify that you are not a robot. However, you will not be signed up for automatic renewal and will only pay for what you use. In addition to having access to the many data services offered by the Google Cloud Platform, BigQuery customers can store up to 10GB and analyze up to 1TB monthly for free.

How Do I Export BigQuery to Google Sheets?

In this section, you will learn two different ways to get BigQuery data into Google Sheets: export from BigQuery and connect from Sheets.

Export Data from BigQuery to Google Sheets

You can quickly export data to Google Sheets directly from BigQuery.

  1. 1. In BigQuery, find the table you want to export.
How to Get Data from Big Query to Google Sheets Table in Big Query
How to Get Data from BigQuery to Google Sheets - Table in BigQuery
  1. 2. Click on “Export” from the toolbar and select “Explore with Sheets”.
How to Get Data from Big Query to Google Sheets Explore with Sheets
How to Get Data from BigQuery to Google Sheets - Explore with Sheets
  1. 3. That’s it. You can now analyze your BigQuery table from Google Sheets.
How to Get Data from Big Query to Google Sheets Data Connected
How to Get Data from BigQuery to Google Sheets - Data Connected
How to Use IMPORTDATA in Google Sheets
How to Use IMPORTDATA in Google Sheets

Google offers multiple options to import data from external sources. Here’s how to use IMPORTDATA to import .csv and .tsv files from a URL into Google Sheets.

READ MORE

Get BigQuery Data from Sheets

Follow the steps below to connect to BigQuery from Google Sheets to take advantage of Connected Sheets.

  1. 1. Go to Data > Data connectors > Connect to BigQuery
How to Get Data from Big Query to Google Sheets Connect to Big Query
How to Get Data from BigQuery to Google Sheets - Connect to BigQuery
  1. 2. You will see a message like the one below. Click on “Get connected”.
How to Get Data from Big Query to Google Sheets Get Connected
How to Get Data from BigQuery to Google Sheets - Get Connected
  1. 3. The next pop-up will ask you to choose a cloud project.
How to Get Data from Big Query to Google Sheets Choose Project
How to Get Data from BigQuery to Google Sheets - Choose Project
  1. 4. Next, you need to select a dataset in that project. In my example, I will choose a public dataset, “census_bureau_usa”.
How to Get Data from Big Query to Google Sheets Choose Dataset
How to Get Data from BigQuery to Google Sheets - Choose Dataset
  1. 5. Once you have selected your dataset, you can choose a table or view, then click on “Connect”.
How to Get Data from Big Query to Google Sheets Choose Table
How to Get Data from BigQuery to Google Sheets - Choose Table
  1. 6. You will see a message once the connection is made.
How to Get Data from Big Query to Google Sheets Data Connected 2
How to Get Data from BigQuery to Google Sheets - Data Connected
  1. 7. That’s it. You can comfortably analyze your dataset from Sheets, even though it’s a large file since the data is not actually loaded into Sheets.
How to Get Data from Big Query to Google Sheets Analyze from Sheets
How to Get Data from BigQuery to Google Sheets - Analyze from Sheets

How To Use IMPORTRANGE Function In Google Sheets?

Google Sheets allows you to import and link a specific range of cells from another spreadsheet. Here's how to use the IMPORTRANGE function in Google Sheets

READ MORE
How To Use IMPORTRANGE Function In Google Sheets

Get BigQuery data from Google Sheets automatically with Sheetgo

Although the methods above allow you to get BigQuery data into Google Sheets, Sheetgo provides additional benefits and automation features to make the process as effortless as possible. 

Sign up for Sheetgo to automatically connect BigQuery to Google Sheets. 

The following are some of the benefits that Sheetgo offers over Connected Sheets:

  • Ease of use: Sheetgo offers features like point-and-click setup, pre-configured templates, and automation options that simplify the process.

  • Advanced data transformation: Sheetgo offers more advanced data transformation capabilities, allowing you to perform data cleaning, filtering, and transformation tasks directly within the tool.

  • Automation: Sheetgo provides automation features that allow you to schedule data updates or trigger them based on specific events or conditions.

  • Error handling: Sheetgo makes it easier to identify and address issues with data synchronization between Google Sheets and BigQuery.

  • Customization: Sheetgo offers more customization options for data integration workflows, allowing you to tailor the process to your specific needs.

  • Compatibility: Sheetgo offers compatibility with other data sources and destinations beyond BigQuery, making it a more versatile solution for data integration.


Follow the steps below to connect BigQuery to Google Sheets and automate the workflow.


  1. Click “Create a connection” and choose BigQuery as the source.

Screen Shot 2023 09 22 at 16 02 15
Select BigQuery as a source

2. Select your BigQuery project, dataset, table, or view.

Screen Shot 2023 09 22 at 16 02 33
Select your table

3. Write your Query

Screen Shot 2023 09 22 at 16 02 46
Write your Query

4. Select Google Sheets as the destination and click save.

Screen Shot 2023 09 22 at 16 02 57
Select your preferred destination type

How Do I Migrate Google Sheets Data to BigQuery?

Follow the steps below to get Google Sheets data into BigQuery.

  1. 1. From your account, select the project, click on the three dots, then on “Create dataset”.
How to Get Data from Big Query to Google Sheets Choose Project 2
How to Get Data from BigQuery to Google Sheets - Choose Project
  1. 2. Add a “Dataset ID”, the only required field, then click on “Create dataset”.
How to Get Data from Big Query to Google Sheets Dataset ID
How to Get Data from BigQuery to Google Sheets - Dataset ID
  1. 3. Click on the dataset, then on “Create table”, as shown in the screenshot below.
How to Get Data from Big Query to Google Sheets Create Table
How to Get Data from BigQuery to Google Sheets - Create Table
  1. 4. Select “Drive” as the “Source” of the table.
How to Get Data from Big Query to Google Sheets Drive Table
How to Get Data from BigQuery to Google Sheets - Drive Table
  1. 5. Add the link to the spreadsheet as the “Drive URI” and select “Google Sheet” as the “File format”.
How to Get Data from Big Query to Google Sheets Drive URI File Format
How to Get Data from BigQuery to Google Sheets - Drive URI & File Format
  1. 6. For “Destination”, add a table name and choose whether to “auto-detect” the schema or add it manually. Once you’re happy with the settings, click on “Create table”.
How to Get Data from Big Query to Google Sheets Destination Table Schema
How to Get Data from BigQuery to Google Sheets - Destination Table & Schema
  1. 7. The table has been created and is available in BigQuery.
How to Get Data from Big Query to Google Sheets Table in Big Query 2
How to Get Data from BigQuery to Google Sheets - Table in BigQuery

Conclusion

As you have seen, Connected Sheets allows you to use all your favorite Sheets functionality on massive datasets stored on BigQuery. One of Google Sheets’ limitations was the size of the datasets that you could comfortably analyze without the spreadsheet becoming slow and unmanageable, occasionally crashing completely. Thanks to BigQuery and Connected Sheets, this is no longer a problem. Instead of loading the data into Sheets, you can work with an extract that you can customize according to your needs.

You now know how to get BigQuery data into Google Sheets using two different methods: exporting from BigQuery and importing from Sheets. You also know how to migrate Sheets data to BigQuery as well as how to connect Google Sheets and BigQuery however you want. Finally, you know about an alternative that allows you to connect BigQuery to Google Sheets in just a few clicks. Sign up for Sheetgo and start automating your data transfers. 

Hady ElHady
Hady is Content Lead at Layer.

Hady has a passion for tech, marketing, and spreadsheets. Besides his Computer Science degree, he has vast experience in developing, launching, and scaling content marketing processes at SaaS startups.

Originally published Aug 26 2022, Updated Sep 22 2023

Layer is now Sheetgo

Automate your procesess on top of spreadsheets