How To Connect Google Sheets to BigQuery: 3 Ways

If you are looking for a convenient way to transfer data from Google Sheets into BigQuery, this article is for you. Learn how you can build BigQuery Tables with Google Sheets data.

We also have another article covering 3 ways to connect BigQuery to Google Sheets for building awesome reports so you can build visual reports on top of your corporate data, pivots, and charts in Sheets tables based on data from GBQ and how to avoid common 50,000-100,000 rows limitations, file sizes, or using CSV files.

Note: This post was originally published in November 2019 and was completely updated in March 2024 for accuracy and comprehensiveness on the state of BigQuery, Google Sheets, and connector services in 2024.

It's difficult to find a marketer doing data analysis, a data professional, or any business spreadsheet user who doesn't work with Google products. And, of course, one of the most common is G Sheets.

It's a free tool, with many functions and built-in formulas, smart access control management, and it is very convenient to work with. You can create pivot tables, and charts, use Google Docs as well, and seamlessly connect to Google Cloud storage.

In addition, it's probably the best spreadsheet tool out there to collaborate with team members as well as other stakeholders whenever and wherever.

Google Sheets Spreadsheet is a very simple and convenient tool for anybody within a small company, as well as for a huge enterprise to perform analysis. However, the use cases might be different.

If you don't have a lot of information for analysis, and the data you use is required by only a few team members, then it's difficult to find a better tool to build, basically, any reports, visualize, and share them.

However, as the company grows and data volumes increase (including the use of data from different sources), spreadsheets are still the best tools for ad-hoc reporting, to analyze data, but it's not suitable for storing the data (as sheet and cell range is getting to the point where PIVOT table populates slowly) or handling data preparations for reporting (like JOINs or VLOOKUPs) and that's exactly where modern data warehouse would help you streamline the data processes.

At this point, you might need to implement a data warehouse solution, just like BigQuery.

Google BigQuery cloud storage allows you to collect data from different sources, process it in seconds using SQL queries, and build reports with any metrics you need avoiding any restrictions such as data sampling in GA4 or simply slow report processing time in Sheets.

It's one of the most popular relationship management database systems out there and definitely one of the most suitable for marketing and digital analytics. Why? Because of the native integrations with other Google products such as GA4, Google Ads, Search Console, and at the end of the day Google Chrome and Workspace Marketplaces with thousands of apps there.

Simply put, you don't need to waste time finding external pipelines if you don't want to. You have a lot of out-of-the-box Google BigQuery connectors made by Google.

Here are a few of the other benefits of storing and processing data in BigQuery:

1. It's very fast, and reliable and processes gigabytes of data in seconds;
2. Continuous improvement and new features added by developers (over 155 new things announced for 2024);
3. Out-of-the-box SQL queries;
4. Strong machine learning (ML) and artificial intelligence (AI) capabilities;
5. Fully serverless and doesn't force you to own your server.
6. Sage, secure with control access management.
7. BigQuery offers free credits to get started.

If you want to use all the advanced analytics capabilities for marketing, finance and any other industry, sooner or later, you'll have a case when you need to transfer the data from Google Sheets to a data warehouse like BigQuery.

To connect data stored in Google Sheets to BigQuery, you can use one of the following sheets data connector methods:

Step 1: Install the extension from the Workspace Marketplace. You can type in BigQuery and just select the first one:

Step 2: Open a Google Sheet Spreadsheet you want to upload to BigQuery. (or upload a csv to Google Drive)

Step 3: Go to ‘Extensions' and select ‘OWOX BI BigQuery Reports' — ‘Upload data to BigQuery':

Step 4: A new pop-up window opens. In the destination section - you need to select the project ID, and the dataset ID, and come up with a name for the table in which you want to load your single spreadsheet.

Step 5: Then, check the boxes for the columns you want to import.

Step 6: Next, we need to specify the data type for the fields. By default, every column has a ‘STRING', so you have to replace the types of data according to your business context.For example, for numeric identifiers, the type is ‘INTEGER' data type, for prices use the 'NUMERIC' data type or 'FLOAT', for dates - the 'DATE' data type, etc.

Step 7: Click Start Upload, and your document will be uploaded to BigQuery.

Step 8: Great. Your Data is now Uploaded from Google Sheets to BigQuery table and you can click the link to check the ingested data in BigQuery UI right away.

Here are the top 4 reasons to choose OWOX BI BigQuery Reports Extension as a two-way Google Sheets to BigQuery connector:

1. This Upload is free.
2. Table schema auto-detection.
3. The extension works both ways, meaning you can send data from BigQuery to Sheets as well.
4. It's safe and secure. Only Google's official service APIs. All data is transferred to your Cloud Platform Project which you control access to, meaning your data never stays in the wrong hands.

