Fill reopened Acuity slots from a Google Sheets waitlist

Every hour during business hours (for example 8am to 7pm on weekdays, configurable), watch Acuity Scheduling for freshly opened appointment slots and offer them to the best-fit waitlisted client via a personalized Gmail draft. The trigger is cron.

Configuration lives at the top of the workflow: the Google Sheets spreadsheet ID and tab name for the waitlist, the list of appointment type IDs to watch (the popular sessions the practice actually wants to fill), the sender name and signature to use in the draft, the self-scheduling base link for Acuity, and the hours-of-day / days-of-week window.

Step 1: snapshot current availability. For each configured appointment type, call Acuity's Get Available Dates for the next 14 days (month by month if the range crosses a month boundary). For every date that comes back, call Get Available Times to enumerate the individual slots. Build a normalized set of slot identifiers keyed by appointment type, date, and start time.

Step 2: detect newly opened slots. Persist the previous run's availability snapshot in a hidden tab of the same waitlist spreadsheet (for example a tab named 'availability_snapshot' with columns appointment_type_id, date, time, first_seen_at). Use Google Sheets Get Values to read the last snapshot, diff against the fresh set from step 1, and treat any slot present now but not before as newly opened. On the very first run there is no prior snapshot: seed the snapshot, log that it was a cold start, and skip offering (nothing is truly 'new' yet). Use Update Values to overwrite the snapshot tab with the fresh set at the end of the run so the next hour has a clean baseline.

Step 3: read the waitlist. The waitlist lives in the main tab of the configured spreadsheet with columns: client name, email, phone, preferred session type, preferred days, preferred times, notes, added-on date, status (blank or 'offered'), last offered slot, last offered at. Use Google Sheets Get Values to load all rows. Ignore rows where status is 'offered' or the email is missing.

Step 4: match newly opened slots to waitlist entries. For each new slot, filter to waitlist rows whose preferred session type matches the slot's appointment type, whose preferred days-of-week include the slot's day, and whose preferred time window covers the slot's start time. Rank candidates by how long they have been waiting (older added-on date wins), with a small tie-breaker for tighter preference fit (an exact time-window match beats a broad 'anytime'). Pick the single best match per slot. If no candidate scores a real fit, skip the slot cleanly rather than spamming a marginal match. Do not offer the same slot to the same client twice: if a row's last offered slot equals the candidate slot, skip. Do not offer two different slots to the same client in the same run; pick their strongest match.

Step 5: draft the offer email. For each matched pair, use Gmail's Create a Draft to write a warm, personal, non-pushy note in the practice's voice. Open with the client's first name, mention that a slot matching their preferences just opened up, give the specific date, day of week, start time, and session type, include the Acuity self-scheduling link so they can grab it themselves, note that the slot is first-come first-served, and close with a graceful out (something like 'no pressure, just wanted to give you first look'). Keep it to a few short sentences; do not sound automated.

Step 6: mark the waitlist row. Use Google Sheets Update Values to set the row's status column to 'offered', write the specific slot into the last offered slot column (formatted as appointment_type / date / time), and stamp last offered at with the current timestamp. This prevents the same client from being emailed again for a slot they already saw and lets a human triage the row after the offer lands.

Step 7: log the run summary. Return a brief message to the workflow feed noting how many new slots were detected, how many drafts were written, and any slots that had no strong waitlist match (so the practice can eyeball whether the rules are too strict). If nothing new opened, exit cleanly with a one-line 'no new slots this hour' summary.

Guardrails: never send email directly, always create a draft so a human reviews before it goes out. Never book on the client's behalf; only offer the self-scheduling link. If Acuity returns no availability at all, treat it as a data hiccup and skip the snapshot update rather than wiping the baseline.