may the VBA be with you

Excel VBAとか業務自動化とか

GASでメール本文から銀行口座の情報をゲットする(自分用)

自分用トリアーエズ
正規表現とかかなり怪しい(例外のほうが多いかも)

色々試して大丈夫そうなら、いずれちゃんと記事に。。。なるかなあ

BankCode API使用
https://bankcode-api.appspot.com/

var mySpreadSheet = SpreadsheetApp.getActiveSpreadsheet();
var mailSheet = mySpreadSheet.getSheetByName('メール');
var infoSheet = mySpreadSheet.getSheetByName('口座情報');

function screeningMailData(){
  var len = mailSheet.getLastRow();
  var range = mailSheet.getRange(2,1,len,5);
  var values = range.getValues();
  var newValues = [];
  var cnt = -1;
  var mailBodyCol = 4;

  for(var i=0;i<len;i++){
    var mailStr = values[i][mailBodyCol - 1];
    var codeNumStr = getCodeNumStr(mailStr);
    if(codeNumStr!=''){
      cnt++;
      newValues[cnt] = [];
      newValues[cnt] = values[i];
      
      var bankName = getBankName(mailStr);
      newValues[cnt][5] = bankName;      
      newValues[cnt][6] = getBankCode(bankName);
    
      var branchName = getBranchName(mailStr);
      newValues[cnt][7] = branchName;   
      newValues[cnt][8] = getBranchCode(newValues[cnt][6], branchName);
      
      newValues[cnt][9] = codeNumStr;
      newValues[cnt][10] = getKatakanaName(mailStr);
    }
 }      
  infoSheet.getRange(2,1,cnt + 1,newValues[1].length).setValues(newValues);  
}

//口座番号
function getCodeNumStr(mailStr){
  var re = /\d{7}/;
  var myArray = mailStr.match(re);
  if (myArray == null){
    return '';
  }
  return myArray[0];
}

//銀行名
function getBankName(mailStr){
  var re = /\n.{1,5}銀行/;
  var myArray = mailStr.match(re);
  if (myArray == null){
    return '';
  }
  var str =  myArray[0];
  str  = str.replace(/\r?\n/g, '');  //改行を消去
  return str ;
}

//銀行コード
function getBankCode(bankName){
  bankName = bankName.replace('銀行','');
  var url = 'https://bankcode-api.appspot.com/api/bank/JP?name=' + bankName;
  Utilities.sleep(1000);  //短時間に何度もアクセスしないように
  try{
    var response = UrlFetchApp.fetch(url);
    var content = response.getContentText('UTF-8');
    var obj = JSON.parse(content);
    var info = obj['data'][0]['code'];
  }catch(e){
    return '';
  }
  return info;
}

//支店名
function getBranchName(mailStr){
  var re = /[^  ))\n]{1,5}支店/;
  var myArray = mailStr.match(re);
  if (myArray == null){
    return '';
  }
  return myArray[0];
}

//支店コード
function getBranchCode(bankCode, branchName){
  if (bankCode=='' || branchName==''){    
    return '';
  }
  branchName = branchName.replace('支店','');
  var url = 'https://bankcode-api.appspot.com/api/bank/JP/' + bankCode + '?name=' + branchName;
  Utilities.sleep(1000);  //短時間に何度もアクセスしないように
  try{
    var response = UrlFetchApp.fetch(url);
    var content = response.getContentText('UTF-8');
    var obj = JSON.parse(content);
    var info = obj['data'][0]['code'];
  }catch(e){
    return '';
  }
  return info;
}

//口座名義
function getKatakanaName(mailStr){
  var re = /[ァ-ヴ]{1,10}\x20[ァ-ヴ]{1,10}/;
  var myArray = mailStr.match(re);
  if (myArray == null){
    return '';
  }
  return myArray[0];
}