Use Connected Sheets in your organization

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.

You can use VPC Service Controls to restrict access to Google Cloud resources. Because VPC Service Controls does not support Sheets, you might not be able to access BigQuery data that VPC Service Controls is protecting. If you have the required permissions and meet the VPC Service Controls access restrictions, you can configure the VPC Service Controls perimeter to allow queries issued through Connected Sheets.

Important: Sheets using the traditional data connector will display an option to upgrade to Connected Sheets. Users may continue to view 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 bigquery.user or bigquery.jobUser and bigquery.dataViewer.

To learn about these roles, see BigQuery predefined IAM roles.

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 OR 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 OR 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 Google Cloud is turned on for your organization. As an administrator, turn on Google Cloud by following the steps in Control creation of Google Cloud projects.

For instructions on how to use Connected Sheets, see Get started with BigQuery data in Google Sheets.

Use Audit Logs with Connected Sheets

When Connected Sheets accesses BigQuery data, entries are recorded in Cloud Audit Logs. You can use the Logs Explorer in the Google Cloud console to analyze these logs.

Note that every spreadsheet has a unique ID found in the URL for the spreadsheet. Log entries in the BigQueryAuditMetadata format contain the ID of the spreadsheet from which the BigQuery data access request was sent. Follow the steps below to find log entries for Connected Sheets.

Analyze Cloud Audit Logs using the Logs Explorer

Using the Logs Explorer in the Google Cloud console, you can build queries to retrieve and analyze logs. In Logs Explorer enter:

protoPayload.metadata.firstPartyAppMetadata.sheetsMetadata.docId != NULL_VALUE

This shows entries with a non-empty spreadsheet ID, as shown in this example:

metadata: {

@type: "type.googleapis.com/google.cloud.audit.BigQueryAuditMetadata"

firstPartyAppMetadata: {

sheetsMetadata: {

docId: "aBC-123_xYz"

}

Sheets adds additional information to query jobs using job labels. These can provide more data for you to analyze, as shown in this example:

jobInsertion: {

job: {

jobConfig: {

labels: {

sheets_access_type: "normal"

sheets_connector: "connected_sheets"

sheets_trigger: "user"

}

The value of the sheets_trigger field is set based on how the query is invoked from Sheets:

Label	How query is executed
user	Manually through the Sheets UI
schedule	Through the scheduled refresh feature in Sheets
API	Through the Sheets API
apps-script	Through the Apps Script API

For example, to find entries corresponding to scheduled refreshes of Connected Sheets, you can use the following query in Logs Explorer:

protoPayload.metadata.firstPartyAppMetadata.sheetsMetadata.docId != NULL_VALUE

protoPayload.metadata.jobInsertion.job.jobConfig.labels.sheets_trigger = "schedule"

For more in-depth documentation see Using the Logs Explorer and Build queries in the Logs Explorer.

Learn more about BigQuery audit logs, spreadsheet IDs, BigQueryAuditMetadata format, SheetsMetadata, sharing spreadsheets, and the Google Sheets API.

Troubleshoot

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.

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?