TOSCTOSC
📅

Staff Coverage Gap Detector

Multi-provider clinics discover coverage gaps the morning of. This script scans your Google Calendar every Monday and emails you any staffing holes for the next two weeks.

Google Calendar + Apps ScriptEasyPrevents same-day scrambles

The problem

Multi-provider clinics manage physician and MOA schedules across shared Google Calendars or whiteboards. Coverage gaps — no MOA on Thursday afternoon, no physician Friday morning, overlapping vacations without backup — are discovered the morning of. The clinic manager scrambles to find coverage, reschedule patients, or close a session. No EMR manages staff scheduling. Google Calendar shows events but doesn’t analyze them for gaps.

Why not just use your EMR / existing tools?

EMRs handle patient scheduling, not staff scheduling. Google Calendar shows events but doesn’t analyze coverage gaps. No clinic tool proactively alerts on staffing holes. This is the only automation that runs on a system clinics ALREADY use (Google Calendar for staff scheduling) with zero new data entry.

How it works

  1. 1Reads your existing “Staff Schedule” Google Calendar — no new data entry required
  2. 2Runs every Monday morning: scans the upcoming 2 weeks for coverage gaps
  3. 3Identifies roles from event titles (Dr/Physician/NP = provider, MOA/Admin/Reception = admin)
  4. 4Checks each clinic session against your minimum staffing rules
  5. 5Sends a formatted email to the clinic manager listing every gap with details

What you need

  • ✓Google Calendar used for staff scheduling (most clinics already have this)
  • ✓Google account with Gmail and Apps Script access
  • ✓5 minutes to paste the script and configure your rules

Download

Setup guide

1

Verify your Staff Schedule calendar

You likely already have a shared Google Calendar for staff shifts. If not, create one called "Staff Schedule" and add events for each staff member's shifts. Event titles should include a role keyword like "Dr. Smith - Morning" or "MOA Sarah - Full Day".

The script identifies roles from event titles. Include keywords like Dr, Physician, NP for providers, or MOA, Admin, Reception for admin staff. Events with Vacation, Off, or Away are treated as absences.
2

Get your calendar ID

In Google Calendar, click the three dots next to your staff schedule calendar → Settings. Scroll to "Integrate calendar" and copy the Calendar ID (it looks like abc123@group.calendar.google.com).

3

Create a Google Sheet and paste the script

Create a new Google Sheet (it's just a host for the script — no data goes here). Go to Extensions → Apps Script. Delete any existing code and paste the full script from the download below.

4

Update the configuration

At the top of the script, update: CALENDAR_ID with your staff calendar ID, ALERT_EMAIL with the clinic manager's email, SESSIONS with your clinic's operating hours, and MIN_PROVIDERS / MIN_ADMIN with your minimum staffing requirements.

5

Run the setup function

Select setupCoverageDetector from the function dropdown and click Run. Grant permissions when prompted. The script will immediately scan and send a test alert, then run automatically every Monday.

Full code

coverage-gap-detector.gs
/**
 * TOSC Staff Coverage Gap Detector
 * Scans Google Calendar for staffing gaps and alerts the clinic manager.
 *
 * SETUP:
 * 1. Create a Google Calendar called "Staff Schedule" (or use existing)
 * 2. Add events for each staff member's shifts (e.g., "Dr. Smith - Morning")
 * 3. Open Google Sheets → Extensions → Apps Script, paste this code, save
 * 4. Run setupCoverageDetector() once to create the weekly trigger
 * 5. Update CONFIG below with your calendar ID and clinic rules
 *
 * HOW IT READS EVENTS:
 * - Events must include a role keyword: "Dr", "Physician", "NP" (provider)
 *   or "MO

// ... Enter your email above to see the full code

Need help setting this up?

Book a free 30-minute call. We'll walk through the setup together and customise it for your clinic.

Book a Setup Call