How to Connect to Google Sheets and BigQuery: Full Tutorial

Google Sheets has undeniably become a staple in most business operations, thanks to all its simple yet powerful functions for data processing.

Combining it with the functionalities of Google BigQuery, businesses can unlock new possibilities for working with their data.

BigQuery comes with built-in features such as business intelligence, geospatial analysis, and machine learning that can work well with data in Google Sheets.

In this tutorial on connecting Google Sheets and BigQuery, we’ll explain how you can use these tools together to perform data analysis on big data. Let’s get started!

What is Google BigQuery?

In a nutshell, BigQuery is a serverless cloud storage platform mainly designed to help users assimilate machine learning and AI functions by using SQL queries. It is capable of handling and processing large amounts of data and meeting big data processing requirements.

It comprises a four-layer structure that consists of a Project, Datasets, Tables, and Jobs.

• Project is the topmost layer and it contains all the data and information you want to keep in Google Cloud Platform.
• Datasets refer to the second layer and contain all the files or records for a particular project. Each project may have one or more datasets.
• Tables keep all your data in columns and rows.
• Jobs are the final layer. Here, SQL queries are run to acquire, input, and convert or transform data.

BigQuery is available in flat-rate and on-demand subscription models, and users are charged for individual queries and data storage.

The simple structure of BigQuery makes it ideal for beginners with little to no knowledge or experience in writing code or machine learning.

Why Use Google Sheets and BigQuery Together?

One of the key selling points of BigQuery is its serverless design. It is a scalable solution that both large organisations and SMEs can benefit from. The platform enables querying petabytes of data in mere minutes.

Since BigQuery is a platform developed by Google, this can only mean one thing: it is best paired with an application from the same multitech giant, Google Sheets. Google Sheets deals with all things data, whether it be data entry, creating and embedding charts, generating statistical reports, or simplifying workflow collaboration for organisations.

There are tons more benefits to this innovative combination. Let’s discuss them one by one:

• Sorting and filtering data with ease — Google Sheets has the capacity to extract, classify, and filter massive volumes of datasets from BigQuery. Simply open or select the spreadsheet with the data you want to work on, make sure it is connected to BigQuery, then start sifting through rows upon rows of data.
• Efficient reporting and data analysis — You can choose from pivot tables, charts, and functions to help generate comprehensive reports for your project. Add calculations in each of your data sets rows if you want to view stats for your columns.
• Smoother collaboration — A single spreadsheet may be enough to enable designating tasks and keeping track of your project’s progress more easily.
• Consolidated data — The combined efficiency of Google Sheets BigQuery guarantees having consistently available & accessible data anytime you need it. Alternatively, you can limit who can access your data. Use the audit log’s spreadsheet ID to check who’ve opened and made changes to the sheet
• Real time data integration — When you start to use Connected Sheets, you’ll marvel at how easy it is to load, integrate, and analyse data from BigQuery in real time.

What is Connected Sheets?

Connected Sheets simplifies the access, conceptualization, and analysis of petabytes of BigQuery data right from Google Sheets.

On its own, Google Sheets isn’t powerful enough to process that much data. With Connected Sheets, however, you can process and analyse large amounts of data within Sheets.

It’s a useful tool that expedites the processes entailed in internal business processes such as generating comprehensive sales reports, evaluating customer support feedback, and creating business plans.

What’s more, you won’t need SQL knowledge to run queries in Connected Sheets. Overall, it’s a great tool that can help you gain new insights from your data.

How to Connect Google Sheets to BigQuery

There are two different ways to link Google Sheets to BigQuery and get started with working on your data.

Exporting BigQuery Data to Google Sheets

You don’t need to be an expert at coding, nor do you need to learn how to do it, to pull this off. All you need to do is follow these steps:

1. In BigQuery, select the table you want to export.
2. Click on “Export”.
3. Start modifying and analysing your BigQuery data in Google Sheets.

Connecting BigQuery from Google Sheets

Did you know that you can use the BigQuery web UI, or alternatively data connector, to connect BigQuery from Google Sheets?

Connecting using the web UI:

1. Once you’ve successfully run a query, look for the “Save Results” option.
2. Clicking on “Save Results” reveals a drop-down menu with different options to save your end results.
3. Scroll down until you find the option to save the data in Google Sheets.

Keep in mind that using the web UI allows you to export up to 16,000 rows at a time.

Also, in case you update your existing data on BigQuery, the web UI does not automatically update the exported database. You’ll have to start over and manually export the new data.

Connecting using the data connector:

The data connector option is ideal for users who have either the Essentials, Enterprise, or Enterprise for Education type of G Suite account. If you do have any of these three accounts, then the data connector allows you to view and open your data from BigQuery through a spreadsheet.

1. Click on the “Data” menu in the toolbar in Google Sheets,
2. Click on “Data connectors” at the bottom part of the drop-down list.
3. Select “BigQuery”

The data connector is best if you have the budget for it. It will also require a bit of background in coding. The reason being that is you’ll need to write a script to ensure the connector automates updates of your BigQuery data while following a specified schedule. But, note the data connector allows a significantly lower number of rows to be exported— 10,000 rows to be exact.

How to Import Data from Google Sheets to BigQuery?

Alternatively, you can also transfer data from Google Sheets to BigQuery. There are two ways by which you can import Google Sheets data into BigQuery: one is by using BigQuery Connector and the other is by using Sheets Connector.

