Google Sheets to Google BigQuery — Move Your Data

If you are looking for a convenient way to transfer data from and to Google Sheets and Google BigQuery this article is for you. Learn how you can build any report or graph in Google tables based on data from GBQ with no need to upload CSV files or use third-party paid services.

Table of contents

What are Google Spreadsheets

It’s difficult to find a marketer who doesn’t work with Google Cloud platform products. And of course, all marketers use Google Spreadsheets to one degree or another. Free service, with many functions and built-in formulas is very convenient to work with. In addition, it’s convenient to use to work with colleagues anywhere and whenever.

Google Spreadsheets

Useful links

When it becomes necessary to incorporate a data warehousing tool

Thanks to its advantages, Google Sheets are simply indispensable in the work of a marketer for small companies. If you don’t have much information for analysis and it’s required by only a few people, then it’s difficult to choose a more convenient service for work.

However, as the company grows and data volumes increase (including the use of data from different sources), restrictions appear in the spreadsheet work. At this point, you already need to connect the data warehouse like Google BigQuery.

Overview of the main Google BigQuery features — practice writing requests for marketing analysis.

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 without sampling and without restrictions.

It’s one of the most popular, cloud-based services, and definitely one of the most convenient for marketers. Why for them? Because Google is one of the giants of the market, with its products like Google Analytics and Google Ads. And the built-in storage of BigQuery in the line of cloud services means seamless, native integration. Simply put, you don’t need to waste time finding connectors, you have everything working out of the box.

Google BigQuery

Among other benefits of storage:

  • fast and simple
  • continuous support and development by developers
  • out-of-the-box SQL query sets
  • machine learning (ML) and artificial intelligence (AI)
  • doesn’t require servers

Before you can upload data from BigQuery, you must collect and merge it into data storage. If you haven’t done so yet, use OWOX BI for this task.

Useful links

Why Google BigQuery is the perfect data lake for marketing.

How to move data from Google Sheets to Google BigQuery

If you want to use all the advanced analytics capabilities for marketing, sooner or later you’ll need to transfer information from Google Sheets to the data warehouse. For example, you can upload offline order statistics to cloud storage to build a ROPO analysis. Let’s look at ways on how you can download the information you need quickly and easily.

Using OWOX BI addon

7 reasons to choose OWOX BI add-on as your BigQuery to Sheets/Sheets to BigQuery connector

  1. The addon is completely free, you only pay for processing your data in GBQ.
  2. You can quickly create reports and graphs with any amount of data directly in Google tables.
  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. Addon 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.
DOWNLOAD THE ADDON

How to transfer data from Google Sheets to Google BigQuery

Install the addon from OWOX BI into your Chrome browser. Go to your data table and select OWOX BI BigQuery Reports — Upload data to BigQuery:

OWOX BI BigQuery Reports

If you are working with this addon for the first time, you will need to provide access to your BigQuery:

access to your BigQuery

A window opens in which you need to select the project and dataset in GBQ and come up with a name for the table you want to load the figures to. 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.

set destinations and schema

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

Using BigQuery Data Connector

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 the new connector, you need to upgrade your account. This native connector isn’t available to all users, but exclusively to customers who purchased G Suite Enterprise and G Suite Enterprise for Education.

To download the needed data from BigQuery, you need to:

  • Open the spreadsheet you work with.
  • Click the Data menu, choose Data connectors line, and click on Connect to BigQuery.
  • Choose the project and a table you want to get data from.
  • Download the dataset by clicking on Connect and then Start analyzing.
Connected Sheets

Useful links

bonus for readers

12 Google Analytics reports

Download now

How to get data from BigQuery to Google Sheets automatically

Using OWOX BI addon

Open the table and select OWOX BI BigQuery Reports — Add a new report:

OWOX BI BigQuery Reports

Next, in the right sidebar, specify the GBQ project you want to see in the report. If you have already created the SQL query you want in 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:

auto-suggest in the Query Editor

If you want to create a new query, scroll through the list of queries to the end and click the Add new query line, enter text, and click Save & Run:

Add new query line

Select dynamic parameters if they were specified in your SQL query. In our example, on the screenshot below, the parameters are reporting period and category name. Start the query by clicking Add & Run:

Start the query

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

query result

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

How to send data from Google BigQuery to Google Sheets and Excel.

How to configure automatic report updates in Google Sheets

With the OWOX BI addon, 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 — Schedule reports from the Add-Ons tab:

Schedule reports

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.

schedule reports

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

Tasks OWOX BI BigQuery Reports can be used for — examples from our clients

  1. Perform cohort analysis at Google Sheets to calculate income, cost of attracting each cohort, and optimize the budget.
  2. Segment users by behavioral characteristics and send each segment unique advertising offers.

Our clients
grow 22% faster

Grow faster by measuring what works best in your marketing

Analyze your marketing efficiency, find the growth areas, increase ROI

Get demo

FAQ

Expand all Close all
  • When does it become necessary to use a data warehouse?

    If you don’t have much information for analysis and it’s required by only a few people, then it’s difficult to choose a more convenient service for work than Google Sheets.
    However, as the company grows and data volumes increase (including the use of data from different sources), restrictions appear in the spreadsheet work. At this point, you already need to connect the data warehouse like Google BigQuery.
  • How to move data from Google Sheets to Google BigQuery?

    If you are looking for a convenient and free way to move data from Google Sheets to Google BigQuery, use the addon OWOX BI BigQuery Reports. With it you can build any report or graph in Google tables based on data from GBQ. There’s no need to upload data to CSV files or use third-party paid services.
  • Can I use Connected Sheets, the new BigQuery data connector?

    To use the new connector, you need to upgrade your account. This native connector isn’t available to all users, but exclusively to customers who purchased G Suite Enterprise and G Suite Enterprise for Education.