Evaluation Caseload Tracker

A Google Sheets caseload tracker for Texas educational diagnosticians — tracks student info, evaluation dates, EB status, data collection, assessment progress, and compliance steps. Auto-calculates age and ARD due dates. Color-codes rows by deadline urgency. Campus view tabs pull live from the main sheet. Includes the Student Card, calendar sync, and parent email generator.

📋Make a Copy

🔒

FERPA reminder: This file will contain student names, DOBs, and student numbers — all protected under FERPA. Use only with your district-approved Google account. Do not share the file or its link with unauthorized individuals. At year end, archive or delete per your district's records retention policy.

🚀 Getting Started

Make your own copy using the button above. Google will prompt you to save it to your Drive. You are working in your own file — nothing syncs back to the original.

Authorize the script. In the menu bar, click 📋 Student Card → Show Card. This triggers the one-time authorization process. When the window appears: click Allow → then Advanced → then Go to Student Card View (unsafe) (this is your own private script running entirely inside your own file — it is safe) → check Select All → click Continue. This only happens once. If you don't see the 📋 Student Card menu yet, wait a moment for the sheet to fully load and refresh if needed.

Set up your campuses. After authorization, click 📋 Student Card → Setup Campuses. A dialog will appear — choose how many campuses you work with, enter each campus name, and click Save Campuses. This creates your campus view tabs and sets up the campus dropdown on the CASELOAD sheet. You can run Setup Campuses again anytime if your campuses change.

Set up daily reminders (optional). Right after you save your campuses, a Daily Reminder Setup dialog will appear automatically. Choose Yes, enable to receive a daily email summary of FIEs and ARDs due in the next 7 days, then select your preferred reminder time — options run from 6:00 AM through 5:00 PM. Click Skip if you'd rather not use reminders. If you skip and change your mind later, go to Extensions → Apps Script → Triggers and create a time-based trigger for sendDailyReminder.

Sync your calendar anytime (optional). No setup needed — calendar sync runs on demand. Whenever you want your Google Calendar updated with FIE and ARD deadlines, click 📋 Student Card → Sync Calendar. Run it after adding new students or changing any evaluation or ARD dates. Existing calendar events are updated rather than duplicated.

Work only on the CASELOAD tab. When a new student is referred, add a new row. Fill in columns left to right as the evaluation progresses. Campus view tabs update automatically — do not edit them directly.

Do not type in the AGE or ARD DUE DATE columns. Both are auto-calculated — AGE from DOB, ARD DUE DATE from EVALUATION DATED + 30 calendar days. Typing over them deletes the formula.

At year end, consider moving DONE and WITHDRAWN rows to an archive tab before starting the new school year.

🎬 Visual Walkthrough

Prefer to see it in action? Page through the annotated setup guide below — use the PDF controls to zoom, scroll, or go full screen.

📋 Sheet Structure

The tracker contains multiple tabs. All student data lives on CASELOAD — the campus tabs pull from it automatically.

START HERE

Setup instructions, status vocabulary, row color guide, abbreviations, and campus tab tips. Read this first.

CASELOAD

The main data entry tab. Every student lives here. All other tabs pull from this one — this is the only tab you type in directly.

Campus Tabs

Auto-filtered views of CASELOAD by campus. Created automatically during setup. No data entry needed — they update instantly. Never type directly in these tabs.

Student Card

A single-student view created by the 📋 Student Card menu. Edit amber cells and use Save Card to write changes back to CASELOAD. This tab is rebuilt each time you open a new card.

🗂 Student Card

The Student Card gives you a clean, focused view of one student at a time — no scrolling across 55 columns.

Click any cell in a student's row on CASELOAD or a campus tab.

Click 📋 Student Card → Show Card. The Student Card tab opens with that student's full record.

Edit the amber cells. Only editable fields are highlighted in amber. Read-only fields (like auto-calculated dates) appear in gray.

