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)
//// == 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]
[Data Items list]
[Free Download]
- Business Template: Activity Log Registration
- Episode 488: Collaboration with Cloud Accounting (Part 3) (2016-06-20)
- Episode 484: Addition of New Client via kintone API (2016-05-23)
- Episode 464: Out-of-pocket Expenses Reimbursement Claim (Starter Template) (2016-01-04)
- M213 OPERATING SCREEN: Guidance Shown on Operating Screen (HTML/JavaScript)
- M230 AUTOMATED STEP: Auto Executing Complicated Data Processing (ECMAScript)
- M225 AUTO EVENT: Auto Sending of HTTP Request with Business data Inserted
[和文記事 (Japanese Entry) ]