Utilize Customer Data on Google Sheets as Choices

A useful tool for managing data tables such as customer data is spreadsheet software. I’m sure that you have created a data table with spreadsheet software, as well. In this post, I will show you how to acquire the data on a Google Sheet with Questetra BPM Suite and create an “Options Master” based on it.

By saving a list of choices as an “Options Master”, you can use it as master data for options in a multiple choice Select-*type Data Items. If you save the customer data as the “Options Master” for example, you can set the customer data as options for any Select-type data items.

During configuration you will require System Administration authority. make sure the account you create an App with has been granted System Administrator authorization.
For the account you are using to log in you can confirm it at “Authorization” < “Account Settings.”

Spreadsheet to use

In this article, we will create an App using an example Google Sheet as shown above in which the unique IDs and customer names given to each customer are written. Please prepare a similar spreadsheet yourself.

To create a workflow diagram

Now, let’s create an App to acquire data from Google Sheets and update/generate the Options Master. Create a new App with the name “Google Sheet: Choice Acquisition Test.”

After you launch the Modeler, let’s first create a Workflow diagram.

Name the first Human Task “Start”, and the second one “Check.”

You can select “Google Sheet: Download Choices Data” and “Service Task (Choices Update)” from the [advanced] palette.

Data Items settings

Next, let’s prepare the data item to be used. In addition to the Title, there are three more Data Items needed.

Data Item Name Data Type
Customer ID String type-multiple lines
Customer Name String type-multiple lines
Master Data Check Select type-Radio Button

Regarding “Master Data Check”, you need further detail settings. Click on the [Edit] button to open [Choices Setting]. Where [Specify individually below] has been selected by default change the radio button to [Specify by string type data item.] As dropdown menus are displayed to specify a String-type Data Item for Choice ID and Label, select “Customer ID” and “Customer Name”https://docs.google.com/document/d/12mrgeGT-0kMFSgJ2Qw4SH7SJmQystHSCmuSv4hxdt4g/edit?usp=sharing.

Data on the Google Sheet is stored in the String-type Data Items of “Customer ID” and “Customer Name” one item by one row using [Google Sheet: Download Choices Data]. “Master Data Check” is a Select-type Data Item which combines these two strings.

Settings in [Google Sheets: Download Choice Data]

We are going to continue configuring the settings in [Google Sheets: Download Choice Data] (hereinafter referred to as just Download Choice Data), which is an important object in this App.

OAuth 2 setting

You need to set up OAuth 2 so that Questetra BPM Suite can retrieve data from the Google Sheet. Open the property screen of “Download Choices Data” then click on the button labeled as [Set up OAuth 2 from here.] beneath “C1: OAuth2 Setting Name.”

The “Set up OAuth 2 from here” button cannot be clicked unless you are logged in to an account which is granted System Administrator authorization. This OAuth2 configuration will be shared by all Workflow Apps.

When you click on the button, the settings page will open in another tab. At first, the OAuth2 configuration list will be empty, so click on the [+ Add] button to create a new OAuth2 configuration. Give the configuration a proper name such as “Connect with {Google USER}”. After you have created a new configuration, click “Get token”. Clicking on the button will open a Google page. Sign in with an account that is able to browse the spreadsheet you are using, then allow Questetra BPM Suite to access it. Once this is done it will return to Questetra BPM Suite OAuth2 Settings page. Check if you’ve gotten a refresh token and if so, close the OAuth2 setting page.

Go back to the Modeler and see the dropdown window of “C1: OAuth2 Setting Name” to find the configuration name you have created. Select the name and now you have completed the OAuth2 setting.

ID of the spreadsheet

Concerning “C2: Source Spreadsheet ID”, enter the ID of the spreadsheet which contains the data to be retrieved. The ID of the spreadsheet is included in its URL like the following.

https://docs.google.com/spreadsheets/d/(sheet ID)/edit#gid=0

Title of the Sheet

Into the “C3: Source Sheet Title”, enter the title of the sheet that contains the data cells.

Range of Choice IDs and Choice Labels

