Case study · Automation
Directory Operations System
A multi-site counseling practice keeps a listing for every clinician on three external therapist directories. Someone has to track, per person per platform: does a profile exist, is it live, is it accepting patients, who holds the login — and when a clinician leaves, get all three listings taken down.
Confidential mockup — all data fabricated
That tracking lived in scattered spreadsheets with no automation. This consolidated it into one workbook with a bound Apps Script: ~2,600 lines automating onboarding, offboarding, bulk campaigns, and login-failure triage.
Client name, clinician names, profile URLs and all credentials are replaced with invented equivalents. The record counts reflect the real system's scale; nothing below identifies a real person.
01 — What the system holds
02 — One dropdown, five tabs
Offboarding used to be a manual sweep through every tab that mentioned a person, which is exactly how records get half-deleted. Now it's one status change. Flip a clinician to Offboarded in column E — or run it from the Directory tools menu, which is where the rest of the automation lives.
Directory tools
Prepare removal emails for ?
This confirms the request is a genuine offboarding and any required sign-off is cleared. Type SEND-PREP to continue, or archive without preparing emails.
Replay —
Gmail drafts — annotation, not part of the sheet
Emails are drafted, never sent — they land in Gmail drafts for a human to review. A platform with incomplete or contradictory data doesn't get a guessed-at draft; it gets a blocked task with the reason written out.
03 — What the incidents taught
Address columns by name
Someone reordered a column by hand and writes started landing in the wrong fields — a checkbox column receiving text. Every write now resolves its column by header name, so the layout can move and the code still lands correctly.
Never reuse an ID
IDs were once assigned by incrementing the last row, which could silently merge two people's records. Assignment now scans every active and archived row, behind a lock that blocks concurrent operations.
Capture before you delete
The email step used to re-read profile data after those rows had already been deleted in the same run, producing empty drafts. Data is now captured during archiving and passed forward.