Workflow Sample: Episode 492: Referring to Client Master SpreadSheet (Sheets API v4)

In many companies, "Client lists" are managed with spreadsheet software.

And I just think that "Google SpreadSheet" and "kintone", which are Cloud-based database, are utilized more and more now, comparing to 10 years ago when "Excel files" had been created as a matter of course. After all, at a reasonable price (or even for Free), you can enjoy the benefit of convenience,such as

Accessible from anywhere at any time
Minimize the risk of data lost
No need to duplicate data to carry
Access logs are preservable depending on the service plan

It would be grateful especially for the small and medium-sized and micro enterprises.

[API communication]

Moreover, you will be able to build a mechanism which an external system to refer a "Client list", using Cloud-based database. (API function: Application Programming Interface)

The following Workflow is an automated flow for retrieving a "Client list on Google SpreadSheet" into Workflow platform. In this example, "Client list" will be automatically synchronized every night at one o'clock. (The flow itself is almost the same as Synchronization with kintone which I introduced previously.)

[Client master Synchronization - SpreadSheet Integration]

Speaking of "APIs", it is not that complicated. About Google SpreadSheet, when you throw a request which is;

https://sheets.googleapis.com/v4/spreadsheets/1PfOZTv8l7FX-l-Gk2K2FOiK9f-aFdTRUGiuHlMPEhVg/values:batchGet?ranges=pubtest!A2:B

then a response like below will be returned. That's what it's all about.

=Sample of Response=

{
  "spreadsheetId": "1PfOZTv8l7FX-l-Gk2K2FOiK9f-aFdTRUGiuHlMPEhVg",
  "valueRanges": [
    {
      "range": "pubtest!A2:B1000",
      "majorDimension": "ROWS",
      "values": [
        [
          "www.toyota.co.jp",
          "Toyota Motor Corporation"
        ],
        [
          "www.nissan.co.jp",
          "NISSAN MOTOR CO., LTD. "
        ],
        [
          "www.honda.co.jp",
          " Honda Motor Co., Ltd. "
        ],
        [
          "www.mazda.com",
          "Mazda Motor Corporation"
        ],
        [
          "www.fhi.co.jp",
          "Fuji Heavy Industries Ltd. "
        ]
      ]
    }
  ]
}

After that, a person who has mastered the JavaScript will be capable of various data processing. Even though it is designed to set Options XML into Workflow platform (Questetra) in this example, you can apply it to a variety of business operations other than this.

* Indeed, you need to configure security communication referred to OAuth 2. I hope you would get over it somehow.
* Manual for Google Sheets API ->
https://developers.google.com/sheets/reference/rest/v4/spreadsheets.values/batchGet

Note that, it is designed to go through a human Step of " Update Approval" in this example. And within the grace period of one hour, it is also possible to stop the synchronization. You may climb down the bed at midnight and check it when you feel anxious about if the auto-processing is properly working or not.

[Client master Synchronization-SpreadSheet Integration: "1. Synchronization Check" screen]

= Sample setting of Script Step "List Generate" (Server side JavaScript)

//// == Retrieving == 
var myJson = data.get("1") + ""; // JSON text 

//// == Calculating == 
var customersData = JSON.parse( myJson ); 
var numOfRecords = customersData.valueRanges[0].values.length; 

var value_id_list = ""; 
var display_label_list = ""; 
for (i = 0; i < numOfRecords; i++){ 
  value_id_list += customersData.valueRanges[0].values[i][0] + "\n"; 
  display_label_list += customersData.valueRanges[0].values[i][1] + "\n"; 
} 

//// == Updating == 
retVal.put("4", value_id_list); 
retVal.put("5", display_label_list);

[Data Items list]