Automation Recipe: Prior Authorization Status Reminder System

What This Builds

An automatic reminder system that monitors your prior authorization tracking spreadsheet and sends you a daily email listing every case that needs follow-up — cases that have been pending for 5+ days, cases with surgery approaching without approval, and cases that were denied and need an appeal. Instead of manually reviewing your spreadsheet every morning, your inbox tells you exactly what needs attention.

Prerequisites

• Your Prior Authorization Tracking Dashboard in Google Sheets (from Level 2 guide)
• A Zapier account — free tier covers this automation (zapier.com)
• Gmail or Outlook for the notification emails
• Comfortable opening and editing Google Sheets

The Concept

Zapier is an automation platform that connects apps and runs actions automatically. Think of it like a rule: "Every morning at 8am, look at my Google Sheet, find rows that match certain criteria, and email me a list." You set up the rule once, and it runs every day without you doing anything.

This is different from the Google Sheets dashboard you built — that's a visual tool you have to open and look at. This automation comes to you, in your inbox, before you've even opened your spreadsheet.

Build It Step by Step

Part 1: Prepare your Google Sheets authorization tracker

Before building the Zapier automation, make sure your Google Sheets tracker has these exact column headers (these will be referenced in Zapier):

• A: CaseID (a unique identifier — like case number or patient initials + procedure date)
• B: PatientInitials
• C: Procedure
• D: Surgeon
• E: InsuranceCompany
• F: SubmissionDate (format: MM/DD/YYYY)
• G: SurgeryDate (format: MM/DD/YYYY)
• H: AuthStatus (values: Pending, Approved, Denied, Appealed)
• I: DaysPending (formula from Level 2 guide)
• J: DaysUntilSurgery (formula from Level 2 guide)
• K: ActionNeeded (formula from Level 2 guide)
• L: Notes

If your column names are different, that's okay — just note what yours are called.

Part 2: Create your Zapier account

1. Go to zapier.com and click "Sign up free"
2. Create an account with your Google or work email
3. The free tier allows 5 "Zaps" (automations) with 100 tasks/month — more than enough for this

Part 3: Build the daily "urgent auth list" Zap

Step 1: Start a new Zap In Zapier, click "Create" → "Zaps" → "+ Create Zap"

Step 2: Set the Trigger — Schedule Search for "Schedule by Zapier" as your trigger app. Select trigger event: "Every Day" Set the time: 7:30am (before you normally start reviewing auths) Set the day(s): Monday through Friday Click Continue.

Step 3: Add an Action — Google Sheets (Get Many Spreadsheet Rows) Search for "Google Sheets" as your action app. Select event: "Get Many Spreadsheet Rows" Connect your Google account when prompted. Select your spreadsheet and the sheet tab name. In the "Filter By" section: set Column K (ActionNeeded) → "Contains" → "CRITICAL" OR "FOLLOW UP" OR "APPEAL" Set Max Rows: 50

What you should see: Zapier connects to your spreadsheet and shows sample matching rows.

Part 4: Add the email action

Step 4: Add another Action — Gmail (Send Email) Click the "+" button to add another action. Search for "Gmail" → "Send Email"

In the To field: enter your own email address In the Subject field: type: "⚠️ Prior Auth Action Needed — [today's date]" (Zapier will automatically insert the current date from the Schedule trigger)

In the Body field, type (insert Zapier field references using the {Insert Data} button):

Good morning! Here are your prior authorization cases that need action today:

CRITICAL (surgery in 3 days or less, still pending):
[List rows from Google Sheets where ActionNeeded = CRITICAL]
- Case: {CaseID} | Patient: {PatientInitials} | Procedure: {Procedure} | Surgeon: {Surgeon} | Insurance: {InsuranceCompany} | Surgery: {SurgeryDate}

FOLLOW UP (pending 5+ days):
[List rows where ActionNeeded = FOLLOW UP]
- Case: {CaseID} | Patient: {PatientInitials} | Procedure: {Procedure} | Insurance: {InsuranceCompany} | Submitted: {SubmissionDate}

APPEALS DUE:
[List rows where ActionNeeded = APPEAL]
- Case: {CaseID} | Patient: {PatientInitials} | Procedure: {Procedure} | Insurance: {InsuranceCompany}

Open your tracking spreadsheet to see full details:
[paste the Google Sheets link here]

Note: Because Zapier's "Get Many Rows" returns multiple rows, the email will repeat the row template for each matching case. It may not look perfect on the first try — test it and adjust the formatting.

Part 5: Test the automation

1. Click "Test Step" on the Google Sheets action to verify it's finding the right rows
2. Click "Test Step" on the Gmail action — this sends a real test email to your address
3. Review the email: Are the right cases listed? Is the format readable?
4. If something looks wrong, click back into the action step and adjust

Turn on the Zap: Once testing looks good, toggle the Zap from "Off" to "On." It will run automatically at 7:30am every weekday from now on.

Real Example: What the Morning Email Looks Like

What triggers: 7:30am Monday, Zapier runs. Reads your Google Sheet. Finds 4 rows with action needed.

What arrives in your inbox:

⚠️ Prior Auth Action Needed — 03/22/2026

CRITICAL (surgery in 3 days or less, still pending):
- Case: WJ-0322 | Patient: W.J. | Procedure: TKR | Surgeon: Dr. Wilson | Insurance: Anthem | Surgery: 03/24/2026
- Case: PC-0325 | Patient: P.C. | Procedure: Cataract | Surgeon: Dr. Kim | Insurance: UHC | Surgery: 03/25/2026

FOLLOW UP (pending 5+ days):
- Case: MR-0315 | Patient: M.R. | Procedure: Lumbar Fusion | Insurance: Cigna | Submitted: 03/13/2026

APPEALS DUE:
- Case: TB-0310 | Patient: T.B. | Procedure: Shoulder Scope | Insurance: Aetna | Submitted: 03/10/2026

What you do: Open the email at 7:30am. You know exactly what needs attention before you've opened a single browser tab. Call Anthem first about the TKR case — surgery is in 2 days.

Time saved: The daily manual review of all open auths (15–20 minutes) is replaced by a 2-minute email scan.

What to Do When It Breaks

• No email arrives → Check the Zap is "On" in Zapier; check your spam folder; verify the trigger time is correct for your time zone
• Email arrives but no cases listed → The filter criteria may be too specific. Check that your ActionNeeded column has values that exactly match what Zapier is filtering for (case-sensitive)
• Wrong cases appearing → Check your spreadsheet's ActionNeeded formula from the Level 2 guide — the formula may be miscategorizing cases
• Zap runs but skips some rows → Free Zapier tier has a 100 task/month limit. If you're hitting this, upgrade to Starter ($20/mo) or optimize the Zap to only run when new rows are added

Variations

• Simpler version: Skip the filtering — just send yourself the full tracking sheet as an email attachment each morning (requires only the Schedule trigger + Gmail action)
• Extended version: Add a third action that creates a Google Calendar event for each CRITICAL case, putting the follow-up reminder directly on your calendar

What to Do Next

• This week: Build the Zap and run it for 5 business days; adjust the format based on what you receive
• This month: Track whether the daily emails result in fewer cases reaching surgery without authorization
• Advanced: Add a second Zap that sends an SMS alert via Twilio when a CRITICAL case appears — for days when you're not checking email first thing

Advanced guide for ASC coordinator professionals. Zapier free tier supports this automation. Google account required. No patient PHI should be stored in the automation itself — use case IDs and patient initials only.