How To Connect Google Sheets to BigQuery: 2 Ways

Data Integration Google BigQuery Google Sheets Tips

icon Get in-depth insights

Modern Data Management Guide

icon Get in-depth insights

Modern Data Management Guide

If you are looking for a convenient way to transfer data from Google BigQuery into Sheets or upload a spreadsheet file as a BigQuery table, 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 Sheets tables based on data from GBQ and how to avoid common 50,000 rows limitations, file sizes, or using CSV files.

Sheets BigQuery Connector

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

What are Google Sheets

It's difficult to find a marketer, data analyst, or any business 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 management, and it is very convenient to work with. In addition, it's probably the best spreadsheet tool out there to collaborate with team members as well as other stakeholders whenever and wherever.

what are google sheets

When Google Sheets is not enough

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.

Dive deeper with this read

Google BigQuery: The Best Marketing Data Warehouse

Image for article: Google BigQuery: The Best Marketing Data Warehouse

What is 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 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.

what is google bigquery

Here are a few of the other benefits of storing and processing data 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.

How to Upload Data from Google Sheets to BigQuery

If you want to use all the advanced data analysis 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 Google Sheet. Let's look at ways how you can upload the information you need quickly and easily.

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

Option #1: OWOX BI BigQuery Reports Extension

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

OWOX BI BigQuery Reports Extensions
  1. Create a new table or open an existing Spreadsheet.

  2. Go to ‘Extensions' and select ‘OWOX BI BigQuery Reports' — ‘Upload data to BigQuery':

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

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

Note: If you need to create a new dataset first - you can do that in BigQuery UI.

  1. Then, check the boxes for the columns you want to import.

  2. 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.

  1. 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'

  1. Great. Your Data is now Uploaded to the BigQuery table and you can click the link to check the injested data in BigQuery UI right away.

Why use OWOX BI as Sheets to BigQuery Connector

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

  1. This Extension starts free.

  2. You can quickly create reports and charts with any amount of data directly from Sheets (avoiding 50k rows limitation).

  3. You can configure reports to be updated automatically at the frequency you want.

  4. A convenient query editor allows you to save and share queries with your team members so they can replicate your report.

  5. You can pre-specify dynamic parameters in a query that even people who are unfamiliar with SQL can apply to filter the query result.

  6. 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.

  7. The extension works both ways, meaning that you can also send data from Sheets to GBQ.

  8. It's safe and secure. We only use Google's official service APIs. All data is transferred to your Cloud Platform Project which you control access to.

table

Seamless BigQuery Integration in Sheets

Get real-time, automated, and deeply insightful reporting at your fingertips. Connect BigQuery, run reports, and prepare dashboards in your favorite Google Sheets

Enhance Your Sheets Now

Option #2: Connect to BigQuery

Google has updated its BigQuery Data Connector from Sheets. It's designed for anybody to upload Google Sheets to BigQuery tables

Important! To use a connected sheets connector, you might need to upgrade your account. As of 2024, this native connector isn't available to all users, but to customers who purchased G-Suite Enterprise Standard and Enterprise Plus; Education Standard and Education Plus; Enterprise Essentials and Enterprise Essentials Plus.

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

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

  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 ID with billing enabled and click on it.


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

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

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).

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

Dive deeper with this read

OWOX BI BigQuery Reports Extension vs. Connected Sheets: Deciding the Best Fit for Your Reporting Needs

Image for article: OWOX BI BigQuery Reports Extension vs. Connected Sheets: Deciding the Best Fit for Your Reporting Needs

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

  • 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.

  • 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.

  • Error Handling: There is no error reporting or handling. That's why it's difficult to troubleshoot and resolve issues during the upload process.

  • Volume Limitation: One of the primary limitations of using Connected Sheets to link Google Sheets with BigQuery is the data volume constraint. The process cannot handle more than 50,000 rows 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 by the end-user and visualized within Google Sheets.

  • Account Type Requirement: 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 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 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.Now let's flip this around and talk about building reports in Sheets based on the data already available in Google BigQuery.

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.

Why Connect BigQuery to Google Sheets

Connecting Google BigQuery to Sheets is a strategic move for comprehensive analytics in Google Sheets on top of the vast data storage and processing power of BigQuery. Data stored in BigQuery. But they live when the business user can control access and 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 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 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.

How to Get Data from BigQuery to Google Sheets Automatically

  1. Open a new or existing Spreadsheet and select OWOX BI BigQuery Reports Extension — 'Add a new report':

