A common issue facing many schools is finding internal substitute coverage. This usually begins as a blast email to the entire faculty pleading for help, then another blast email saying coverage has been found. Rather than using email, I’ve developed what amounts to be an automated community job board approach for open substitute positions using Google Sheets, Google Forms, Google Calendar and Looker Studio. A teacher simply opens the Google Workspace add-on in Google Calendar, selects the teaching period on their own calendar for which they need coverage, answers a few brief questions about the job listing, including sub plans, then submits the form.

A new event is generated on a dedicated Sub Manager calendar and an email is sent to a designated individual managing this process about the listing. This individual would also have the ability to update the form immediately, as well as purge all pending and declined guests.

Because the teacher does not have edit access to the Google Form, the listing will not be added immediately to the form. Instead, a time-driven script scans this dedicated Sub Manager calendar about every 30 minutes for open positions and lists them in a checkbox question on a connected Google Form. Job listings are automatically removed as jobs are taken or have passed. It is possible to accept multiple positions with one form submission.

Each listing has a maximum guest capacity of 1, so only one person can claim a job. The job is only fully considered to be taken when the calendar invite has been accepted. A script attached to a time-driven trigger removes all pending guests over night, so if an invite hasn’t been accepted, the listing returns to the Google Form. If two people are signing up for the same job around the same time, the invite will be sent to whoever submitted the form first. The second individual, will receive an email saying the the job is no longer available.

The added value in tracking internal coverage like this is that you can see who are the teachers that are always helping out and the ones who never sub for anybody. Likewise, a second tab on the Form Response sheet is set to export all events from the Sub Manager Calendar making it easy to see who subbed and when. This Google Sheet can be connected to a Looker Studio report that filters by a moving date range and individual teachers accommodating the needs of payroll.