How to build an automated holiday tracker with google sheets & forms
Build an easy way to track time off without an enterprise solution
So I recently stumbled onto this post in reddit which was quite interesting and I thought I would help
This is pretty straightforward explanation but I also thought I would flesh it out a bit for anyone who wanted a full build.
In Europe, taking time off is sacred. We usually get 21 days plus a year off and typically without the shame element that is felt by many of our US counterparts.
However, this can be a little bit stressful when you’re in a small team without the fancy gadgets. Is there enough cover? Is the person covering capable? Are we even tracking how much time John has actually taken off???
So here is a little tutorial to create an automated workflow that gives you a breakdown of your teams time off. It also comes with an approval flow for easy management. Here’s what you will receive at the end:
Step 1: Create a google form
In order to gather data about who is taking time off, we first need to create a form with some basic information. This will be shared with the members that need to log requests.
To get started, either log into google drive and select forms or if you use chrome, just type in “form.new” into the browser search bar.
For the google form we only need 3 required fields:
- Name (multiple choice field)
- Holiday start (date field)
- Holiday end (date field)
It should look like this:
Next up, connect that google form to a google sheet, this is pretty simple to do as well. Click on the “Responses” tab at the top and select the sheets icon. It will prompt you to create a new spreadsheet and once selected, it will direct you there.
Next, go back to the form and submit a response, the quickest way to do that is to click on “eye” icon at the top right which will show you a preview of the page. I would create 2–3 responses just to see how the logic works.
Step 2: Adding Google Sheet Logic
This section is slightly more technical so I will provide my template if you don’t want to delve too deeply into the logic.
The google sheet is our data backend, and as such, only the manager/approver needs to see and have access to it.
We’re going to be adding 2 additional columns to the responses sheet in order to do some calculations on time off + add approvals.
- Days off
- Status
For the Days off column, input the following function into only the second line (in this case E2).
=arrayformula(if(len(A2:A),D2:D-C2:C,””))
Now if you have ever used google sheets, you might be wondering “why on earth would I need to use that complicated formula? can’t I just do d2-c2 and drag the formula down????”
Well in this case you can’t. The reason being that whenever you submit a google form, it actually deletes the last row (including your dragged down formula) before inserting a new row with data. This means, that you will constantly have to drag down and reapply the formula to keep up. With the formula above you just set it and forget it!
Now with the status column, we’re going to add data validation to create a dropdown menu for approving or denying requests. Follow the gif below:
Step 3: Building the Summary Page
Now pulling this all together, we want to create an easy top down view for the approver. The way this works is that holiday days, if approved, will roll up to the summary tab along with other relevant information like when they are next off.
Here are the list of formulas, insert all into the relevant second row only:
Name: =UNIQUE(‘Form responses 1’!B2:B)
Number of days allowed: =ARRAYFORMULA(IF(LEN(A2:A),21,””))
Number of days off: =ARRAYFORMULA(IF(LEN(A2:A),if(‘Form responses 1’!F2:F=”Approved”,SUMIF(‘Form responses 1’!B2:B,A2:A,’Form responses 1'!E2:E),0),””))
Holidays left: =ARRAYFORMULA(IF(LEN(A2:A),B2:B-C2:C,””))
Holiday Status: =ARRAYFORMULA(IF(LEN(A2:A),IF(D2:D>1,”Holiday Available”,”No more holiday available”),””))
Next day off: =ARRAYFORMULA(if(len(A2:A),LOOKUP(A2:A,SORT(‘Form responses 1’!B2:B),SORT(‘Form responses 1’!C:C,’Form responses 1'!B:B,true)),””))
Step 3: Send a once off or scheduled Email via SheetSpread
It’s been a long post so far but this is the easiest part!
SheetSpread is a tool that enables you to select a data table in a google sheet and send it in a nice responsive format via email. We’re going to get our summary data and do just that.
Start off by downloading it from the google marketplace, once done, navigate to the Addons menu and under the SheetSpread option select “Workspace”.
Once selected, you should see the sidebar:
Now all we need to do is click on the “Snapshot” dropdown, add a range (A:F), Colour, and click Send Now. It will then prompt you to add a title, subtitle and recipients (add your email). Once that’s done, click Send!
And that’s it! check your email and you should see a template with your chosen colour, mine’s blue this time :).
P.S — If you want to go more pro, try clicking “Send Later” on SheetSpread instead. You can use that to set up hourly, daily, weekly or even monthly emails to your inbox completely automating the process. You can always choose to delete anything scheduled by clicking on the View Email Schedule button at the bottom of the sidebar.
I hope you have enjoyed this article and found it useful! I’ll be writing more posts as time goes on so if you like it and want to be the first to see them, then sign up to my email list below.
