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.
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 |
You assign IAM roles to your datasets in the BigQuery console. For details, go to Controlling access to datasets.
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
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
-
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
.
- To apply the setting to everyone, leave the top organizational unit selected. Otherwise, select a child organizational unit or a configuration group.
- For Delegation settings, check or uncheck the Allow users with edit access to a spreadsheet to enable access delegation for Connected Sheets box.
- If you’re configuring for 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. 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
At the top of the sheet, click Send Feedback.
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.
