How do large data sets work in Google Sheets?

• Home
• Docs
• BigQuery
• Documentation
• Guides

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

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. An enterprise workspace account is required to use Connected Sheets with BigQuery.

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 Cloud project. Learn how to check if billing is enabled on a 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 Cloud project. Learn how to check if billing is enabled on a 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.

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.

   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 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:

Caution: Deleting a project has the following effects:
• Everything in the project is deleted. If you used an existing project for this tutorial, 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 tutorials and quickstarts, reusing projects can help you avoid exceeding project quota limits.

1. In the 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.