Leave Request Automator

Problem:

As a headteacher, I get lots of staff asking for leave for a variety of reasons (funerals, medical, special, etc.). Sometimes they ask in person, sometimes by email, sometimes written and it is hard to keep track of which have been allowed/denied. I would like an automated system that helps me with this issue.

Solution:

Make a copy of the spreadsheet here

This solution works as follows:

  • A member of staff fills in the Google Form asking for leave, selecting from the categories and stating the start/end dates of leave.

  • The headteacher will instantly get a notification email with the details of the leave request and a link to the spreadsheet.

  • From the spreadsheet, the headteacher can select from the drop down menu whether to 'Approve with pay', 'Approve without pay', or 'Deny' and then click send.

  • The teacher who requested the leave would then get an email with the decision and any other comments.

  • If the leave has been approved, it will automatically add an event to the school's shared Google Calendar stating that member of staff will be absent on that day (or between two times if only an afternoon/morning).

The spreadsheet also acts as a record of who asked for leave when, whether it was approved and when staff will be absent.

Instructions for Leave Request Automator

Video Demo:

Leave Request Automator.mp4

FAQs:

Can I change the content of the email or calendar event title that gets generated automatically?

Yes you can do this from the script editor (from the spreadsheet go to tools>script editor). Look for the code in red which is normally the writing that would be visible and edit this however you like. The variables (EG ${row[4]} ) are the bits that change depending on what the teacher put in their request so probably best to leave them as they are.

How do I save the event in our shared calendar?

If you go to calendar.google.com and find the shared calendar in the bottom left, click the three dots next to that calendar and go to settings and sharing. Scroll down to where it says Calendar ID which will look like a long email address. Copy that and go back to the code in the script editor and paste it at the top inside the speech marks for the CALENDAR_ID variable. The next time it runs (when someone fills in the form) it will create the event in the calendar you just specified.