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

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

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

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

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,

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

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

// 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;
  }
}

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

About Vikas Hazrati

Vikas is the CTO @ Knoldus which is a group of software industry veterans who have joined hands to add value to the art of software development. We do niche product and project development on Scala, Spark and Java. We consult and coach on effective software development and agile practices. With our focus on software craftsmanship you can be assured of a good quality at the right price. To know more, send a mail to info@knoldus.com or visit www.knoldus.com
This entry was posted in Cloud, Java and tagged , , , , . Bookmark the permalink.

2 Responses to Google Apps: Creating a Workflow Based Expense Tracking System

  1. Pingback: Google Appscript: Prefil Form based on sheet contents | FYTRO SPORTS

  2. Joseph says:

    i have tried to add a second approval am having a challenge is possible to assist

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