Write & edit a query

If you want to do more complicated analysis, such as joining data from more than one BigQuery table, you can write a custom query.

1. On your computer, go to Google Sheets.
2. Open a new spreadsheet.
3. In the menu at the top, click Data  Data connectors  Connect to BigQuery.
4. Select a Google Cloud project.
5. Click Write a custom query.
6. Enter a query.
   • To preview how much data your query scans, at the bottom right of the editor, click the green check mark.
7. Click Connect.

Tip:

• If you're a developer, learn how to Use Apps Script with Connected Sheets.
• You can access saved queries in Connected Sheets for BigQuery. To keep your saved queries up-to-date, sync them whenever you make changes or find an error in the Info .

To use the value of a cell in a query, set up a parameter.

1. At the right side of the query editor, click Parameters  Add.
2. Enter a parameter name and the cell you want to reference.
3. Click Add.

To compare data from Sheets with data in BigQuery, you might need to change the data type. For example, if a column is a String type in BigQuery, then it must be plain text in Sheets to be compared correctly. If the data type in the spreadsheet and BigQuery are different, you can:

• 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).

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

 

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;