How to Get Data From BigQuery to Google Sheets

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.

1. 2. Click on “Export” from the toolbar and select “Explore with Sheets”.

1. 3. That’s it. You can now analyze your BigQuery table from Google Sheets.

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

1. 2. You will see a message like the one below. Click on “Get connected”.

1. 3. The next pop-up will ask you to choose a cloud 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”.

1. 5. Once you have selected your dataset, you can choose a table or view, then click on “Connect”.

1. 6. You will see a message once the connection is made.

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

1. 2. Add a “Dataset ID”, the only required field, then click on “Create dataset”.

1. 3. Click on the dataset, then on “Create table”, as shown in the screenshot below.

1. 4. Select “Drive” as the “Source” of the table.

1. 5. Add the link to the spreadsheet as the “Drive URI” and select “Google Sheet” as the “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”.

1. 7. The table has been created and is available in BigQuery.

Want to Boost Your Team's Productivity and Efficiency?

Transform the way your team collaborates with Confluence, a remote-friendly workspace designed to bring knowledge and collaboration together. Say goodbye to scattered information and disjointed communication, and embrace a platform that empowers your team to accomplish more, together.

Key Features and Benefits:

• Centralized Knowledge: Access your team’s collective wisdom with ease.
• Collaborative Workspace: Foster engagement with flexible project tools.
• Seamless Communication: Connect your entire organization effortlessly.
• Preserve Ideas: Capture insights without losing them in chats or notifications.
• Comprehensive Platform: Manage all content in one organized location.
• Open Teamwork: Empower employees to contribute, share, and grow.
• Superior Integrations: Sync with tools like Slack, Jira, Trello, and more.

Limited-Time Offer: Sign up for Confluence today and claim your forever-free plan, revolutionizing your team’s collaboration experience.

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.