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.