Getting Sarah's Law Student Tracker Running

Step-by-step guide for going from demo mode to live production.
\nSarah is starting mid-semester, so some steps account for importing existing data.

\n\n

What the system does (30-second version)

Every Monday and Wednesday:

Before class — Sarah gets an email listing which students are on-call
After class — Sarah fills out a quick web form: who she called on, how they did, who was absent
Automatically — The system tracks absences, bad performances, and concerning patterns. If a student crosses a threshold (e.g. 3 absences), Sarah gets an alert email

Everything lives in a Google Sheet that Sarah can view anytime. The system writes to it; Sarah reads from it (and can manually correct it).

\n\n

Before you start

You'll need:

Access to Val.town (Sahar's account)
The Google service account credentials file
Sarah's T&E roster (students with first + last names)
Sarah's L&C roster (students with first + last names)
Sarah's L&C syllabus with on-call assignments (which students on which dates)
The Google Form that students use to self-report absences (you need the spreadsheet ID it writes to)

\n\n

Phase 1: Set up the production Google Sheet

\n\n

1.1 Create the spreadsheet

Create a new Google Spreadsheet (or use existing production one)
Share it with the service account email (from credentials JSON, client_email field) — give it Editor access
Copy the spreadsheet ID from the URL (long string between /d/ and /edit)

\n\n

1.2 Create the T&E Roster tab

Name the tab exactly: T&E-Roster
Add these column headers in row 1:

Fill in student_name (e.g. \"Alice Adams\") and last_name (e.g. \"Adams\")
Leave pool empty (system calculates it)
Set all counter columns to 0

Mid-semester note: If Sarah already has data (e.g. \"Alice has 2 absences\"), enter those counts now. The system tracks from here forward.

\n\n

1.3 Create the L&C Roster tab

Name the tab exactly: L&C-Roster
Same columns as T&E (pool not used for L&C — leave blank)
Fill in student names and any existing counts

\n\n

1.4 Create the L&C Schedule tab

Name the tab exactly: L&C-Schedule
Two columns: date and student_name
Enter each remaining class date and assigned students

\n\n \n \n \n \n

date	student_name
2026-02-18	Alice Adams
2026-02-18	Bob Brown
2026-02-20	Carol Clark

Use YYYY-MM-DD format. Multiple students per date = multiple rows.

\n\n

1.5 Other tabs (auto-created)

You don't need to create these — the system creates them on first run:

Cold-Call-Log (log) — audit trail
T&E-On-Call-Next (auto) — today's on-call list
Watch-List (auto) — students at risk
Cancelled-Classes (log) — cancelled class dates
Config (auto) — system configuration

\n\n

Phase 2: Configure Val.town environment variables

Go to Val.town → Settings → Environment Variables:

\n\n \n \n \n \n \n \n \n

Variable	Value	Notes
`GOOGLE_APPLICATION_CREDENTIALS`	Entire JSON content of credentials file	Copy-paste the whole file
`REPORT_API_KEY`	Secret string	Already set; auths the report form
`MAIN_SHEET_ID`	Production spreadsheet ID	From Phase 1
`TE_STUDENT_FORMS_SHEET_ID`	T&E student form spreadsheet ID	Where Google Form writes
`LC_STUDENT_FORMS_SHEET_ID`	L&C student form spreadsheet ID	May be same spreadsheet
`NOTIFICATION_EMAIL`	Any value	Set LAST — enables cron emails

Important: NOTIFICATION_EMAIL doesn't control where emails go (Val.town free tier always emails the account owner). It's an on/off switch. Set = emails on. Unset = emails off.

\n\n

Phase 3: Run the setup endpoints

One-time setup steps. Hit each URL in your browser:

\n\n

3.1 Assign T&E pools

Visit the assign-pools endpoint (add ?dryRun=true first to preview)

This finds the letter that gives the most even 50/50 split and writes mon or wed to each student's pool column.

\n\n

3.2 Add tracking columns (if missing)

Visit the add-roster-columns endpoint (?dryRun=true first, then without)

\n\n

3.3 Verify the connection

Visit the connection test endpoint
Should return JSON showing spreadsheet title, headers, and sample rows
If error: check service account has Editor access to the sheet

\n\n

Phase 4: Configure cron schedules

Set in the Val.town web UI (code doesn't control schedules):

\n\n \n \n \n \n \n \n

Cron file	What it does	Schedule (EST)	UTC Cron
`lc-oncall-notify`	Emails L&C on-call list	8 AM Mon/Wed	`0 13 * * 1,3`
`lc-report-prompt`	Reminds Sarah to report (L&C)	12 PM Mon/Wed	`0 17 * * 1,3`
`oncall-notify`	Emails T&E on-call list	3 PM Mon/Wed	`0 20 * * 1,3`
`te-report-prompt`	Reminds Sarah to report (T&E)	6 PM Mon/Wed	`0 23 * * 1,3`
`weekly-summary`	Friday risk summary	Fri 9 AM	`0 14 * * 5`

To set: Open cron file in Val.town → click schedule icon → enter cron expression. Make sure toggle is ON.

\n\n

Phase 5: Connect the student self-report form

Confirm you have the spreadsheet ID(s) where the form responses go
Make sure the service account has read access to those spreadsheets
Verify the form has these exact questions:

\"Date of class you'll be absent, late-arriving/early-departing, or unprepared\"
\"Will you be absent, unprepared, or late/early?\"
\"Your name\"

If form questions differ: Update constants FORM_COL_DATE, FORM_COL_TYPE, FORM_COL_NAME in utils.tsx, then redeploy with vt push.

\n\n

Phase 6: Test end-to-end

\n\n

6.1 Test on-call selection

Visit T&E selection endpoint — should return 6 students (Mon/Wed only; use ?pool=mon to force)
Visit L&C selection endpoint — should return today's assigned students

\n\n

6.2 Test the report form

Open Sarah's form (link below)
Select a class — on-call list should appear
Submit a test report with a student name
Check Google Sheet — Cold-Call-Log should have a new row, roster counts should update

\n\n

6.3 Test email

Set NOTIFICATION_EMAIL in Val.town env vars
Run a cron manually from Val.town dashboard (or wait for next scheduled run)
Confirm email arrives

\n\n

Phase 7: Hand off to Sarah

\n\n

Sarah's daily workflow

\n\n \n \n \n \n \n \n

When	What happens	What you do
Before class	Email arrives listing on-call students	Note who's on the list
During class	Teach normally	Note performance + absences
After class	Reminder email arrives	Open the form, fill it out (< 2 min)
Friday	Weekly summary email	Review any at-risk students
Anytime	Alert email about a student	Follow the recommended action

\n\n

Filling out the report form

Select the class (T&E or L&C)
On-call students appear at the top for reference
Type student names into the right boxes:\n
- Great — did well when called on
- Normal — adequate
- Poor — struggled or unprepared
- Refused — declined to answer
- Absent — ALL absent students (on-call or not)
\n
Submit. Done.

\n\n

If something goes wrong

Form shows warnings for names it couldn't match — check spelling
\"Need to correct a mistake?\" section at the bottom links to the spreadsheet
Fix wrong counts: Edit the student's row directly in T&E-Roster or L&C-Roster
Remove a bad entry: Delete the row from Cold-Call-Log (log)
Duplicate submissions are safe — system skips them automatically

\n\n

If class is cancelled

Open the form, select the class, mark \"Yes - class was cancelled\", submit. This prevents false alerts.

\n\n

What Sarah should NOT touch

Tabs ending in (auto) — system overwrites these
Tabs ending in (log) — append-only, don't edit unless fixing a mistake
The pool column in T&E-Roster — auto-calculated

\n\n

What Sarah CAN edit freely

Student names in either Roster tab
Counter values (absences, concerning_events, etc.) — these are the source of truth
L&C-Schedule (add/change on-call assignments)
Add or remove students from rosters

\n\n

Troubleshooting

\n\n \n \n \n \n \n \n \n \n \n

Problem	Fix
No emails arriving	Check `NOTIFICATION_EMAIL` is set. Check crons are toggled on with correct schedules.
\"Selection failed\" email	Check Google Sheet is accessible (service account has Editor access)
Wrong students on call	Check pool assignments in T&E-Roster. Run assign-pools to recalculate.
Form shows \"Unauthorized\"	Check `REPORT_API_KEY` in Val.town env vars
Student name not matching	System does fuzzy matching. If still failing, check exact spelling against roster.
Too many emails	Unset `NOTIFICATION_EMAIL` to disable all cron emails instantly.
Student added mid-semester	Add to roster with counters at 0. For T&E, run assign-pools.
Student dropped	Delete row from roster. Cold-Call-Log history stays.

\n\n

Quick reference: All URLs

\n\n \n \n \n \n \n \n \n

What	URL
Sarah's report form	https://sahar--ab2d2176f16511f0a75a42dde27851f2.web.val.run
T&E on-call selection	https://sahar--78f39bfeef4911f0a9a742dde27851f2.web.val.run
L&C on-call selection	https://sahar--2b3b7afaef5611f0bec642dde27851f2.web.val.run
Connection test	https://sahar--c49a6e2cef4711f0998442dde27851f2.web.val.run
Watch list refresh	https://sahar--b1ff5bc6f08f11f0a81342dde27851f2.web.val.run
This setup guide	(you're here)

\n\n\n`;\n\n return new Response(html, { headers: { \"Content-Type\": \"text/html\" } });\n}\n","deleted","label",[100],"permalink","https://www.val.town/x/sahar/law-student-tracker/code/setup-guide.http.tsx","extension","actionData","errors"]