Build a customer-facing status page

When something goes wrong, clients want one place to check before they email support. Northwind used to answer “is the portal down?” by hand, one reply at a time, while the team was already busy fixing the actual problem. A status page turns those questions into a link.

This automation publishes a live status page straight from a Google Sheet. A team member flips a cell from operational to degraded or down, and the public page reflects it on the next refresh. There is no separate dashboard to keep in sync — the spreadsheet is the dashboard, and the web app just renders it.

What you’ll need

• A Google Sheet with one row per service. Column A holds the service name, column B holds the status — one of operational, degraded, or down. Row 1 is a header.
• An HTML file named Status in the Apps Script project, created with File > New > HTML file.
• The script deployed as a web app set to Anyone access, so customers can open it without signing in.

The HTML (Status.html)

<h1>Northwind status</h1>
<ul>
  <!-- Loop over the services array passed in from doGet. -->
  <? for (const s of services) { ?>
    <li>
      <!-- A coloured dot: green, amber, or red per status. -->
      <span style="color: <?= s.color ?>">●</span>
      <?= s.name ?>: <?= s.status ?>
    </li>
  <? } ?>
</ul>

The script

// The spreadsheet that holds the live status of each service.
const STATUS_SHEET_ID = '1abcStatusId';

// Status value -> dot colour. The single source of truth for the page.
const STATUS_COLOURS = {
  operational: '#10b981', // green
  degraded: '#f59e0b',    // amber
  down: '#ef4444',        // red
};

// Fallback colour for any status value not in the map above.
const UNKNOWN_COLOUR = '#6b7280'; // grey

/**
 * Entry point for the web app. Renders Status.html with the
 * current service list pulled from the spreadsheet.
 */
function doGet() {
  const template = HtmlService.createTemplateFromFile('Status');

  // Expose the service list to the template's <? ?> tags.
  template.services = readStatus();

  return template.evaluate()
    .setTitle('Northwind status');
}

/**
 * Reads the status sheet and returns one object per service,
 * with a colour resolved from the status value.
 */
function readStatus() {
  // Skip the header row, keep the data rows.
  const [, ...rows] = SpreadsheetApp.openById(STATUS_SHEET_ID)
    .getSheets()[0]
    .getDataRange()
    .getValues();

  return rows
    // Ignore any blank trailing rows.
    .filter((row) => row[0])
    .map(([name, status]) => ({
      name,
      status,
      // Look up the colour, falling back to grey for unknown values.
      color: STATUS_COLOURS[status] || UNKNOWN_COLOUR,
    }));
}

How it works

1. A client opens the web app URL, which calls doGet — the standard entry point for an Apps Script web app.
2. doGet creates a template from Status.html and attaches the service list to it as template.services.
3. readStatus opens the status spreadsheet, drops the header row, and skips any blank rows so a stray empty cell never renders an empty bullet.
4. For each service it builds an object with the name, the status text, and a colour resolved from STATUS_COLOURS. Any unrecognised status falls back to grey rather than breaking the page.
5. template.evaluate() runs the <? ?> loop in the HTML, producing one list item per service with a coloured dot.
6. The rendered page is returned to the browser. Every visit re-reads the sheet, so the page is always current.

Example run

The status sheet looks like this:

Service	Status
Client portal	operational
File uploads	degraded
Email delivery	down

Visiting the web app URL renders:

Northwind status

• 🟢 Client portal: operational
• 🟠 File uploads: degraded
• 🔴 Email delivery: down

When someone edits the sheet to set File uploads back to operational, the next page load shows a green dot — no redeploy needed.

Trigger it

The page updates itself on every visit, so the only setup is the deployment:

1. In the Apps Script editor, choose Deploy > New deployment.
2. Pick Web app as the type.
3. Set Execute as to yourself and Who has access to Anyone.
4. Click Deploy, approve the prompt, and copy the web app URL.
5. Point status.northwind.studio at that URL with a redirect or an iframe.

Watch out for

• Anyone access means the page is fully public. Never put internal notes or customer names in the status sheet — only the service rows you want the world to see.
• The page reads the sheet on every request. A burst of traffic during an outage means a burst of SpreadsheetApp reads; if the page is busy, cache the result with CacheService for 30–60 seconds.
• Editing the script and redeploying creates a new URL unless you update the existing deployment. Use Manage deployments and edit in place so the status.northwind.studio link keeps working.
• Status values are case-sensitive. Operational with a capital O will not match STATUS_COLOURS and falls through to grey — keep the sheet lowercase or normalise the value in readStatus.
• getDataRange reads the whole used range. Keep the status sheet to just the service rows so it stays fast and predictable.