Regarding “C4: Range of Choice IDs” and “C5: Range of Choice Labels”, enter the range of cells which contain Choice ID data and Choice Label data respectively .”

Confirm the cell range
Enter to Config. screen

Save destination for Choice IDs/Choice Labels

You must specify the save destination for the retrieved data. In this example, select “Customer ID” for “C6: STRING Data item that will save Choice IDs” and “Customer Name” for “C7: STRING Data item that will save Choice Labels.”

Finally, check the box “Token will move to Error Boundary Event when processing fails” above item C1. The Task icon changes to display an Error Boundary Event, a pink circle. If the processing in the “Download Choice Data” event fails, the token moves to this event. Add another End Event and draw a flow as in the following figure.

Add End Event and draw a flow

With this setting a Process can be ended without updating the Options Master if the processing failed such as when “Data could not be retrieved from the Google Sheet.”

Settings in [Service Task (Choices Update)]

“Master file” is a type of add-on that can be used in an App, and can be referenced by Select-type Data Items in an App. If you register this “Master File” as “App Shared Add-on” in Questetra BPM Suite, the registered master data can be referenced from all the Apps. To create/update a “Master file” [Service Task (Choices Update)] is used.

There are three items to set

User who updates choices

Click on the [Me] button to specify your own account. As it is noted on the screen, System Administrator authority is required for updating a Master file.

Select-type data item whose choices will be saved as “App-shared Add-on”

Specify a Data Item to save as an “App-shared Add-on Master File”. Select “Master Data Check” which stores data that has been acquired from the Google Sheet.

Name when saving as App-shared Add-on”

Type “Customer List” to be the name of the Add-on.

Data editing permission

Finally, set up the Data editing permissions. Set up in the Tasks of “Start” and “Check” as in the following diagram.

Start Check
Title Editable Only display
Customer ID No display Only display
Customer Name No display Only display
Master Data Check No display No display

Operation check

Now let’s save and release the App and check its actual operation.

After you have completed the “Start” Task, a moment later you will be allocated the “Check” Task. Open the Task operating screen to see the displayed String type Data Items of “Customer ID” and “Customer Name” and confirm if data on the Google Sheet has been acquired. If it seems fine, finish the “Check” Task and wait for the Process to be completed.

You can check the saved “Options Master” in the “Master file of Select type data item” tab in “App-shared Add-on” < “App Settings.”

You can check the contents of the saved “Options Master” by clicking on the magnifying glass icon

If it does not work properly

If the “Check” Task was not allocated, confirm if the token has advanced to the “Error Boundary Event”. You can see the status of the Process in “Started Processes” in the Workflow page, etc.

If the token has reached the End Event via the “Error Boundary Event” instead of the “Check” Task, you must confirm what kind of error has occurred in the “Download Choices” Service Task. To check the log of Tasks that have been automatically processed enter “Administrator Mode” on the Process details or Task details.

When “Administrator mode” is enabled, you can check the automatic processing log

When you check the “Administrator Mode” box, automatically-processed items which were not displayed in the “Operation History” will now be displayed. Now the “Auto-Processing Log” of “Download Choices” can be viewed, so let’s see the error contents.

Mistyped in the sheet name as “Shet1”!

In the message, it says “Unable to parse range”. In this example, an incorrect sheet name has been entered on purpose to cause an error. As a result, the data acquisition range could not be parsed, resulting in an error log. If the App does not work properly, please check the “Auto-Processing Log”.


Appendix: Setting of Select type Data Item using Options Master

Since we have created a choice master, we will briefly introduce an example of setting a select-type Data Item using the choice master. By setting the definition of choices to “Specify by App Shared Add-on” in the choice settings for the Select-type Data Item, the Options Master is read as the choices for that Select-type Data Item.

A simple App only to load the Options Master and to make a selection on the choices at the “Check Choices” Task
Click on [Edit] button on Select type Data Item and set as above

For example, create a simple Workflow as shown above, and set the Select type Data Item which loads the Options Master of “Customer List” to be editable at the “Check Choices” Task.

As a result, the “Customer List” is displayed as a Select type Data Item as in the above figure.

%d bloggers like this: