Multi-City Weather Import Dashboard — WeatherWise Analytics
Beginner
20 min
84 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.
Data Sources
https://docs.google.com/spreadsheets/d/1ksZ1gBj0YlfRLJkel6JhGSeHBvin62cgmaTA5VLiGH0/edit?usp=sharing
Solution Frameworks
Live Dashboard Architecture: Sources (city sheets) → Aggregation (IMPORTRANGE) → Analysis (QUERY) → Visualization.
Data Source Management: Document source URL and last-verified date for every IMPORTRANGE connection in a comment.
QUERY Clause Order: SELECT → WHERE → GROUP BY → ORDER BY → LIMIT. Wrong order causes syntax errors.
Data Source Management: Document source URL and last-verified date for every IMPORTRANGE connection in a comment.
QUERY Clause Order: SELECT → WHERE → GROUP BY → ORDER BY → LIMIT. Wrong order causes syntax errors.
Solver Guidance & Tutorials
IMPORTRANGE: Copy the full URL from the address bar of each city sheet. Range string must match the tab name exactly.
Authorization: Click 'Allow Access' when prompted — otherwise cell shows #REF! and nothing loads.
IMPORTIMAGE: Use any public PNG/JPG URL. OpenWeatherMap icon format: https://openweathermap.org/img/wn/[email protected]
QUERY with two conditions: =QUERY(A2:F35,"SELECT * WHERE C>30 AND F='Sunny' ORDER BY C DESC",1)
GROUP BY: =QUERY(A2:F35,"SELECT B,AVG(C),AVG(D),COUNT(A) GROUP BY B LABEL AVG(C) 'Avg Temp',AVG(D) 'Avg Rainfall'",1)
Tutorial Link: https://aplly.xyz/case-studies?search=&type=2&difficulty=Beginner&status=
Authorization: Click 'Allow Access' when prompted — otherwise cell shows #REF! and nothing loads.
IMPORTIMAGE: Use any public PNG/JPG URL. OpenWeatherMap icon format: https://openweathermap.org/img/wn/[email protected]
QUERY with two conditions: =QUERY(A2:F35,"SELECT * WHERE C>30 AND F='Sunny' ORDER BY C DESC",1)
GROUP BY: =QUERY(A2:F35,"SELECT B,AVG(C),AVG(D),COUNT(A) GROUP BY B LABEL AVG(C) 'Avg Temp',AVG(D) 'Avg Rainfall'",1)
Tutorial Link: https://aplly.xyz/case-studies?search=&type=2&difficulty=Beginner&status=
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