Google Apps: Creating a Workflow Based Expense Tracking System

In my last example on Google Apps within the enterprise, you had seen how easy it was to implement a Leave tracking system with the help of Google Apps. In this post, we would look at the introduction of workflow in an expense system and also how easy it is to embed the system in your existing Google sites infrastructure.
Note:- This example enhances the expense report system of Google App Script.

Technologies used
1) Google Spreadsheet
2) Google Apps Script
3) Google Sites
4) Google Mail

Let us see how the workflow looks like

As you can see, the employee can fill in the timesheet on the browser or through the mobile. As soon as he hits submit, a trigger is activated which updates the “Expense Report” spreadsheet and sends a mail to the manager. (Click here to view a sample copy of the Expense Report spreadsheet)

The expense report form looks like this

and the corresponding spreadsheet looks like this

We have set up a trigger to execute the script as soon as the form is submitted

Let us see what the script looks like

[sourcecode language=”javascript”]
function onReportOrApprovalSubmit() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];

var approvalsSpreadsheet = SpreadsheetApp.openById(APPROVALS_SPREADSHEET_ID);
var approvalsSheet = approvalsSpreadsheet.getSheets()[0];

var data = getRowsData(sheet);

var approvalsData = getRowsData(approvalsSheet);

// For every expense report
for (var i = 0; i < data.length; ++i) {
var row = data[i];
row.rowNumber = i + 2;
if (!row.state) {
// This is a new Expense Report.
// Email the manager to request his approval.
sendReportToManager(row);
// Update the state of the report to avoid email sending multiple emails
// to managers about the same report.
sheet.getRange(row.rowNumber, COLUMN_STATE).setValue(row.state);
} else if (row.state == STATE_MANAGER_EMAIL) {
// This expense report has already been submitted to a manager for approval.
// Check if the manager has accepted or rejected the report in the Approval Spreadsheet.
for (var j = 0; j < approvalsData.length; ++j) {
var approval = approvalsData[j];
if (row.rowNumber != approval.expenseReportId) {
continue;
}
// Email the employee to notify the Manager’s decision about the expense report.
sendApprovalResults(row, approval);
// Update the state of the report to APPROVED or DENIED
sheet.getRange(row.rowNumber, COLUMN_STATE).setValue(row.state);
break;
}
}
}
}
[/sourcecode]

As you would notice, in the expense report spreadsheet, we scan all the rows and when there is a row which does not have a state, we know that it is a new expense report. The next task is to send the manager an email specifying that an expense report is waiting for approval.

The sendReportToManager(row);
function looks like this

[sourcecode language=”javascript”]
function sendReportToManager(row) {
var message = ""
+ "" + row.emailAddress + " has requested your approval for an expense report."
+ "" + "Amount: $" + row.amount + "" + "Description: " + row.description + "" + "Report Id: " + row.rowNumber
+ ‘Please approve or reject the expense report <a href="’ + APPROVAL_FORM_URL + ‘&entry_0=’+row.emailAddress + ‘&entry_1=’+ row.rowNumber +’">here</a>.’
+ "";
MailApp.sendEmail(row.managersEmailAddress, "Expense Report Approval Request", "", {htmlBody: message});
row.state = STATE_MANAGER_EMAIL;
}
[/sourcecode]

In simple terms, we are sending an email to the manager on his email id which was filled in the expense report form. In this email we are linking the approval form which is pre-filled with the employees email and his unique report request.

The following line,

[sourcecode language=”language="text”]
<a href="’ + APPROVAL_FORM_URL + ‘&amp;entry_0=’+row.emailAddress + ‘&amp;entry_1=’+ row.rowNumber +’">here</a>.’
<p style="text-align: center;">
[/sourcecode]

prefills the first and the second field of the approval form with the details. The form looks like this

He can either accept or reject the request and enter a description. Once he submits the form, we should activate the workflow logic again so that either the expense request can be marked APPROVED or DENIED.The entries of the manager are posted as a part of the spreadsheet like this (click here to view this spreadhseet)

Ideally, we would have liked to call the next step of the workflow which is the same logic as present in the “Expense Report” however as of now, Google does not allow calling the function of Spreadsheet A from Spreadsheet B. Follow this link and vote for it if you need it.

Anyhow, to circumvent that issue we have a timer trigger mentioned as a part of the Expense Report spreadsheet which executes the workflow method as a part of time interval, currently set to one minute. As soon as the workflow method triggers, the results are sent back to the employee with the following method

[sourcecode language=”javascript”]
// Sends an email to an employee to communicate the manager’s decision on a given Expense Report.
function sendApprovalResults(row, approval) {
var approvedOrRejected = (approval.approveExpenseReport == "Yes") ? "approved" : "rejected";
var message = ""+ "" + approval.emailAddress + " has " + approvedOrRejected + " your expense report."
+ "Amount: $" + row.amount
+ "Description: " + row.description
+ "Report Id: " + row.rowNumber
+ "Manager’s comment: " + (approval.comments || "")
+ "";
MailApp.sendEmail(row.emailAddress, "Expense Report Approval Results", "", {htmlBody: message});
if (approval.approveExpenseReport == "Yes") {
row.state = STATE_APPROVED;
} else {
row.state = STATE_DENIED;
}
}
[/sourcecode]

The sent email looks like this

In order to embed the expense form into your Google sites, simply goto the site and click on Insert->Spreadsheet form. The form would look like this and is ready for your organization to fill in expenses.

Happy expense tracking!

For any questions/clarifications send a mail to vhazrati at inphina dot com