Notification

Duet AI is now Gemini for Google Workspace. Learn more

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

Expand section  |  Collapse all

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

  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. (Optional) To apply the setting only to some users, at the side, select an organizational unit (often used for departments) or configuration group (advanced). Show me how

    Group settings override organizational units. Learn more

  5. For Delegation settings, check or uncheck Allow users with edit access to a spreadsheet to enable access delegation for Connected Sheets.
  6. If you’re configuring 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. 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.

Expand section  |  Collapse all

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:

GET https://admin.googleapis.com/admin/reports/v1/activity/users/all/applications/drive?eventName=connected_sheets_query

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.

"actor": {
  "email": "collaborator@example.com",
  "profileId": "user’s unique Google Workspace profile ID"
}

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

"parameters": [
  {
    "name": "execution_trigger",
    "value": "sheets_ui"
  },
  {
    "name": "query_type",
    "value": "big_query"
  },
  {
    "name": "data_connection_id",
    "value": "The Cloud project ID"
  },
  {
    "name": "execution_id",
    "multiValue": [
      "jobs/big_query_job_id"
    ]
  },
  {
    "name": "delegating_principal",
    "value": "owner@example.com"
  },

 

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

{
      "kind": "admin#reports#activity",
      "id": {
        "time": "2022-10-26T17:33:51.929Z",
        "uniqueQualifier": "report’s unique ID",
        "applicationName": "drive",
        "customerId": "ABC123xyz"
      },
      "actor": {
        "email": "collaborator@example.com",
        "profileId": "user’s unique Google Workspace profile ID"
      },
      "events": [
        {
          "type": "access",
          "name": "connected_sheets_query",
          "parameters": [
            {
              "name": "execution_trigger",
              "value": "sheets_ui"
            },
            {
              "name": "query_type",
              "value": "big_query"
            },
            {
              "name": "data_connection_id",
              "value": "The Cloud project ID"
            },
            {
              "name": "execution_id",
              "multiValue": [
                "jobs/big_query_job_id"
              ]
            },
            {
              "name": "doc_id",
              "value": "aBC-123-xYz"
            },
            {
              "name": "doc_type",
              "value": "spreadsheet"
            },
            {
              "name": "is_encrypted",
              "boolValue": false
            },
            {
              "name": "doc_title",
              "value": "Document title"
            },
            {
              "name": "visibility",
              "value": "shared_internally"
            },
            {
              "name": "actor_is_collaborator_account",
              "boolValue": false
            },
            {
              "name": "delegating_principal",
              "value": "owner@example.com"
            },
            {
              "name": "owner",
              "value": "owner@example.com"
            },
            {
              "name": "owner_is_shared_drive",
              "boolValue": false
            },
            {
              "name": "owner_is_team_drive",
              "boolValue": false
            }
          ]
        }
      ]
    }

Analyze Cloud Audit Logs using the Logs Explorer for BigQuery connections

Every spreadsheet has a unique Sheet 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.

You can build queries to retrieve and analyze logs using the Logs Explorer in the Google Cloud console. 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 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, use the following query in Logs Explorer:

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

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

If delegated access was enabled, you can find the email address of the user whose credentials were used to run the query in the logs. You can also find the email address of the user who triggered the query, as shown in the following example:

"authenticationInfo": {

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

  "serviceAccountDelegationInfo": [

    {

      "firstPartyPrincipal": {

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

          "serviceMetadata": {

             "service": "sheets"

          }

       }

     }

   ]

}

Note: The serviceAccountDelegationInfo field is present only if delegated access was used for the query. In this case, the person listed under principalEmail is the one who delegated access.  

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.

Analyze Looker system activity
  1. On your Looker instance, on the left, click Exploreand thenHistory.
  2. For Find a Field, enter API Client Name and click Filter  to add this field to the dataset.
  3. Under Filters, select is equal to and in the field next to this, enter Connected Sheets.
  4. For Find a Field, enter Connected Sheets Spreadsheets ID to add this field to the dataset.
  5. For Find a Field, enter Connected Sheets Trigger to add this field to the dataset.
  6. For Find a Field, enter History Slug to add this field to the dataset.
  7. History Slug is equivalent to the QUERY_TASK_ID that is logged in Drive log events. If you want to find a specific query in the Drive log, add a filter on that field.
  8. (Optional) To add any additional fields, such as User Name and History Created Date, to the dataset, select them.
  9. (Optional) To add any filters, select them.
    For example, you can filter History Created Date to is in the past 7 days, or you can filter on a specific Spreadsheet ID to see only the Looker queries initiated from a specific spreadsheet ID.
  10. Click Run.

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, those users will not be able to open Connected Sheets files. To change the permissions, see Set Drive users' sharing permissions.

If you’re still having issues, see Fix problems with BigQuery data in Google Sheets and Troubleshooting Connected Sheets for Looker.

Related topics

Was this helpful?

How can we improve it?
Search
Clear search
Close search
Google apps
Main menu