Use Google Sheets' AI to Build a Prior Authorization Status Dashboard
What This Does
Creates a real-time prior authorization tracking dashboard that shows every open auth request, how many days it's been pending, whether surgery is approaching, and which cases need your attention today — automatically.
Before You Start
- You have a Google account (free)
- You have Google Sheets open (sheets.google.com)
- You have the Gemini AI sidebar enabled (see previous guide for instructions)
- You have a list of your current pending prior authorizations
Steps
1. Set up your prior authorization tracking columns
Create a new Google Sheet. In row 1, enter these headers:
- A: Patient Name (or initials for privacy)
- B: Procedure / CPT Code
- C: Surgeon
- D: Insurance Company
- E: Submission Date
- F: Surgery Date
- G: Auth Status (Pending / Approved / Denied / Appealed)
- H: Auth Number (fill in when received)
- I: Days Pending
- J: Days Until Surgery
- K: Action Needed
2. Enter your current open authorizations
Fill in columns A–H for each open auth. Leave H blank for pending ones. Use MM/DD/YYYY for dates.
3. Build the "Days Pending" formula
Click on cell I2. Open the Gemini sidebar and type:
"In cell I2, write a formula that calculates how many days have passed since the date in E2 (submission date). Only calculate this if the status in G2 is 'Pending' — if the status is anything else, show a dash."
Drag down to all rows.
4. Build the "Days Until Surgery" formula
Click on cell J2. In the Gemini sidebar:
"In cell J2, calculate the number of days between today and the date in F2 (surgery date). If surgery date is in the past, show 0."
Drag down to all rows.
5. Create the "Action Needed" alert column
Click on K2. In the Gemini sidebar:
"In cell K2, write a formula that shows: 'CRITICAL - surgery in 3 days or less, still pending' if J2 is 3 or less AND G2 is Pending; 'FOLLOW UP - pending 5+ days' if I2 is 5 or more AND G2 is Pending; 'APPEAL NOW' if G2 is Denied; 'OK' if G2 is Approved; otherwise show blank."
Drag down to all rows.
6. Sort by urgency
Highlight all your data rows. Go to Data → Create a filter. Click the dropdown on column K and sort so CRITICAL rows appear first. Now your most urgent authorizations are always at the top.
What you should see: A dashboard where the most critical cases (surgery soon, still pending) are immediately visible. Red or flagged rows require action today.
Troubleshooting: If formulas break when you add new rows, click on a working formula cell, copy it, then paste it into the new blank rows.
Real Example
Scenario: You have 31 open prior authorization requests. Monday morning you open the dashboard — 2 rows show CRITICAL (surgery in 2 days, both still pending). You immediately call those two payers before checking anything else. Without this dashboard, you might have missed those cases until the day of surgery.
What you type/do: Open Google Sheets, look at column K. Every cell showing CRITICAL or APPEAL NOW gets your attention before anything else.
What you get: A prioritized action list that replaces manual review of all your open auths every morning.
Tips
- Update the Auth Status column (column G) as soon as you hear back from a payer — the entire dashboard recalculates instantly
- Add a Notes column (column L) for free-text notes on each case: "Left voicemail 3/19", "Peer-to-peer scheduled for 3/22"
- Print this sheet every morning and review it at the start of your day — a 2-minute daily ritual that prevents missed auths
- Share with your administrator so they can see the same real-time status without asking you
Tool interfaces change — if the Gemini sidebar has moved, look for AI or Help me features in the Extensions menu.