Connect to an air-quality and weather feed

Northwind’s office manager wants a simple environmental dashboard for the London studio — how clean the air is right now, and what the next few days look like. The data is all freely available, but it lives across two separate APIs and nobody wants to check two websites every morning.

This script stitches both feeds into one Sheet. It pulls the current PM2.5 reading from OpenAQ and a five-day weather forecast from Open-Meteo, then writes them to a dated log. Run it on a schedule and the Sheet becomes a living dashboard — air quality history plus an at-a-glance forecast — with no API keys to manage, because both feeds are free and keyless.

What you’ll need

• A Google Sheet to log into, with its ID copied into the config below. The script writes to two tabs and creates them if they are missing.
• The latitude and longitude of the location you want to track. The script ships with London’s coordinates; swap in your own.
• Nothing else. OpenAQ and Open-Meteo are both free and need no API key.

The script

// Location to monitor — Northwind's London studio.
const LATITUDE = 51.5;
const LONGITUDE = -0.12;
const TIMEZONE = 'Europe/London';

// The Sheet that holds the environmental dashboard.
const ENV_SHEET_ID = '1abcEnvId';

/**
 * Pulls the current PM2.5 reading and a 5-day forecast, then writes
 * both to the dashboard Sheet. Designed to run on a daily trigger.
 */
function logEnvironmentData() {
  const ss = SpreadsheetApp.openById(ENV_SHEET_ID);
  logAirQuality(ss);
  logForecast(ss);
}

/**
 * Fetches the latest PM2.5 measurement from OpenAQ and appends a
 * dated row to the "Air quality" tab.
 */
function logAirQuality(ss) {
  // 1. Ask OpenAQ for the single most recent PM2.5 measurement
  //    near our coordinates.
  const url =
    'https://api.openaq.org/v3/measurements' +
    '?coordinates=' + LATITUDE + ',' + LONGITUDE +
    '&parameter=pm25&limit=1&order_by=datetime&sort=desc';
  const data = JSON.parse(UrlFetchApp.fetch(url).getContentText());

  // 2. The reading lives at results[0].value — guard against an
  //    empty response so a quiet feed does not throw.
  const value = (data.results && data.results[0])
    ? data.results[0].value
    : null;
  if (value === null) {
    Logger.log('No PM2.5 reading available — skipping air-quality log.');
    return;
  }

  // 3. Append a dated row to the "Air quality" tab.
  const sheet = ss.getSheetByName('Air quality') || ss.insertSheet('Air quality');
  if (sheet.getLastRow() === 0) {
    sheet.appendRow(['Logged at', 'Parameter', 'Value (µg/m³)']);
  }
  sheet.appendRow([new Date(), 'pm2.5', value]);
  Logger.log('Logged PM2.5: ' + value);
}

/**
 * Fetches a 5-day weather forecast from Open-Meteo and rewrites the
 * "Forecast" tab so it always shows the latest outlook.
 */
function logForecast(ss) {
  // 1. Ask Open-Meteo for daily highs, lows, and rain totals.
  const url =
    'https://api.open-meteo.com/v1/forecast' +
    '?latitude=' + LATITUDE +
    '&longitude=' + LONGITUDE +
    '&daily=temperature_2m_max,temperature_2m_min,precipitation_sum' +
    '&forecast_days=5' +
    '&timezone=' + encodeURIComponent(TIMEZONE);
  const data = JSON.parse(UrlFetchApp.fetch(url).getContentText());
  const daily = data.daily;
  if (!daily || !daily.time.length) {
    Logger.log('No forecast returned — skipping forecast log.');
    return;
  }

  // 2. Zip the parallel arrays into one row per day.
  const rows = daily.time.map((day, i) => [
    day,
    daily.temperature_2m_max[i],
    daily.temperature_2m_min[i],
    daily.precipitation_sum[i],
  ]);

  // 3. Rebuild the "Forecast" tab from scratch — a forecast is a
  //    snapshot, not a log, so the tab always shows the latest five days.
  const sheet = ss.getSheetByName('Forecast') || ss.insertSheet('Forecast');
  sheet.clear();
  sheet.getRange(1, 1, 1, 4)
    .setValues([['Date', 'High °C', 'Low °C', 'Rain mm']]);
  sheet.getRange(2, 1, rows.length, 4).setValues(rows);
  Logger.log('Wrote a ' + rows.length + '-day forecast.');
}

How it works

1. logEnvironmentData is the entry point. It opens the dashboard Sheet once and hands it to the two worker functions, so neither has to reopen it.
2. logAirQuality asks OpenAQ for the single newest PM2.5 measurement near the configured coordinates. It reads results[0].value, guarding against an empty response, then appends a dated row to the Air quality tab — and adds a header first if the tab is brand new.
3. logForecast asks Open-Meteo for five days of daily highs, lows, and rain totals. The API returns parallel arrays, so the script zips them with map into one row per day.
4. Because a forecast is a snapshot rather than a history, logForecast clears the Forecast tab and rewrites it every run — it always shows the latest five-day outlook.

Example run

OpenAQ returns the latest measurement:

{ "results": [{ "parameter": "pm25", "value": 11.4 }] }

After a run, the two tabs look like this:

Air quality (a growing log)

Logged at	Parameter	Value (µg/m³)
2025-12-29 07:00	pm2.5	9.8
2025-12-30 07:00	pm2.5	11.4

Forecast (rewritten each run)

Date	High °C	Low °C	Rain mm
2025-12-30	8	3	0.0
2025-12-31	7	2	4.2
2026-01-01	9	5	1.1

Trigger it

To keep the dashboard current, run it once a day:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Click Add Trigger.
3. Choose logEnvironmentData, event source Time-driven, type Day timer, and pick an early hour such as 7am to 8am.
4. Save and approve the authorisation prompt.

The air-quality tab then grows by one row a day, while the forecast tab is refreshed in place every morning.

Watch out for

• OpenAQ coverage is uneven. Some areas have no nearby PM2.5 sensor, so results can come back empty — the guard handles that, but you may need to widen the search or accept gaps in the log.
• The current reading is whatever the nearest station last reported. It can be an hour or more old; OpenAQ relays sensor data, it does not generate it.
• Open-Meteo forecasts shift between runs. The Forecast tab is deliberately overwritten so it never shows stale figures — do not switch it to appendRow expecting a history.
• Both APIs are free but rate-limited. One run a day is well within limits; do not loop either call.
• Parallel arrays only line up if every variable is present. If you add a daily variable that a location does not support, a row can end up short — check the response before extending the map.