Click 📋 Student Card → Save Card to write your changes back to CASELOAD. The header will flash green to confirm the save.

📅 Calendar Sync

Sync your FIE and ARD deadlines directly to your Google Calendar.

Click any cell in a student row on CASELOAD (or leave any cell selected).

Click 📋 Student Card → Sync Calendar. All students with FIE Due, FIE Complete, ARD Due, or ARD Date entries will be added to your default Google Calendar as all-day events.

Run it anytime you update dates. Existing events are updated rather than duplicated if the title and date already exist.

📧 Parent Email Generator

Generate ready-to-send parent emails pre-filled with the student's name and evaluation information. Available in English and Spanish.

Click any cell in a student's row on CASELOAD or a campus tab.

Click 📋 Student Card → Generate Parent Email. Choose Consent Email or Completed Eval Email, select language and pronouns, fill in guardian name and your name, then copy the finished email.

Paste into AdobeSign, Gmail, or your email platform. The Parent Input Form link is included automatically in consent emails.

🎨 Row Color Coding — Two Texas Clocks

Rows color off the FIE due date (45 school days from consent — TAC §89.1011) until you enter EVALUATION DATED. Then the FIE clock retires and the row colors off the ARD clock (30 calendar days after evaluation is dated) until an ARD DATE is entered.

⬛ Dark GrayCOMPLETED = DONE

⬜ Light Gray ItalicCOMPLETED = WITHDRAWN

🟥 Dark Red, BoldA clock is OVERDUE — FIE due date or ARD due date has passed

🔴 RedDue within 7 days (either clock)

🟠 OrangeDue in 7–14 days

🟡 YellowFIE clock: due in 14–21 days

🟢 GreenFIE clock: due in 21–28 days

🔵 BlueFIE clock: due in 28–35 days

🟣 PurpleFIE clock: due in 35–75 days

No colorNo due date yet, or ARD is scheduled and on track

📑 Column Guide

All 55 columns are grouped by function. Header colors in the spreadsheet group related columns visually.

Column(s)	What it tracks	Notes
LAST NAME, FIRST NAME	Student identity	Frozen — visible when scrolling right
CAMPUS	School campus	Dropdown — drives campus view tabs. Frozen.
DOB	Date of birth	Enter as date; drives the AGE formula
AGE	Current age	Auto-calculated — do not type here
GRADE	Grade level	Dropdown: EE, PK, K, 1–5
STUDENT NUMBER	District/state ID	Confidential; used for TSDS cross-referencing
TEACHER(S)	Classroom teacher(s)	Free text
EB	Emergent Bilingual status	Dropdown: YES / NO
PRIMARY LANGUAGE	Home/primary language	Dropdown; accepts typed values not on list
INTERPRETER NEEDED	Interpreter required for ARD	Dropdown: YES / NO / N/A
TESTING ONLY	Testing-only referral	Dropdown: YES / NO
MTSS MEETING / REED PLANNER COMPLETE	Required MTSS/REED documentation complete	Dropdown: YES / NO / N/A
ORIGIN	How referral was initiated	Dropdown: CAMPUS / PARENT
CONSENT SENT	Date consent docs sent to parent	Enter as date
CONSENT RECEIVED	Date signed consent returned	Starts the 45-day clock
TSDS DATE	Date consent entered in TSDS	Must be entered promptly
EVALUATION DUE DATE	FIE deadline	Drives all color coding — keep accurate
EVALUATION DATED	Date FIE report is finalized	Retires the FIE clock; starts ARD clock
ARD DUE DATE	ARD deadline (30 days after eval dated)	Auto-calculated — do not type here
REVIEW W/ PARENT(S)	Date eval reviewed with parent	Required before ARD meeting
ARD DATE	Date of eligibility ARD meeting	Retires the ARD clock
EVALUATION	Type of evaluation	Dropdown: INITIAL, RE-EVAL, SPEC. REQ., RE-EVAL TO DISMISS
SUSPECTED AREA(S)	Suspected eligibility at referral	Dropdown; accepts multiple values
CURRENT ELIGIBILITIES	Existing eligibility categories	Dropdown; accepts multiple values
EVAL. TEAM	Evaluation team members	Dropdown; accepts multiple values
TEACHER / PARENT RATING SCALES	Rating scale completion status	Dropdown: NEEDS / SENT / DONE / N/A
Data pull columns	CBM/STAAR, Work Samples, TELPAS, Intervention, IEP Progress, REED/Previous FIE, Attendance, Discipline, Grades	Dropdown: NEEDS / DONE / N/A
OBSERVATIONS	Classroom observation status	Dropdown: NEEDS 2 / NEEDS 1 / DONE / N/A
ADHD SCALES / ADAPT SCALES	Rating scale status	Dropdown: NEEDS / SENT / DONE / N/A
Assessment columns	COG-GIA, COG-BIA, OL, Reading, Dyslexia, Writing, Dysgraphia, Math	Dropdown: NEEDS / IN PROGRESS / DONE / N/A
ROUGH DRAFT	FIE draft status	Dropdown: NEEDS / IN PROGRESS / DONE
PDI AGREEMENT	Dyslexia/Dysgraphia PDI agreement status	Dropdown: NEEDS / SENT / RECEIVED / N/A
INPUT TO ESPED	Entered into eSped	Dropdown: NEEDS / DONE / N/A
SENT TO PARENT(S)	FIE report sent to parent	Dropdown: NEEDS / DONE / N/A
ELIGIBILITY	Eligibility determination outcome	Dropdown: SLD, DYSL, DYSG, OHI (ADHD), ID, OTHER, DNQ
COMPLETED	Overall completion status	Dropdown: DONE / WITHDRAWN. DONE → row turns gray.
NOTES	Free-text notes	Use for anything not captured elsewhere

