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 CloudMake 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.
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 orbigquery.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 |
You assign IAM roles to your datasets in the BigQuery console. For details, go to Control access to resources with IAM.
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
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.
-
Sign in to your Google Admin console.
Sign in using your administrator account (does not end in @gmail.com).
-
In the Admin console, go to Menu
Apps
Google Workspace
- For Connected Sheets delegated access, click Edit
.
-
(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
- For Delegation settings, check or uncheck Allow users with edit access to a spreadsheet to enable access delegation for Connected Sheets.
- If you’re configuring an organizational unit or group, select Only users within a specific organizational unit or group can use delegation.
- 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.
-
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 APIFor 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
}
]
}
]
}
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.
- On your Looker instance, on the left, click Explore
- For Find a Field, enter API Client Name and click Filter
to add this field to the dataset.
- Under Filters, select is equal to and in the field next to this, enter Connected Sheets.
- For Find a Field, enter Connected Sheets Spreadsheets ID to add this field to the dataset.
- For Find a Field, enter Connected Sheets Trigger to add this field to the dataset.
- For Find a Field, enter History Slug to add this field to the dataset.
- 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.
- (Optional) To add any additional fields, such as User Name and History Created Date, to the dataset, select them.
- (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. - Click Run.
Troubleshoot
If Sheets crashesAt the top of the sheet, click Send Feedback.
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
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.
