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 April 2025 for accuracy and comprehensiveness on the state of BigQuery, Google Sheets, and connector services in 2025.*

What is Google Sheets?

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

It’s a free tool with numerous functions and built-in formulas, as well as smart access control management, making it very convenient to work with. You can create pivots and charts, use Google Docs as well, and seamlessly connect to Google Cloud storage. Additionally, the Google BigQuery connector plays a crucial role in integrating Google Sheets with BigQuery for data loading, enhancing its capabilities for both batch and real-time streaming.

Additionally, it’s likely the best spreadsheet tool available for collaboration with team members and other stakeholders, allowing for seamless interaction whenever and wherever needed.

‍

Why Google Sheets is Not Enough for Data Analysis

Google Sheets 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 may differ.

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, visualize, and share reports.

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. To upload data to BigQuery from Google Sheets, you can select 'Drive' as an option in the Create table menu. It is also important to use effective tools to import data from Google Sheets to BigQuery for seamless data integration.

What is Google BigQuery Data Warehouse

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 in BigQuery:

1. It’s very fast, 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.
8. BigQuery stores data in a columnar format optimized for analytics workloads.
9. BigQuery enables you to achieve greater data compression to improve read performance.

Uncover in-depth insights

Ultimate Data Quality Checklist

Download now

Benefits of Connecting Google Sheets to BigQuery

Integrating Google Sheets with BigQuery combines the familiarity of spreadsheets with the power of scalable analytics. 

Let's look at some of the benefits for connecting Google Sheets to BigQuery:

• Analyze Large Datasets Easily: With Connected Sheets, you can work with billions of rows from BigQuery without running SQL. It brings advanced data access directly into Google Sheets.
• Use Pivot Tables and Charts on BigQuery Data: Create pivot tables, filters, charts, and formulas in Sheets using live BigQuery data. This allows non-technical users to generate visual insights easily.
• Real-Time Data Refresh: Refresh your data source to instantly reflect the latest changes from BigQuery. This keeps your reports up-to-date without manual reloading.
• No SQL Knowledge Required: Analyze BigQuery data with spreadsheet tools you already know—no need to write SQL queries. It lowers the barrier for business users to explore data.
• Supports Collaborative Analysis: Share Connected Sheets with teams for real-time collaboration on live BigQuery data. It ensures everyone works with the same, accurate data.
• Faster Decision-Making: By combining BigQuery’s speed with the accessibility of Google Sheets, you can generate reports and insights quickly to support timely decisions. Using BigQuery's SQL querying capabilities helps in the creation of insightful visualizations and reports.

Prerequisites to Connect Google Sheets to BigQuery

Before connecting Google Sheets to BigQuery, users need to prepare their environment. This includes creating a Google Cloud project, enabling the BigQuery API, and setting up a BigQuery dataset. Users also need to ensure that they have the necessary permissions and access controls in place to manage their data. 

Furthermore, users should familiarize themselves with the BigQuery data model and understand how to optimize their data for analysis. By taking these steps, users can ensure a smooth and successful integration of Google Sheets and BigQuery data connector.

3 Ways to Move Data from Google Sheets to BigQuery

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 access Google Cloud services and manage resources, you will need a Google Cloud account.

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

Option #1: Upload Google Sheets Data to BigQuery with OWOX BI Reports Extension

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':

Note: If you are working with this extension for the first time, you'll need to select your GCP (Google Cloud Platform) Project ID.

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.

Note: If you need to create a new dataset first - you can do that in BigQuery UI.(You can find a detailed instruction about this below)

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.

Note, when you reload data to the same table, you will see the message 'Table exists, choose write action': 'APPEND' or 'TRUNCATE'

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.

Why use OWOX BI as Google Sheets to BigQuery Connector

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.

Turn Data into Actionable Insights

Auto-generate reports and dashboards from your data in Google Sheets... 100% Free!

Visualize Your Data

Option #2: Transfer Data from Google Sheets using the BigQuery Interface

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.

Note: If you entered an incorrect cell range then you may get an unexpecteddata when you query your table or your query may fail to execute.

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.

Option #3: Connect to BigQuery from Google Sheets With Connected Sheets

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.

Use Cases for Connected Sheets in Google Sheets and BigQuery

Connected Sheets enables non-technical users to explore BigQuery data directly within Google Sheets, eliminating the need to write SQL queries.

Here are a few practical use cases for using Connected Sheets:

• Business Planning: Utilize Connected Sheets to extract live sales data from BigQuery and create planning dashboards in Sheets. For example, compare product performance across regions to support inventory and budgeting decisions.
• Customer Service Monitoring: Identify high-volume complaint areas by connecting support data in BigQuery with Sheets. Calculate complaints per 10,000 customers and track changes over time.
• Sales and Revenue Reporting: Automate weekly sales reports using Connected Sheets. Pull live BigQuery revenue data and create regional dashboards sales reps can access in real time.

‍

Challenges of Using the Connect to BigQuery Option

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 the Connected Sheets Connector for BigQuery, users must have a Business, Enterprise, or Education G Suite account (an Education G Suite account would not work). These account types come with higher subscription costs than basic or standard Google Workspace accounts.
5. The maximum number of rows for results returned for pivot tables is 50,000.

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.

Best Practices for Google Sheets and BigQuery Data

Strong data management is the backbone of accurate analysis and efficient collaboration. These best practices will help you maintain clean, structured, and secure data workflows across Google Sheets and BigQuery data.

• Logical Dataset Structure: Organize your data into datasets that reflect your business domains or functions. This makes querying and managing data much easier in the long run.
• Clear and Consistent Column Names: Use readable, standardized column names that reflect the content they hold. Avoid abbreviations and update names when column usage changes.
• Optimized Data Types: Assign appropriate data types to each column, such as numbers, dates, or strings. This reduces processing time and prevents formatting errors during analysis.
• Scheduled Data Refreshes: Automate updates using scheduled queries or connectors. This ensures that your reports and dashboards always reflect real-time or near-real-time data.
• Access Control and Permissions: Use role-based access to limit editing or viewing rights to the appropriate users. This protects data integrity and minimizes accidental changes.
• Regular Data Quality Checks: Implement checks for missing values, duplicates, or outliers. Use conditional formatting or validation rules to flag issues early.
• Data Documentation : Maintain a shared reference with definitions for each field and how the data is sourced. This reduces confusion and helps onboard new collaborators quickly.

Conclusion

In conclusion, connecting Google Sheets to BigQuery offers a powerful solution for data management. By leveraging the advanced analytics capabilities of BigQuery and the familiarity of Google Sheets, users can unlock new insights and make data-driven decisions with ease. 

Whether you’re a business analyst, data scientist, or simply a user looking to gain more insights from your data, connecting Google Sheets to BigQuery is a step in the right direction. With Connected Sheets, you can analyze petabytes of data, create pivot tables and charts, and refresh your data in real-time, all within the familiar interface of Google Sheets.