If you’re an HR manager, managing your team’s annual leave and vacation days can be a real headache. Receiving and approving vacation requests via email and manually entering or deleting dates from a spreadsheet is incredibly time-consuming and can lead to mistakes.Looking for an automated vacation tracking system? We’ve got a free Sheetgo template and a step-by-step guide to help you set up the system.
This way, there’s no need to buy any new software — our user-friendly, free holiday tracking template is a complete system for the Google Workspace, integrating Google Forms, Google Sheets, Gmail and Google Calendar.
And the best part? Once holiday requests are approved, employees’ vacation dates will automatically populate your company’s Google Calendar.
The template is ready to use — we’ve written the formulas and script for you.
How the vacation tracking system works:
- Block any dates when your team is not permitted to take days off (for example, important company events or quarterly deadlines) in the holiday tracking spreadsheet.
- The staff submits their vacation requests via a user-friendly Google Form.
- Requests are sent to the holiday tracking spreadsheet automatically.
- If the requested date is available, the employee receives an automated confirmation email. If unavailable, the employee receives an automated rejection email, prompting them to select another date.
- The spreadsheet automatically updates your company’s Google Calendar.
Ready to automate annual leave requests and vacation tracking in your company?
Then here’s how to get started:
Step 1: Copy and configure the template
Get a copy of the Holiday tracking template. The template consists of a holiday request form and a holiday tracking spreadsheet which will automatically accept or deny the holidays requested by your team members.
Inside the holiday tracking spreadsheet, manage your team’s days off in the 2021 tab. Firstly, in row 2, fill out the emails of all your employees you sent the Form to. Optionally, fill out the emails of the backup employees in row 1. As a result, these employees cannot take days off at the same time.
In column B, lock certain days. For example, lock the last weeks of every quarter (it’s not possible to take days off during these periods).
Step 2: Connect your Google Calendar to the template
This template automatically updates your Google Calendar directly from the responses in the Google Sheets. In other words, our Google Apps Script takes inputs from the spreadsheet and automatically creates Calendar events. We configured the script to execute once a day so that any changes to the spreadsheet will automatically be reflected on the calendar within a day. Therefore minimizing manual work and eliminating errors.
As you can see, this is a great productivity hack. You stop wasting time scheduling and coordinating holidays amongst your team. Below are the step by step instructions to configure the script for your company.
Create new Google Calendar
Open your Google Calendar and create a new calendar (this will be the calendar that you’ll share with the team).
Copy the calendar ID by selecting Settings and Sharing, scroll down to Integrate Calendar and copy the calendar ID.
Add Calendar ID to Parameters
Finally, in your Holiday Tracking spreadsheet, go to the Parameters tab and fill out the following:
<Calendar_ID>: Calendar ID that you just copied in the previous step.
Sign up for Sheetgo and start tracking worked hours automatically
Step 3: Configure your template timezone
The next necessary step to make the system work is setting the timezone on your spreadsheet template to the current timezone.
Click on File > Spreadsheet settings > General > Timezone. Doing so, the system records your spreadsheet in that specific timezone and affects all time-related functions.
Step 4: Use your Holiday tracking system
To use your system simply send the form to your team. Open your Holiday tracking spreadsheet and click on Form -> Send Form.
After your employees requested their days off, simply click on the menu Holiday tracking > Run. This configures the email that’s being sent to the person requesting the days off and at the same time automatically adjusts your Google Calendar.
Step 5: Set an automated trigger
To make sure your Holiday tracking system automatically responds whenever an employee requests days off, you need to set up an automated trigger.
Go to Extensions > Apps Script in your spreadsheet.
Now, on the bottom-right side of the Triggers page, click on +Add trigger. It will open a screen where you can add the automated trigger. First, choose the runHolidayTracking function to run automatically.
Next, under the third option Select event source, choose Time-driven. Finally, use the other settings to customize the frequency of the automation.
Done — your holiday tracking system in Google is up and running!
Every time an employee requests time off now, the system will automatically approve or deny the request, notify the employee, and add the approved holidays to your shared Calendar.
Automate more HR processes
Now you can control and monitor your coworkers’ days off in a simple and flexible way.
Looking for other HR solutions using Google Sheets? Check out our employee timesheet template; it’s a complete workflow solution to help you monitor the number of hours worked per employee.