BigQuery Google Sheets Connector
  1. Next, in the right sidebar, specify the Google Cloud Account you want to run your report from.

  2. If you want to create a new Query just click on Query Title -> then ‘+ Add new query...'.

  1. Add your SQL query, give it a title and click 'Save & Run'.

Note: If you or any of your team members have already loaded the SQL query for your report, you can just select it from the drop-down menu.
If you want to edit an existing query, click Edit and adjust the code.
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.

5. Add dynamic parameters to your report. Here is the syntax example:

{event_name default="page_view" type="input"}
{start_date default="20210101" type="input"}
{end_date default="20210110" type="input"}

In our example, on the screenshot below, the parameters are event_name, start_date, and end_date. To apply dynamic parameters, just click 'Save & Run'.

  1. The data is then processed in BigQuery, and the query results are automatically imported into Sheets on a new sheet, that is renamed automatically to the Query Title.

  1. Finally, you can visualize the query results: create pivot tables, graphs, and charts.

How to configure automatic report updates in Google Sheets

With this BigQuery Reports Extension, you can enable automated refreshes for query results to avoid manually running updates when you or another stakeholder needs data.

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

Specify how often you want to update the data in your report (which time of the day, what days of the week, and which weeks of the month) and set it just once to run the query and all of your reports will be saved automatically.
If you want to receive an email alert when the report is updated, check the appropriate box. Save the settings.

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

BigQuery Reports Extension in Action

  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.
pipeline

Simplify BigQuery Reporting in Sheets

Easily analyze corporate data directly into Google Sheets. Query, run, and automatically update reports aligned with your business needs

Simplify Reporting Now

Articles

FAQ

Expand all Close all
  • How do I transfer data from Google Sheets to BigQuery?

    For transferring data from Google Sheets to BigQuery, you can use OWOX BI BigQuery Extension or Connect to BigQuery. Configure the table schema by specifying the data type, and selecting the project_id, dataset_id, and table name.
  • How do I transfer data from BigQuery to Google Sheets?

    Data transfer from Google Sheets to BigQuery can be accomplished through the Google Sheets to Google Bigquery Connector. This involves selecting a data set in BigQuery, using the "Create Table" option, selecting Google Drive as the source, and specifying your sheet's URL. Choose the appropriate file format and schema settings to complete the transfer.
  • How do I create a table in BigQuery from Google Sheets?

    To create a table in BigQuery from a Google Sheet, navigate to the BigQuery interface, select "Create Table" button, choose "Drive" as the source, and input the Google Sheet's URL. Select the file format as Google Sheets, configure the schema either manually or by auto-detection, and finalize by naming and creating the table.
  • How do I create a Google BigQuery dataset?

    Creating a dataset in Google BigQuery involves logging into the BigQuery console, selecting your project, and clicking "Create Dataset." You'll need to provide a unique dataset ID, choose a data location, and set data expiration terms if necessary. Once configured, click "Create Dataset" to finalize the setup.
  • How to connect Google Sheets to BigQuery?

    To connect Spreadsheets to BigQuery, use the BigQuery Data Connector in Google Sheets. Open your sheet, click on "Data" > "Data Connectors" > "Connect to BigQuery," and follow the prompts to select your BigQuery project and dataset. This allows you to query BigQuery data directly from within Google Sheets.
  • How to connect BigQuery to Google Sheets?

    Connecting BigQuery to Google Sheets can be achieved through the Google Sheets Data Connector. Start by opening a Google Sheet, navigate to "Data" > "Data Connectors" > "Connect to BigQuery." Follow the instructions to link your BigQuery project. This setup enables direct querying and analysis of BigQuery data within Google Sheets.
  • Is it secure to import sensitive data using the BigQuery Sheets Connector?

    Yes, the BigQuery Sheets Connector uses Google Cloud's secure authentication framework, ensuring that only authorized users can access sensitive data. Additionally, all imported data is encrypted both in transit and at rest for maximum security.
  • How do I install the BigQuery Sheets Connector?

    To install the BigQuery Sheets Connector, simply navigate to the Google Sheets Extensions menu, click Add-ons, then 'Get Add-ons'. Search for "OWOX BI BigQuery Reports" and click 'install'. Once it's complete, you should see a new 'BI BigQuery Reports' Add-on in the Extensions menu in your Google Sheets toolbar.
  • What is the BigQuery Sheets Connector?

    The BigQuery Sheets Connector is a tool that allows users to import data from Google Sheets directly into BigQuery, enabling data analysts to easily access crucial information and perform advanced analysis.

icon Get in-depth insights

Modern Data Management Guide

icon Get in-depth insights

Modern Data Management Guide