Operations Dashboard for a Service-Based Healthcare Team
Appointments, staff schedules, and billing follow-ups lived in disconnected spreadsheets and chat threads. With no single view, follow-ups slipped and piecing together the daily picture took an hour.
Industry
Service-based healthcare clinic
Role
Systems consultant — workflow design, automation build, documentation and handover
Impact
The morning status check dropped from close to an hour of cross-referencing to a few minutes on one screen, and overdue follow-ups now surface themselves instead of waiting to be found.
01 - Problem
The problem
This service-based healthcare practice ran daily operations across three sheets that never talked to each other: appointments, a staff schedule in a different format, and a billing follow-ups sheet updated whenever someone had a spare moment. Coordination happened in a busy group chat on top of that — "did anyone call back the Tuesday patient?" — with answers scrolling out of view within hours. Every morning the practice manager spent the better part of an hour cross-referencing tabs to see who was working, which appointments were unconfirmed, and which billing items were aging.
Billing follow-ups suffered most, surfacing only when someone happened to open that sheet. There was also no view of workload per staff member, so some people were quietly overloaded. The real problem wasn't a missing tool — it was missing structure: the same status word meant different things in different sheets, dates were free text, and items had no owner. Any automation built on that would break within a week or alert on bad data, so the engagement started with data structure and only then moved to the dashboard and automation.
02 - System Flow
How the system moves
Staff update sources
Daily entries in Google Sheets
Scheduled refresh runs
Apps Script time-driven trigger
Data validated, normalized
Header checks and date parsing
Dashboard recalculates
Consolidated Google Sheets view
Status flags applied
Overdue and aging logic
Make scans flagged rows
Scheduled Make scenario
Slack alerts posted
Ops channel plus daily digest
Team clears items
Flags reset on next refresh
03 - Build
What I built
Data structure cleanup before any automation
I standardized the three source sheets first: one shared status vocabulary, an owner column on every item, due dates stored as real dates, and validation on the fields staff touch daily. Automating on inconsistent data just produces faster wrong answers. I also agreed with the team, in writing, on what each status means, so the flags read the same way for everyone.
Consolidated operations dashboard in Google Sheets
One dashboard tab became the single view of the day: today's appointments and confirmation status, staffing against the schedule, and open billing follow-ups sorted by how long they've waited. A workload section shows open items per staff member, so the manager sees who is stretched at a glance. The team already lived in Google Sheets, so this internal tool meant no new logins and no adoption battle.
Automated refresh pipeline with Apps Script
A time-driven Apps Script pulls from the three source sheets several times a day, validates and normalizes the data, and rebuilds the dashboard. It matches columns by header name rather than position, parses dates defensively, and routes anything it can't safely interpret to a "needs fixing" list instead of guessing. Every run stamps a visible last-refreshed time.
Status flags and Slack alerts via Make
Flag logic marks items needing attention: billing follow-ups past due, appointments unconfirmed inside a set window, and items with no owner. A Make scenario reads flagged rows on a schedule and posts to the Slack ops channel — one morning digest plus targeted alerts for genuinely overdue items, with a last-alerted timestamp so nothing spams the channel. Alerts carry reference IDs, not patient details, and link to the dashboard.
Documentation and handover
I wrote a short operations guide covering how the pipeline works, what each flag means, how to clear the "needs fixing" list, and what to check if alerts stop. We ran two failure drills together — a renamed column and a stale refresh — so the team has seen the system fail safely and knows the recovery steps. The goal is a system the team owns, not one they call me to operate.
04 - Impact
Business impact
The morning coordination check went from close to an hour of cross-referencing sheets and scrolling chat to a few minutes on one screen.
Overdue billing follow-ups now surface through flags and Slack alerts instead of depending on someone remembering to look, so items stopped quietly aging.
Per-staff workload visibility turned rebalancing assignments into a quick, factual conversation instead of guesswork.
The group chat stopped being the system of record; Slack alerts point back to one dashboard, so decisions come from one shared picture of the day.
The practice manager can answer "what needs attention today" without messaging three people and waiting for replies.
Onboarding new admin staff got easier because the workflow is documented in one place instead of living in one person's head.
05 - Edge Cases
What had to be handled
Dates typed as free text or inconsistent formats: the refresh parses what it can and routes malformed values to a "needs fixing" list with a reason, rather than dropping the row or miscalculating an overdue flag.
Renamed or deleted source columns: the script matches by header name and halts with a clear Slack error if one is missing, instead of mapping data into the wrong fields.
Repeat alerts for the same item: Make stamps each row with a last-alerted timestamp, so items re-alert on a set cadence rather than on every run.
Weekends and clinic holidays: overdue logic counts working days from a small holiday reference tab, so Monday mornings don't open with false alarms.
Sensitive details in a chat tool: Slack alerts carry only an internal reference ID, item type, and owner — patient-identifying details stay in the source sheets.
Silent pipeline failure: if the last-refresh timestamp goes stale, a separate Make heartbeat check fires a warning, so the team knows the data is old instead of trusting an outdated view.
06 - Improvements
What I would improve next
Connect the booking system directly via API integration so appointment data lands in the pipeline automatically instead of being re-typed.
Add two-way Slack actions so staff can mark a follow-up done or reassign it straight from the alert, without opening the spreadsheet.
Build a weekly reporting dashboard on the same data — follow-up aging trends and workload per staff over time, not just today's snapshot.
Plan a migration off spreadsheets to a lightweight database as volume grows, since Google Sheets has practical limits as a long-term system of record.
Related capabilities