How to connect BigQuery to Google Sheets: three ways

Way 1: save query results to Google Sheets

The BigQuery console can write a result straight into a new spreadsheet. From Google’s export query results page (checked October 2026):

1. Open BigQuery in the Google Cloud console and run your query.
2. When the results appear, click Save results and choose Google Sheets.
3. The first time, allow BigQuery to write to your Google Drive.

The result lands in a new spreadsheet in your Drive. It is a copy: it does not refresh, and the next person who needs newer numbers runs the query again. That is fine for a quick answer and a poor base for anything recurring.

Google notes two limits: this works only from the console, not from the bq tool or the API, and an error when opening the sheet usually means your account needs Drive SDK access to Google Workspace.

Way 2: Connected Sheets

Connected Sheets is Google’s own connector inside Google Sheets. It runs queries on BigQuery on your behalf, manually or on a schedule, and keeps the results in the spreadsheet. From Google’s Connected Sheets guide:

1. In Google Sheets, open Data, then Data connectors, then Connect to BigQuery.
2. Choose a Google Cloud project.
3. Choose a table or view, from your projects or from public datasets.
4. Click Connect.

You then work with the table through pivot tables, charts, extracts and calculated columns that run as BigQuery queries, so a sheet can analyse far more rows than it could hold.

What you need: access to Google Cloud and BigQuery, and a project with billing. The queries run in that project and are billed there. Access through Connected Sheets is recorded in Cloud Audit Logs, and you cannot change BigQuery data from the sheet.

It fits people who are comfortable picking tables and building their own analysis. Every person who connects also decides which table to use and how to aggregate it, so two sheets built on the same data can still disagree.

Way 3: a scheduled report from a Data Mart

The third way puts one step between BigQuery and the spreadsheet: the analyst publishes the query once as a Data Mart in OWOX Data Marts, and Sheets receives that. The spreadsheet user needs no SQL and no BigQuery access, and the analyst writes the query once instead of once per request.

There are two ways to deliver a Data Mart to Sheets: a scheduled report, set up by the analyst, and the Google Sheets extension, used by the business user. Both read the same Data Marts.

Step 1: connect BigQuery

In your OWOX Data Marts project, open Storages, click New Storage and choose Google BigQuery. The setup guide lists what to fill in:

• Project ID: the Google Cloud project that will run the queries. BigQuery usage is billed to this project.
• Location: the data location, such as US or EU.
• Authentication: a service account JSON key, or your Google account through OAuth. Either way the account needs the BigQuery Data Editor and BigQuery Job User roles. For a service account, the guide recommends a dedicated one with least-privilege access.

Step 2: create the Data Mart

A Data Mart can be your own SQL, an existing table or view, or data that an OWOX connector loads into BigQuery. For a channel report, the SQL might be:

SELECT
  order_date,
  channel,
  COUNT(DISTINCT order_id) AS orders,
  SUM(amount) AS revenue
FROM mydataset.orders
WHERE order_date >= DATE_SUB(
    CURRENT_DATE(), INTERVAL 90 DAY)
  AND status = 'paid'
GROUP BY order_date, channel;

OWOX Data Marts runs this SQL as written, in your BigQuery project. It does not transform the data; the logic is yours. Write the filter on the partition column of a partitioned table and each scheduled run scans only what it needs.

Step 3: describe the output

Once the Data Mart is published, its Output Schema lists every column. Give each a business-friendly alias and a description. The descriptions travel into Google Sheets as notes on the header cells, so a reader can see what “revenue” includes without asking.

Step 4: deliver it to Google Sheets

Add Google Sheets as a destination once, with Google OAuth or a service account key. Then, on the Data Mart’s Destinations tab, create a report: the spreadsheet and tab to fill, and the columns to include.

When the report runs, the rows are written into the sheet. Headers and data in the imported range are replaced on each refresh. Columns you add to the right are left alone, and a formula in row 2 is filled down to the new rows, so the reader’s own calculations survive the refresh. Where formulas, totals and lookups can sit so they survive is covered in why a scheduled refresh keeps wiping Google Sheets formulas.

Step 5: schedule it

On the Triggers tab, add a report run trigger: daily, weekly or at an interval, in the time zone you choose. Each run executes the query in BigQuery and rewrites the sheet, and Run History lists every run with its status.

Then share the spreadsheet as usual. The readers get current numbers and never touch BigQuery.

For business users: the Google Sheets extension

A scheduled report answers the questions someone predicted. For the others, the OWOX Data Marts extension lets business users on OWOX Cloud pull a Data Mart into the current sheet themselves: choose a Data Mart, pick columns, add filters and sorting, and run it. Only Data Marts the analyst marked Available for reporting appear. If the analyst has declared how two Data Marts join, their columns show up together in one list. More in spreadsheet reporting.

Which way to choose

• One answer, now: save the results. Do not build on the copy.
• An analyst exploring a big table in a spreadsheet: Connected Sheets.
• A report that several people open every week: a scheduled report from a Data Mart, so the query and the refresh live in one place and every copy of the sheet agrees.
• Business users asking new questions of approved data: the extension, on the same Data Marts.

Many teams use more than one. The cost of each is the queries it runs in BigQuery: a scheduled report is one query per run, however many people read the sheet. See BigQuery pricing for how those queries are billed.

Going the other way: Sheets to BigQuery

To load a spreadsheet into BigQuery as a table, see how to connect Google Sheets to BigQuery.

Before you start

• A Google Cloud project with BigQuery and billing set up.
• A Google account with access to Google Sheets.
• For the third way, an OWOX Data Marts project and the roles above for the account that connects BigQuery.

Google BigQueryHow to load data into BigQuery: every method and when to use itOctober 6, 2026→Google BigQueryIs BigQuery a data warehouse or a data lake? A marketing guideOctober 6, 2026→AI in AnalyticsHire AI ChatGPT for Generating SQL Queries 50x FasterJune 8, 2024→

See all articles →