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

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

How to automate your FP&A on top of Google Sheets?

Layer is an add-on that equips finance teams with the tools to increase efficiency and data quality in their FP&A processes on top of Google Sheets. Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds.

Using Layer, you can:

  • Share & Collaborate: Automate your data collection and validation through user controls.
  • Automate & Schedule: Schedule recurring data collection and distribution tasks.
  • Integrate & Sync: Connect to your tech stack and sync all your data in one place.
  • Visualize & Report: Generate and share reports with real-time data and actionable decisions.

Limited Time Offer: Sign up today and get free access to Layer, including all the paid features, so you can start managing, automating, and scaling your FP&A processes on top of Google Sheets!

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.

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 23 2022