# See where every traveling employee is during a disruption

> Turns your trip roster into a live board of who is in the air, landed, delayed or cancelled, so you can see who needs help first.

- Workflow type: app
- Services: AviationStack, Google Sheets, Slack Bot
- Categories: Operations, HR & People
- Published: 2026-08-14

## What it does

- Pulls your trip roster straight from a Google Sheet and checks each traveler's flight live, so the board is current the moment you open it.
- Sorts everyone into In the air, Landed, Delayed, Cancelled and Needs attention, with the worst disruptions sitting at the top.
- Shows every time in the local time of the airport it belongs to, so nobody is doing timezone math in the middle of a bad afternoon.
- Lets you post a heads-up to your travel desk channel and mark a traveler as handled, writing your name and the time back to the roster.

## What you'll need

- A Google Sheet holding your trip roster with traveler name, flight number, flight date, destination and phone.
- Two spare columns in that roster for who handled a traveler and when.
- A flight data account from AviationStack. The free tier is enough for a small roster; heavy use or past-date lookups need a paid plan.
- A Slack workspace and a channel your travel desk watches.

## Prompt

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.

## How to customize

- Change what counts as Needs attention. The default is anything cancelled or running more than an hour late.
- Point the heads-up button at a different Slack channel, and edit the message wording before it sends.
- Add your own roster columns, such as trip purpose or the traveler's manager, and filter the board by them.

## FAQ

### What happens to a row with a missing or unrecognized flight number?

It still appears on the board, marked Unknown, showing everything the roster does know such as name, destination, date and phone. Rows are never dropped silently, and the board keeps a visible count of the ones it could not match so you can clean up the roster.

### Does this change my spreadsheet?

Only the two columns you set aside for tracking who handled a traveler and when. Everything else in the roster is read, never edited.

### Will this work on the free AviationStack plan?

Yes for live, same-day flight status on a modest roster. The free plan comes with a small monthly request allowance, so the board checks flights when you open it or press Refresh rather than polling all day. Looking up flights on past dates requires a paid plan.

### Are times shown in my timezone or the traveler's?

Each time is shown in the local timezone of the airport it belongs to, with the zone labelled. A departure reads in departure-airport time and an arrival reads in arrival-airport time.

### Can two coordinators work the board at the same time?

Yes. When someone marks a traveler as handled, their name and the time land in the roster and show up on the board for everyone, so two people never end up chasing the same traveler.

Use this prompt in General Input: https://www.generalinput.com/prompts/see-where-every-traveling-employee-is-during-a-disruption