Analyze & refresh BigQuery data in Google Sheets using Connected Sheets

You can use functions, charts, extracts, pivot tables, and refresh BigQuery data in Google Sheets. 

Important: To access BigQuery data in Google Sheets, you need access to BigQuery. Learn how to get started with BigQuery.

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

Learn more about charts in Google Sheets.

Tip: To update the chart based on the latest BigQuery data, at the bottom of the chart, click Refresh.

 
To group numeric values in charts into a histogram, under X-axis, you can check Aggregate and click Edit.
  • In the "Grouping rule" pop-up, specify any of Minimum value, Maximum value, or Interval size, then click OK to apply those parameters for data aggregation.
Add a pivot table
  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 and then 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.
Use a function

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. 

Pull data into an extract

You can analyze data offline, or reference individual values and ranges within functions. You can pull up to 500k rows or 10MB of your BigQuery data. If you try to pull more than this limit, only the first 500k rows will appear.

If the number of rows are:

  • Less than or equal to 50k, then there’s no cell limit.
  • Greater than 50k but less than or equal to 500k, then the number of cells must be less than or equal to 5m.
  • Greater than 500k, then the data pull isn’t supported.
  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.
Add a calculation to all rows in a data set and view column stats

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 Down arrow and then View Column Stats.

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

Lookup Functions

Filter Functions

  • FILTER
Text Functions
  • LEFT, RIGHT, MID
  • UPPER, LOWER
  • LEN
  • CONCAT (&)
Basic Math
  • +, -, *, /
  • 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
Comparison
  • =, <>
  • >, >=, <, <=
Aggregations
  • 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)

Refresh your data

BigQuery data doesn't automatically sync with Connected Sheets. To sync the data, you can refresh a specific item, everything within a data source, or everything within all data sources. Once you’ve connected to a data source, you can refresh that data and all of the objects like Charts, Functions, Extracts, and Pivot Tables associated with that data. 
  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 More and then Refresh options.
  3. To the right, under "Refresh options," click the items you want to refresh. To refresh all of the data, at the bottom right, click Refresh all.
Schedule a refresh

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 More and then 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.
Take over a scheduled refresh

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 More and then 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.
     

View query details and cancel a query

Editors of a spreadsheet and users with the required BigQuery permissions can cancel a query that is running. 

To view query details for any database objects:

  1. On your computer, open a spreadsheet in Google Sheets that is connected to BigQuery data.
  2. At the bottom pop-up Refresh button, click info The info icon. to see query details in BigQuery.

To cancel a query for any database objects:

  1. On your computer, open a spreadsheet in Google Sheets that is connected to BigQuery data.
  2. When a query is running or pending: At the bottom pop-up Refresh button, next to “Running query” or “Query pending”, click Cancel next to the calculated running time.
  3. Click Confirm.

You can also cancel a query from Refresh options sidebar:

  1. On your computer, open a spreadsheet in Google Sheets that is connected to BigQuery data.
  2. At the bottom, hover over the refresh icon Refresh, next to "Refresh" click More More and then Refresh options.
  3. To cancel running refresh individually, next to each item, click Cancel.
  4. Click Confirm.
Note:
  • If you do not have the required BigQuery permission for a BigQuery dataset, the action to cancel a query cannot be completed.
  • A query that failed due to a Sheets timeout may keep running in the database.
  • If you cancel a query that has multiple formulas, all of the other formulas in the query are also canceled.

Next: Write & edit a query with BigQuery data in Sheets

Related articles

Need more help?

Try these next steps:

Post to the help community Get answers from community members
true
Visit the Learning Center

Using Google products, like Google Docs, at work or school? Try powerful tips, tutorials, and templates. Learn to work on Office files without installing Office, create dynamic project plans and team calendars, auto-organize your inbox, and more.

Search
Clear search
Close search
Google apps
Main menu
7645271950138914966
true
Search Help Center
true
true
true
true
true
35
false
false
false
false