Connected Sheets (BigQuery) in Google Sheets - Dashboard Guide

Dashboard Mode - Connected Sheets (BigQuery)

Dashboard Goal

Analyze sales data from BigQuery directly in Google Sheets to track total sales, average sales per region, and monthly sales trends.

Sample Data

OrderID	Region	Month	Sales
1001	East	January	1200
1002	West	January	1500
1003	East	February	1100
1004	West	February	1600
1005	East	March	1300
1006	West	March	1700

Dashboard Components

• Total Sales (KPI Card): Formula: =SUM(BQData!D2:D7) - Shows total sales from all orders.
• Average Sales by Region (Table): Formula: =QUERY(BQData!B2:D7,"select B, AVG(D) group by B label AVG(D) 'Average Sales'") - Displays average sales per region.
• Monthly Sales Trend (Line Chart): Data range: BQData!C2:D7 - Plots sales over months to see trends.

Dashboard Layout

+----------------------+-----------------------+
|   Total Sales (KPI)  | Average Sales by Region |
|       (Top Left)     |       (Top Right)       |
+----------------------+-----------------------+
|              Monthly Sales Trend (Chart)           |
|                     (Bottom Full Width)            |
+----------------------------------------------------+

Interactivity

Adding a filter for Region will update the Average Sales table and Monthly Sales Trend chart to show data only for the selected region. The Total Sales KPI will also update to reflect the filtered total sales.

Self Check

If you add a filter to show only Region = East, which components update and what changes occur?

• Total Sales: Updates to sum sales only for East region (1200 + 1100 + 1300 = 3600).
• Average Sales by Region: Shows only East region with average sales of 1200.
• Monthly Sales Trend: Displays sales trend for East region months January, February, and March.

Key Result

Dashboard showing total sales, average sales by region, and monthly sales trends from BigQuery data in Google Sheets.