Use Connected Sheets in your organization

This article is for administrators. For help managing your own files, go to the Learning Center.

You can access, analyze, visualize, and share billions of rows of data from your spreadsheet with Connected Sheets, the Sheets 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.
  • Analyze data within a perimeter that restricts access based on attributes, such as the user's IP address and device information.

You can run queries from Connected Sheets on BigQuery or Looker either manually or on a defined schedule. Sheets saves the results of these queries in your spreadsheet so you can analyze and share them. Watch these video tutorials to learn more about Using Connected Sheets with BigQuery.

You can view Connected Sheets query events in Drive log events.

Set up BigQuery to analyze data

Step 1: Turn on Google Cloud

Make sure Google Cloud is turned on for your organization. For instructions, go to View which apps are turned on for a user, group, or organizational unit. If you need to turn on Google Cloud, go to Turn Google Cloud on or off for users.

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

Step 2: Review IAM roles

You use IAM (identity and access management) 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

Step 3: Assign IAM roles

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

Step 4: (Optional) Configure VPC Service Controls to allow Connected Sheets

In addition to using IAM to authorize which users can access BigQuery data, you can use VPC Service Controls to create a service perimeter that restricts access based on attributes such as the user's IP address and device information. Users can use Connected Sheets to access BigQuery data protected by VPC Service Controls only if you configure the perimeter to allow Sheets to copy query results to users' spreadsheets. For details, go to Access control.

Set up Looker to analyze data

To use Connected Sheets with Looker, you must turn on access to services that are not controlled individually in your Google Admin console. For more information, see Manage access to services that aren't controlled individually. Additionally, a Looker admin must first enable Connected Sheets in the Looker admin UI. For more detailed instructions, see Using Connected Sheets for Looker.

Allow users to delegate access to Connected Sheets for BigQuery

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

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

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 Drive log events or Cloud audit logs.

Turn delegated access on or off

Before you begin: If needed, learn how to apply the setting to a department or group.

  1. In the Google Admin console, go to Menu and then Apps and then Google Workspace and then Drive and Docs and then Features and Applications.

    Requires having the Service Settings administrator privilege.

  2. For Connected Sheets delegated access, click Edit .
  3. (Optional) To apply the setting only to some users, at the side, select an organizational unit (often used for departments) or configuration group (advanced).

    Group settings override organizational units. Learn more

  4. For Delegation settings, check or uncheck Allow users with edit access to a spreadsheet to enable access delegation for Connected Sheets.
  5. If you're configuring an organizational unit or group, select Only users within a specific organizational unit or group can use delegation.
  6. 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.
  7. Click Save. Or, you might click Override for an organizational unit.

    To later restore the inherited value, click Inherit (or Unset for a group).

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

View log events for Connected Sheets

When Connected Sheets accesses BigQuery and Looker data, entries are recorded in Drive log events. Entries are also recorded in Cloud Audit Logs for BigQuery access and in the System Activity History Explore for Looker access. The logs show who accessed the data and when.

Analyze Drive log events using the Reports API

For details on how to analyze Drive log events from the Google Admin console, see Access Drive log event data.

Using the Reports API, you can view the Connected Sheets Query events. The following example retrieves all Drive events by the Connected Sheets Query event type:

The complete JSON response to this API call is shown in the Full JSON Response section below on this page.

The user who initiated the query is shown as the actor.

Sheets provides additional information about the query that was executed as parameters.

The execution_trigger field is set based on how the query is invoked from Sheets:

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

The query_type field is set based on the data connector.
Label Data connector
big_query BigQuery
looker Looker

The data_connection_id field is set based on the ID of the data connection. For BigQuery, this is the billing project ID. For Looker, this is the URL of the instance.

The execution_id is set based on the ID of the query that was executed.

Value structure Query entity
jobs/<JOB_ID> BigQuery Job
datasets/<DATASET_NAME>/tables/<TABLE_NAME> BigQuery Table
query_tasks/<QUERY_TASK_ID> Looker Query

The email address of the user whose credentials were used is available in the logs as the delegating_principal field.

Full JSON response