Analyze & refresh BigQuery data in Google Sheets using Connected Sheets

1. On your computer, open a spreadsheet in Google Sheets that is connected to BigQuery data.
2. At the top, click Pivot table.
3. Choose where to place it.
4. Click Create.
5. At the right, choose your settings.
6. At the bottom right, click Apply.

Tips:

• Pivot tables can currently support up to 100k results.
• To limit the amount of data you see in your pivot table, specify the number of rows or columns to return. Update “sort by” and “order” to specify which rows or columns are returned. 
• To add calculated fields with a SUM or custom formula, in the side panel, next to “Values,” click Add Calculated field. Learn how to add Calculated fields in pivot tables.
• To update to the latest BigQuery data, at the bottom of the pivot table, click Refresh.
• To view values at intervals within a range, use histogram grouping. Values that are higher than the maximum and lower than the minimum are grouped together.

You can use some functions with BigQuery data.

1. On your computer, open a spreadsheet in Google Sheets that is connected to BigQuery data.
2. At the top, click Function.
3. Choose a function from the list.
4. Select where to place the function.
5. Click Create.
6. Choose your reference column and add any other required parameters.
7. Press Enter.
8. Put your mouse over a cell that contains the formula, and click Apply changes.

Tip: You can also type functions inside any cell. 

1. On your computer, open a spreadsheet in Google Sheets that is connected to BigQuery data.
2. Select an option:
   1. At the top, click Extract.
3. Choose where to put it.
4. Click Create.
5. At the right, under "Extract editor," add columns, filters, and sort settings.
6. At the bottom left of the extract, click Apply changes.

You can apply a calculation to all rows by adding a calculated column.

1. On your computer, open a spreadsheet in Google Sheets that is connected to BigQuery data.
2. At the top, click Calculated column.
3. Enter a formula using column names and supported Sheets functions. You can use basic math operations like =price * quantity or manipulate text with =RIGHT(orderId, 4). 
4. Click Add.
5. On the bottom left of the Sheet, click Apply.

Tip: To view column stats, at the top right of a column, click Down arrow   View Column Stats.

• IF (can be used to detect potential errors, such as division by zero)
• AND
• OR
• NOT

Lookup Functions

• ROWS 
• VLOOKUP: Learn how to use VLOOKUP in Connected Sheets.
• XLOOKUP: Learn how to use XLOOKUP in Connected Sheets.

Filter Functions

• FILTER

• LEFT, RIGHT, MID
• UPPER, LOWER
• LEN
• CONCAT (&)

• +, -, *, /
• The negative sign (-) and the percent sign (%)

Math Functions

• ROUND
• POW/POWER (^)
• SQRT
• ABS
• LOG, LN, LOG10

Dates and Times

• DATE, TIME
• YEAR, MONTH, DAY
• HOUR, MINUTE, SECOND

• =, <>
• >, >=, <, <=

• SUM
• SUMIF
• SUMIFS
• COUNT
• COUNTBLANK
• COUNTIF
• COUNTIFS
• COUNTUNIQUE
• COUNTUNIQUEIFS
• MIN
• MINIFS
• MAX
• MAXIFS
• AVERAGE
• AVERAGEIF
• AVERAGEIFS
• VAR
• VARP
• STDEV
• STDEVP

Other

• ISBLANK (returns whether or not the given argument is NULL)

Important: Scheduled refreshes of Connected Sheets don't propagate any end-user context such as IP address or device information. Virtual Private Cloud Service Controls (VPC-SC) perimeters that use end-user context to restrict access will cause scheduled refreshes to fail.

A scheduled refresh will update all objects and data sources at a specific, preset time.
Important:

• A scheduled refresh will run as the user that set up the schedule. If another user adds or updates an existing datasource, the schedule will automatically pause. To unpause, contact the schedule owner or take over the refresh.
• Objects in the preview or failed state will not refresh with a scheduled refresh. 

1. On your computer, open a spreadsheet in Google Sheets that is connected to BigQuery data.
2. At the bottom, next to "Refresh" click More   Refresh options.
3. At the bottom of the sidebar to the right, under “Scheduled refresh,” click Setup now.
4. Choose your refresh frequency.
5. Click Save.

To run a scheduled refresh as yourself instead of the original owner, you can take over a refresh. 

1. On your computer, open a spreadsheet in Google Sheets that is connected to BigQuery data.
2. At the bottom, next to "Refresh" click More   Refresh options.
3. At the bottom of the sidebar to the right, under “Scheduled refresh,” click Edit.
4. Choose your refresh frequency. 
5. Click Save to transfer the scheduled refresh ownership to you.