Preparing a Leave Tracking System in less than 30 minutes with Google Apps

On one of our fun filled iBAT sessions, we decided to build something which would be quick and also have business value for Inphina. Amongst the top candidates was a leave tracking system. Inphina is an Agile organization and believes that when one has to take a leave then one has to take a leave. But, our accountants also need the records for accounting and payroll generation. So we decided to build one quickly with Google Apps.

Technologies used – Google Forms, Google Spreadsheet, Google App Script and Google Calendar

Concept,

As you can see from the diagram, an Inphiner would fill the leave form on the browser either from his laptop or his phone. The leave form then gets recorded on the Spreadsheet. Once the form is filled, the Inphiner also gets a mail about the details of the form filled for his reference.

Now, is the interesting part. The spreadsheet uses the power of Google App script and creates an entry on the Inphina calendar about the leave with all the required details. The script gets triggered on the submit of the form using the trigger events. Note, that the onEdit event of the spreadsheet does not trigger with the form submit hence we had to fall back on the trigger.

Once we have the new leave information added on the spreadsheet, a call is made to the calendar API to add the leave there.

Let us look at some code now for the app script. As you would observe that we have set up the trigger for executing the onEdit() function whenever there is a form submit.

Next, the onEdit() function is executed.

[sourcecode language=”javascript”]
var ADDED = "Added";
var BASE_SPREADSHEET = SpreadsheetApp.openById("tKjgRu1IdJ8uRMUBFF0W9wY");
var LEAVE_SHEET = SpreadsheetApp.openById("tKjgRu1IdJ8uRMUBFF0W9wY").getSheetByName("records");
var START_ROW = 2; //This is the row where the data starts (2 since there is a header row)

function onEdit(){
var dataRange = LEAVE_SHEET.getRange(2, 1, LEAVE_SHEET.getMaxRows() + 1, 10);

// Create one JavaScript object per row of data.
objects = getRowsData(LEAVE_SHEET, dataRange, 1);

// For every leave record, check if it needs to go the calendar
for (var i = 0; i < objects.length; i++) {
var row = objects[i];

// Browser.msgBox("value of row.added = " + row.added );
if (row.added != ADDED) {
LEAVE_SHEET.getRange(START_ROW + i, 9).setValue(ADDED);
addLeaveToTheCalendar(row.date)
}
}
}

function addLeaveToTheCalendar(date){
// write add to calendar API
var cal = CalendarApp.getCalendarById("inphina.com_67n7c5adhbnauaf06jcae7kjgg@group.calendar.google.com");
cal.createAllDayEvent("Leave Record", new Date(date), {location:’Nap room’});
}
[/sourcecode]

The logic is simple, on the addition of a new leave, we scan the records to see the rows which have not been added to the calendar. Then using the Calendar API, these rows are added to the calendar as events and the row is marked as added in the spreadsheet.

Fortunately for us, our accountant has access to our calendar and can easily track the leaves during the month for our payroll generation.

Thus using the power of Google Apps we quickly built the leave tracking system which works for us. Of course, you could enhance the functionality more if you like and build in an approval process too, but for now this works for us.

If you would like to get a copy of our leave record spreadsheet send us a mail at googleapps@inphina.com