Use Google Sheets' AI to Build an Authorization Expiry Tracker

What This Does

Builds an automated dashboard in Google Sheets that turns red when a client's authorization is about to expire — so you never miss a renewal date or send a caregiver to an unbillable visit.

Before You Start

• You have a Google account (free)
• You have a list of clients with their authorization expiry dates
• You're logged into Google Sheets (sheets.google.com)

Steps

1. Open a New Google Sheet and Set Up Your Columns

Open sheets.google.com and create a new blank spreadsheet. In row 1, add these headers: Client Name (A1), Auth Expiry Date (B1), Payer (C1), Status (D1).

What you should see: A clean spreadsheet with four labeled columns across the top.

2. Enter Your Client Authorization Data

Add your clients in rows 2 and below: client name in column A, authorization expiry date in column B (format: MM/DD/YYYY), payer name in column C.

What you should see: A growing list of clients with their expiry dates filled in. Enter at least 5-10 to test the next steps properly.

3. Open the Gemini Sidebar

Click the Extensions menu at the top → look for Gemini or Duet AI. If you see it, click it to open the sidebar. Alternatively, look for the small sparkle icon (✨) in the formula bar when you click on a cell.

Troubleshooting: If you don't see Gemini in Extensions, make sure you're using a personal or Workspace Google account. You may need to enable it under Extensions → Add-ons.

4. Ask Gemini to Write the Status Formula

Click on cell D2. In the Gemini sidebar, type:

Gemini will write the formula. Click Insert to place it in D2.

What you should see: Cell D2 shows EXPIRED, EXPIRES SOON, or OK depending on the date in B2.

Troubleshooting: If the formula shows an error, check that your dates in column B are formatted as dates (not text). Right-click the cells → Format cells → Date.

5. Copy the Formula Down the Column

Click cell D2. Copy it (Ctrl+C). Select D3 through D100 (or however many rows you need). Paste (Ctrl+V).

What you should see: Every row now shows a status based on its expiry date.

6. Add Conditional Formatting for Color Alerts

Select column D (all status cells). Click Format → Conditional formatting.

• Add rule: Text is exactly "EXPIRED" → fill color red
• Add rule: Text is exactly "EXPIRES SOON" → fill color yellow
• Add rule: Text is exactly "OK" → fill color green

What you should see: The status column is now color-coded — expired rows are red, soon-to-expire are yellow.

7. Create a Filter View for Quick Review

Click any cell in your data. Click Data → Create a filter. Click the dropdown arrow on the Status column header. Uncheck "OK" to show only EXPIRED and EXPIRES SOON rows.

What you should see: Only clients that need action are visible. Clear the filter to see everyone.

Real Example

Scenario: You manage 80 active clients and want to check authorization status every Monday morning.

What you do: Open the spreadsheet → apply the status filter (uncheck OK) → you see 4 clients: 1 EXPIRED and 3 EXPIRES SOON. You address those 4 first before anything else.

What you get: A targeted list instead of manually scanning 80 rows of dates.

Tips

• Update the expiry dates every time you get an authorization renewal — the status will recalculate automatically
• Ask Gemini to add a "Days Until Expiry" column: "Write a formula that shows the number of days between today and the date in B2"
• Share the sheet with your billing team so they can see auth status without calling you