API calls from Apps Script in Google Sheets - Dashboard Guide

Dashboard Mode - API calls from Apps Script

Goal

See how to fetch live data from a web API into Google Sheets using Apps Script and analyze it in a dashboard.

Sample Data

City	Temperature (°C)	Humidity (%)	Condition
New York	22	60	Clear
London	18	75	Cloudy
Tokyo	25	70	Rain
Sydney	20	65	Clear
Mumbai	30	80	Sunny

Dashboard Components

• KPI Card: Average Temperature
  Formula: =AVERAGE(B2:B6)
  Shows the average temperature of all cities.
• KPI Card: Highest Humidity
  Formula: =MAX(C2:C6)
  Shows the highest humidity value.
• Table: Weather Data
  Shows live data fetched from API and placed in cells A2:D6.
• Apps Script Function: fetchWeatherData()
  Calls a weather API, parses JSON, and writes city, temperature, humidity, and condition to the sheet.

Dashboard Layout

+----------------------+----------------------+
| Average Temperature   | Highest Humidity     |
|       (KPI)           |       (KPI)          |
+----------------------+----------------------+
|                                              |
|            Weather Data Table                  |
|                                              |
+----------------------------------------------+

Interactivity

User clicks a custom menu button in Google Sheets to run the fetchWeatherData() script. This updates the weather data table with fresh API data. The KPI cards automatically recalculate based on the updated data.

Self Check

If you add a filter to show only cities with temperature above 20°C, which components update?

• The Weather Data Table will show only cities with temperature > 20°C.
• The Average Temperature KPI recalculates based on filtered data.
• The Highest Humidity KPI recalculates based on filtered data.

Key Result

Dashboard shows live weather data fetched via Apps Script API calls with KPIs for average temperature and highest humidity.