Using Connected Sheets

With Connected Sheets, you can access, analyze, visualize, and share billions of rows of BigQuery data from your Google Sheets spreadsheet.

You can also do the following:

• Collaborate with partners, analysts, or other stakeholders in a familiar spreadsheet interface.

• Ensure a single source of truth for data analysis without additional spreadsheet exports.

• Streamline your reporting and dashboard workflows.

Connected Sheets runs BigQuery queries on your behalf either upon your request or on a defined schedule. Results of those queries are saved in your spreadsheet for analysis and sharing.

Example use cases

The following are just a few use cases that show how Connected Sheets lets you analyze large amounts of data within a sheet, without needing to know SQL.

• Business planning: Build and prepare datasets, then allow others to find insights from the data. For example, analyze sales data to determine which products sell better in different locations.

• Customer service: Find out which stores have the most complaints per 10,000 customers.

• Sales: Create internal finance and sales reports, and share revenue reports with sales reps.

Access control

Direct access to BigQuery datasets and tables is still controlled within BigQuery. If you want to give a user Google Sheets access only, share a spreadsheet and don't grant BigQuery access.

A user with Google Sheets-only access can perform analysis in the sheet and use other Google Sheets features, but the user won't be able to perform the following actions:

• Manually refresh the BigQuery data in the sheet.
• Schedule a refresh of the data in the sheet.

When you filter data in Connected Sheets, it refreshes the query that you send to BigQuery against the project that you selected. You can view the executed query with the following log filter in the related project:

resource.type="bigquery_resource"
protoPayload.metadata.firstPartyAppMetadata.sheetsMetadata.docId != NULL_VALUE

VPC Service Controls

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 allow permissions and meet the VPC Service Controls access restrictions, you can configure the VPC Service Controls perimeter to allow queries issued through Connected Sheets. To do so, you must configure the perimeter using the following:

• An access level or ingress rule to allow requests from trusted IP addresses, identities, and trusted client devices from outside of the perimeter.
• An egress rule to allow query results to be copied to users' spreadsheets.

Learn about configuring ingress and egress policies and configuring access levels to properly configure the rules. To configure a perimeter to allow the required data copying, use the following YAML file:

# Allows egress to Sheets through the Connected Sheets feature
- egressTo:
    operations:
    - serviceName: 'bigquery.googleapis.com'
      methodSelectors:
      - permission: 'bigquery.vpcsc.importData'
    resources:
    - projects/628550087766 # Sheets-owned Google Cloud project
  egressFrom:
    identityType: ANY_USER_ACCOUNT

Note: Scheduled refreshes of Connected Sheets don't propagate any end-user context such as IP address or device information. VPC Service Controls perimeters that use end-user context to restrict access cause scheduled refreshes to fail.

Before you begin

First, make sure that you meet the requirements for accessing BigQuery data in Sheets, as described in the "What you need" section of the Google Workspace topic Get started with BigQuery data in Google Sheets.

If you do not yet have a Google Cloud project that is set up for billing, follow these steps:

1. Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.

2. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

   Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

   Go to project selector

3. Make sure that billing is enabled for your Google Cloud project.

4. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

   Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

   Go to project selector

5. Make sure that billing is enabled for your Google Cloud project.

6. BigQuery is automatically enabled in new projects. To activate BigQuery in a preexisting project, go to

   Enable the BigQuery API.

   Enable the API

When you finish this topic, you can avoid continued billing by deleting the resources you created. See Cleaning up for more detail.

Open BigQuery datasets from Connected Sheets

The following example uses a public dataset to show you how to connect to BigQuery from Google Sheets.

1. Create or open a Google Sheets spreadsheet.

2. Click Data, click Data connectors, and then click Connect to BigQuery.

   Note: If you do not see the Data connectors option, see Before you begin.

3. Click Get connected.

4. Select a Google Cloud project that has billing enabled.

5. Click Public datasets.

6. In the search box, type chicago and then select the chicago_taxi_trips dataset.

7. Select the taxi_trips table and then click Connect.

   

   Your spreadsheet should look similar to the following:

   

Start using the spreadsheet. You can create pivot tables, formulas, and charts using familiar Google Sheets techniques.

Although the spreadsheet shows a preview of only 500 rows, any pivot tables, formulas, and charts use the entire set of data. The maximum number of rows for results returned for pivot tables is 50,000. You can also extract the data to a sheet. The maximum number of rows for results returned for data extracts is 50,000. For more information, see the Connected Sheets tutorial.

Open tables in Connected Sheets

To open tables in Connected Sheets from the Google Cloud console, use one of the following methods:

• Use the Explorer pane:

  1. In the Explorer pane, expand the dataset that contains the table that you want to open in Google Sheets.

  2. Next to the table name, click View actions, and then select Open with > Connected Sheets:

     

• Use the table toolbar:

  1. In the Explorer pane, click the table that you want to open in Google Sheets.

  2. On the table toolbar, click Export, and then click Explore with Sheets:

     

Cleaning up

To avoid incurring charges to your Google Cloud account for the resources used in this tutorial:

Caution: Deleting a project has the following effects:

• Everything in the project is deleted. If you used an existing project for the tasks in this document, when you delete it, you also delete any other work you've done in the project.
• Custom project IDs are lost. When you created this project, you might have created a custom project ID that you want to use in the future. To preserve the URLs that use the project ID, such as an appspot.com URL, delete selected resources inside the project instead of deleting the whole project.

If you plan to explore multiple architectures, tutorials, or quickstarts, reusing projects can help you avoid exceeding project quota limits.

1. In the Google Cloud console, go to the Manage resources page.

   Go to Manage resources

2. In the project list, select the project that you want to delete, and then click Delete.
3. In the dialog, type the project ID, and then click Shut down to delete the project.

What's next

• Get more information from the Google Workspace Get started with BigQuery data in Google Sheets topic.

• View videos from the Using Connected Sheets playlist on YouTube.