Workflow Sample: Episode 491: Bank CSV Data into Google SpreadSheet

"Are you transcribing the banking logs?"

Every bank in Japan give you a booklet referred to "Passbook". When you insert it into an ATM, all the records of deposit and withdrawal will be printed on it fine and clear. However, as long as the "Passbook" is a paper booklet, people who are accessible to the information in it are limited. Yet, it is cumbersome to enter the data into "Bank book.xis" manually.

The following Business flow is an operation of exporting account activity to Google SpreadSheet.

It is a mechanism that automatically appending to specified SpreadSheet when importing "Account activity" which can be obtained through online service provided by the bank. In this example, a Script has been set to retrieve a tab delimited file in a format referred as "ANSER-API" which is widely used in Japanese Banks.

I would call this as "Online Passbook"... Isn't it convenient?

[Activity Log Registration]

"ANSER-API format" file corresponds to the so-called "Account activity logs".

The content of this file is simple SJIS TSV Texts. Account activity information has been described in [Row record].

In this example, only [Column data] of "Account number, Activity, Handling month, Handling date, Amount, Payee, Financial institution, Branch" are retrieved and transmit it to Google Sheets API v4. (Column id is respectively, 10, 12, 15, 16, 19, 21, 25, 26) Create a Google SpreadSheet, and input "Account number, Activity, Handling date, Amount, Payee, financial institution, Branch" in the top row. Now, it is ready.

What you have to do is registering "ANSER-API format" file, daily or weekly.

In addition, Activity information is recorded also on the side of Workflow system. Therefore, restore of the Google SpreadSheet is easy, even if the SpreadSheet file was crushed. It is useful in the both aspects of information backup and deterrent for falsification. Also, if you use the Spreadsheets on Google Apps Vault, so that all the user who accessed to bank balance data and its time will be recorded. "Passbook booklet" will not be able to record "who saw / who spied". It would lead to an enhancement of the internal control and fraud deterrent.

Note that you should configure not indicating Process data on "Notification email". (* An option of Questetra version 11.1 and later) Even though it won't be a problem in this example, however, you should consider the possibility of notification email to be transmitted at downstream Step, which could be added in the future, for "Not being processed".

[Operation movie]

= Sample setting of Communication event: "Sheets API"
POST: https://sheets.googleapis.com/v4/spreadsheets/#{data['2']}:batchUpdate
Content-Type: application/json

<Reference: Related Google Sheets API v4 manual>

List of Request for Files (Request Resource)
Data appending Request for File (AppendCellsRequest)
Row data (RowData)

= Sample setting of Script Step "JSON generation" (Server side JavaScript)

//// == Retrieving == 
var bankStatementTsv = data.get("q_bankStatementTsv") + ""; 
// var outputSpreadsheetId = data.get("q_outputSpreadsheetId") + ""; 
var outputSheetId = data.get("q_outputSheetId") + ""; 
var yearString = data.get("q_yearString") + ""; 
// Be cautious when Collective registration of spanning the year 

//// Calculating == 
var myObj = {}; 
myObj.requests = []; // Array 

// === post cell data === 
myObj.requests[0] = {}; 
myObj.requests[0].appendCells = {}; 
myObj.requests[0].appendCells.rows = []; // Array 

myObj.requests[0].appendCells.sheetId = outputSheetId; 
myObj.requests[0].appendCells.fields = "*"; 

var bankRecordsArray = bankStatementTsv.split("\n"); 
// Divide at every line break, store into a string [] array 
var appendId = 0; 

