How To Connect Google Sheets to BigQuery

If you are looking for a convenient way to transfer data from and to Google BigQuery, this article is for you. Learn how you can build BigQuery Tables with Google Sheets data, as well as any visual reports, pivots and charts in Google sheet tables based on data from GBQ and how to avoid a common 50,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, Spreadsheets and connector services in 2024.

It’s difficult to find a marketer, analyst or any business user who doesn’t work with Google products. And, of course, one of the most common is Google Sheets to one degree or another. Free tool, with many functions and built-in formulas, it is very convenient to work with. In addition, it’s convenient to colaborate with colleagues and team members whenever and wherever.

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

If you don’t have a lot of information for analysis, and that information is required by only a few team members, then it’s difficult to choose a better tool to build, basically, any reports.

However, as the company grows and data volumes increase (including the use of data from different sources), spreadsheets is still one of the best tools for analyzing data, but not storing the data or handling data preparations for reporting.

At this point, you might need to implement a data warehouse solution, just like Google 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.

It’s one of the most popular relationship management database system out there and definitely one of the most suitable for marketing analytics. Why for them? Because of other Google products such as Google Analytics 4, Google Ads, Google Search Console and, at the end of the day, Google Workspace. And the built-in BigQuery data storage in the line of serverless cloud services means seamless, native integration. Simply put, you don’t need to waste time finding external data connectors if you don’t want to. You have everything in place available out of the box.

Among other benefits of storage:

1. fast and simple
2. continuous support and development by developers
3. out-of-the-box SQL query sets
4. machine learning (ML) and artificial intelligence (AI) capabilities
5. doesn’t require owning your server

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 information from Google Sheets to the data warehouse.

For example, you might want to encounter on the affiliate costs in you digital marketing performance report. Or you can upload offline order data to the cloud storage to build a ROPO analysis. Or if you want to manage custom channel grouping in a spreadsheet. Let’s look at ways how you can upload this information you need quickly and easily.

To connect Google Sheets to BigQuery, you can use one of the following methods:

7 reasons to choose OWOX BI BigQuery Reports Extension for Google Sheets as a two-way BigQuery to Sheets / Sheets to Google BigQuery connector:

1. The Extension starts free.
2. You can quickly create reports and charts with any amount of data directly in Google Sheets.
3. You can configure reports to be updated automatically at the frequency you want.
4. A convenient query editor with an autosuggest saves time when creating a report. In addition, you can pre-specify dynamic parameters in a query that even people who are unfamiliar with SQL can use.
5. It will be convenient for you to share reports with your colleagues — you only need to allow them access to the document. And employees who have access to your project in GBQ will be able to manage the queries used in the report. Simultaneously, you can control the change history of each request.
6. The extension works both ways, meaning that you can also send data from Sheets to GBQ.
7. It’s safe and secure. We only use Google’s official service APIs. All data is transferred to your Cloud Platform project that you control access to.

Install the extension from Google Workspace Marketplace. Open new or existing Google Sheet. Go to ‘Extensions’ and select ‘OWOX BI BigQuery Reports’ — ‘Upload data to BigQuery’:

If you are working with this extension for the first time, you will need to provide access to your Google Cloud Platform Project.

A window opens. And you’ll need to select the project in GBQ, the dataset, and come up with a name for the table in which you want to load the figures.

Then, check the boxes for the fields whose values you want to import.

All fields are ‘STRING’ by default, so you have to replace the data types according to the context. For example, for numeric identifiers, the type is ‘INTEGER’, for prices, the type is “FLOAT," etc.

For example, the cost data fields must be set as follows:

Click Start Upload, and your statistics will be uploaded to BigQuery.

Note, when you reload data to the same Google BigQuery table, you will see the message “Table exists, choose to write action”. In this case, select the “TRUNCATE” option.

Google has updated its data connector from Google Sheets, and it’s now called Connected Sheets. It helps you build queries and analyze datasets using familiar spreadsheet tools and operations.

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

1. Open the spreadsheet you work with.
2. Click the Data menu, choose the Data connectors line, and click on Connect to BigQuery.
3. Click Get connected.
4. Select a Google Cloud project that has billing enabled. (If you don’t find any projects, you need to add one.)
5. Choose a table or view. (You can pick from any company table you have access to or from public data sets.)
6. Upload the table by clicking on Connect.

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

• Data Volume Limitation: One of the primary limitations of using the Sheets Connector to link Google Sheets with BigQuery is the data volume constraint. The process cannot handle more than 50,000 rows of data in a single spreadsheet. This limitation can be a significant hurdle for users looking to analyze large datasets, as it restricts the amount of data that can be directly manipulated and visualized within Google Sheets.
• Account Type Requirement: Another challenge is the requirement for a specific type of Google Workspace account. To utilize the Sheets Data Connector for BigQuery, users must have a Business, Enterprise, or Education G Suite account. These account types come with higher subscription costs compared to the basic or standard Google Workspace accounts. This requirement can pose a financial barrier for small businesses, individual users, or organizations that do not already subscribe to these higher-tier Google Workspace plans.

These challenges highlight the need for users to carefully consider their data analysis needs and account capabilities before relying on the Sheets Connector as the primary method for integrating Google Sheets with BigQuery.

Now let’s flip this around and talk about building reports in Google Sheets based on the data already available in Google BigQuery.

Connecting Google BigQuery to Google Sheets is a strategic move for comprehensive analytics in Google Sheets on top of the vast data storage and processing power of BigQuery. Data is stored in BigQuery. But they live when the business user can play around, aren’t they?

