Turn a guest flight spreadsheet into an airport pickup plan

Build an app our events and office team opens on arrivals day to turn a spreadsheet of guest flights into an actual pickup plan. The main screen is a pickup board for a single arrivals day: every arriving guest, when their plane really lands, and which car or shuttle is collecting them. This is a working surface the coordinator keeps open and refreshes across the day, not a report or a notification.

Load the guest list with Google Sheets Get Values from a tab the coordinator chooses. Each row is one guest with their name, flight number, arrival airport IATA code, arrival date, and group or company. Let the coordinator set the spreadsheet, the tab and the arrivals date at the top of the board.

For each guest row, pull live flight detail from AviationStack Get Flights using the flight number and arrival date. Take the actual or estimated landing time, the arrival terminal and gate, and the flight status such as scheduled, active, landed, delayed or cancelled. This is the per guest source of truth and it is what the pickup buckets are built from.

Separately, show the airport's current day arrivals board from AviationStack Get Flight Timetable next to the guest list for context, so the coordinator can see what is happening at that airport more broadly. Get Flight Timetable requires both an iataCode and a type, and this app always passes type=arrival.

Rate limiting is load bearing here and the app must be built around it. Get Flight Timetable requires a paid AviationStack plan and is throttled to roughly one request every 10 seconds on paid plans, and one per 60 seconds on Free. Fetch the arrivals board once per airport per refresh and cache it in app state for that whole refresh cycle. Never call it once per guest row. If guests are landing at three airports, that is three timetable calls per refresh, spaced out, and never more. Per guest status always comes from Get Flights instead.

Render every arrival time in the arrival airport's local timezone and label it clearly, because the coordinator is often sitting in a different country. Look up each airport's timezone with AviationStack Get Airports, matching on IATA code, and cache it per airport since it does not change during the day.

Bucket guests into 30 minute pickup windows using actual or estimated landing time, never scheduled time. Everyone landing at the same airport inside the same 30 minute window forms one pickup group so a single car can collect several guests at once. Show each window as a group header with the airport, the window, the terminals involved and the guest count, with the individual guest rows underneath. Make the window length a setting the coordinator can change, and allow a buffer after landing for immigration and baggage.

In each row the coordinator assigns a driver or shuttle. Saving that assignment writes straight back to the spreadsheet with Google Sheets Update Values into a driver column on that guest's row, so the sheet stays the record of truth and the work survives a reload.

From an assigned pickup window, the coordinator creates the slot on the driver's calendar with Google Calendar Create Event. The event spans the pickup window, and its title and description carry the airport, the terminal, the landing time and the names of the guests in that pickup.

When the day plan is ready, post the finished schedule to the ops channel with Slack Bot Send a Message, grouped by pickup window and showing time, airport, terminal, assigned driver and guest names. Use the slackbot service for this.

Build in change detection that is visible rather than silent, because this is the difference between a plan the team trusts and one they re-check by hand. Store the landing time and terminal each pickup was assigned against. On refresh, if a flight is delayed enough to move that guest out of the window their pickup was assigned in, or if the arrival terminal changes, do not quietly re-sort them into a new bucket. Leave the assignment where it is and flag it loudly on the board, showing the old value beside the new one, so the coordinator can see that an already assigned pickup has gone stale and decide what to do. Guests with no assignment yet can re-bucket freely. Give the coordinator a one click way to accept the change, which moves the guest into the correct window and clears the flag.

AviationStack is read only and every one of its endpoints is a GET, so nothing is ever written back to it. All persisted state lives in Google Sheets for driver assignments, Google Calendar for the pickup slots, and Slack for the posted schedule.