Sign up
Sign in
Sign up
Sign in
OWOX
Follow
Towards Data Science
—
1
Listen
Share
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 Sheets tables based on data from GBQ and how to avoid 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. A free tool, with many functions and built-in formulas, it is very convenient to work with. In addition, it’s convenient to collaborate with colleagues and team members whenever and wherever.
Ok. Google Sheets is a very simple and convenient tool for anybody within a small company, as well as for a 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 are 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 systems 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:
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 the affiliate costs in your 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 the 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:
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 automatically ‘STRING’, but we recommend replacing the types with those that match the field contents. For example, for numeric identifiers, the type is ‘INTEGER’, for prices, the type is “FLOAT,” etc.
Click Start Upload, and your statistics will be uploaded to BigQuery.
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.
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:
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:
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:
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:
{dimension default=”CategoryName” type=”input”}
{startDate default=”20150422" type=”input”}
{endDate default=”20150822" type=”input”}
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 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:
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
Towards Data Science
Help
Status
About
Careers
Press
Blog
Privacy
Terms
Text to speech
Teams
