Holiday Planner for Google Workspace

So far there is no special vacation planner in Google Workspace. However, since holiday planning is a necessary and recurring task in every team, we have developed a tool for this. This allows you to manage vacation requests in a structured and clear manner. Below we give you step-by-step instructions on how to implement the vacation planner as a Google Workspace customer.

The basis for the vacation planner is a Google sheet with an associated script. This allows you to easily manage data sets and dynamically change them using functions.

With the free Javascript Web Framework Vue.js, the data can be integrated into a web application and displayed in a modern and clear way by Vuetify. The application runs in Google Apps Script, which is part of Google Workspace and is provided by Google at no additional cost.

Functions

The application offers the following features:

• Interface for users and supervisors
• Submission of leave requests (full and half day)
• Confirmation of applications by supervisors
• Automatic mail delivery
• Overview of remaining vacation
• Overview of booked vacations in the team
• Admin function for management
• Automatic storage of public holidays
• Database for overview and maintenance of vacation requests and employees
• Automatic synchronisation with Google Calendar

Since the database is based on a Google Sheet, you can manage it without any further knowledge of database systems.

To use the vacation planner in Google Workspace, just follow the steps below:

1. Filling the database

As already described, you can manage all the data you need using a Google Sheet. To start using the vacation planner, all you have to do is this:

Copy the original sheet

Then store the employees who are to use the tool later in your copy in the 'Employees' sheet.

• email: The employee's email address
• firstname: First name of the employee
• manager: The e-mail address of the employee's manager (if no manager can be assigned, you should enter your own email)

• team: abbreviation of the team to which the employee belongs
• state: State in which the employee is employed
• holidaysPerYear: How many vacation days are available to the employee per year
• holidays: Number of holidays that the employee still has available in the current year
• role: available roles are ‘manager’ and ‘admin’

  • manager - can view/approve and reject the leave requests of the employees they are assigned as a manager
  • admin: sees requests from all employees and can reject or approve them
  • for other roles you can either leave the field blank or add a new role, this has no effect
• wantsSync: Is filled automatically (regulates whether vacation requests are automatically synchronised with Google Calendar)

2. Creating the calendar

In order to enable automatic calendar synchronisation, you need to create a Google calendar in which the vacation requests will be processed. All you have to do is create a new calendar in your Google Calendar app under “Other Calendars”.

In the settings of the new calendar you will find the "Calendar ID" under the item "Integrate calendar". Copy the ID, because this has to be stored later in the script.

3. Installation

In order to make the application available for your company, you now have to generate a link that your colleagues can use to access the webpage. Links are provided in Apps Script via a deployment. To do this, first open the script from your spreadsheet.

You are now in the Google Apps Script development environment. This is where all the functions and calculations are done. To generate your new link, all you have to do is start the creation of a new deployment with a button and select 'Web App' as 'Type' in the configuration window and store your account in 'Execute as'. Under 'Who has access' select 'Anyone within *your domain*'.

All you have to do is create a new deployment at the push of a button and store the generated link in the upper part of the code.

Your URL will be displayed to you after successful deployment. Copy this and paste it in the script in the 'Globals' section at DEPLOYLINK between the double quotes. Now insert the calendar ID created in step 2 under BASECALENDAR.

With this deploy link, all employees, managers and admins can now access the new tool.

4. Navigating the interface

When opening the web app for the first time, each user is asked whether they want to activate automatic synchronisation with their Google Calendar. You can change this at any time in the settings.

Now that the database is populated and ready to use, you can open the interface with the link you just generated. The application always automatically recognises the current user and shows him various options depending on his stored role.

Employee without/ custom role

Role 'manager' or 'admin'

5. Apply for leave

The 'Overview' tab looks the same for all roles. Here, every employee can apply for new leave and view past leave requests.

6. Supervisor review

When a user has submitted all requests, he can send a request to his manager via a button. He then has the opportunity to check all applications and to confirm them or, if necessary, reject them. The applicant will be notified automatically.

7. User and team overview

After the supervisor's reaction, the user receives information on all open and completed applications in his overview.

In the 'Team Overview' tab, each employee receives an overview of all vacation requests that colleagues from their team have requested and received. This serves to improve planning within the team and to coordinate vacation requests.

Since the vacation planner is now fully configured and ready to use, you can forward the link to all future users or store it directly on your intranet.

Ready for your vacation?

The holiday planner enables the uncomplicated processing of holiday requests and offers added value for all Google Workspace customers who previously had to do their annual planning manually.

It combines the uncomplicated database system of Google Sheets with the complexity and automation possibilities of Apps Script and together with Vue.js results in a modular and extensible program that can be individually adapted.