Build a multi-API health-check monitor

Northwind’s tools lean on a handful of outside services — a payment provider, a mapping API, a couple of status pages. When one of them goes down, the studio usually finds out the worst way: a customer complains, or a script fails halfway through. Nobody is sitting there refreshing status pages all day.

This script turns a single sheet into a watchtower. Each row lists a service, a URL to check, and the HTTP status that means “healthy”. On every run it hits each URL, records when each service was last seen up or down, and emails the ops team the moment anything fails. Put it on a trigger and the studio hears about an outage before its customers do.

What you’ll need

• A Health checks sheet with a header row and these columns: service (a friendly name), url (the endpoint to check), expectedStatus (the HTTP code that means healthy, usually 200), lastOk, and lastFail — the last two are filled in by the script.
• The spreadsheet’s ID, pasted into the HEALTH_SHEET_ID constant.
• A monitoring address for the alert email, set in the ALERT_EMAIL constant.

The script

// The spreadsheet that holds the health-check rows.
const HEALTH_SHEET_ID = '1abcHealthId';

// Where failure alerts are sent.
const ALERT_EMAIL = '[email protected]';

/**
 * Checks every service in the Health checks sheet, stamps the last
 * success or failure time, and emails ops if anything is down.
 */
function runHealthChecks() {
  const sheet = SpreadsheetApp.openById(HEALTH_SHEET_ID).getSheets()[0];

  // 1. Read the whole sheet; keep the full grid so we can write it back.
  const values = sheet.getDataRange().getValues();
  const [header, ...rows] = values;
  const col = Object.fromEntries(header.map((name, i) => [name, i]));

  if (!rows.length) {
    Logger.log('No services to check — nothing to do.');
    return;
  }

  // 2. Check each service in turn, collecting any failures.
  const failures = [];
  rows.forEach((row, i) => {
    // i+1 because values[0] is the header row.
    const rowIndex = i + 1;
    try {
      // Fetch the URL; muteHttpExceptions lets us inspect any status code.
      const res = UrlFetchApp.fetch(row[col.url], { muteHttpExceptions: true });
      const code = res.getResponseCode();
      const ok = code === parseInt(row[col.expectedStatus], 10);

      // Stamp lastOk or lastFail depending on the result.
      values[rowIndex][ok ? col.lastOk : col.lastFail] = new Date();
      if (!ok) {
        failures.push(row[col.service] + ' (' + code + ')');
      }
    } catch (err) {
      // A thrown error usually means the host is unreachable entirely.
      values[rowIndex][col.lastFail] = new Date();
      failures.push(row[col.service] + ' (error)');
    }
  });

  // 3. Write the updated timestamps back to the sheet in one call.
  sheet.getDataRange().setValues(values);

  // 4. If anything failed, alert the ops team.
  if (failures.length) {
    GmailApp.sendEmail(
      ALERT_EMAIL,
      'API checks failing',
      'These services failed their health check:\n\n' + failures.join('\n')
    );
  }

  Logger.log('Checked ' + rows.length + ' services; ' +
    failures.length + ' failing.');
}

How it works

1. runHealthChecks opens the Health checks sheet and reads the whole grid in one call, keeping values intact so the updated grid can be written back later. It splits off the header to build a col lookup.
2. If there are no service rows it logs a message and stops.
3. For each row it fetches the URL with muteHttpExceptions: true, which means a 404 or 500 comes back as a status code to inspect rather than a thrown error.
4. It compares the response code to the row’s expectedStatus. A match stamps the lastOk cell with the current time; a mismatch stamps lastFail and adds the service to the failures list.
5. If the fetch throws — typically a host that does not resolve or refuses the connection — the catch block stamps lastFail and records the failure as an error.
6. It writes the whole grid back in a single setValues call, then, if the failures list is non-empty, emails the ops team a plain list of what is down.

Example run

A Health checks sheet before a run:

service	url	expectedStatus	lastOk	lastFail
Payments API	https://api.pay.example/health	200
Maps API	https://maps.example/status	200

If the Maps API is returning a 503, after the run the sheet reads:

service	url	expectedStatus	lastOk	lastFail
Payments API	https://api.pay.example/health	200	2025-11-12 09:00
Maps API	https://maps.example/status	200		2025-11-12 09:00

And the ops inbox gets an email:

Subject: API checks failing

These services failed their health check:

Maps API (503)

Trigger it

A health check is only useful if it runs often, so put it on a frequent timer:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Click Add Trigger.
3. Choose runHealthChecks, a Time-driven source, a Minutes timer, and an interval such as every 15 or 30 minutes.
4. Save, and approve the URL-fetch and Gmail authorisation prompt the first time.

Watch out for

• Every run sends one alert email per failing batch, so a long outage on a 15-minute trigger means an email every 15 minutes. To quieten that, only email when a service flips from healthy to failing rather than on every run.
• Each check is one UrlFetchApp call. A sheet of many services on a frequent trigger eats into the daily fetch quota — widen the interval or trim the list if you hit the limit.
• expectedStatus must be a single code. A service that legitimately returns 301 or 302 will be flagged as failing unless you set its expected status to match, or follow redirects.
• A slow endpoint can make UrlFetchApp.fetch hang until it times out, and many slow rows together can push the run past the six-minute execution limit. Keep the URL list focused on real health endpoints.
• The script checks reachability and status code only — it does not inspect the response body. A service that returns 200 with an error message inside will still look healthy.