There are two good options for connecting BigQuery to a spreadsheet, depending on your dataset size:
- Small data extracts (under 10K-100K rows): Google Sheets has a built-in BigQuery connector that makes it easy to connect Google Sheets to BigQuery using Connected Sheets. You can analyze and manipulate massive datasets, but are limited by the amount of raw data you can place in the spreadsheet.
- Big data extracts (scales to billions of rows): Row Zero has a built-in BigQuery connector that seamlessly connects to BigQuery and lets you import 10s of millions of rows on a free plan and billion row datasets on enterprise plans.
The key difference between the two is Row Zero imports full datasets into the spreadsheet, where you can edit, clean, and transform the raw data directly in the spreadsheet, whereas Google Sheets queries BigQuery and returns smaller subsets of data or analysis output like pivot tables and charts.
In this guide, we'll show how to connect BigQuery to Google Sheets and Row Zero and the pros and cons of each solution.
Table of Contents:
How to Connect Google Sheets to BigQuery
You can use the built-in BigQuery connector to easily connect Google Sheets to BigQuery. Here's how:
- Open Google Sheets and go to Data in the header menu and select Data connectors and then Connect to BigQuery.
- Add a data connection by choosing a cloud project, dataset, and table or view to connect to and clicking 'Connect'.
Note you also have the option to open a query editor where you can write a SQL query rather than connecting to a full table or view. 
- You'll see a success message and can click 'Get started' to continue.
- You'll see a connected sheet, which shows a preview of up to 500 rows of data and options to create a chart, pivot table, function, or calculated column.
These will be based on the entire data set, not just the 500 row preview. However, each will kick off a query to BigQuery to get the data and return back the output to your Google Sheet. You can also create an extract which lets you pull the raw BigQuery data directly into Google Sheets, but BigQuery extracts are limited to 10 MB, 5 million cells, and 500K rows.
Working with Connected Sheets
Using functions
You can use the full suite of functions to analyze BigQuery data, with 2 key differences: You can only reference full columns of data in your formula, and you have to click "Apply" to actually execute your formula and "Refresh" to re-run it. This is because Google Sheets is querying BigQuery each time and returning the output of the query. 
Creating pivot tables and charts
The experience is similar for creating pivot tables and charts in connected sheets. When you create a pivot table, you have to click 'Apply' to execute the query to BigQuery and return the pivot table result. Each time you edit the pivot table, you need to click 'Apply' to update it. 
Charts in Connected Sheets have this same experience. You must click "Apply" and wait for a query to execute each time you want to create or edit a chart with your connected sheet. Once you've created your pivot tables and charts, you can refresh them at any time by clicking the "Refresh" button to re-run your query and get the latest data. 
Updating data
There are options to manually refresh data throughout your connected sheet, with blue refresh icons on anything that can be refreshed with updated data. You can also click "Schedule Refresh" on your Connected Sheet to automate data updates throughout your sheet. 
Creating an Extract
Click the 'Extract' button on your connected sheet to create a new sheet of raw data extracted from BigQuery. This provides more flexibility in working with BigQuery data but is limited to 10 MB, 5 million cells, and 500K rows. When you create an extract, a new Extract sheet is created and an Extract editor opens. 
Here you can select your columns, filters, sort, and row limit. The row limits range from 10 to 100K. If your dataset is larger than this limit, you'll want to add a sort (for example Date descending) so that you get a logical subset of your data up to this limit.
Note that when working with larger data, the extract editor may include the following warning: "This extract may be slow - A large extract can cause Sheets to be slow. Reduce the number of rows or select fewer columns to avoid performance issues". 
Limitations of Connected Sheets
While Connected Sheets offer a convenient way to connect Google Sheets to BigQuery, there are significant limitations:
- Connected Sheets are limited to a preview of up to 500 rows. Beyond this, you'll need to create an extract.
- Extracts are limited to 10 MB, 5 million cells, and 500K rows.
- Whenever you filter or sort a Connected Sheet or create or edit a function, chart, or pivot table, you have to click "Apply" to execute a query to BigQuery and return your output. If you're doing serious analysis and making a lot of changes this can be inefficient and annoying.
- The extract editor specifically warns that "a large extract can cause Sheets to be slow" and suggests limiting the number of rows and columns to avoid performance issues.
- Google Sheets has a total data limit of 10 million cells. As you approach these limits, your Google Sheet may slow down or crash, especially when doing heavy analysis.
In sum, Connected Sheets can be great for basic analysis or working with small data extracts, but may not be a good solution for big data analysis. If you need to work with large datasets from BigQuery directly in your spreadsheet, Row Zero is a good solution, which we cover below.
Big Data Solution - Row Zero
Row Zero is an enterprise-grade spreadsheet built for big data that works like Google Sheets and connects directly to BigQuery. You can work with 10s of millions of rows on a free plan and billion row datasets on Enterprise plans and all of the raw data is available in the spreadsheet. You pull in data once and then calculations, charts, pivot tables, etc. execute seamlessly in the spreadsheet, without needing to re-query BigQuery each time. This makes it much faster to do big data analysis in the spreadsheet and you can quickly build BigQuery connected spreadsheets that auto-update with the latest data.
There are 3 steps to easily connect BigQuery to Row Zero and analyze big data in a spreadsheet:
- Connect Row Zero to your BigQuery account: Row Zero supports connecting to BigQuery via OAuth. Once your organization has set up BigQuery OAuth in Row Zero, you'll see it as an option when creating a new connection and can connect with one click by going to the 'Data' icon in the upper right-hand corner of a workbook, clicking "+ Add connection', and selecting BigQuery OAuth.
If your organization has not yet set up BigQuery OAuth, contact Row Zero to get it enabled. - Write a query to import data: Click the '+' sign next to your BigQuery connection to open a query editor. Write your query, click 'Run', and your BigQuery data imports to a connected table.
- Build out your BigQuery connected spreadsheet: Row Zero works like Google Sheets with 200+ formula functions, pivot tables, charts, etc. Everything you build on top of connected data stays in sync and dynamically updates with new data. You can schedule auto updates or manually update at any time. Here's more info on how to build connected spreadsheets.
Conclusion
If working with small data extracts or doing basic analysis, Google Sheets connected to BigQuery can be a good solution and is easy to set up. If you want to work with big data extracts, do big data analysis, or want faster performance, use Row Zero. Row Zero makes it easy to connect BigQuery to a big spreadsheet and work with large datasets in the spreadsheet directly. The key difference is Google Sheets extracts only a subset of large datasets and queries BigQuery each time you filter or sort a connected sheet or create or edit a function, chart, or pivot table. Row Zero imports full datasets into the spreadsheet where you can work with the raw data and analyze with functions, charts, and pivot tables without re-querying BigQuery each time. Both solutions are free to try, so you can try each and see which better suits your needs.
If you use another cloud data warehouse or blob storage, Row Zero also connects to Snowflake, Redshift, Databricks, Postgres and S3.