Automation: Never Miss a Treatment Plan Review Deadline Again

What This Builds

A fully automated reminder system that checks your caseload tracker every week and sends you a notification — via email or text — listing every client whose 30, 60, or 90-day treatment plan review is due in the next 7 days. You build it once, and it runs automatically every Monday morning without you doing anything.

Missing a treatment plan review deadline is a compliance violation and can jeopardize reimbursement. This automation eliminates that risk entirely.

Prerequisites

• A Google Sheet caseload tracker with client codes and treatment plan due dates (see Level 2 guide)
• A free Zapier account at zapier.com (free tier supports this automation)
• A Gmail account (for notifications) or phone number (for SMS via Zapier's built-in SMS)
• 1–2 hours to build; ~0 minutes per week to run

The Concept

Zapier is like a digital assistant who checks your spreadsheet every week, compares dates to today's calendar, and sends you a note about what's coming up. You don't have to look at the spreadsheet — it looks at it for you and only tells you when there's something to act on.

More precisely: Zapier watches your Google Sheet on a schedule, applies a filter, and triggers a notification when a client's review date is within 7 days. It's the same logic as a calendar reminder, but driven by your actual caseload data rather than manual calendar entries.

Build It Step by Step

Part 1: Prepare your Google Sheet

Before building the automation, your Google Sheet needs a specific structure. Update it to match:

1. Open your caseload Google Sheet
2. Add a new column F: Next Review Due — this will show the nearest upcoming review date for each client
3. In cell F2, enter this formula: =MIN(IF(C2>TODAY(),C2,""),IF(D2>TODAY(),D2,""),IF(E2>TODAY(),E2,""))
4. This shows the nearest future review date for each client
5. In column G, keep your existing "Days Until" formula: =F2-TODAY()
6. Copy both formulas down for all client rows
7. Name your sheet tab something clear like "Caseload" (right-click the tab at the bottom)

What you should see: Column F showing the next upcoming review date for each client; column G showing how many days away it is.

Part 2: Create your Zapier account and start a new Zap

1. Go to zapier.com and sign up for a free account
2. Click Create Zap (or + New Zap)
3. You'll see a two-panel builder: Trigger on the left, Action on the right

Part 3: Set up the Trigger — "Check spreadsheet on a schedule"

1. Click on the Trigger panel
2. Search for and select Schedule by Zapier
3. Choose Every week
4. Set day: Monday, time: 7:00 AM (so you get it before your workday starts)
5. Click Continue

What you should see: A trigger configured to run every Monday at 7 AM.

Part 4: Add a Google Sheets lookup step

1. Click the + to add a step after the trigger
2. Search for Google Sheets → select it
3. Choose action: Get Many Spreadsheet Rows
4. Connect your Google account when prompted
5. Select your spreadsheet → select your "Caseload" sheet tab
6. Under Filter rows, set: Column G (Days Until) → is less than or equal to → 7
7. This filters to only clients with reviews due in 7 days or fewer

What you should see: In the test step, Zapier pulls a sample of rows matching your filter.

Part 5: Set up the Action — send yourself a notification

1. Add another step after the Google Sheets lookup
2. For email: choose Gmail → Send Email

   • To: your email address
   • Subject: "Treatment Plan Reviews Due This Week"
   • Body: Set up the body to include the client code and days-until fields from your spreadsheet rows using Zapier's dynamic field insertion

For the email body, type something like:

The following clients have treatment plan reviews due in the next 7 days:

[Client Code field from Sheets] — Review due in [Days Until field] days

Check your caseload tracker for details.

1. Use Zapier's Insert Data feature to pull in the Google Sheets columns dynamically — click the blue + in the text field and select the column names

What you should see: A preview email showing real client codes from your spreadsheet.

Part 6: Test and activate

1. Click Test step in each section to verify data flows correctly
2. Send yourself a test email to confirm formatting
3. When satisfied, click Publish Zap
4. Toggle the Zap to ON

What you should see: Your Zap is live. Every Monday at 7 AM, it will check your spreadsheet and email you a list of clients with upcoming reviews.

Real Example: What the Monday Email Looks Like

Setup: Your caseload has 22 clients with varying admission dates.

Monday morning email:

Treatment Plan Reviews Due This Week:

CLIENT-04 — Review due in 3 days
CLIENT-11 — Review due in 5 days
CLIENT-18 — Review due in 7 days

Check your caseload tracker for details.

Time saved: You would have checked this spreadsheet manually twice a week, sometimes caught it, sometimes not. Now it finds you — and compliance violations drop to zero.

What to Do When It Breaks

• No email arrives Monday → Check Zapier's Task History for error messages; the most common issue is the Google Sheets connection needing to be re-authorized
• Wrong clients appear in the email → Check your filter logic in the Google Sheets step — confirm column G contains the right formula
• Email body shows column letters instead of values → Go back to the Gmail step and reinsert the dynamic fields using the + button
• Too many clients in every email → Tighten the filter to 5 days instead of 7, or confirm your "Next Review Due" formula in column F is excluding past dates correctly

Variations

• Simpler version: Set a recurring Google Calendar event every Monday to manually check your spreadsheet — much lower tech but still better than relying on memory
• Extended version: Add a second Zap that triggers 24 hours before a review is due and sends a text message (use Zapier's built-in SMS or Twilio integration) for day-before reminders on the highest-risk reviews

What to Do Next

• This week: Get your Google Sheet into the right format — that's the foundation
• This month: Build and activate the Zapier automation; run it for 4 weeks to verify it works
• Advanced: Add a column to your sheet for "Supervisor Notification Required" and build a second Zap that automatically CCs your supervisor when specific clients' reviews are due

Advanced guide for Substance Abuse Counselor professionals. Keep client codes and deidentified information only in any spreadsheet connected to third-party automation tools. Verify your facility's data governance policy before connecting clinical data to external services like Zapier.