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.

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.

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.

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.

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

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.

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.

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:

If you are working with this addon for the first time, you will need to provide 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.

Click Start Upload and your statistics will be uploaded to Google 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 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.

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

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:

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:

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:

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

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!

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.

Articles:

• Why Google BigQuery is the perfect data lake for marketing.
• How to send data from Google BigQuery to Google Sheets and Excel.
• Overview of the main Google BigQuery features — practice writing requests for marketing analysis.
• Get started with Google Sheets tutorial.
• Quickstart using the Cloud Console.

Video:

• Google Forms Tutorial For Beginners playlist.
• Google Sheets Intermediate tutorials.
• Google Sheets Advanced tutorials.
• Google Sheets — Tips and Tricks.
• What is BigQuery?
• BigQuery in a minute.
• BigQuery Web UI Quickstart.
• Intro to Connected Sheets and BigQuery.
• Using Connected Sheets playlist on Youtube.