Workflow Sample: Episode 561: Reason for Not Using Cloud Expense Management System (3)

Operation: Reporting and Reimbursement of Expense

We have realized systematization of "Expense report flow" in cloud based Workflow! (see Episode 559: Reason for Not Using Cloud Expense Management System)

Moreover, we have introduced a mechanism to realize "multilateral grouping" (clustering)! (see Episode 560: Reason for Not Using Cloud Expense Management System (2))

Staffs in the Accounting team says they are turning their eyes on expense reports on which the rule "deemed to be approved by manager after 48 hours remaining automatically" has been applied.

Challenge: Sharing data with audit corporation

However, for the amount of information, it is not "the more, the better."

In the workflow, various kinds of data are included not only basic information such as "reimbursement amount" and "posting month" but also "person / time who made report", "document certifying payment", "project name" or "client company name". Certainly it is an important job to extract "information" from various data if it is a person inside a company.

Whereas, to an accounting auditor for example, "process of approval" and "time required" are unnecessary data. Or, for top executives such as a president and a board officer, there is no time to check "who spent a lot of expenses". For these people, it is more important to have the necessary data listed briefly, than "multilateral aggregate filtering".

Hmm, then should I "handcraft" the Spreadsheet for reporting?

[Expense Report flow-Spreadsheet]

Solution: Real time appending to Spreadsheet

In this Business Process Definition, an automatic Step of "Append Sheet" has been placed immediately after the Step of "Correction / Rejection" of accounting at the most downstream.

There, the following six items will be "real time appended" to Google Spreadsheet automatically.

A, Date of payment
B, Posting month
C, Classification
D, Amount of reimbursement
E, Assignment of Applicant
F, Approval ID

Accounting auditor is able to confirm "all the Issues of expense that approved" efficiently by simply referring to the Spreadsheet. No need to log in to the Workflow platform.

Discussion: Significance of external cooperation

The mechanism for automatically outputting the data flowing in the Workflow to the "Google Spreadsheet, an external system" is not merely to realize "easy viewing of data" or "real-time sharing".

That is, it has a significance as existence as log data, since its characteristic that "it is performed automatic and unattended".

Specifically, it is possible to prevent fraud such as concealment, deleting or tampering with business data, and in case of a system failure, it can be used as an alternative reference destination for business data. (Mirroring of Business data)

P.S. : Customizing formats or background colors, etc.

In addition, the "Append Sheets" is an automatic Step which demands many requests for customizing tailored to each corporate culture.

In other words, the basic requirement for logging varies depending on the type and the scale of business, such as "minus amounts must be written in red", "round up / truncate if 1 million yen or less" and "append division of quarter". In order to realize a more harmonious business data environment, it may be better to actively promote common use in the whole company.

<?xml version="1.0" encoding="UTF-8"?><service-task-definition>

<label>Google Sheets Append Cells</label>

<summary>Adds new cells after the last row with data in a sheet, inserting new rows into the sheet if necessary.</summary>

<help-page-url>https://www.questetra.com/tour/m4/m415/addon-googlesheets-appendcells/</help-page-url>


<configs>
  <config name="conf_OAuth2" required="true" form-type="TEXTFIELD">
    <label>V: Set OAuth2 Config Name (at [OAuth 2.0 Setting])</label>
  </config>
  <config name="conf_DataIdW" required="true" form-type="SELECT" select-data-type="STRING_TEXTFIELD|SELECT_SINGLE">
    <label>W: Select STRING/SELECT DATA for Spreadsheet ID (File ID)</label>
  </config>
  <config name="conf_DataIdX" required="true" form-type="SELECT" select-data-type="STRING_TEXTFIELD|SELECT_SINGLE|DECIMAL">
    <label>X: Select STRING/SELECT/NUMERIC DATA for SheetGID (Worksheet ID)</label>
  </config>
  <config name="conf_DataIdY" required="false" form-type="SELECT" select-data-type="STRING_TEXTAREA">
    <label>Y: Select STRING DATA for Access Log (update)</label>
  </config>
  <config name="conf_DataIdA" required="false" form-type="SELECT" select-data-type="STRING|DECIMAL|DATE|DATETIME|SELECT_SINGLE|QUSER|QGROUP|FILE">
    <label>A: Select DATA for Column-A Value of New Row</label>
  </config>
  <config name="conf_DataIdB" required="false" form-type="SELECT" select-data-type="STRING|DECIMAL|DATE|DATETIME|SELECT_SINGLE|QUSER|QGROUP|FILE">
    <label>B: Select DATA for Column-B Value of New Row</label>
  </config>
  <config name="conf_DataIdC" required="false" form-type="SELECT" select-data-type="STRING|DECIMAL|DATE|DATETIME|SELECT_SINGLE|QUSER|QGROUP|FILE">
    <label>C: Select DATA for Column-C Value of New Row</label>
  </config>
  <config name="conf_DataIdD" required="false" form-type="SELECT" select-data-type="STRING|DECIMAL|DATE|DATETIME|SELECT_SINGLE|QUSER|QGROUP|FILE">
    <label>D: Select DATA for Column-D Value of New Row</label>
  </config>
  <config name="conf_DataIdE" required="false" form-type="SELECT" select-data-type="STRING|DECIMAL|DATE|DATETIME|SELECT_SINGLE|QUSER|QGROUP|FILE">
    <label>E: Select DATA for Column-E Value of New Row</label>
  </config>
  <config name="conf_DataIdF" required="false" form-type="SELECT" select-data-type="STRING|DECIMAL|DATE|DATETIME|SELECT_SINGLE|QUSER|QGROUP|FILE">
    <label>F: Select DATA for Column-F Value of New Row</label>
  </config>
  <config name="conf_DataIdG" required="false" form-type="SELECT" select-data-type="STRING|DECIMAL|DATE|DATETIME|SELECT_SINGLE|QUSER|QGROUP|FILE">
    <label>G: Select DATA for Column-G Value of New Row</label>
  </config>
  <config name="conf_DataIdH" required="false" form-type="SELECT" select-data-type="STRING|DECIMAL|DATE|DATETIME|SELECT_SINGLE|QUSER|QGROUP|FILE">
    <label>H: Select DATA for Column-H Value of New Row</label>
  </config>
  <config name="conf_DataIdI" required="false" form-type="SELECT" select-data-type="STRING|DECIMAL|DATE|DATETIME|SELECT_SINGLE|QUSER|QGROUP|FILE">
    <label>I: Select DATA for Column-I Value of New Row</label>
  </config>
  <config name="conf_DataIdJ" required="false" form-type="SELECT" select-data-type="STRING|DECIMAL|DATE|DATETIME|SELECT_SINGLE|QUSER|QGROUP|FILE">
    <label>J: Select DATA for Column-J Value of New Row</label>
  </config>
