See where every traveling employee is during a disruption

Build me a live traveler status board that our travel manager or executive assistant opens the moment there is a disruption, to see where every traveling employee is right now. It is a single working screen, not a report: one board, filters across the top, and per-traveler actions on every row.

Roster source. Read our trip roster from a Google Sheets spreadsheet using Get Values. The roster columns are traveler name, flight number, flight date, destination and phone, plus two columns the app writes into: handled by, and handled at. Make the spreadsheet ID, sheet name and header row configurable in settings, and map the columns by header name rather than hard-coding letters so a reordered roster does not break the board.

Live enrichment. For every roster row, look up current status with AviationStack Get Flights, matching on the flight number (flight_iata) and the flight date. Pull flight status, scheduled, estimated and actual departure and arrival times, delay minutes, the departure and arrival airport IATA codes, and the airline. Multiple travelers on the same flight must share one lookup, not one lookup each.

Airport-local times. Load the airport reference catalog with AviationStack Get Airports and build an IATA code to timezone lookup, cached for the session rather than refetched per row. Every time displayed on the board must render in the local timezone of the relevant airport: departure times in the departure airport's zone, arrival times in the arrival airport's zone, each with the zone labelled next to it so a coordinator never has to convert anything in their head.

The board. Group travelers into five lanes: In the air, Landed, Delayed, Cancelled, and Needs attention. Needs attention is the lane the coordinator works first, so put it at the top: it collects cancellations, delays past a configurable threshold (default 60 minutes), and rows the app could not resolve. Sort Needs attention by cancellation first, then by delay minutes descending, so the most stranded traveler is always the top row. Each row shows traveler name, flight, airline, route, status, delay in minutes, the current arrival estimate in airport-local time, destination, phone, and the handled-by owner if one is set.

Unknown rows must never vanish. A roster row with a missing, malformed or unrecognized flight number, or one that AviationStack returns no match for, still renders as an Unknown row carrying everything the roster does know (name, destination, date, phone) plus a short reason such as no flight number in roster or no live match found. These sit in Needs attention, and the board shows a visible count of unresolved rows so the coordinator can fix the roster. Never silently drop a row.

Filters. Filter by region (derive it from the arrival airport's country or continent using the Get Airports data, falling back to the roster destination), by traveler (type-ahead on name), and by date. Filters combine, and the per-lane counts update as filters change.

Row action one, post a heads-up. From any row, send a message to our travel desk channel using Slack Bot Send a Message. Prefill the message with traveler name, flight number, current status, delay, the new arrival estimate in airport-local time, and the traveler's phone, and let the coordinator edit the text before it goes. The target channel is configurable in settings. After a successful post, show on the row that a heads-up was sent and when.

Row action two, claim ownership. A Mark handled button writes the coordinator's name and the current timestamp into that row's handled by and handled at columns using Google Sheets Update Values, targeting only that row's two cells. The board then shows the owner name and time on the row so two coordinators never chase the same person twice. Refreshing the board re-reads those columns from the sheet, so ownership survives a reload and is visible to everyone working the board. Allow clearing an owner if someone claimed a row by mistake.

Fetching model and plan limits. AviationStack is a read-only API, so nothing is ever written back to it. Every write in this app lands in Google Sheets or Slack. Fetch flight status when the board loads and when the coordinator presses Refresh, rather than polling continuously, because the AviationStack Free plan is capped near 100 requests per month and is HTTP only. Show the timestamp of the last refresh prominently. Historical lookups by flight date require a paid AviationStack plan, so if a past-date lookup is refused, render that row as Unknown with that reason rather than failing the whole board. The same applies to a quota or rate-limit error: degrade to Unknown rows with a clear banner explaining why, never a blank screen.

Design. This gets used during a stressful hour, so make it dense but legible: compact rows, status carried by clear color and label together, Needs attention visually distinct, and the whole board scannable from a step back.