The Problem
My school operates a daytime testing center where students complete makeup exams and retakes. Previously, when a teacher needed to refer a student to the center, they submitted a Google Form. This process was inefficient for several reasons:
- Repetitive Data Entry: Teachers had to complete a separate form for every single student, which was time-consuming for large batches.
- Manual Lookups: Teachers had to manually locate student ID numbers.
- Lack of Communication: There was no automated system to notify students of their referral or to alert teachers when an exam was completed.
Because of the large amount of friction these inefficiencies presented, teachers would often forgo filling out the form, opting instead to drop off tests without any advance notice.
The Solution
The new solution is a web application built with Google Sheets and Google Apps Script. It authenticates users via their Google accounts, ensuring that only authorized staff - and no students - can access the system.
The application integrates a database of students and course rosters. This allows teachers to search for and select multiple students simultaneously, filtering the list to show only those enrolled in their specific classes. The system handles duplicate names by displaying middle names for clarity when necessary. Once students are selected, the remaining form fields can take advantage of the browser’s autofill, significantly reducing administrative time.
Upon submission, the system emails each student referred with test details and reporting instructions, with the teacher copied on the message.
The second component is a front-end interface for testing center proctors. This allows staff to mark exams as “started” or “finished” without directly editing the raw spreadsheet data—preventing accidental data corruption. This interface also manages technical requirements, such as Zulu-time timestamp encoding, which is necessary for an automated trigger that sends an email to notify teachers when an exam has been completed.
Overall, the new system has been highly successful.
AI Disclosure
A generative AI assistant was heavily used for the javascript logic required for the dynamic student list filtering, form submit and reset functions, admin portal, and email handling.