Supported editions for this feature: Enterprise; Enterprise for Education. Compare your edition
You can access, analyze, visualize and share billions of rows of data from your spreadsheet with Connected Sheets, the new BigQuery data connector. You can also use Connected Sheets to:
- Collaborate with partners, analysts, or other stakeholders in a familiar spreadsheet interface.
- Ensure a single source of truth for data analysis without additional .csv exports.
- Streamline your reporting and dashboard workflows.
Connected Sheets runs queries on BigQuery on your behalf either when manually requested or on a defined schedule. Results of those queries are saved in your spreadsheet for analysis and sharing. Watch these video tutorials to learn more about Connected Sheets.
Important: Starting September 8, 2020, Sheets already using the traditional data connector will display an option to upgrade to Connected Sheets. Users may continue to view and refresh Sheets that are already using the traditional data connector, but no new Sheets can be created using the traditional data connector. If you're using the traditional data connector, learn how to Upgrade to Connected Sheets.
Set up Connected Sheets
The first step is to review IAM roles for users. You use identity and access management (IAM) roles to assign permissions on what data users can access. To add or use a BigQuery project in Sheets, a user’s IAM role in BigQuery must be one of the following options:
- bigquery.user
- bigquery.jobUser and bigquery.dataViewer
To learn about these roles, see Predefined roles and permissions.
The actions users can take depend on their IAM role and spreadsheet permissions (not the spreadsheet owner's permissions). People outside of your organization can only interact with Sheets in your organization if you allow it.
| Actions in Sheets | Required IAM role in BigQuery | Required permissions in Sheets |
|---|---|---|
| Create charts, pivot tables, formulas, or extracts using BigQuery tables or views |
|
Editor |
| View charts, pivot tables, formulas, extracts, or previews created from BigQuery data | None | Editor or viewer |
| Create or edit a custom BigQuery query |
|
Editor |
| View a custom BigQuery query | None | Editor or viewer |
| Refresh data from BigQuery |
|
Editor |
Next, you need to assign IAM roles to your datasets for your users. To learn about assigning IAM roles in the BigQuery console, see Controlling access to datasets.
And finally, make sure GCP is turned on for your organization. As an administrator, turn on Google Cloud Platform by following the steps in Control creation of Google Cloud Platform projects.
For instructions on how to use Connected Sheets, see Get started with BigQuery data in Google Sheets.
Troubleshoot
My users can't access datasets
Some Sheets features aren't available
REST API Sheets features are not yet available with Connected Sheets. All other Sheets features (printing, downloading, exporting, and so on) and functions (such as AVERAGE, SUM, and COUNTIF) are available.
Connected sheets also does not currently support organizations using VPC Service Controls.
If a feature causes Sheets to crash, click Send Feedback at the top right of the datasheet.
BigQuery updates aren't showing up in connected sheets
Users can't open a connected sheets file
If you set up certain permissions on Sheets files for your organization, such as restricting users outside of your organization from accessing Sheets files, they will not be able to open Connected Sheets files. To change the permissions, see Set Drive users' sharing permissions.
If you’re still running into issues, see the article Fix problems with BigQuery data in Google Sheets.
Resources
- Upgrade to Connected Sheets
- Get started with BigQuery data in Google Sheets
- Sort & filter BigQuery data in Google Sheets
- Analyze & refresh BigQuery data in Google Sheets using Connected Sheets
- Write & edit a query
- Fix problems with BigQuery data in Google Sheets
- BigQuery documentation
- Google Cloud Support plans
