第491話：銀行 CSV を Google Sheets に流し込む

2016年7月11日
「通帳のログ、転記してるの？」

日本の銀行は「通帳」という冊子をくれる。ATM に挿し込めば、入出金の記録を全てキレイに印字してくれる。しかし「通帳」が紙としての冊子である限り、そこに記載された情報にアクセスできる人間が限られてしまう。かと言って「銀行帳.xls」に手入力するのもメンドウだ。

以下の業務フローは、銀行の入出金ログを Google SpreadSheet に流し込むという業務だ。

銀行のオンラインサービスで入手できる「入出金ログ」をインポートすれば、指定した SpreadSheet に、自動的に追記される仕組みとなっている。この例では「みずほ銀行」や「多くの地銀」が提供している『ANSER-API形式』というタブ区切りファイルを取り込むスクリプトがセットされている。

「オンライン通帳」とでも言えば良いのだろうか。。。便利だ。



[入出金のログ登録]

『ANSER-API形式』ファイルは、いわゆる「入出金ログ」に該当する。

ファイルの中身は、単純な SJIS TSV テキストだ。先頭が「明細」となっている【行レコード】に入出金情報が記載されている。

この例では入出金情報の内、「口座番号、取引名、取扱日付月、取扱日付日、金額、摘要、金融機関名、支店名」の【列データ】だけを参照し、 Google Sheets API v4 にデータ送信する仕組みとなっている。（列idはそれぞれ、10、12、15、16、19、21、25、26）　Google SpreadSheet ファイルと作り、一列目に「口座番号、取引名、取扱日付月、取扱日付日、金額、摘要、金融機関名、支店名」と書けば準備 OK だ。

後は、日次なり、週次なりで、『ANSER-API形式』ファイルを登録すれば良いのだ。


ちなみに、入出金情報はワークフローシステム側にも記録される。すなわち、万が一 Google Spreadsheets のファイルを破損してしまっても、改めて作成することもカンタンだ。情報のバックアップ、改ざん抑止という視点でも有効だ。また、もし Google Apps Vault 上の Spreadsheets を利用すれば、銀行残高データにアクセスしたユーザや参照した時刻もすべて記録されるようになる。「誰が見たか／誰に見られたか」の記録は「冊子の通帳」には真似できない。内部統制や不正抑止を強化策にもなるだろう。


なお、「通知メール」にプロセスデータを載せない設定（※）にしておく方が良い。（Questetra では v11.1 からの機能）　この例では問題にならないが、もし将来、下流工程を追加したとして、その際に「未処理状態」で通知メールが飛ぶ、という可能性を想定しておきたい。

＜操作動画＞


▼通信イベント『Sheets API』の設定サンプル
POST: https://sheets.googleapis.com/v4/spreadsheets/#{data['2']}:batchUpdate
Content-Type: application/json

＜参考: 関連する Google Sheets API v4 マニュアル＞
◇ ファイルに対するリクエストの一覧（Requests オブジェクト）
◇ ファイルに対する行データ追加リクエスト（AppendCellsRequestオブジェクト）
◇ 行データ（RowData オブジェクト）

▼スクリプト工程『JSON 生成』の設定サンプル （サーバサイド 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") + ""; 
// 年マタギの一括登録は注意 

//// == 演算 / 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");   
// 改行毎に分割し string[] 配列に格納 
var appendId = 0; 

for (var i=0; i < bankRecordsArray.length; i++){ 
  var cellsArray = bankRecordsArray[i].split("¥t");   
  // Tab毎に分割し string[] 配列に格納 

  if( cellsArray[0] == "明細" ){  // 先頭が「明細」の行のみ抽出 
    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() } }; // 口座番号 
    myObj.requests[0].appendCells.rows[appendId].values[1] 
      = {"userEnteredValue": {"stringValue" : cellsArray[12] } }; // 取引名 

    var tmpM = ("0" + cellsArray[15]).slice( -2 ); // 取扱日付の月: zeroPadding 
    var tmpD = ("0" + cellsArray[16]).slice( -2 ); // 取扱日付の日: zeroPadding 
    myObj.requests[0].appendCells.rows[appendId].values[2] 
      = {"userEnteredValue": {"stringValue" : yearString + "-" + tmpM + "-" + tmpD } }; 

    if( cellsArray[12] == "出金" ){ 
      myObj.requests[0].appendCells.rows[appendId].values[3] 
        = {"userEnteredValue": {"numberValue" : 0 - cellsArray[19] } }; // 金額 
    }else{ 
      myObj.requests[0].appendCells.rows[appendId].values[3] 
        = {"userEnteredValue": {"numberValue" : cellsArray[19] } }; // 金額 
    } 

    myObj.requests[0].appendCells.rows[appendId].values[4] 
      = {"userEnteredValue": {"stringValue" : cellsArray[21] } }; // 摘要 
    myObj.requests[0].appendCells.rows[appendId].values[5] 
      = {"userEnteredValue": {"stringValue" : cellsArray[25] } }; // 金融機関名 
    myObj.requests[0].appendCells.rows[appendId].values[6] 
      = {"userEnteredValue": {"stringValue" : cellsArray[26] } }; // 支店名 
    myObj.requests[0].appendCells.rows[appendId].values[7] 
      = {"userEnteredValue": {"formulaValue" : "=INDIRECT(ADDRESS(ROW(),COLUMN()-4))+INDIRECT(ADDRESS(ROW()-1,COLUMN()))" } };
      // 残高の集計 

    appendId++; 
  } 
} 

myObj.requests[0].appendCells.rows[appendId-1].values[8] 
  = {"userEnteredValue": {"stringValue" : new Date() } }; // （更新日） 


// === 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 );


▼「ファイル・オープン」の設定サンプル（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>


▼入力画面『File URL』ボタンの設定サンプル（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>

[入出金のログ登録：「1.ログ登録」画面]

＜データ項目一覧画面＞


[雛形ダウンロード (無料)]
＜類似プロセス＞
≪関連記事≫

[英文記事 (English Entry) ]
時刻:
ラベル: , , , , , , ,

0 件のコメント :

コメントを投稿

登録: コメントの投稿 ( Atom )