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.

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 “Update Choices” from the Auto-Processing Task group.

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 the setting screen and change the Type of data source pulldown where [Fixed choice list] has been selected by default to [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”.

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 [OAuth2 Setting] beneath “C1: OAuth2 Setting Name.”

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.

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 .”

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, turn on the “Token will move to Error Boundary Event when processing fails” toggle switch 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.

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 [Update Choices]

“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” [Update Choices] is used.

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.”

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 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.

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.

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.