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.

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'});
}

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

Advertisements

About Vikas Hazrati

Vikas is the Founding Partner @ Knoldus which is a group of software industry veterans who have joined hands to add value to the art of software development. 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). To know more, send a mail to hello@knoldus.com or visit www.knoldus.com
This entry was posted in Agile, Architecture, Cloud and tagged , , , , . Bookmark the permalink.

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

  1. Pingback: Google Apps: Creating a Workflow Based Expense Tracking System « Inphina Thoughts

  2. revathi.l says:

    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

  3. Thanks for some great and informative information. Have some very relevant information.

  4. TravisD says:

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

  5. Pingback: Google Apps – Employee Time &amp;amp; Time-Off tracking — iWorkPay.com

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