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.
- Ensure a single source of truth for data analysis without additional .csv exports.
- Streamline your reporting and dashboard workflows.
Connected Sheets runs queries on BigQuery on your behalf either 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.
You can use VPC Service Controls to restrict access to Google Cloud resources. Because VPC Service Controls does not support Sheets, you might not be able to access BigQuery data that VPC Service Controls is protecting. If you have the required permissions and meet the VPC Service Controls access restrictions, you can configure the VPC Service Controls perimeter to allow queries issued through Connected Sheets.
Important: Sheets using the traditional data connector will display an option to upgrade to Connected Sheets. Users may continue to view Sheets that are already using the traditional data connector, but no new Sheets can be created using the traditional data connector. If you're using the traditional data connector, learn how to Upgrade to Connected Sheets.
Set up Connected Sheets
The first step is to review IAM roles for users. You use identity and access management (IAM) 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 |
Next, you need to assign IAM roles to your datasets for your users. To learn about assigning IAM roles in the BigQuery console, see Controlling access to datasets.
And finally, make sure Google Cloud is turned on for your organization. As an administrator, turn on Google Cloud by following the steps in Control creation of Google Cloud projects.
For instructions on how to use Connected Sheets, see Get started with BigQuery data in Google Sheets.
Use Audit Logs with Connected Sheets
When Connected Sheets accesses BigQuery data, entries are recorded in Cloud Audit Logs. 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 the Apps Script API |
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"
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
Some Sheets features aren't available
REST API Sheets features are not yet available with Connected Sheets. All other Sheets features (printing, downloading, exporting, and so on) and functions (such as AVERAGE, SUM, and COUNTIF) are available.
If a feature causes Sheets to crash, click Send Feedback at the top right of the datasheet.
BigQuery updates aren't showing up in Connected Sheets
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
- Upgrade to Connected Sheets
- Get started with BigQuery data in Google Sheets
- Sort & filter BigQuery data in Google Sheets
- Analyze & refresh BigQuery data in Google Sheets using Connected Sheets
- Write & edit a query
- Fix problems with BigQuery data in Google Sheets
- BigQuery documentation
- Google Cloud Support plans