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.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||
|View charts, pivot tables, formulas, extracts, or previews created from BigQuery data||None||Editor or viewer|
|Create or edit a custom BigQuery query||
|View a custom BigQuery query||None||Editor or viewer|
|Refresh data from BigQuery||
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.
My users can't access datasets
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.