BigQuery
Run read-only SQL queries against BigQuery, with UNNEST examples for nested fields, and bring the results into the sheet.
The BigQuery connector runs a SQL query of your choice, read-only, against your Google BigQuery data. You authorize access with your Google account (same sign-in as Google Analytics 4 or Google Ads) - no API key or service account needed.
When creating a report, you specify two things: a billing project ID (the Google Cloud project the query will be billed to, based on bytes scanned - not necessarily the project where your data lives, since you can reference tables in other projects directly in the query), and the SQL query itself, in BigQuery Standard SQL.
Only SELECT queries are allowed (optionally preceded by WITH): no INSERT/UPDATE/DELETE/DDL, checked before every run.
Nested and repeated fields (RECORD/STRUCT, arrays): BigQuery tables - especially exports like the GA4-to-BigQuery export - often contain fields that hold a whole structure or a list of values in a single column (e.g. event_params in the GA4 export). A spreadsheet cell cannot hold a native nested structure, so the connector handles this in two ways depending on how you write the query:
- If you select the field as-is, without flattening it in the query, the connector writes it as a JSON text in the cell - this guarantees no data is silently lost, even for fields you didn't think to flatten, but it's not pleasant to read.
- The recommended approach is to flatten the field directly in the SQL query, using
UNNEST. There are two common patterns:
Pattern 1 - extract a single value (keeps one row per original record), useful when you only need one specific value out of the structure:
SELECT event_date, event_name,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location
FROM `project.dataset.events_*`
WHERE _TABLE_SUFFIX = '20261029'Pattern 2 - explode into multiple rows (one row per array element), useful when you want every key/value pair as its own readable row instead of a single value:
SELECT event_date, event_name, ep.key, ep.value.string_value AS value
FROM `project.dataset.events_*`, UNNEST(event_params) AS ep
WHERE _TABLE_SUFFIX = '20261029'Tip for daily updates: to automatically pull only new data every day (e.g. with a schedule and "Append new results to old" enabled), write a condition directly in the query using a date field, evaluated at execution time, e.g. a date-sharded table wildcard with _TABLE_SUFFIX, or WHERE event_date = CURRENT_DATE(). The saved query text never changes, but the result changes every day because the condition is evaluated when the report runs.
Available metrics
There are no fixed metrics or dimensions: the report columns are exactly the columns your SQL query returns (the column names or aliases used in the SELECT). Numbers, dates and timestamps are converted automatically based on BigQuery's own schema types - no manual conversion needed.
Known limits
- SELECT-only queries (or WITH)One statement per query, no INSERT/UPDATE/DELETE/DDL: checked before every run. A query that fails this check shows an error instead of running.
- Queries are billed to your Google Cloud projectBigQuery charges based on bytes scanned by each query, billed to the project you specify as "Billing project ID" when creating the report - independently of which project actually holds the data. Write selective queries (filter on partitioned/sharded columns, avoid SELECT * on huge tables) to control costs.
- No test when you connectThe connection page only handles sign-in: the real test happens the first time you select that connection to configure a report, not when you connect.
- Nested and repeated fields need UNNEST to read cleanlyFields that hold a structure (RECORD/STRUCT) or a list (REPEATED, arrays) are written as JSON text if you select them as-is - correct but not pleasant to read. Flatten them in the query with UNNEST for a clean result (see the examples above).