🌦️
What You're BuildingA live weather analytics dashboard that pulls data from two external city sheets using IMPORTRANGE, displays weather condition icons using IMPORTIMAGE, and generates aggregated summaries with QUERY — all updating automatically when source data changes.
Three-Layer ArchitectureCity Sheets (2 external sources) → IMPORTRANGE (aggregation) → QUERY (analysis) → Dashboard. You own only the dashboard layer. The city teams own their data.
🪜
Step-by-Step Guide1
Create the Two City Source Sheets
Create 2 separate Google Sheets. Name them City_A_WeatherWise and City_B_WeatherWise. Both need identical columns: Date, City, Temperature (°C), Rainfall (mm), AQI Index, Weather Condition. Add 15 rows each — City A covers Delhi & Mumbai, City B covers Bengaluru & Chennai. Share both as "Anyone with link → Viewer". Copy their URLs.
2
IMPORTRANGE — Pull Both City Sheets
In your Day3 tab, pull City A data into A3 and City B into A20:
Cell A3 — City A
=IMPORTRANGE("YOUR_CITY_A_URL", "Sheet1!A2:F16")
Cell A20 — City B
=IMPORTRANGE("YOUR_CITY_B_URL", "Sheet1!A2:F16")
Authorization PopupEach IMPORTRANGE shows a yellow "Allow Access" bar. Click it for BOTH sheets. If you miss it, the cell shows #REF! — just click the cell again and the prompt reappears.
3
IMPORTIMAGE — Weather Condition Icons
In column H rows 3–6, display one icon per weather condition:
H3: =IMPORTIMAGE("https://openweathermap.org/img/wn/[email protected]") ← Sunny
H4: =IMPORTIMAGE("https://openweathermap.org/img/wn/[email protected]") ← Rainy
H5: =IMPORTIMAGE("https://openweathermap.org/img/wn/[email protected]") ← Cloudy
H6: =IMPORTIMAGE("https://openweathermap.org/img/wn/[email protected]") ← Stormy
Resize the rows to about 80px height and set column H to ~120px wide so the images display clearly. Label each image in column G.
4
QUERY — Filter Hot Sunny Days (Row 40)
Show only records where Temperature > 30°C AND Condition is Sunny, sorted hottest first:
Cell A40
=QUERY(A3:F35,
"SELECT *
WHERE C > 30
AND F = 'Sunny'
ORDER BY C DESC", 1)
Column Letters in QUERYThe letters A, B, C... in QUERY refer to the columns of your range — not the sheet columns. If your range starts at A3, then A=Date, B=City, C=Temperature, F=Weather Condition.
5
QUERY — City Aggregation (Row 55)
Show average temperature, average rainfall, and record count per city:
Cell A55
=QUERY(A3:F35,
"SELECT B, AVG(C), AVG(D), COUNT(A)
GROUP BY B
LABEL AVG(C) 'Avg Temp (°C)',
AVG(D) 'Avg Rainfall (mm)',
COUNT(A) 'Records'", 1)
6
IMPORTRANGE Refresh Note (Cell M1)
Merge cells M1:P3. Set background to yellow. Write 3 sentences explaining auto-refresh:
"IMPORTRANGE creates a live connection between this dashboard and the source city sheets. Any data update made by a city analyst in their sheet automatically reflects here within a few seconds. This eliminates manual copy-pasting and ensures the dashboard always shows the most current weather readings."