🔤 Key Abbreviations

EB — Emergent Bilingual

TSDS — Texas Student Data System

FIE — Full Individual Evaluation

REED — Review of Existing Evaluation Data

ARD — Admission, Review, and Dismissal (IEP meeting)

PDI — Provider of Dyslexia Instruction

eSped — Electronic Special Education data system

CBM — Curriculum-Based Measurement

TELPAS — Texas English Language Proficiency Assessment System

GIA / BIA — General / Brief Intellectual Ability

OL — Oral Language

SLD — Specific Learning Disability

DYSL / DYSG — Dyslexia / Dysgraphia

OHI — Other Health Impairment (ADHD category)

ID — Intellectual Disability

DNQ — Did Not Qualify

LSP / LSSP — Licensed Specialist in School Psychology

SLP / OT / PT / AdPE — Speech / Occupational / Physical / Adapted PE

💡 Tips & Best Practices

Never type in AGE or ARD DUE DATE

Both columns auto-calculate. AGE uses a DATEDIF formula from DOB. ARD DUE DATE is EVALUATION DATED + 30 calendar days. Typing over either one deletes the formula for that row.

Edit students on CASELOAD or via Student Card only

Campus view tabs are read-only — they auto-populate from CASELOAD using FILTER formulas. Typing directly in a campus tab won't save and may break the formula.

Keep EVALUATION DUE DATE accurate

All row color coding runs off this column. If it's wrong, the color indicators mislead you. Update it whenever dates change.

Sync your calendar after updating dates

Calendar sync doesn't run automatically — run it manually after adding or changing FIE and ARD dates to keep your Google Calendar current.

Mark COMPLETED = DONE when finished

This turns the row dark gray and removes it from the active visual. At year end, consider archiving completed rows to keep the main sheet manageable.

CONSENT RECEIVED starts the 45-day clock

Per TAC §89.1011, the evaluation window begins the day after parental consent is received. Fill in CONSENT RECEIVED and TSDS DATE promptly — TSDS entry has its own deadline.