Connected Sheets (BigQuery) in Google Sheets - Step-by-Step Guide

Introduction

Connected Sheets lets you link Google Sheets directly to BigQuery data. This helps you work with large datasets without needing to know SQL. You can analyze and visualize BigQuery data right inside your spreadsheet.

When you want to analyze large company sales data stored in BigQuery without exporting it.

When your marketing team needs to create reports from BigQuery data but prefers using Sheets.

When you want to refresh data in your spreadsheet automatically from BigQuery.

When you want to use familiar spreadsheet tools like filters and pivot tables on BigQuery data.

When you want to share BigQuery data insights with colleagues who only use Google Sheets.

Steps

Step 1: Open

- Google Sheets

A blank or existing spreadsheet opens ready for data connection

Step 2: Click

- Data menu > Data connectors > Connect to BigQuery

A sidebar opens showing your BigQuery projects and datasets

Step 3: Select

- BigQuery project and dataset in the sidebar

Tables from the selected dataset appear for you to choose

Step 4: Choose

- A BigQuery table or saved query

A preview of the data shows in the sidebar

Step 5: Click

- Connect button in the sidebar

BigQuery data loads into your spreadsheet as a connected table

Step 6: Use

- Spreadsheet tools like filters, pivot tables, and charts

You analyze and visualize BigQuery data directly in Sheets

Step 7: Click

- Data menu > Data connectors > Refresh all

The connected BigQuery data updates to show the latest information

Before vs After

Before

Spreadsheet is empty or has unrelated data

After

Spreadsheet shows a table with live data from BigQuery, ready for analysis

Settings Reference

Data source selection

📍 Data menu > Data connectors > Connect to BigQuery sidebar

Choose which BigQuery data to connect to your sheet

Default: No selection

Refresh options

📍 Data menu > Data connectors > Refresh all

Update connected data to show current BigQuery information

Default: Manual refresh

Query filters

📍 Connected Sheets sidebar when setting up connection

Reduce data size by filtering rows before loading

Default: No filters

Common Mistakes

Trying to connect without BigQuery permissions

You cannot access BigQuery data without proper permissions

Ensure your Google account has BigQuery read access before connecting

Loading too large a dataset without filters

This can slow down Sheets or cause errors due to data size limits

Use filters or saved queries to limit data size before loading

Expecting automatic data refresh without manual action

Connected Sheets data refreshes only when you click refresh or schedule it

Manually refresh data or set up scheduled refresh if available

Summary

Connected Sheets links Google Sheets directly to BigQuery data for easy analysis.

You can select tables or saved queries and load data into your spreadsheet.

Remember to have proper permissions and manage data size with filters.

Refresh data manually or schedule refreshes to keep your sheet up to date.