Referral Tracker
Track every referral from the moment it’s sent to the day the patient is booked. Automatic alerts when referrals go stale so nothing falls through the cracks.
The problem
Referral leakage is one of the biggest hidden costs in Canadian clinics. A referral gets sent, and nobody follows up. The specialist never confirms receipt. The patient never gets booked. Weeks pass. The patient calls back, frustrated.
How it works
- 1Log referrals in a structured Google Sheet with status tracking
- 2Status changes auto-timestamp (Sent → Confirmed → Booked → Complete)
- 3Daily check at 8 AM flags stale referrals (7+ days unconfirmed, 14+ days unbooked)
- 4Email alert sent to clinic manager with a summary of overdue referrals
What you need
- ✓A Google account with Google Sheets access
- ✓Access to Google Apps Script
- ✓5 minutes for setup
Download
Setup guide
Create a new Google Sheet
Open Google Sheets and create a blank spreadsheet. Name it 'Referral Tracker'.
Open Apps Script
Go to Extensions → Apps Script. Delete any existing code.
Paste the script
Copy the full Referral Tracker script below and paste it into the editor. Save.
Set your manager email
Find MANAGER_EMAIL at the top of the script and set it to your clinic manager’s email address.
Run setup
Select 'setupReferralTracker' from the function dropdown and click Run. Authorise when prompted. This creates the sheet headers, validation rules, formulas, and triggers.
Start logging referrals
Go back to the spreadsheet. You’ll see the Referrals sheet with headers. Start entering referral data. The Status column is a dropdown (Sent, Confirmed, Booked, Complete, Cancelled). When you change the status, the date columns auto-fill.
Full code
/**
* TOSC Referral Tracker
* Lifecycle tracking from sent → confirmed → booked.
* Auto-reminders on stale referrals.
*
* SETUP:
* 1. Create a new Google Sheet
* 2. Open Extensions → Apps Script → paste this code
* 3. Run setupReferralTracker() once
* 4. Set MANAGER_EMAIL below
*/
const MANAGER_EMAIL = ""; // e.g. "moa@yourclinic.ca"
const SHEET_NAME = "Referrals";
const STALE_CONFIRM_DAYS = 7; // days before flagging unconfirmed
const STALE_BOOK_DAYS = 14; // days before flagging unbooked
function setupReferralTracker() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
l
// ... Enter your email above to see the full codeSheet structure
Tab: Referrals
| Column | Description | Example |
|---|---|---|
| Patient Name | Full patient name | Jane Smith |
| PHN | BC Personal Health Number | 9876543210 |
| Specialist | Specialist or clinic referred to | Dr. Lee - Cardiology |
| Referred By | Referring provider | Dr. Patel |
| Date Sent | Date referral was sent | 2025-03-01 |
| Status | Current status (dropdown) | Sent |
| Date Confirmed | Auto-filled when status changes to Confirmed | 2025-03-05 |
| Date Booked | Auto-filled when status changes to Booked | 2025-03-10 |
| Appointment Date | Actual appointment date with specialist | 2025-04-15 |
| Notes | Any additional notes | Faxed to office |
| Days Since Sent | Auto-calculated formula | 14 |
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