Write & edit a query

If you want to perform more complex analyses (e.g. join data from more than one BigQuery table), you can write a custom query.

1. On your computer, open a spreadsheet in Google Sheets.
2. In the menu at the top, click Data  Data connectors  Connect to BigQuery.
3. Choose a project.
4. Click Write a custom query.
5. Enter a query.
   • To preview how much data your query scans, in the bottom right of the editor, click Preview results.
6. Click Insert results.

To compare data, you might need to change the data type. For example, if a column is string data in BigQuery, then it must be plain text in Sheets to be compared correctly.

Tip: if you're a developer, learn how to use Apps Script with Connected Sheets.

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

1. At the right 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, you might need to change the data type. For example, if a column is string data 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 do the following:

• 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;

Parameterised query:

SELECT SUM(kw_total)
FROM `bigquery-public-data.sunroof_solar.solar_potential_by_censustract`
WHERE state_name = @STATENAME;