Connected Sheets (BigQuery) in Google Sheets - Real Business Scenario

Scenario Mode

👤 Your Role: You are a data analyst at an e-commerce company.

📋 Request: Your manager wants a monthly sales report that updates automatically with the latest data from BigQuery.

📊 Data: You have access to a BigQuery table named 'ecommerce.sales_data' with columns: OrderID, OrderDate, Region, ProductCategory, SalesAmount.

🎯 Deliverable: Create a Google Sheet connected to BigQuery that shows total sales by month and region, updating automatically when data changes.

Sample Data

OrderID	OrderDate	Region	ProductCategory	SalesAmount
1001	2024-01-15	North	Electronics	250
1002	2024-01-20	South	Clothing	150
1003	2024-02-05	East	Electronics	300
1004	2024-02-18	West	Home	200
1005	2024-03-10	North	Clothing	180
1006	2024-03-15	South	Home	220
1007	2024-03-20	East	Electronics	350
1008	2024-04-01	West	Clothing	130

1

Step 1: Open a new Google Sheet and connect it to your BigQuery project using Connected Sheets.

In Google Sheets, go to Data > Data connectors > Connect to BigQuery. Select your project and the 'ecommerce.sales_data' table.

Expected Result

The BigQuery data is imported into your Google Sheet as a connected table.

2

Step 2: Create a pivot table to summarize total sales by month and region.

Select the connected data range, then Insert > Pivot table. Set Rows to 'Region', Columns to 'OrderDate' grouped by Month, Values to SUM of 'SalesAmount'.

Expected Result

Pivot table shows total sales for each region by month.

3

Step 3: Format the 'OrderDate' column in the pivot table to show month names.

Right-click a date in the pivot table, choose 'Create pivot date group' > 'Month'.

Expected Result

Dates in the pivot table are grouped and displayed by month.

4

Step 4: Set the connected sheet to refresh data automatically.

Go to Data > Data connectors > Refresh options, and enable automatic refresh daily or on open.

Expected Result

The sheet updates automatically with the latest BigQuery data.

5

Step 5: Add a chart to visualize monthly sales by region.

Select the pivot table data, then Insert > Chart. Choose a column chart and set the X-axis to months and series to regions.

Expected Result

A clear chart displays monthly sales trends by region.

Final Result

Monthly Sales Report

Region | Jan | Feb | Mar | Apr
--------------------------------
North  | 250 |  0  | 180 |  0
South  | 150 |  0  | 220 |  0
East   |  0  | 300 | 350 |  0
West   |  0  | 200 |  0  | 130

[Column Chart showing sales trends by region over months]

✓North region had strong sales in January and March.

✓East region sales peaked in February and March.

✓West region sales appeared only in February and April.

✓South region had consistent sales in January and March.

Bonus Challenge

Add a filter to the pivot table to allow viewing sales by specific product categories.

Show Hint

In the pivot table editor, add 'ProductCategory' as a filter and select categories to display.

Practice

1. What is the main benefit of using Connected Sheets with BigQuery?

easy

A. It replaces the need for Google Sheets entirely.

B. It automatically creates charts without user input.

C. It allows users to analyze large datasets directly in Google Sheets without coding.

D. It exports data only as CSV files.

Solution

1. Step 1: Understand Connected Sheets purpose

   Connected Sheets connects BigQuery data to Google Sheets for easy analysis.

2. Step 2: Identify main benefit

   This connection allows working with big data in Sheets without writing code.

3. Final Answer:

   It allows users to analyze large datasets directly in Google Sheets without coding. -> Option C

4. Quick Check:

   BigQuery + Sheets = easy data analysis [OK]

Hint: Think: BigQuery data + Sheets = no coding needed [OK]

Common Mistakes:

• Thinking Connected Sheets replaces Google Sheets
• Assuming it only exports CSV files
• Believing charts are auto-created without user action

2. Which of the following is the correct way to start using Connected Sheets in Google Sheets?

easy

A. Click Data > Data connectors > Connect to BigQuery.

B. Click Insert > Chart > BigQuery Connector.

C. Click File > Import > BigQuery Data.

D. Click Tools > Script Editor > BigQuery.

Solution

1. Step 1: Locate Connected Sheets menu

   Connected Sheets is accessed via Data > Data connectors > Connect to BigQuery.

2. Step 2: Confirm correct menu path

   Other options refer to unrelated menus or tools.

3. Final Answer:

   Click Data > Data connectors > Connect to BigQuery. -> Option A

4. Quick Check:

   Data connectors menu starts Connected Sheets [OK]

Hint: Look under Data menu for BigQuery connection [OK]

Common Mistakes:

• Choosing Insert or File menus incorrectly
• Confusing Script Editor with Connected Sheets
• Assuming chart insertion starts connection

3. Given a Connected Sheet linked to a BigQuery table with columns Product and Sales, what will happen if you refresh the data after new sales are added in BigQuery?

medium

A. The Google Sheet will only update if you export and re-import the data.

B. The Google Sheet will keep showing old data until manually reconnected.

C. The Google Sheet will delete all existing data and show an error.

D. The Google Sheet will update to show the latest sales data from BigQuery.

Solution

1. Step 1: Understand refresh behavior

   Connected Sheets refresh pulls latest data from BigQuery into the sheet.

2. Step 2: Identify correct update action

   Refreshing updates the sheet with new data automatically.

3. Final Answer:

   The Google Sheet will update to show the latest sales data from BigQuery. -> Option D

4. Quick Check:

   Refresh = latest BigQuery data in Sheets [OK]

Hint: Refresh pulls newest BigQuery data into Sheets [OK]

Common Mistakes:

• Thinking refresh requires manual reconnection
• Assuming data deletes on refresh
• Believing export/import is needed for updates

4. You try to refresh your Connected Sheet but get an error saying you lack permissions. What is the most likely cause?

medium

A. Your Google Sheet is not saved.

B. You do not have BigQuery read access to the linked dataset.

C. You have too many rows in the sheet.

D. Your internet connection is offline.

Solution

1. Step 1: Analyze permission error

   Permission errors usually mean missing access rights to BigQuery data.

2. Step 2: Confirm cause of error

   Without BigQuery read permission, refresh fails with an error.

3. Final Answer:

   You do not have BigQuery read access to the linked dataset. -> Option B

4. Quick Check:

   Permission error = missing BigQuery access [OK]

Hint: Check BigQuery permissions if refresh fails [OK]

Common Mistakes:

• Blaming sheet save status
• Assuming row count causes permission errors
• Ignoring internet connectivity issues

5. You want to create a live sales report in Google Sheets using Connected Sheets with BigQuery data. Which of the following best practices should you follow to optimize performance?

hard

A. Limit the query to only necessary columns and use filters to reduce data size.

B. Import the entire BigQuery dataset without filters for completeness.

C. Manually copy data from BigQuery to Sheets daily to avoid refresh delays.

D. Use multiple Connected Sheets for the same dataset to increase speed.

Solution

1. Step 1: Understand performance optimization

   Fetching only needed columns and filtering reduces data size and speeds up refresh.

2. Step 2: Identify best practice

   Limiting data improves performance; importing all data or manual copying is inefficient.

3. Final Answer:

   Limit the query to only necessary columns and use filters to reduce data size. -> Option A

4. Quick Check:

   Smaller queries = faster Connected Sheets [OK]

Hint: Filter and select only needed data for faster refresh [OK]

Common Mistakes:

• Importing full dataset unnecessarily
• Copying data manually instead of refreshing
• Using multiple connections for same data