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 :
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.
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.
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.
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
Steve, thanks a LOT.
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.
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
Post a Comment