Approval Workflow using Apps Script

Tuesday, April 17, 2012 | 3:08 PM

Labels: ,

Editor’s Note: This blog post is authored by Dito’s Steve Webster who is a Google Apps Script Top Contributor - Saurabh Gupta

Ethics Disclosure Review Workflow

Recently a company who operates retail stores throughout a few states reached out to Dito. When their associates conduct business discussions with vendors or customers where monetary exchanges are involved, their Ethics Disclosure policy requires a log for each event, a supervisor approval, and committee review.

The customer’s existing disclosure review process was cumbersome and time consuming. The employees would add a disclosure review request to a spreadsheet with 29 columns. The new review request would then be reviewed by their supervisor. The supervisor and the employee would go back and forth exchanging emails and making changes to a dense spreadsheet until an approval was granted. It was clear that the customer needed a workflow solution. They decided to hire Dito to build a workflow solution based on Google Apps Script.

Workflow Solution based on Google Apps Script

To make the process more user friendly and productive, Dito decided to build a user interface to collect ethics disclosure events, make updates, and automate the routing of email notifications. Writing a Google Apps Script to create a user interface (UI), enabled associates to interact with their contacts to select their supervisor’s email address and simplify the data collection with list boxes. The script sends approval emails with HTML form radio buttons, text box, approve/decline buttons, and a “Post” command to invoke other workflow scripts. Below are some of the main design points for this Approval Workflow script.


1. Disclosure Review Workflow

The Disclosure Review workflow requires (a) Associates to fill out the Ethics Disclosure form. (b) Supervisor to either approve or decline the submission. (c) If supervisor approves, the Ethics Disclosure Committee is notified. (d) If supervisor declines, the associate is notified to make corrections. (e) After improving the submission, the workflow repeats itself.


2. Disclosure Review Request Form

Dito developed a custom review request form. A form was developed using Google Apps Script’s UI Services. The form provides the ability to look up data to populate such things as a drop-down list box. This form allowed real-time email look-ups by using the Apps Script’s Contacts service.  First efforts included per character look-ups in a list box, but since they have over 1,000 users, it was best to enter the first and/or last name of their supervisor before initiating the look-up (see code snippet below).

var byName = ContactsApp.getContactsByName(searchKey);
for (var i in byName) {
  var emailStr  = byName[i].getPrimaryEmail();
  // If there is no 'primary' email, try again for the next email
  if (emailStr == null)
     var emailStr  = byName[i].getEmails()[0].getAddress(); 
  // If emailStr is still null, try again by getting the next email
  if (emailStr == null) 
     var emailStr  = byName[i].getEmails()[1].getAddress();
}

Another dynamic field was the “activity type”.  Depending on the selection more form fields are displayed. For example, if the activity type is “Meals”, display a list box to select lunch or dinner.


3. Approve or Reject directly in Gmail

When an associate submits his/her review request by using the custom form within a spreadsheet, their supervisor receives an email with easy-to-read HTML formatted results.  The approval decision, as well as a comment field (e.g. decline reason), is made within the email. This is more productive and prevents unnecessary back and forth into the spreadsheet.

If the request is declined by the supervisor, the associate who submitted the review request receives an email and can review the details. The email also contains a “Continue” button which opens the form in a new browser tab. After corrections are submitted, the supervisor receives another email and the workflow repeats itself.

When approved, the Ethics Disclosure Committee is notified by sending a group email within the script.


4. Saving Workflow History

Since history records existed in their original spreadsheet form and they wanted to insert these records into the new work flow spreadsheet as a one-time task, an Apps Script was used to copy the data.  Of course their columns did not match the new spreadsheet.  By using a mapping approach and a “read once” and “write once” technique, the Apps Script quickly made the changes.

function myFunction() {
  var ss           = SpreadsheetApp.getActiveSpreadsheet();
  var sheet        = ss.getSheetByName('Sheet 1');
  var rowsWithData = sheet.getRange(2, 1, sheet.getLastRow(), 
                     sheet.getLastColumn()).getValues()
  var sheet1Data = []; 
  var sheet2Data = []; 
  for (var i = 0; i < rowsWithData.length; i++) {
    switch (rowsWithData[i][4])   // This is the activity type
    {
      ...
      case "Gift":
            sheet1Data.push([rowsWithData[i][12], rowsWithData[i][13], 
                             rowsWithData[i][14]]);
            sheet2Data.push([rowsWithData[i][15]]);
            continue;
      ...
      default:
            continue;
    }
  }
  sheet.getRange(2, 6,  sheet1Data.length, 3).setValues(sheet1Data);
  sheet.getRange(2, 12, sheet2Data.length, 1).setValues(sheet2Data);
}

Google Apps Script is very powerful and Dito uses it to build interesting solution for its customers. If you are using Google Apps then be sure to use Google Apps Script. You’ll be amazed with what you can build with it.




Steve Webster   profile

Google Sites and Scripts expert from Dito specializing in training and application development. When not busy finding solutions to enhance customer capability in Google Apps, Steve shares examples of his work in the Google Apps Developer Blog.

7 comments:

Pakorn Ngammanussiri said...

Great example on apply Google Apps platform to suit customer needs.

I'm interested in learning this.

Especially, where can I start on the part that create an email which supervisor can response within the email.

Alberto Bartoli said...

Great example.

I am missing a point: could you please give me a hint about how you can accept/decline directly in Gmail ? (step 3).

I don't grasp the structure of the script code that constructs the form and sends it via Gmail.

Thanks a lot.

Eric Gonzalez said...

Really hoping to meet other Google Apps customers at Google IO that can share examples of how they have used App Script to automate internal processes.

Steve Webster said...

Before sending the email, build the HTML body like this:

var body =
...
'<.form action="' + PublishedURL + '" method="post">' +
'<.input type="hidden" name="row" value="' + row +'" /> ' +
'<.input type="radio" name="decision" value="Approve" checked="checked" /> Approved
' +
'<.input type="radio" name="decision" value="Decline" /> Declined

' +
'Declined Reason or Comment: ' +
'<.input type="text" size="75" name="reason" width="200px" value="(enter comment/decline reason)" />

' +
'<.input type="submit" value="Submit" />' +
'<./form>' +
...
MailApp.sendEmail(supervisor, "Ethics Disclosure Approval Needed" , "", {htmlBody: body});

Note: periods were inserted in HTML tags so Blogger would accept it. Ignore them.

Questions like these are best answered on the Google Apps Script Forum:
https://groups.google.com/a/googleproductforums.com/forum/#!categories/apps-script

Contact Dito for development services here: www.ditoweb.com

Alberto Bartoli said...

Steve, thanks a LOT.

Harry said...

This is very basic workflow using script. We have developed google docs based workflow app. see demo video: http://www.youtube.com/watch?v=w2ouZ7OUZ8k contact us www.netkiller.com if you are interested or to see live demo.

jack hussey said...

The architecture of mobile GIS is very similar to that of Internet-based GIS, using a client-server architecture.Client-server applications usually implement what is referred as a Three Tiered Architecture.
iphone app development