Schedule Gap Filler
Your EMR has a waitlist. Your calendar has gaps. Nothing connects them. This tool scans provider calendars for open slots, matches waitlist patients by preference, and emails your front desk with who to call.
The problem
A 3-provider physio clinic averaging $98/session with a 15% no-show rate loses over $88,000/year in unfilled slots. The waitlist exists, but it’s static — it doesn’t match patients to openings, prioritize by urgency, or alert anyone when a slot opens.
How it works
- 1Scans provider calendars daily for unfilled slots during clinic hours
- 2Identifies gaps of 30+ minutes between appointments (configurable)
- 3Cross-references a simple waitlist spreadsheet with patient preferences
- 4Matches patients to open slots by preferred provider, day, and time
- 5Emails your MOA a formatted summary with matched patients and phone numbers
What you need
- ✓A calendar per provider (Google Calendar, Outlook, or Apple Calendar)
- ✓A simple waitlist spreadsheet (Google Sheets, Excel, or Numbers)
- ✓5–10 minutes to configure and paste the script
Download
Setup guide
Create your Waitlist spreadsheet
Create a spreadsheet with these columns: Patient Name | Phone | Preferred Day | Preferred Time | Preferred Provider | Status. Add your waitlist patients and set Status to "Waiting" for active entries. This works in Google Sheets, Excel, or Numbers.
Create your Config sheet
Add a second tab called "Config" with two columns: Setting | Value. Required settings: Provider Calendars (comma-separated calendar IDs or names), MOA Email (who gets the alerts). Optional: Slot Duration (min), Scan Days Ahead, Clinic Start Hour, Clinic End Hour.
Google ecosystem setup
Download the Google Apps Script file below. In your Google Sheet, go to Extensions → Apps Script. Delete any existing code and paste the script. Save. Back in the sheet, click "Schedule Gap Filler → Scan Now" from the menu bar to run your first scan. Click "Set Up Daily Scan" to automate it at 7 AM daily.
Microsoft ecosystem setup
Download the VBA module below. Open Excel, press Alt+F11 to open the VBA editor. Insert → Module, then paste the code. Save the workbook as .xlsm (macro-enabled). Make sure Outlook is open, then run the ScanForGaps macro. You can add a button to the Config sheet for easy one-click scanning.
Apple ecosystem setup
Download the Apple Shortcuts file below and open it on your Mac or iPhone. Edit the variables at the top: CalendarName (your provider schedule calendar name), MOAEmail, SlotMinutes, ScanDays, ClinicStart, ClinicEnd. Run manually or set up a Shortcuts Automation to trigger daily at 7 AM.
Sheet structure
Tab: Waitlist
| Column | Description | Example |
|---|---|---|
| Patient Name | Patient’s name (initials OK for privacy) | J. Smith |
| Phone | Contact number for booking | 604-555-0123 |
| Preferred Day | Day preference (blank = any) | Monday |
| Preferred Time | Time preference (blank = any) | Morning |
| Preferred Provider | Provider preference (blank = any) | Dr. Patel |
| Status | Current status | Waiting |
Tab: Config
| Column | Description | Example |
|---|---|---|
| Setting | Configuration key | Provider Calendars |
| Value | Configuration value | dr.smith@clinic.ca, dr.patel@clinic.ca |
Need help setting this up?
Book a free 30-minute call. We'll walk through the setup together and customise it for your clinic.