Turn on connected sheets (beta)

This feature is available with G Suite Enterprise, Enterprise for Education, Drive Enterprise, Business, Education, and Nonprofits edition. Compare editions

Your organization’s users can now manipulate large data sets stored in BigQuery (part of Google Cloud) and integrate them with Google Sheets using connected sheets. Your organization can create charts, pivot tables, and formulas on up to 10 billion rows of data stored in BigQuery without using SQL.

Step 1: Review requirements and sign up

To use connected sheets in your organization:

  • You need to set up BigQuery for your organization. For details, see BigQuery.
  • Users must have access to tables or views in BigQuery.

Ready to get started? Sign up for connected sheets (beta)

Step 2: 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

Step 3: Manage what data users can access

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.

Troubleshoot

My users can't access datasets

Even if users have the proper IAM roles assigned, they may not be using the correct G Suite 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

The following Sheets features are not yet available with connected sheets:

  • REST API
  • Google Apps Script support

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 connected sheet.

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 Dataand thenData connectorsand thenRefresh dataand thenRefresh all.

Users can't use connected sheets in a Sheets file

Users must create a new sheet before they can access connected sheets. They can't use connected sheets in an existing sheet.

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.

Related topics

Was this helpful?
How can we improve it?