This BigQuery to Sheets data integration enables a smooth data transfer between BigQuery's powerful data warehouse and the user-friendly spreadsheet interface of Google Sheets.

Here are several reasons why this connection is beneficial:

• Enhanced Data Analysis and Visualization: Google Sheets provides a suite of analytical tools, including pivot tables, charts, and formulas, which can transform raw data into insightful visual representations. By connecting BigQuery to Google Sheets, users can apply these tools to large datasets stored in BigQuery, making it easier to interpret and share findings.
• Real-time Data Updates: Maintaining a live connection between BigQuery and Google Sheets ensures that the data in Sheets is always current. This is particularly useful for dynamic datasets where new data is constantly being added or updated, such as Google search trends. Users can analyze up-to-date data without manual imports, enabling timely decision-making.
• Scalability and Efficiency: BigQuery's ability to handle massive datasets complements Google Sheets' analytical tools, allowing for the analysis of large volumes of data that would be impractical to process directly in Sheets. This combination supports scalable analytics workflows without sacrificing performance.
• Accessibility and Collaboration: Google Sheets is widely accessible and supports real-time collaboration, making it an ideal platform for sharing insights and working together on data analysis projects. Connecting it to BigQuery allows teams to collaborate on the same dataset, ensuring consistency and efficiency in data-driven projects.
• Leveraging Google's Ecosystem: For organizations already using Google's suite of products, integrating BigQuery with Google Sheets streamlines workflows and maximizes the value of their existing tools. This integration simplifies the process of extracting, analyzing, and reporting data, all within the familiar interface of Google Sheets.
• Case Study Applications: In practical terms, connecting BigQuery to Google Sheets can significantly enhance data-driven decision-making processes. For instance, analyzing Google search trends data in Sheets can help businesses identify popular trends, forecast future search behaviors, and optimize marketing strategies accordingly. This real-time analysis can uncover valuable insights that drive strategic business decisions.

In summary, connecting BigQuery to Google Sheets unlocks a powerful combination of data processing and analysis capabilities. It enables businesses and analysts to manage and analyze large datasets efficiently, derive actionable insights, and foster collaborative data-driven decision-making.

Open a new Google Sheet and select OWOX BI BigQuery Reports Extension — Add a new report:

Next, in the right sidebar, specify the GCP project you want to run your report from. If you have already loaded the SQL query for this report, select it from the drop-down list.

If you want to edit the query, click Edit and adjust the text. To do this, you can use auto-suggest in the Query Editor, which offers syntax highlighting, auto-additions, versioning, query validation, and preliminary estimation of the amount of data processed:

If you want to create a new Query just click on ‘+ Add new Query’, enter or write your SQL, and click Add & Run.

Select dynamic parameters if they were specified in your SQL query. Here is the syntax example:

In our example, on the screenshot below, the parameters are reporting period and category name. Start the query by clicking Add & Run:

The data is then processed in BigQuery, and the query result is automatically imported into Sheets on a separate sheet:

Next, you can visualize the information you want in order to create pivot tables, graphs, charts, and so on.

With the this BigQuery Reports Extension, you can enable scheduled query execution to avoid manually running calculations whenever data is needed.

To do this, open the required report and select OWOX BI BigQuery Reports — ‘Scheduled Refresh’ from the ‘Extensions’ tab:

Specify how often to update the statistics in the report (hourly, daily, weekly, or monthly) and set the time to run the query. If you want to receive an email alert when the report is updated, check the box. Save the settings.

Your report will then be automatically updated at the specified time and frequency!

Connecting Google Sheets to Google BigQuery using OWOX BI offers several benefits that enhance data analysis, reporting, and decision-making processes. Here are the key advantages:

• Data Import: OWOX BI enables the data import from Google Sheets into BigQuery. No need for manual data transfer and risking errors.
• Data Freshness: By connecting Google Sheets to BigQuery, users can analyze data when they need, getting timely and informed decisions made.
• Cost-Efficiency: Advanced scheduler ensures you process the data only when you need it, saving on data processing costs on BigQuery side.
• Data Consolidation: BigQuery is perfect for blending data from multiple sources, tables or datasets. And you don’t need to prepare those datasets separately. Simply query all the data you need from the OWOX BI BigQuery Reports Extension and analyze comprehensive data.
• Collaboration: Since Google Sheets is widely used for collaborative work, integrating it with BigQuery through OWOX BI ensures that collaborative efforts are seamlessly extended into the realm of big data analysis.
• Security: BigQuery, as well as OWOX BI offers comprehensive access control and security functionalities, guaranteeing the safeguarding of sensitive data imported from / to Google Sheets.
• Integrity: By automating the process, the likelihood of human error is minimized, thereby enhancing the reliability and accuracy of the data within BigQuery for analysis purposes.
• Scalability: Google BigQuery can handle massive datasets much more efficiently than Google Sheets alone. This scalability ensures that as your data grows, you can still analyze it efficiently without performance issues.

By leveraging OWOX BI to connect Google Sheets with BigQuery, organizations can significantly enhance their data analytics capabilities, making it easier to derive actionable insights and make data-driven decisions.

1. Run Cohort analysis in Google Sheets. You can calculate the revenue as well as the cost of attracting each user cohort and optimize the budget.
2. Segment users by behavioral characteristics and send each segment unique advertising offers.

1. Why Google BigQuery is the perfect data lake for marketing.
2. How to send data from Google BigQuery to Google Sheets and Excel.
3. Overview of the main Google BigQuery features — practice writing requests for marketing analysis.