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.
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.
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 email@example.com