Using Google Apps to automate staff cover

I took over the coordination of staff absence and daily cover last September.  It was previously superbly managed by one cover manager, a team of 5 cover supervisors, a series of differently coloured forms – one for personal absence, another one for professional reasons and a series of notebooks and planners.  In September we moved to me as SLT lead of cover and one cover supervisor with a pre purchased cover agreement with a local supply agency.

I needed to overhaul the system – partly to force myself to comprehend the flow of procedures necessary for managing staff absence and deploying cover but also because the coloured forms rarely seemed to appear on my desk until after the absence and I am just not paper effective and knew I could not use the same system for tracking and planning as the previous cover manager did.  For all of my inexperience with this new role I did have an ace up my sleeve…..Google Apps.

In true deconstruction style I set out to note down the new procedures I wanted to use to automate as much of the cover admin as possible.  We frequently get around 40-60 requests for absence per month and that is just for planned – let alone the sickness on the day and when you factor in room bookings for special events and exams this project quickly assumed Everest-like features.

Google Form for planned absence

Planned absence seemed the easiest to solve.  I binned all the coloured forms and built one Google Form with drop down options.  To request an absence staff just had to complete the form (I know many of you will snigger at this… fill the form in you have to find it on a regular basis) and I put the form in the staff area of our Moodle platform.  This meant that every absence request was date and time stamped and dropped into one spreadsheet – easy to track and easy to archive each month by copying the previous month into an archive tab and deleting afresh for the new month.

Staff Absence Request Google Sheet
Archiving older requests

Notifying staff that their absence had been received and processed was a problem though – I quickly fell behind in emailing staff back and would then end up having to deal with more email asking if a) I had got their original request or b) was there a problem with having that time off.  Clearly I needed to speed up the notification back but I wanted to also send back the details of their request so they could double check the details.  After a long search through some Google Scripts (Flubaroo has taught me then someone far better than I will have already found a way to do a task in half the time) and I found GSM Mail Merge a quite stunningly simple add-on with mail merge features.  So by changing my field headings on my collection sheet and adding in the whole staff email directory in a cell data validation applied to a column, I could send an email back, up to 50 in one go, notifying staff that their request had been processed and could they be kind enough to check the details were correct…with no more than a few clicks of a mouse button.

GSM Mail Merge uses your draft emails as templates

GSM Mail merge uses draft emails in your GMail (you can have more than one template) to send to a list of users – it merges the details in your table and then creates a new datestamp so you can track when the member of staff was emailed back their notification.

Draft email to notify staff of absence

I quickly saw another fab use for this mail merge.  When staff are sick for less than 3 days they need to complete a self-certify medical form.  These were on paper and I had to routinely list who was sick on what days and then remind myself to send out a self-certify form upon their return.  My solution to this was to create a new sheet for staff sickness.  When staff ring in sick in the morning I log the reason for their absence and the first date on the staff sickness Google sheet.  This serves as my Friday reminder of who was off during the week.  I created a new email template with a link to a new self-certify form (created in Google Forms of course) and then could batch send these to staff,via GSM Mail Merge, for their completion.  The answers are dropped into the reply sheet which I share with our HR manager for payroll purposes.

Staff self certify form

These processes automate the vast majority of work with staff absence leaving me to deal with the ill staff who ring in in the morning.  I ask them to do this by 7:45 so that I can ring our supply agency based locally and request x number of supply to augment our one cover supervisor.  We bulk buy cover credits in bundles of 100 days – we’ve costed out that this will be cheaper that maintaining our previous team of cover supervisors over the year although clearly the Autumn and Spring terms are busy.

If you’d like a copy of my sheets to enable you or your cover administrator to use Google Apps then please feel free to copy these files at the link here

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s