Google BigQuery ↔ Google Sheets connector

959.24 Kb

If you are looking for a convenient and free connector for Google BigQuery and Google Sheets, congratulations - you have found it :) With the addon OWOX BI BigQuery Reports, you can build any report or graph in Google tables based on data from GBQ. You will no longer need to upload data to CSV files or use third-party paid services.

In this article, you will learn:

Table of contents

Opportunities and advantages of OWOX BI BigQuery Reports

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.

If you want to automate data collection in BigQuery and build marketing reports with any parameters, order a demo and we will show how OWOX BI will help solve your problems.


Once you combine and process data in GBQ, you can visualize it in Google tables. The Chrome browser addon from OWOX BI allows you to manage SQL queries and import query results directly into Sheets.

7 reasons to choose OWOX BI add-on as your BigQuery ↔ Sheets 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.

    Find out the real value of campaigns

    Automatically import cost data to Google Analytics from all your advertising services. Compare campaign costs, CPC, and ROAS in a single report.

    How to upload data from Google BigQuery to Google Sheets

    Download and install the addon from OWOX BI into your Chrome browser. Then open the table and select OWOX BI BigQuery Reports — Add a new report:
    Add a new report

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

    access to your BigQuery
    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:
    Add & Run
    The data is then processed in BigQuery and the query result is automatically imported into Google 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 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 (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!

    How to transfer data from Google Sheets to Google BigQuery

    In addition, the free OWOX BI connector allows you to transfer data directly from Google Sheets to Google BigQuery. For example, you can upload offline order information to cloud storage to build a ROPO analysis.

    Go to your data table and select OWOX BI BigQuery Reports — Upload data to BigQuery:

    Upload data to 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 data 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.
    destination scheme
    Click Start Upload and your data will be uploaded to Google BigQuery.

    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.