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. 
  • Let users delegate access to collaborators.
  • Ensure a single source of truth for data analysis without additional .csv exports.

Connected Sheets runs queries on BigQuery 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.

Using Connected Sheets with VPC Service Controls

VPC Service Controls does not support Google Sheets. Therefore, you might not be able to access BigQuery data protected by VPC Service Controls. However, if you have the required permissions and meet access restrictions, you can configure the VPC Service Controls perimeter to allow queries issued through Connected Sheets.

Set up Connected Sheets

Before you begin: Sheets using the traditional data connector will display an option to upgrade to Connected Sheets. Users can continue to view sheets that have the traditional data connector, but they can’t create any new sheets with it. For more information, go to Upgrade to Connected Sheets.

Expand section  |  Collapse all

Step 1: Review IAM roles

You use IAM (identity and access management) roles to assign permissions on what data users can access. A user’s IAM role in BigQuery must be bigquery.user or bigquery.jobUser and bigquery.dataViewer to add or use a BigQuery project in Sheets.

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
Step 2: Assign IAM roles

You assign IAM roles to your datasets in the BigQuery console. For details, go to Controlling access to datasets.

Step 3: Turn on Google Cloud

Make sure Google Cloud is turned on for your organization. For more information, go to Control creation of Google Cloud projects.

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

Allow users to delegate access to Connected Sheets

Supported editions for this feature: Enterprise; Education Standard and Education Plus.  Compare your edition

You can let users delegate access to Connected Sheets so they can collaborate with other users to analyze data and run queries with BigQuery.

To delegate access, users need to share the sheet with the other user. However, they can’t delegate access to a sheet that’s shared publicly with a link. You can review the user who delegates access and the user who runs a query in the BigQuery logs.

Turn delegated access on or off

  1. Sign in to your Google Admin console.

    Sign in using your administrator account (does not end in @gmail.com).

  2. In the Admin console, go to Menu ""and then"" Appsand thenGoogle Workspaceand thenDrive and Docsand thenFeatures and Applications.

  3. For Connected Sheets delegated access, click Edit"".
  4. To apply the setting to everyone, leave the top organizational unit selected. Otherwise, select a child organizational unit or a configuration group.
  5. For Delegation settings, check or uncheck the Allow users with edit access to a spreadsheet to enable access delegation for Connected Sheets box.
  6. If you’re configuring for an organizational unit or group, select Only users within a specific organizational unit or group can use delegation.
  7. If you want to allow any user with access to the sheet to delegate access, select Any users can use delegation.

    This option includes users outside of your organization if they have access to the sheet.

  8. Click Save. If you configured an organizational unit or group, you might be able to Inherit or Override a parent organizational unit, or Unset a group.

If you turn on delegation, tell your users they can follow these steps to delegate access to a sheet.

Use audit logs with Connected Sheets

When Connected Sheets accesses BigQuery data, entries are recorded in Cloud Audit Logs. The logs show who accessed the data and when. 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 Apps Script


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"

The email address of the user whose credentials were used to run the query can also be found in the logs, as well as the email address of the user who triggered the query if delegated access was enabled, as shown in this example.

"authenticationInfo": {

  "principalEmail": "owner@example.com",

  "serviceAccountDelegationInfo": [

    {

      "firstPartyPrincipal": {

        "principalEmail": "collaborator@example.com",

          "serviceMetadata": {

             "service": "sheets"

          }

       }

     }

   ]

}

Note: The serviceAccountDelegationInfo field will only be present if delegated access was used for the query.

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

Expand section  |  Collapse all

If Sheets crashes

At the top of the sheet, click Send Feedback.

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 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?

Need more help?

Sign in for additional support options to quickly solve your issue

Search
Clear search
Close search
Google apps
Main menu
Search Help Center
true
73010
false
false