Here are the 4 reasons to use OWOX BI as the BigQuery to Google Sheets Connector:

1. You can quickly create reports and charts with any amount of data directly from Sheets (avoiding any of the row limitations).
2. You can configure reports to be updated automatically at the frequency you want.
3. A convenient query editor allows you to save and share queries with your team members so they can replicate your report.
4. You can pre-specify dynamic parameters in a query that even people who are unfamiliar with SQL can apply to filter the query result.
5. You can share the reports with stakeholders — just share the document. But also, the employees who have access to your project in GBQ will be able to re-use the queries you built. Plus, you can control the edit history for SQL queries.
6. The extension works both ways, meaning that you can also send data from Sheets to GBQ.

Step 1: Navigate to your BigQuery account: Open your BigQuery console.

Step 2: Open the drop-down menu at the top of the BigQuery interface where the project is specified. [Skip this if you have the project prepared]

Step 3: Click on the 'New Project' button and create a new project. [Skip this if you have the project prepared]

Step 4: Give your project a name, select Billing Account, desired location, and click 'Create'. [Skip this if you have the project prepared]

Step 5: Select 'SELECT PROJECT' on the screen.

Step 6: Click on 3 dots near your project ID and ‘Create Dataset' on the right-hand side of your screen.

Step 7: Name your dataset (e.g., ‘SheetsDemo’), select Location Type, and click 'Create dataset'.

Step 8: Find your dataset, click on it, and tap on ‘CREATE TABLE’.

Step 9: Enter a new table name (e.g., ‘SheetsDemoTable’, and where it says ‘Create new table from’ open the drop-down.

Step 10: Select ‘Drive’ option

Step 11: Grab the URL from your Google Drive Doc.

Step 12: Enter the Drive URI, and click on the drop-down for File Format.

Step 13: Select ‘Google Sheet’ as a File format.

Step 14: Specify the list and the cell range in Google Sheets to determine where the data comes from for the new table in BigQuery. Ignore the heading row when doing this.

Step 15: Now it’s time to define a schema for your table based on your Google Sheets data types.

Step 16: Click on the "Add field" button to start defining the schema for your table.

Step 17: Input the name of your field, choose the data type, and add the field to the schema.

Step 18: Repeat the process of adding fields to complete the schema.

Step 19: Create the table by clicking the 'Create table' button.

Your table is created, now let’s check whether your data is successfully imported.

Step 20: Select the table and use the 'QUERY’ button to start querying.

Step 21: Write a SQL statement: SELECT * FROM {project_id.dataset_id.table_name} and click 'Run'

Step 22: Handle any errors; if necessary, adjust your table or schema and recreate the table.

Optional step 23: Save your query results by selecting 'SAVE RESULTS' and choosing the save option.

Step 24: Store the results in BigQuery by selecting a table and clicking 'Save'.

Step 25: Preview your saved data by clicking on the 'Preview' tab.

Google has updated its Sheets to BigQuery Connector from Sheets. It's now called Connected Sheets and is designed for anybody to upload Google Sheets to BigQuery tables

To upload the required data to BigQuery, you'll need to:

Step 1: Open the spreadsheet you want to upload into BigQuery.

Step 2: Click the Data menu, choose the Data connectors line, and click on Connect to BigQuery.

Step 3: Click Get Connected.

Step 4: Select a Google Cloud project ID with billing enabled and click on it.

(If you don't find any projects, you need to add one)

Step 5: Choose a dataset (including the public datasets).

Step 6: Choose a table or view and click Connect. (You can pick from any company table you have access to or from a public dataset).

Step 7: Now your data is connected. You now can create connected charts, pivot tables, and functions that work on the entire dataset or extract a subset of the raw data directly into Google Sheets.

While Connected Sheets as a native Google Sheets connector offers a lot of advantages, there are notable challenges that users might encounter when using it:

1. Size Limitation: There is a limit on the size of the data that can be uploaded at one time. If your document contains a large amount of information, you might need to break it into smaller chunks.
2. Formatting Issues: There is no auto-detect schema here. Some fields in Google Sheets may not correspond directly to BigQuery data types, and you don't have any options to influence this while uploading the data. This leads to errors, loss, or poor data quality during the transfer.
3. Error Handling: There is no error reporting or handling. That's why it's difficult to troubleshoot and resolve issues during the upload process.
4. Another challenge is the need for a specific type of Workspace account. To use Connected Sheets Connector for BigQuery, users must have a Business, Enterprise, or Education GSuite account (an Education G Suite account would not work). These account types come with higher subscription costs than basic or standard Google Workspace accounts.

These challenges highlight the need for users to carefully consider their needs of analyzing data, as well as BigQuery account capabilities before relying on the Sheets Connector as the primary method for integrating Google Sheets to BigQuery.