Automation: Build an Authorization Expiry Alert System with Zapier

For Home Health Care Schedulers

Tools: Zapier + Google Sheets | Time to build: 1-2 hours | Difficulty: Intermediate-Advanced Prerequisites: Comfortable using Google Sheets for tracking — see Level 2 guide: "Use Google Sheets' AI to Build an Authorization Expiry Tracker"

What This Builds

An automated system that checks your authorization expiry spreadsheet every morning and sends you an email or Slack message listing every client whose authorization expires within 14 days — without you ever having to open the spreadsheet. You'll get a daily alert at 8am that reads: "Authorization Alert: Mrs. Garcia expires March 24, Mr. Chen expires March 26, Mrs. Williams expires March 28." No missed renewals, no revenue loss, zero manual checking.

Prerequisites

• Comfortable using Google Sheets (specifically the auth expiry tracker from the Level 2 guide)
• A free Zapier account at zapier.com
• A Gmail account (for receiving alerts) or Slack (if your agency uses it)
• Account/subscription needed: Zapier free plan (5 Zaps) covers this; Zapier Starter ($20/mo) if you want more automations
• Cost: Free to start

The Concept

Think of Zapier as a bridge builder. You have two tools — Google Sheets (where your data lives) and email/Slack (where you want to receive information). Zapier connects them: "Every morning, look at the spreadsheet and if any authorization expires within 14 days, send me an email about it."

You set it up once. After that, it runs automatically every day without any action from you. It's like having an assistant who checks the expiry tracker at 8am and only calls you when there's actually something that needs attention.

Build It Step by Step

Part 1: Prepare Your Google Sheets Tracker

Your auth expiry tracker needs one additional column to make this automation work cleanly.

1. Open your authorization tracking spreadsheet from the Level 2 guide
2. Add a new column header: Alert Status (let's say column E)
3. In cell E2, enter this formula: =IF(B2-TODAY()<=14, "ALERT", "OK")

   • This shows "ALERT" when the expiry date in B2 is 14 days or less away
   • Copy this formula down column E for all client rows
4. Test it: manually change a date in column B to 7 days from today — column E should show "ALERT"

What you should see: Column E automatically shows ALERT or OK for each client based on their expiry date.

Part 2: Set Up Your Zapier Account

1. Go to zapier.com → click Sign up free
2. Sign up with your Google account (easiest) or email
3. After signing in, click + Create → + New Zap
4. You'll see a visual workflow builder with a trigger and an action

What you should see: A two-step workflow builder. The left side is the trigger (what starts the automation) and the right side is the action (what happens when it triggers).

Part 3: Set Up the Trigger — Schedule

You want this Zap to run once every morning, not just when the spreadsheet changes.

1. In the trigger step, search for Schedule by Zapier and select it
2. Choose Every Day
3. Set the time to 8:00 AM
4. Select your time zone
5. Click Continue

What you should see: A scheduled trigger that says "This Zap will run every day at 8:00 AM."

Part 4: Add a Google Sheets Lookup Step

1. Click + to add a new step after the trigger
2. Search for Google Sheets and select it
3. Choose Lookup Spreadsheet Row (or "Find Rows")
4. Connect your Google account when prompted
5. Select your authorization tracking spreadsheet from the dropdown
6. Select your sheet tab
7. In "Lookup Column," select your Alert Status column (column E)
8. In "Lookup Value," type: ALERT
9. Check the box for "Find Multiple Rows" if available

What you should see: When you test this step, Zapier finds all rows where column E says "ALERT" — these are your clients with expiring authorizations.

Troubleshooting: If Zapier can't find your spreadsheet, make sure you're connected to the right Google account (the one that owns the spreadsheet). Go to My Apps → reconnect Google Sheets if needed.

Part 5: Set Up the Action — Send Alert Email

1. Click + to add the final step

2. Search for Gmail and select it

3. Choose Send Email

4. Connect your Gmail account

5. Fill in:

   • To: your work email address
   • Subject: Authorization Alert — [agency name] — {{date}}
   • Body:

     Copy and paste this

     Daily Authorization Expiry Report
     
     The following clients have authorizations expiring within 14 days:
     
     {{client name}} — Expires: {{auth expiry date}} — Payer: {{payer}}
     
     Please renew these authorizations before their expiry date to prevent unbillable visits.
     

   • Replace the {{}} placeholders with the actual field names from your Google Sheets data (Zapier will show you the available fields in a dropdown)

6. Click Test to send a test email

What you should see: A test email arrives in your inbox showing the client data from your spreadsheet.

Part 6: Turn On Your Zap

Review your complete Zap: Schedule (8am daily) → Find ALERT rows in Google Sheets → Send email with those clients.

Click Publish Zap. It's now live.

What you should see: A confirmation that your Zap is active. Tomorrow morning at 8am, you'll receive your first automated authorization alert.

Real Example: Full Walkthrough

Setup: Your spreadsheet has 85 active clients. 3 have authorizations expiring in the next 14 days: Mrs. Garcia (7 days), Mr. Chen (11 days), Mrs. Williams (13 days).

Input: It's 8:00 AM Tuesday. Zapier runs automatically.

Output: You receive an email at 8:02 AM:

Time saved: 2-3 hours/week of manual spreadsheet checking. Zero missed expirations.

What to Do When It Breaks

• Email doesn't arrive at 8am → Check Zapier's "Zap History" (left sidebar) for error messages. Usually means Google Sheets connection expired — go to My Apps → reconnect Google Sheets.
• Zap triggers but no clients are listed → Check that column E formulas are returning "ALERT" correctly. Open the spreadsheet and check manually.
• Too many alerts (false positives) → Narrow the window: change <=14 to <=7 in the formula if 14 days is too broad for your workflow.
• Zapier free plan limit reached → Free plan allows 100 tasks/month and 5 Zaps. If you hit the limit, upgrade to Starter ($20/mo) or delete an unused Zap to free up a slot.

Variations

• Simpler version: Skip Zapier entirely and just set a recurring Google Calendar reminder to open the filtered spreadsheet every Monday morning. Less automated but zero setup.
• Extended version: Add a second Zap that creates a Google Calendar event for each expiring authorization, 10 days before expiry, with the client name and payer as the event title. Now it's on your calendar, not just in your email.

What to Do Next

• This week: Build and test the basic Zap; verify you get a correct test email
• This month: Once the email alert is running, add the calendar event Zap as version 2
• Advanced: Connect Zapier to your Slack channel if your agency uses Slack — team alerts are more visible than email

Advanced guide for Home Health Care Scheduler professionals. These techniques use automation features that may require paid subscriptions.