How to Connect Google Sheets to BigQuery: Best Options To Sync Data

Google Sheets is an amazing tool when it comes to sorting, filtering, analyzing, and visualizing data reports. But that’s only up to a point. Think Google Analytics data, Google Ads data, ActiveCampaign, or any marketing tool data, you name it. It’s always been easier to visualize and analyze it using Google Sheets, without being stuck with the built-in limited reports you get inside the other tools. But when you’re dealing with a large amount of data, using a data warehouse like BigQuery to store and analyze data might be a better option.

In this article, we’ll explore why you should connect data from Google Sheets to BigQuery when dealing with data at scale and what the options are to automate this connection.

Benefits of connecting Google Sheets to Google BigQuery

Of course, Google Sheets can be enough for simple analysis and a few pie charts to sum up where you spend that Google ads budget.

But when your data is spread across multiple sheets (this will happen when you are trying to analyze reports from multiple sources) or when you’re trying to run advanced formulas in sheets that contain a lot of data, things get a bit complicated.

When you connect Google Sheets to BigQuery you get:

• Faster processing times: run SQL queries in nanoseconds and analyze data at scale. 
• Data enrichment: easily combine data from multiple sources to get the answers you need from meaningful data sets.
• Advanced reporting: you can create graphs, charts, and pivot tables inside Google Sheets but with limited capabilities, compared to what you can do after manipulating data in BigQuery.
• Data ownership: all the data in BigQuery is owned by you not the tool you used to export it in Sheets.

Best options to connect data from Google Sheets to BigQuery table

Do Google Sheets connect to BigQuery automatically? They’re both owned by Google, right? Yes, they are. But they are not connected out of the box.

Depending on your level of tech savvy-ness, time, and budget, here are the best options we recommend to connect Google Sheets to BigQuery:

• Automate the process using a 3rd party tool: Coupler.io can do a great job here and it’s simple to set up and use.
• Use the native Connected Sheets integration: limited to 25.000 rows and the data sync frequency is up to every hour, compared to every 15 minutes when using Coupler.io. Connected Sheets allows you to enrich and refresh data in your Google Sheets with changes from BigQuery and not the other way around.
• Use API’s, add-ons, or Python: you might encounter reliability issues, will need a developer and it will be harder to automate data sync.

Since we’re all about being efficient, we’ll skip the third option in this article and focus on how you can connect your data in Google Sheets to BigQuery using Coupler.io or Connected Sheets.

Import data from Google Sheets to BigQuery using Coupler.io automation

If you’re looking to import Google Sheets data to BigQuery data warehouse, Coupler.io will make the job go smoothly. 

Coupler.io is a data automation and analytics platform that provides an ETL tool for data integration and a data analytics service for sophisticated data analysis, data visualization, and workflow automation tasks.

You can easily automate the connection from Google Sheets to BigQuery tables with a data refresh frequency of up to every 15 minutes. Here’s how to do it, step by step:

Steps to automate Sheets and BigQuery integration using Coupler.io

To connect data from Google Sheets to BigQuery you will first need to sign up to Coupler.io (you can get started with a free 14-day trial account without a credit card needed).

For the purpose of this article, we’ll assume you already have a Google BigQuery account set up.

Once you’re signed in click on Add new importer to start setting up your Google Sheets to BigQuery connection.

Since Coupler.io allows multiple sources and destinations, you’ll need to select Google Sheets and BigQuery from a drop-down list. Then click Proceed. FYI, you can also connect BigQuery to Excel using Coupler.io ETL solution.

Following the visual importer wizard, select the details of the Google Sheets spreadsheet you want to connect.

• If your Coupler.io account was created with a Google account, you can select this as the source to import Google Sheets to BigQuery as it will show in the drop-down list. However, you can also connect other Google accounts if you want to use data from sheets located under different accounts. Note that you can only connect one spreadsheet at a time.

2. Select your Google Sheets file.

2. Next, select the sheet you want to connect to BigQuery. You will need to select at least one sheet, but you also have the option to select multiple if you want to consolidate data from them.

2. Select the range if needed or click Jump to destination settings to continue.

1. Connect your BigQuery account to Coupler.io by adding your .json key file.  Once you’ve uploaded the key file, click Save.

1. The destination account you’ve linked will appear in the dropdown menu. Select it to move forward.

1. Next, you will need to either specify an existing dataset and table inside your BQ account or create new ones. For the purpose of this example, we’ve created Demo_data as a new dataset and Demo_table as a new table simply by typing in the names in the respective boxes in the visual importer.

1. Click Continue to schedule your data refresh frequency and preferences.

With Coupler.io you can schedule data refresh for up to every 15 mins (compared to up to every hour when using Connected Sheets), on selected days.

1. Make sure the Automatic data refresh toggle is ON (shows green as in the screenshot below), then proceed to select the desired interval.
   • Select the days of the week you want to run data refresh.
   • Select time preference and time zone (or leave the default settings; you can also adjust these at any time later on).

2. Click on Save and Run to finish your Google Sheets to Google BigQuery setup.

