Important:
- To access BigQuery data in Google Sheets, you need access to BigQuery. Learn how to Get started with BigQuery.
- You can only use Google Standard SQL with the BigQuery connector.
- On your computer, go to Google Sheets.
- Open a new spreadsheet.
- In the menu at the top, click Data Data connectors Connect to BigQuery.
- Select a Google Cloud project.
- Click Write a custom query.
- Enter a query.
- To preview how much data your query scans, at the bottom right of the editor, click the green check mark.
- Click Connect.
To use the value of a cell in a query, set up a parameter.
- At the right side of the query editor, click Parameters Add.
- Enter a parameter name and the cell you want to reference.
- Click Add.
- Change how the data is formatted in Sheets. For example, data formatted as number could be changed to plain text.
- Cast the value in a query. For example: WHERE zip_code = CAST(@ZIP AS string).
Example queries
Simple query:
SELECT word, SUM(word_count) AS word_count
FROM `bigquery-public-data.samples.shakespeare`
WHERE word IN ('me', 'I', 'you')
GROUP BY word;
Parameterized query:
- Parameter
@STATENAME
data range =Sheet1!A1
A | |
1 | California |
SELECT SUM(kw_total) as sum_kw_total
FROM `bigquery-public-data.sunroof_solar.solar_potential_by_censustract`
WHERE state_name = @STATENAME;
Parameterized query with IN():
- Parameter
@STATENAMES
data range =Sheet1!A1
A | |
1 |
Alaska, New York, Delaware, Florida |
SELECT state_name, SUM(kw_total) AS sum_kw_total
FROM `bigquery-public-data.sunroof_solar.solar_potential_by_censustract`
WHERE state_name IN (SELECT TRIM(s) FROM UNNEST(SPLIT(@STATENAMES, ',')) s)
GROUP BY state_name;
Next: Fix problems with BigQuery data in Sheets
Related articles
Get started with BigQuery data in Sheets
Sort & filter your BigQuery data in Sheets
Analyze & refresh a query with BigQuery data in Sheets
Fix problems with BigQuery data in Sheets