With our team in Brazil and Spain and our users in over 100 countries around the world, we need to be flexible with our holiday tracking and vacation planning. We set a fixed number of days per year that an employee can take off and let them choose which days they want to take off (whether or not it’s an official holiday).
Google Forms + Sheets + Apps Script + Calendar solution
We have created a Holiday Request Form for the employees to select their dates. Once the form is filled this will automatically send a request to the Holiday Tracking spreadsheet where the manager can accept or deny the request based on the demand of dates, holidays etc (*note: for this solution it is assumed that the user that configures the system will be the one accepting or denying holiday requests).
Information from the form and the manager response will be sent to a sheet with all the dates of the year in a column and a single column for each active team member. This information will also be sent out automatically to the G Suite calendar so that everyone can have a clear view of who is off on any given day.
How to configure your Holiday tracking
We created a Google Apps Script that takes inputs from the spreadsheets and automatically creates calendar events. The script is configured to execute once a day so that any changes to the spreadsheet are automatically reflected on the calendar within a day, thus minimizing manual work and eliminating errors.
We’re releasing this script for free to the community as a small productivity hack so that you can stop wasting time scheduling and coordinating holidays amongst your team. Below
Step 1: Configure the spreadsheets
- Click the following links to create a copy of the Holiday Tracking spreadsheet and Employee Database (click on “Make a Copy” when prompted)
- Inside the “HR Employee Database” spreadsheet, fill out the ‘Database‘ tab with data of your organization or team (note: for the purpose of this template we only need columns A-D, feel free to fill in, change, or delete the other columns)
- Use Sheetgo to connect your spreadsheets by going to https://app.sheetgo.com.
Note: You will be asked to log in with your Google account to make sure that your Google Sheets are available. By clicking the checkbox to agree to
- Once installed and fully authorized, inside the Sheetgo Web app, click on the green ‘+‘ icon on the lower right side to create the connection between the “HR Employee Database” and “Holiday Tracking” spreadsheets.
- Click ‘Select Files’ and find the “HR Employee Database” as a Data Source.
- Now click on ‘Data Destination’ and then ‘Change Destination’ and search for your “Holiday Tracking” sheet
- Under ‘Settings’, scroll down and click to the “Filter by” option and click on the checkbox to Enable the filter.
- Now click Query Language and in the query field type or copy/paste:
SELECT A,B,C,D WHERE C = "Yes"
- Now click on ‘SAVE CONNECTION‘ to complete the connection between the 2 sheets
Step 2: Configure your 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.
In your Holiday Tracking spreadsheet, go to the “Parameters” tab and fill out the following:
- <your_calendar_id>: ID of the calendar that you copied in step 4
- <event_suffix>: Suffix of the events for Time Off of the employees. Ex.: “OFF”
Step 3. Configure your template timezone
The final 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 will record your spreadsheet in that specific timezone and affect all time-related functions.
Step 4. Use your Holiday tracking system
To use your system simply send the form to your team by opening your tracking sheet and clicking ‘Form -> Send Form’. They will fill out the form to request days off which you, as manager, can reject or deny by filling out column F on the tab Time Off Request Form.
After accepting or denying the requested days off, simply click on the green ‘Run Holiday‘ button on the Parameters tab. This will configure the email that’s being sent to the person requesting the days off and at the same time automatically adjust your Calendar.
Done, you’ve successfully created your Holiday tracking system! 🙂
Now you can control and monitor the days off of your coworkers in a simple and flexible way. If you are a programmer and would like to collaborate on this project, help us improve our code so that the solution keeps on evolving let us know! Read this blog post Employee timesheet template to find a perfect workflow solution that helps monitor the hours worked per employee.