A Simple Flow Against Complaint

Monday, June 6, 2011
* Publish weekly since 6th of June for better quality!
* As the first of weekly issue, introducing <How to integrate Google Spreadsheets with QBPMS>Today

We offer Workflow Template for a variety of Business Category in "Workflow-Sample.net". Analyzing on the number of repeaters and duration of visits for each article, we see which category of business or business task draws attention. (Speaking of the recent developments in the Japanese version is "Telecommuting")

Among those categories, "Handling Complaints" gathers attention all the time. I guess all sorts of companies seek how they should build handling flow as well as how to contact with customers.
In particular,
  • When to share information with Supervisors and executives ?
  •  By whom and how the handling be checked ?
would be difficult.

The following is a Workflow model assumed that handling complaint is the most important mission of the Director of Customer Service. All the answering sentence to the complaint will be sent after review (or modified) by the Director of Customer Service.

1. Input Complaint, 2. Creating a primary answer, 3. The primary answer sentence review, 4. Creating a secondary answer, 5. Secondary answer sentence review

[Handle Complaints: "3. The primary answer sentence review" screen]

  • Subject (Name caller complaint / Claim date)
-Claim Information-
  • Claim Date (date)
  • Name Caller Claims (string)
  • Email address (string)
  • Original Text (string)
-Information of Answers-
  • Answer 1 (string)
  • Answer 2 (string)
  • Ignore? (select:yes/no)
  • Review 1 (select: OK/back)
  • Review 2 (select: OK/back)
  • Correspondence (discussion)

If a BPMS Product is based on BPMN, applicative [handling Flow Syste] may be reflected changes in business conditions in real time.
For example, for a business environment(?) that "never send answer to claim until the President reviewed", Workflow model can be changed like following.

1. Input Complaint, 2. Creating a primary answer, 3. The primary answer sentence review, 4. Creating a secondary answer, 5. Secondary answer sentence review, 6. President Review

<How to generate claim reception web form>

Human Centric BPM products like [Questetra BPM Suite] automatically generates Web forms for employees responsible for each task, but "Web Forms for the public" such as claim reception web form is not automatically generated.
Now we are going to explain how to set up "Web form for the public" on Google Spreadsheets, and how input data is integrated with Workflow.

1. [INTERNAL SYSTEM] Prepare an externally-initiated workflow
First, create a process model that has a Message Start Event. After activating the workflow, check the URL address found in the [Version Detail] screen.



In this example, the input values are data[1] (inquirer email address), data[2] (inquirer phone number), and data[3] (inquiry content).

2. [EXTERNAL SYSTEM] Prepare a form in Google Docs
Log into Google Docs and create a new file from [Create new > Form]. (You can change the theme if you want.)

* File name: "Complaint_Management"
* Questions: "Inquirer email address," "Inquirer phone number," "Inquiry"

You should get a spreadsheet with "Inquirer email address" in column A, "Inquirer phone number" in column B, and "Inquiry" in column C.

3. [EXTERNAL SYSTEM] Prepare a script that sends inputted info
Open the spreadsheet, and create a new script from [Tools > Scripts >Script editor].

function myFunction() {


function startWorkflow(e) {
var longurl = "https://s.questetra.net/XXXXXXXX/System/Event/MessageStart/start";
var payload = "processModelInfoId=ZZ&nodeNumber=WW&key=YYYYYYYYYYYYYYYYYYYYYYYYYYYYYY";
payload += '&data[1].input=' + encodeURIComponent(e.values[1]);
payload += '&data[2].input=' + encodeURIComponent(e.values[2]);
payload += '&data[3].input=' + encodeURIComponent(e.values[3]);
var params = {
method: 'post',
payload: payload
UrlFetchApp.fetch(longurl, params);

...and save. Make sure you replace "XXXXXXXX", "YYYYYYYYYYYYYYYYYYYYYYYYYYYYYY", "ZZ" and "WW" with your own info. If you are not in Japan, you will probably want to also change "JST" to your own timezone.

4. [EXTERNAL SYSTEM] Set the script to work whenever someone submits info from the form
In the Script editor, select [Trigger > Current script's triggers]. Add a new trigger and set it to: Run [startWorkflow], Events [From spreadsheet] [On form submit].