Using Connected Sheets  |  BigQuery  |  Google Cloud (2024)

With Connected Sheets, you can access, analyze, visualize, and sharebillions of rows of BigQuery data from your Google Sheetsspreadsheet.

You can also do the following:

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

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

• Streamline your reporting and dashboard workflows.

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

Example use cases

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

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

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

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

Access control

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

A user with Google Sheets-only access can perform analysis in the sheet anduse other Google Sheets features, but the user won't be able to perform thefollowing 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 toBigQuery against the project that you selected.You can view the executed query with the following log filter in the relatedproject:

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

VPC Service Controls

You can use VPC Service Controls to restrict access toGoogle Cloud resources. Because VPC Service Controls does not supportSheets, you might not be able to access BigQuerydata that VPC Service Controls is protecting. If you have the required allowpermissions and meet theVPC Service Controls access restrictions, you can configure theVPC Service Controls perimeter to allow queries issued throughConnected Sheets. To do so, you must configure the perimeter usingthe 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 egresspolicies andconfiguring access levels toproperly configure the rules.To configure a perimeter to allow the required data copying, use the followingYAML 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

Before you begin

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

If you do not yet have a Google Cloud project that is set up for billing, followthese 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.

   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.

   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 theresources 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 toBigQuery from Google Sheets.

1. Create or open a Google Sheets spreadsheet.

2. Click Data, click Data connectors, and then click Connect toBigQuery.

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 thechicago_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 chartsusing 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 forresults returned for pivot tables is 50,000. You can also extract the data to asheet. The maximum number of rows for results returned for data extracts is50,000. For more information, see theConnected Sheets tutorial.

Open tables in Connected Sheets

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

• Use the Explorer pane:

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

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

     

• Use the table toolbar:

  1. In the Explorer pane, click the table that youwant 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 resourcesused in this tutorial:

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 WorkspaceGet started with BigQuery data in Google Sheets topic.

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