Using Connected Sheets

With Connected Sheets, you can access, analyze, visualize, and share billions of rows of BigQuery data from your 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 Sheets access only, share a spreadsheet and do not grant BigQuery access.

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

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

Before you begin

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 Account.

    If you don't already have one, sign up for a new account.

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

    Go to the project selector page

  3. Make sure that billing is enabled for your Google Cloud project. Learn how to confirm billing is enabled for your project.

  4. 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.

Use Connected Sheets with BigQuery

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

  1. Create or open a Sheets spreadsheet.

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

  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.

    Connect to a table

    Your spreadsheet should look similar to the following:

    Taxi trips data

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

Although the spreadsheet shows a preview of only 500 rows, any pivot tables, formulas, and charts use the entire set of data. You can also extract the data to a sheet. For more information, see the Connected Sheets tutorial.

Cleaning up

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

  1. In the Cloud Console, go to the Manage resources page.

    Go to the Manage resources page

  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