Use Google Sheets to Track Your Caseload and IEP Deadlines
What This Does
Google Sheets' built-in AI can write complex formulas and organize data for you — no spreadsheet expertise needed. For OTs, this means building a caseload tracker, IEP deadline calendar, or authorization expiration tracker in minutes instead of hours.
Before You Start
- You have a Google account (free)
- You have Google Sheets open in your browser (sheets.google.com)
- You have a list of your current patients or students to enter
Steps
1. Open a new Google Sheet and describe what you need
Go to sheets.google.com and click "Blank spreadsheet." Then look for the Gemini side panel button on the right side (looks like a sparkle icon) or go to Extensions → Gemini in Sheets if you haven't used it before.
In the Gemini panel, type:
"Create a caseload tracker for an occupational therapist with columns for: patient name, diagnosis, start date, authorization expiration date, sessions authorized, sessions used, sessions remaining (calculated), next appointment, and a status column that turns red when sessions remaining is below 3."
2. Let Gemini build the structure
Gemini will generate the column headers and the formula for "sessions remaining" (=sessions authorized - sessions used) and conditional formatting for the status column. It will insert these directly into your sheet.
3. For IEP deadline tracking (school OTs)
Add a second sheet tab. In the Gemini panel type:
"On Sheet 2, create an IEP deadline tracker with: student name, annual review due date, a column that calculates days until deadline (from today), and a conditional format that turns the row yellow at 60 days and red at 30 days."
4. Review and use the result
Gemini inserts the formulas. You'll see the "Days Until" column auto-calculating from today's date. Enter your student names and due dates and the color coding activates automatically.
Real Example
Scenario: A school OT has 47 students and loses track of which IEP annual reviews are coming up — spring suddenly has 12 reviews due in the same week.
What you type: "Create an IEP annual review tracker with student name, review due date, days remaining calculated from today, and color me yellow at 60 days out and red at 30 days out."
What you get: A live spreadsheet where every student's deadline countdown is automatic. Sort by "Days Until" and you instantly see who needs attention this week.
Tips
- If Gemini isn't available in your Sheets, use the regular chat at gemini.google.com — paste your request there, get the formulas, then paste them manually into your sheet
- Save the sheet to Google Drive and access it from any device — useful if you work across school buildings
- To share with a supervising OT or admin, click Share in the top right corner
Tool interfaces change — if a button has moved, look for similar AI/magic/smart options in the same menu area.