Multi-City Weather Import Dashboard — WeatherWise Analytics
Beginner
20 min
18 views
0 solutions
Overview
WeatherWise Analytics monitors temperature, rainfall, and air quality across 4 cities. Students build a master dashboard by pulling live data from two external sheets via IMPORTRANGE, displaying weather condition icons using IMPORTIMAGE, and building QUERY summaries with GROUP BY and WHERE. A bonus dropdown-driven QUERY is also available.
Case Details
CONTEXT: WeatherWise Analytics monitors Delhi, Mumbai, Bengaluru, Chennai. Each city team has their own Google Sheet. Students act as HQ analyst — building a live dashboard without any copy-paste.
SETUP: Create 2 separate Google Sheets — City A (Delhi + Mumbai, 15 rows each) and City B (Bengaluru + Chennai, 15 rows each). Columns: Date, City, Temperature (°C), Rainfall (mm), AQI Index, Weather Condition (Sunny/Rainy/Cloudy/Stormy). Share both publicly.
TASK 1 — IMPORTRANGE: Pull City A data into A2 of Day3 tab. Pull City B data starting at row 20. Authorize both connections.
TASK 2 — IMPORTIMAGE: In column I, display 4 weather icons using IMPORTIMAGE — one per condition. Label each.
TASK 3 — FILTERED QUERY: Show only records where Temperature > 30°C AND Condition = 'Sunny', sorted by Temperature descending. Place at row 40.
TASK 4 — GROUP BY QUERY: Show City, Average Temperature, Average Rainfall, Count of Records per City. Place at row 55.
TASK 5 — REFRESH NOTE: Merged cell M1 with yellow background — 3 sentences explaining how IMPORTRANGE auto-refreshes when source data changes.
BONUS: Add a Data Validation dropdown in P1 with all 4 cities. Write a QUERY that filters dashboard by the city selected in P1.
SETUP: Create 2 separate Google Sheets — City A (Delhi + Mumbai, 15 rows each) and City B (Bengaluru + Chennai, 15 rows each). Columns: Date, City, Temperature (°C), Rainfall (mm), AQI Index, Weather Condition (Sunny/Rainy/Cloudy/Stormy). Share both publicly.
TASK 1 — IMPORTRANGE: Pull City A data into A2 of Day3 tab. Pull City B data starting at row 20. Authorize both connections.
TASK 2 — IMPORTIMAGE: In column I, display 4 weather icons using IMPORTIMAGE — one per condition. Label each.
TASK 3 — FILTERED QUERY: Show only records where Temperature > 30°C AND Condition = 'Sunny', sorted by Temperature descending. Place at row 40.
TASK 4 — GROUP BY QUERY: Show City, Average Temperature, Average Rainfall, Count of Records per City. Place at row 55.
TASK 5 — REFRESH NOTE: Merged cell M1 with yellow background — 3 sentences explaining how IMPORTRANGE auto-refreshes when source data changes.
BONUS: Add a Data Validation dropdown in P1 with all 4 cities. Write a QUERY that filters dashboard by the city selected in P1.
What You'll Learn
- Problem-solving and analytical thinking
- Data-driven decision making
- Business strategy development
- Professional report writing
0
Solutions Submitted
Difficulty
Beginner
Estimated Time
20 minutes
Relevance
Fresh
Source
Google Sheets Module - Puneet Arora