Using BigQuery Connector

BigQuery’s data connector lets users connect Google Sheets data without much hassle. Check out the simple steps below:

1. Head to the hamburger menu on your Google Cloud Platform dashboard. Select “BigQuery UI”.
2. Once you’re in, check that the Project ID name is correct before clicking the “Create Dataset” button. Also, under the “Advanced Options”, check next to it that the “Google-managed encryption key” option is selected.
3. Next, you need to create a table in BigQuery which will receive all the data imported from Google Sheets. To do this, select “Create a Table” and a box containing options for your table will pop up. Under “Source”, you will see a drop-down menu right under “Create table from”. Click the drop-down and select “Drive.”
4. Next to the drop-down for your table’s source is a box labelled “Select Drive URL”. Input the URL of your Google Sheet. CSV and Sheets formats are both accepted.
5. Add the table name then proceed with creating the table.
6. After successfully importing google sheets data using BigQuery connector, you can continue updating your sheets and the changes will automatically reflect in BigQuery.

Using Sheets Connector

Using the Sheets Connector is exclusive to Enterprise, Enterprise for Education, and Business G Suite accounts. The Sheets connector lets you save SQL queries straight to your Google Sheets with the help of a public dataset.

1. First, make sure your account is upgraded to either G Suite Enterprise or G Suite Enterprise for Education. Google’s data connector is exclusively available to the said accounts.
2. Once that’s settled, open the spreadsheet with the data you want imported to BigQuery.
3. Select the “Data” menu to reveal a drop-down list of options. Click “Data connectors” at the bottom-most section of the list. Then, click on “Connect to BigQuery.”
4. Select “Get Connected” to prompt a box where you are to choose a project. Make sure that the project you choose has the billing enabled.
5. Choose “Public Datasets” and select the table with the data you want to import. Then, click the “Connect” button.
6. On your spreadsheet, you should be able to see all the data from your selected project and table. You can now create pivot tables, charts, and formulas to further modify your data.

Data Analysis and Reporting using Google Sheets and BigQuery

Say you are a marketing agency with upcoming reports to submit to your clients. Each of your clients wants to know how well their ad campaigns are performing by looking at the data generated from both new and existing customers.

Google Sheets and BigQuery offer several options for organising and interpreting large amounts of data. From creating formulas to help you compute and generate statistics to preparing charts and tables, the options are endless. There are also shortcuts and tutorials you can use to make your job easier.

Better yet, try Looker Studio (formerly Data Studio), a free tool that lets you produce reader-friendly and shareable reports and dashboards from your data. All reports you generate using this tool are fully customizable, too.

Finally, users based in Asia, Europe, and the US can take advantage of BigQuery’s Geo Expansion option. The said feature uses machine learning to aid in effectively regulating geographic data for your projects.

Consolidate Your Agency’s Data in BigQuery with the Help of Acuto

Consolidating your agency’s data in a BigQuery data warehouse can prove to be a challenging task, especially if you have lots of data stored in many different sources.

Acuto offers data warehousing services and automation scripts tailored to your business. We identify your business’s specific needs before we start building a BigQuery warehouse to store all your data in one place.

Moreover, our team can write scripts designed to automate any repetitive aspect of your operations to streamline your business operations.

Learn more about our services, or book a call now to get started!

Key Takeaways

That wraps up our basic tutorial on how to get started with using Google Sheets and BigQuery together to get the most out of your data. Let’s recap the main points we covered:

• Google Sheets BigQuery is a serverless cloud storage platform that is capable of handling and processing massive amounts of data for processing and analysis. It also allows users to utilise machine learning and AI functions on their data without the need for coding or programming knowledge.
• Exporting BigQuery data to Google Sheets along with importing data from the latter to the former is pretty simple. There are different options to accomplish both, and they include using the BigQuery web UI, data connector, and sheets connector.
• Connected Sheets facilitate automated data transfer and analysis for agencies and businesses. Specifically, by linking your Google Sheets to BigQuery and vice versa, it becomes easier to automate changes on your existing data and keep it in a secure, unified storage.

Google Sheets and BigQuery FAQ

#1. How do I use BigQuery in Google Sheets?

To use BigQuery in Google Sheets, first you need to open the spreadsheet on Google Sheets that you want to connect to BigQuery. When the spreadsheet is open, click on “Data” in the main toolbar, select “Data Connectors” from the drop-down menu, then select “Connect to BigQuery.” Choose or add a project, then a table. Finally, click on “Connect” and then select “Start analysing.”

#2. How do I automatically get data from Google Sheets to BigQuery?

Using a data connector (BigQuery and Google Sheets both have one) allows you to generate data from Google Sheets and load it into BigQuery without supervision. All data modifications and updates done to your Sheets are reflected on BigQuery in real-time.

#3. Can Google Sheets handle big data?

It all depends on the type of data you input in Sheets. There are quotas and limitations set on the Sheets API to ensure each user gets to maximise use of the system without compromising its efficiency. Check out the Google Workspace guide to learn more.

#4. Can you run queries in Google Sheets?

Yes. Google Sheets has a built-in, easy-to-use QUERY function. All you have to do is input the following formula in a cell: “=QUERY([data], [query], [headers]).” Replace [data] with the specific cell range where you want to retrieve information. [Query] refers to your search query. And [headers] is often used in the event that your heading spans two cells.