卡里路计算

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, open a spreadsheet in Google Sheets.
2. In the menu at the top, click Data Data connectors Connect to BigQuery.
3. Select a project.
4. Click Saved queries and query editor.
5. Enter a query.
   • To preview how much data your query scans, at the bottom right of the editor, click Preview results.
6. Click Insert results.

Tips:

• If you're a developer, learn how to Use Apps Script with Connected Sheets.
• To find certain new saved queries, enable the Dataform API. Learn more about Dataform API.
• Validate data types between BigQuery and Sheets data for analysis.

To create queries in Connected Sheets, you can access saved queries from BigQuery projects. Learn more about saved queries.

1. On your computer, open a spreadsheet in Google Sheets.
2. In the menu, at the top of the spreadsheet, click Data Data connectors Connect to BigQuery.
3. Select a project.
4. Click Saved queries and query editor BigQuery saved queries.
5. Select a project a saved query.
6. Click Connect.

To edit a saved query, update it directly within BigQuery. The updated query appears in Connected Sheets within 5 minutes.

To keep your saved queries up to date whenever you update saved queries in BigQuery:

1. In the Connected Sheet, sync your saved queries through Sync from BigQuery.
2. In the “BigQuery editor,” click Connect.

Learn more about saved queries

When an error occurs due to a modified or deleted saved query, you can update the connection from the error dialog or BigQuery editor.

1. At the top right of the preview sheet, open Connection settings.
2. To update the connection, click Sync from BigQuery Connect.
3. To update the connection for the following cases:
   • Modified query: Click View error Sync saved query.
     • Or, you can open the query editor. Click Edit Sync saved query Connect.
   • Deleted query: Click View error Use as custom query.
     • Or, you can:
       1. To open the query editor, click Edit.
       2. Change to another query or unlink the existing query.
       3. Click Connect.

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;

 

• 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