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

Written by 

Vikas is the CEO and Co-Founder of Knoldus Inc. Knoldus does niche Reactive and Big Data product development on Scala, Spark, and Functional Java. Knoldus has a strong focus on software craftsmanship which ensures high-quality software development. It partners with the best in the industry like Lightbend (Scala Ecosystem), Databricks (Spark Ecosystem), Confluent (Kafka) and Datastax (Cassandra). Vikas has been working in the cutting edge tech industry for 20+ years. He was an ardent fan of Java with multiple high load enterprise systems to boast of till he met Scala. His current passions include utilizing the power of Scala, Akka and Play to make Reactive and Big Data systems for niche startups and enterprises who would like to change the way software is developed. To know more, send a mail to hello@knoldus.com or visit www.knoldus.com

5 thoughts on “Preparing a Leave Tracking System in less than 30 minutes with Google Apps

  1. please leave tracker for the details of the casual leave or sick leave and pl for the details and the attendance for leave tracker of information fast

  2. Hi, your email address listed bounced back. I’d love a copy of this if it is still available – really useful tool here!

Leave a Reply

%d bloggers like this: