Use Connected Sheets in your organization

Supported editions for this feature: Enterprise; Education Plus. 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	bigquery.user bigquery.jobUser and bigquery.dataViewer	Editor
View charts, pivot tables, formulas, extracts, or previews created from BigQuery data	None	Editor or viewer
Create or edit a custom BigQuery query	bigquery.user bigquery.jobUser and bigquery.dataViewer	Editor
View a custom BigQuery query	None	Editor or viewer
Refresh data from BigQuery	bigquery.user OR bigquery.jobUser and bigquery.dataViewer	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

Even if users have the proper IAM roles assigned, they may not be using the correct license. If they’re not using a supported edition (as described at the top of this article), see Change a user’s license to a different subscription.

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

In the Sheets files, click Refresh to pull any updates to BigQuery data into connected sheets. To refresh every item in connected sheets, click Data

Data connectors

Refresh data

Refresh all.

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

Was this helpful?

How can we improve it?