Once you’ve finished your setup, the importer will process the connection and connect data from Google Sheets to BigQuery based on your settings. The run time depends on the amount of data that you have in your sheet that needs to be exported. Once this is ready, you will see the screen below, meaning your imported data from Google Sheets is now available in your BigQuery account.

1. Click on View Results to view your imported data inside BigQuery (this will take you to your BQ account).

2. Find your table in BigQuery and check your schema was imported properly.

Try Coupler.io yourself to import data from Google Sheets to BigQuery or solve more sophisticated automation and analytics tasks.

Sync Google Sheets with BigQuery with Connected Sheets

Using Connected Sheets (former (BigQuery Data Connector) might be a good option to sync your existing sheet data with BigQuery and also import more data from BigQuery to Google Sheets for further analysis without having to use SQL functions in BigQuery. You can either link BigQuery to Sheets and manipulate data there or follow the steps below to use your existing data and add more insights from BQ.

How to use Connected Sheets step by step

1. Navigate to the Google Sheets file that you wish to connect. In the image below we’ve opened the “Website form submissions” sheet that has multiple rows of data under four columns: Submitted On, Name, Country, and Email.  With your sheet open, click Data > Data Connectors > Connect to BigQuery.

2. Click on Get connected to set up your BigQuery connection.

2. Choose the project. If both your Google Sheets and BigQuery are under the same Google account, your available project IDs will be visible.

2. Select the data set; then select your BigQuery table and click Connect.

2. Your data is now connected to BigQuery. This means that you get the power and scale of BQ to analyze your data in Google Sheets without any SQL knowledge. You can use familiar Google Sheets functions, pivot tables, and charts to get insights from big data with this BigQuery connection. Using the Extract tool, you can import other data from BQ into your sheet to add more details to your analysis. You can also use the Calculated column function to manipulate your data and create combinations or transformations of other columns in your dataset.

2. To check the data, go to Google BigQuery and run a simple SQL query to view your data. Simply insert an * after the SELECT operator and click RUN to run your query. This will display the data that we just created from our Google Sheets.

Schedule data refresh in Google Sheets after you’ve connected BigQuery

1. By default, everything you create inside a connected sheet (charts, new columns, pivot tables, or formula values) stays the same even if your data inside BQ changes. Click on Schedule refresh if you wish to update your sheet data with changes that happened inside BigQuery. 

2. Set preferred intervals: you can choose between hours, days, weeks, or months. Keep in mind that all changes made in your Sheet will be overwritten by changes in BigQuery when you refresh the data.

Now, you might think that this is the same as connecting BigQuery to Sheets. 

While using BQ and connecting multiple sheets to it is the primary way to go for a lot of people, going the other way around is a good option if you want to get the power of BigQuery inside Google Sheets without feeling overwhelmed by the apparent complexity of BQ.

It simply adds more options to how you can analyze data without the tech knowledge needed. For example, enriching your spreadsheet with more data available in BQ and performing analysis that simply isn’t available when using a regular Google Sheet that’s not connected.

Connected Sheets limitations

You might be thinking why use third-party tools when you can just use the Connected Sheets functionality? As simple as it looks to set up, it comes with a few limitations:

• Data refresh frequency is up to one hour which may not be enough.
• You can only export up to 25,000 rows.
• When working with larger teams or multiple stakeholders some might bump into permission errors and won’t be able to use the data inside Google Sheets due to a lack of proper permissions in the BigQuery project that you’ve connected. 

Should I use add-ons or manual export to move Google Sheets data to BigQuery?

We’ve covered how you can import Google Sheets to BigQuery and automate data sync but we also wanted to quickly mention that there are also other ways of doing this that of course, come with their own limitation.

Most add-ons are free. But not everything that is free is also reliable. It might work today, and break tomorrow and there’s no one you can ask for help. When it comes to data, you want a stable and scalable solution that doesn’t require you to maintain and secure software on the backend.

The same applies to uploading your data using a .csv file every time you need to make a change. This might work if you want to upload a few files so you can have all your data merged, but it’s not an option when your data needs constant updates. For the latter, it would be best to connect Google Sheets to BigQuery.

Which method is best to connect Google Sheets to BigQuery?

Each option we described here will work for a different use case. Our recommendation? When you want to connect data from Google Sheets to BigQuery and are dealing with large sets of data, at scale, automating the process is always the best solution. 

And while Google intended to make your life easier with their Connected Sheets integrations, what will you do when you have to merge data from other sources?

Using Coupler.io to connect Google Sheets to BigQuery is quite straightforward and fast. The dashboard UI is friendly, and you don’t need to be a developer to figure it out. 

But that’s not the only advantage of using Coupler.io. On top of a great user experience, you also get:

• Frequent data refresh of up to every 15 mins.
• Access to pull data from more than 30 different data sources, not just Google Sheets, all using just one tool.
• An optimized workflow and all your data securely stored inside BigQuery data warehouse with minimum effort.

So, if you’re looking to automate your analytics and reporting needs, give Coupler.io a go and see the benefits for yourself!