Using Google spreadsheet for leave requests

My friend asked me to help him tweak a code to manage leave request he found online. I am sharing how I did that.

The core idea is to create a Google spreadsheet and a form to capture the leave requests. An email is then sent to possibly an admin and an event is created on a company shared calendar so that everyone can view it.

The base template I used can be found at http://info.groveis.com/blog/bid/152618/Tutorial-Build-a-Leave-Request-Management-System-with-Google-Apps

The main issues were that user couldn’t apply for half day leaves, and there was no way of approving leave requests.

The way to fix the former is to have additional questions on the form, capturing both the leave type. The most challenging part is dealing with the logic when creating an event on the calendar.

The second problem can be fixed by having two different calendars, one for requests, and one for those approved and have a link in the email for admin to approve or reject the leave.

That is the gist of it, for more technical breakdown and code, please continue reading.

Code for my tweaks can be found at https://github.com/daniellowtw/ELeave-Gscript

Some Gotchas

When using Google form to capture date, the date format was the UK format dd/mm/yyyy. However, when you create a new date object in javascript as in new Date(“xx/yy/zzzz”), it is interpreted as the US format mm/dd/yyyy. This could be fixed by going to spreadsheet settings and change the locale to US.

In the URL link to accept or reject a leave request, it is somehow possible to approve a leave that has previously been rejected. This seem to suggest that when one deletes a Google calendar event, it is not really deleted until later. The fix is to have a ‘seen’ variable somewhere else to track whether a decision has already been made. This is achieved by the status column in the spreadsheet.

Google apparently doesn’t allow one to delete an event by ID even though it gives an ID to events. There is an ugly fix to this, EventSeries has a method called getEventSeriesById, and it will accept event ID as well. However, the problem is this returns a EventSeries which does not have the same methods as an Event object. For instance, I cannot get start date as I would for an Event object. The fix to this is to store the relevant information in Tags when creating the event, and to modify it later, one would delete the old event and create a new one using those Tag information.

Advertisements

Published by

3 thoughts on “Using Google spreadsheet for leave requests”

  1. I have been working on making your tweaks work. I’m afraid I am a very novice user and am stuck on the proper way to add the Status column and define it in the script.
    On my responses sheet, I added Status in column J. If I put in Script ‘J” for column I get error. I know I’m not defining something correctly but do not know what.

  2. The post have been removed.. to bad. You dont feel for posting it down here right? 🙂

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s