</configs>


<script><![CDATA[
// Google Sheets Append Cells (ver. 20171031)
// (c) 2017, Questetra, Inc. (the MIT License)
// by spreadsheets.batchUpdate [AppendCellsRequest] (not by spreadsheets.values.append)
// https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#appendcellsrequest

// OAuth2 config sample at [OAuth 2.0 Setting]
// - Authorization Endpoint URL: https://accounts.google.com/o/oauth2/auth?access_type=offline&approval_prompt=force
// - Token Endpoint URL: https://accounts.google.com/o/oauth2/token
// - Scope: https://www.googleapis.com/auth/spreadsheets
// - Consumer Key: (Get by Google Developers Console)
// - Consumer Secret: (Get by Google Developers Console)


//// == Config Retrieving ==
var oauth2 = configs.get( "conf_OAuth2" ) + "";
var dataIdW = configs.get( "conf_DataIdW" ) + "";
var dataIdX = configs.get( "conf_DataIdX" ) + "";
var dataIdY = configs.get( "conf_DataIdY" ) + "";
var dataIdA = configs.get( "conf_DataIdA" ) + "";
var dataIdB = configs.get( "conf_DataIdB" ) + "";
var dataIdC = configs.get( "conf_DataIdC" ) + "";
var dataIdD = configs.get( "conf_DataIdD" ) + "";
var dataIdE = configs.get( "conf_DataIdE" ) + "";
var dataIdF = configs.get( "conf_DataIdF" ) + "";
var dataIdG = configs.get( "conf_DataIdG" ) + "";
var dataIdH = configs.get( "conf_DataIdH" ) + "";
var dataIdI = configs.get( "conf_DataIdI" ) + "";
var dataIdJ = configs.get( "conf_DataIdJ" ) + "";
// convert 'java.lang.String' to 'javascript string'


//// == Data Retrieving ==
var spreadsheetId = "";
if( engine.findDataDefinitionByNumber( dataIdW ).matchDataType( "SELECT_SINGLE" ) ){
    spreadsheetId = engine.findDataByNumber( dataIdW ).get(0).getValue() + "";
}else{
    spreadsheetId = engine.findDataByNumber( dataIdW ) + "";
}
var sheetId       = "";
if( engine.findDataDefinitionByNumber( dataIdX ).matchDataType( "SELECT_SINGLE" ) ){
    sheetId       = engine.findDataByNumber( dataIdX ).get(0).getValue() + "";
}else if( engine.findDataDefinitionByNumber( dataIdX ).matchDataType( "DECIMAL" ) ){
    sheetId       = engine.findDataByNumber( dataIdX ) + "";
    sheetId       = sheetId.replace(/,/g,"").replace(/\./g,""); // Remove dots and commas
}else{
    sheetId       = engine.findDataByNumber( dataIdX ) + "";
}


//// == Calculating ==
// preparing for API Request
var apiRequest = httpClient.begin(); // HttpRequestWrapper
// com.questetra.bpms.core.event.scripttask.HttpClientWrapper

// Request HEADER (OAuth2 Token, HTTP Basic Auth, etc)
var token  = httpClient.getOAuth2Token( oauth2 );
apiRequest = apiRequest.bearer( token );

// Request PATH (https://example.com/abc/def/)
var apiUri = "https://sheets.googleapis.com/v4/spreadsheets/";
    apiUri += spreadsheetId + ":batchUpdate";

// Request QUERY (?a=b)
// (no set)

// Request BODY (JSON, Form Parameters, etc)
function cellDataObject( dataId ){
  var dataObj = {};
  dataObj.userEnteredValue = {};
  if( dataId === "" ){
    dataObj.userEnteredValue.stringValue = "";
  }else if( engine.findDataDefinitionByNumber( dataId ).matchDataType( "SELECT_SINGLE" ) ){
    dataObj.userEnteredValue.stringValue = engine.findDataByNumber( dataId ).get(0).getDisplay() + "";
  }else if( engine.findDataDefinitionByNumber( dataId ).matchDataType( "FILE" ) ){
    dataObj.userEnteredValue.stringValue = engine.findDataByNumber( dataId ).get(0).getName() + "";
  }else if( engine.findDataDefinitionByNumber( dataId ).matchDataType( "QUSER" ) ){
    dataObj.userEnteredValue.stringValue = engine.findDataByNumber( dataId ).getName() + "";
  }else if( engine.findDataDefinitionByNumber( dataId ).matchDataType( "QGROUP" ) ){
    dataObj.userEnteredValue.stringValue = engine.findDataByNumber( dataId ).getName() + "";
  }else if( engine.findDataDefinitionByNumber( dataId ).matchDataType( "DATE" ) ){
    var serialDays = ( engine.findDataByNumber( dataId ).getTime() + engine.getTimeZoneOffsetInMinutes() * 60000 ) / 86400000 + 25569;
    dataObj.userEnteredValue.numberValue = serialDays;
    dataObj.userEnteredFormat = {};
    dataObj.userEnteredFormat.numberFormat = {};
    dataObj.userEnteredFormat.numberFormat.type = "DATE";
  }else if( engine.findDataDefinitionByNumber( dataId ).matchDataType( "DATETIME" ) ){
    var serialDays2 = ( engine.findDataByNumber( dataId ).getTime() + engine.getTimeZoneOffsetInMinutes() * 60000 ) / 86400000 + 25569;
    dataObj.userEnteredValue.numberValue = serialDays2;
    dataObj.userEnteredFormat = {};
    dataObj.userEnteredFormat.numberFormat = {};
    dataObj.userEnteredFormat.numberFormat.type = "DATE_TIME";
  }else if( engine.findDataDefinitionByNumber( dataId ).matchDataType( "DECIMAL" ) ){
    dataObj.userEnteredValue.numberValue = engine.findDataByNumber( dataId ) - 0;
  }else{ // for "STRING" 
    dataObj.userEnteredValue.stringValue = engine.findDataByNumber( dataId ) + "";
  }
  return dataObj;
}
var requestObj = {};
  requestObj.requests = [];
  requestObj.requests[0] = {};
  requestObj.requests[0].appendCells = {};
  requestObj.requests[0].appendCells.sheetId = sheetId;
  requestObj.requests[0].appendCells.fields = "*";
  requestObj.requests[0].appendCells.rows = [];
  requestObj.requests[0].appendCells.rows[0] = {};
  requestObj.requests[0].appendCells.rows[0].values = [];

  requestObj.requests[0].appendCells.rows[0].values[0] = cellDataObject( dataIdA );
  requestObj.requests[0].appendCells.rows[0].values[1] = cellDataObject( dataIdB );
  requestObj.requests[0].appendCells.rows[0].values[2] = cellDataObject( dataIdC );
  requestObj.requests[0].appendCells.rows[0].values[3] = cellDataObject( dataIdD );
  requestObj.requests[0].appendCells.rows[0].values[4] = cellDataObject( dataIdE );
  requestObj.requests[0].appendCells.rows[0].values[5] = cellDataObject( dataIdF );
  requestObj.requests[0].appendCells.rows[0].values[6] = cellDataObject( dataIdG );
  requestObj.requests[0].appendCells.rows[0].values[7] = cellDataObject( dataIdH );
  requestObj.requests[0].appendCells.rows[0].values[8] = cellDataObject( dataIdI );
  requestObj.requests[0].appendCells.rows[0].values[9] = cellDataObject( dataIdJ );

apiRequest = apiRequest.body( JSON.stringify( requestObj ), "application/json" );

// Access to the API (POST, GET, PUT, etc)
var response = apiRequest.post( apiUri ); // HttpResponseWrapper
var httpStatus = response.getStatusCode() + "";
var accessLog = "---POST request--- " + httpStatus + "\n";
accessLog += response.getResponseAsString() + "\n";
//var responseObj = JSON.parse( response.getResponseAsString() );

// Retrieve Properties from Response-JSON
// (no action)

// Error Handling - https://stripe.com/docs/api#errors
// (no action)


//// == Data Updating ==
if ( dataIdY !== "" ){ engine.setDataByNumber( dataIdY, accessLog ); }
]]></script>


<icon>
(omission)
</icon>

</service-task-definition>

[Expense Report flow-Spreadsheet:"1. Report" screen]