for (var i=0; i < bankRecordsArray.length; i++){ 
  var cellsArray = bankRecordsArray[i].split("\t"); 
  // Divide at every Tab, store into a string [] array 

  if( cellsArray[0] == "明細" ){ // Extract rows that value of top cell is "明細" (item) 
    myObj.requests[0].appendCells.rows[appendId] = {}; 
    myObj.requests[0].appendCells.rows[appendId].values = []; // Array 

    myObj.requests[0].appendCells.rows[appendId].values[0] 
      = {"userEnteredValue": {"stringValue" : cellsArray[10].trim() } }; // Account number 
    myObj.requests[0].appendCells.rows[appendId].values[1] 
      = {"userEnteredValue": {"stringValue" : cellsArray[12] } }; // Activity 

    var tmpM = ("0" + cellsArray[15]).slice( -2 ); // Handlinng month: zeroPadding 
    var tmpD = ("0" + cellsArray[16]).slice( -2 ); // handlyng date: zeroPadding 
    myObj.requests[0].appendCells.rows[appendId].values[2] 
      = {"userEnteredValue": {"stringValue" : yearString + "-" + tmpM + "-" + tmpD } }; 

    if( cellsArray[12] == "出金" ){ // Extract rows that value of top cell is "出金" (withdrawal) 
      myObj.requests[0].appendCells.rows[appendId].values[3] 
        = {"userEnteredValue": {"numberValue" : 0 - cellsArray[19] } }; // Amount 
    }else{ 
      myObj.requests[0].appendCells.rows[appendId].values[3] 
        = {"userEnteredValue": {"numberValue" : cellsArray[19] } }; // Amount 
    } 

    myObj.requests[0].appendCells.rows[appendId].values[4] 
      = {"userEnteredValue": {"stringValue" : cellsArray[21] } }; // Payee 
    myObj.requests[0].appendCells.rows[appendId].values[5] 
      = {"userEnteredValue": {"stringValue" : cellsArray[25] } }; // Financial institution 
    myObj.requests[0].appendCells.rows[appendId].values[6] 
      = {"userEnteredValue": {"stringValue" : cellsArray[26] } }; // Branch 
    myObj.requests[0].appendCells.rows[appendId].values[7] 
      = {"userEnteredValue": {"formulaValue" : "=INDIRECT(ADDRESS(ROW(),COLUMN()-4))+INDIRECT(ADDRESS(ROW()-1,COLUMN()))" } }; 
      // Aggregate balance

    appendId++; 
  } 
} 
myObj.requests[0].appendCells.rows[appendId-1].values[8] 
  = {"userEnteredValue": {"stringValue" : new Date() } }; // (Date of Updating) 

// === format number data === 
myObj.requests[1] = {};
myObj.requests[1].addConditionalFormatRule = {
  "rule": {
    "ranges": [
      {
        "sheetId": 0,
        "startRowIndex": 1,
        "startColumnIndex": 3,
        "endColumnIndex": 4
      }
    ],
    "booleanRule": {
      "condition": {
        "type": "NUMBER_LESS_THAN_EQ",
        "values": [
          {
            "userEnteredValue": "0"
          }
        ]
      },
      "format": {
        "textFormat": {
          "foregroundColor": {
            "red": 0.9,
            "green": 0,
            "blue": 0
          },
          "bold": true
        }
      }
    }
  }
};

var myJsonText = JSON.stringify( myObj ); 

//// == Updating == 
retVal.put("5", myJsonText );

= Sample setting of "File open" (HTML/JavaScript)

<input type="file" id="localFile" name="nouse" accept='.api'><br> 
<button type="button" id="openFile">TSV Import</button> 

<script type="text/javascript"> 
jQuery('#openFile').on('click',function(){ 

  var myFile = jQuery("#localFile").get(0).files[0]; 
  jQuery('input[name="title"]').val( myFile.name ); 

  var reader = new FileReader(); 
  reader.readAsText( myFile, "shift-jis" ); 

  reader.onload = function(evt){ 
    jQuery('textarea[name="data[0].input"]').val( evt.target.result ); 
  } 

  jQuery('#localFile').replaceWith( jQuery('#localFile').clone() ); // Clear 
}); 
</script>

= Sample setting of "File URL" button (HTML/JavaScript)

<button type="button" id="myUrlCheck">File URL</button> 
<span id="myUrl">
<a target="_Blank" href="https://docs.google.com/spreadsheets/d/●/edit#gid=●">https://docs.google.com/spreadsheets/d/●/edit#gid=●</a>
</span> 

<script type="text/javascript"> 
jQuery('#myUrlCheck').on('click',function(){ 
  var myFileId = jQuery('input[name="data[2].input"]').val(); 
  var mySheetId = jQuery('input[name="data[3].input"]').val(); 

  jQuery( '#myUrl' ).html( 
    '<a target="_Blank" href="https://docs.google.com/spreadsheets/d/' 
    + myFileId 
    + '/edit#gid=' 
    + mySheetId 
    + '">https://docs.google.com/spreadsheets/d/' 
    + myFileId 
    + '/edit#gid=' 
    + mySheetId 
    + '</a>' ); 
}); 
</script>

[Activity Log Registration:"1. Log Registration" screen]