こんにちは、はな夫（@logicalifer）です。
今回は、家計簿ファイルに取り込んだ口座の情報を家計簿の情報と連携させる方法についてご紹介いたします。
我が家では、毎月末にカードの明細を確認するとともに、各銀行口座の残高を確認しています。第4回でご紹介した方法で、銀行口座の取引明細を家計簿に取り込むことができましたが、少し進化させて、毎月月末の残高をサマリのシートへ自動で反映できるようにします。
月末の残高をわかるようにする
前回作成したスクリプトに１列分の情報を追加します。
//8列目に文字列を追加する
if ('Bank' == property){
val[columnIdx] = fileName + '_' + rowIdx;
}内容としては、「ファイル名」_「何行目のデータか」を表示できるようにしました。
スクリプトを全部お見せすると、下記になります。
/**
* カード情報
*/
var item = [];
item[0] = {
property: 'Card',
name: '楽天カード',
id: '「フォルダID」',
prifix: 'enavi',
suffix: '「サフィックス」',
readArr: [0,2,1,3,4],
startRow: 2
};
item[1] = {
property: 'Card',
name: 'イオンカード',
id: '「フォルダID」',
prifix: 'meisai',
suffix: '',
readArr: [0,1,2,3,6],
startRow: 9
};
item[2] = {
property: 'Bank',
name: '家計',
id: '「フォルダID」',
prifix: '',
suffix: '',
readArr: [0,2,3,4,5],
startRow: 2
};
item[3] = {
property: 'Bank',
name: '臨時',
id: '「フォルダID」',
prifix: '',
suffix: '',
readArr: [0,2,3,4,5],
startRow: 2
};
item[4] = {
property: 'Bank',
name: '貯蓄',
id: '「フォルダID」',
prifix: '',
suffix: '',
readArr: [0,1,2,3,4],
startRow: 2
};
/**
* 月定義
*/
var month = ['202011','202012','202101','202102','202103','202104','202105','202106','202107','202108','202109','202110','202111','202112'];
/**
* データ生成関数
* @returns {boolean} true: 正常完了 false：異常完了
*/
function createItemData() {
//スプレッドシートを取得
var ss = SpreadsheetApp.getActiveSpreadsheet();
for ( itemIdx = 0; itemIdx< item.length; itemIdx++ ){
for ( monthIdx = 0; monthIdx < month.length; monthIdx++ ){
//書込先シート取得
var ssDst = ss.getSheetByName(item[itemIdx].property);
//対象のファイル名生成
var fileName = item[itemIdx].prifix + month[monthIdx] + item[itemIdx].suffix;
//書込先シートに記載済みか判定する
if( true == searchFileName(ssDst, item[itemIdx].name, fileName) ){
continue; //既に記載済み ループを抜ける
}else{//まだ記載していない
//ファイルがGoogleドライブにあるかチェックする
var srcFolder = DriveApp.getFolderById(item[itemIdx].id);
var files = srcFolder.getFilesByName(fileName);
if (files.hasNext()){
var file = files.next();
var fileId = file.getId();
Logger.log(fileName + " is exist");
}else{
//ファイルがない場合はループを抜ける
Logger.log(fileName + " is not exist yet");
continue;
}
if (files.hasNext()){
//ファイルは一つしかないはずなので、２つ以上の場合はエラーにする
Logger.log("error: same file name is exist");
return false;
}
//ファイルからデータをコピーする
copyItemData(ssDst, fileId, item[itemIdx].name, fileName, item[itemIdx].startRow, item[itemIdx].readArr, item[itemIdx].property);
}
}
}
return true;
}
/**
* item情報のコピー関数
* @param {Object} ssDst spreadsheet object
* @param {string} fileId
* @param {string} fileName
* @param {string} startRow
* @param {string} readArr
* @param {string} property
*/
function copyItemData(ssDst, fileId, name, fileName, startRow, readArr, property){
var dstRow = ssDst.getLastRow()+1; //次に書き込むべき行を取得する
var srcSheet = SpreadsheetApp.openById(fileId); //対象ファイルを開く
var srcLastRow = srcSheet.getLastRow(); //対象ファイルの最終行を取得する
var srcLastColumn = srcSheet.getLastColumn(); //対象ファイルの最終列を取得する
Logger.log(srcLastRow + " rows and " + srcLastColumn + " columns in " + fileName);
//配列として格納する
var copiedArray = srcSheet.getActiveSheet().getRange(startRow, 1, srcLastRow - startRow + 1, srcLastColumn).getValues();
var arr = [];
for (var rowIdx = 0; rowIdx < copiedArray.length; rowIdx++){
var val = []; //arrに書き込むための1次元配列
//1列目はカード名
val[0] = name;
//2列目はファイル名
val[1] = fileName;
//3列目は日付
//日付は対象によって形式が異なるので確認する
val[2] = convertDateFormat( copiedArray[rowIdx][0] );
//4列目～7列目
//その他は列内容に合わせてコピーする
var columnIdx = 0;
for ( columnIdx = 3; columnIdx < readArr.length + 2; columnIdx++){
val[columnIdx] = copiedArray[rowIdx][readArr[columnIdx-2]]; //readArrで読み場所を変えていく
}
//8列目に文字列を追加する
if ('Bank' == property){
val[columnIdx] = fileName + '_' + rowIdx;
}
//イオンカード対策
//もし価格欄が空欄ならforを抜ける
if (val[6] == ""){
break;
}else{
arr.push(val);
}
}
//書き込み
ssDst.getRange(dstRow,1,arr.length,arr[0].length).setValues(arr);
}
/**
* ファイル名検索関数
* 同じitem、filenameのレコードが既に存在するか確認する
* @param {Object} ss spreadsheet object
* @param {string} name
* @param {string} fileName
* @return {boolean} true: 存在 false：無し
*/
function searchFileName (ss, name, fileName){
var lastRow = ss.getLastRow(); //最終行
var ret = false; //検索結果
var arr = ss.getRange(1,1,lastRow,2).getValues(); //検索対象の配列
//行数分検索を実施する
for (var idx = 0; idx < lastRow; idx++){
if ((arr[idx][0] == name) && (arr[idx][1] == fileName)){ //検索対象にヒット
ret = true;
break;
}
}
//ログ出力用
if (true == ret){
Logger.log(fileName + " is already copied")
}else{
Logger.log(fileName + " is not copied yet")
}
return ret;
}
/**
* 日付変換関数
* @param {string} val
* @return {date} ret
*/
function convertDateFormat(val){
var ret;
str = val.toString(); //文字列へ変換
if (str.length < 8){ //もし文字数が足りないならイオンカード。日付を変換する必要がある
var str = "20" + str;
var dateArr = (str.substr(0, 2) + '/' + str.substr(2, 2) + '/' + str.substr(4, 2)).split('/');
var newDate = new Date(dateArr[0], dateArr[1] - 1, dateArr[2]);
ret = Utilities.formatDate(newDate, 'Asia/Tokyo', 'yyyy/MM/dd');
}else{//その他はそのまま
ret = val;
}
return ret;
}
テスト
前回インポートした銀行口座の明細情報を一度削除し、スクリプトを再実行します。
明細の横に、新たに文字列が追加されれば成功です。
スプレッドシートのサマリシートに銀行口座の欄を追加する
次にサマリのシートに銀行口座の枠を用意します。我が家の家計簿では、F109～I112セルが以下のようになっています。
それぞれのセルには、クエリを入力します。
=iferror(query(Bank!$A$2:$H,"select G where A like '"&$F110&"' and MONTH(C)+1 ="&text(date($A$1,G$1,"01"),"M")&" order by H desc limit 1" ),"")クエリの中身については、
Bank!$A$2:$Hにて、Bankシートの情報を全て対象範囲にしています。
select G にて、G列の残高を取得するようにしています。
where A like ‘”&$F110 にて、対象の口座の名前と同じかを検索しています。
MONTH(C)+1 =”&text(date($A$1,G$1,”01″),”M”) にて、取引のあった日付の月と、記載するセルの月が同一かを検索しています。
order by H desc limit 1 にて、ヒットした項目のうち、先程追加した文字列が一番大きいものを１つだけ表示するようにしています。
このクエリとすることで、該当の口座の、該当の月の、最後の取引後の残高が表示されるようになります。
テスト
銀行口座の明細シートの日付を、テストしたい日付に変更します。
残高がサマリのシートの該当月に反映されれば成功です。
まとめ
今回は、家計簿ファイルに取り込んだ口座の情報を家計簿の情報と連携させる方法についてご紹介いたしました。
第4回でご紹介した方法と合わせることで、銀行口座の取引明細をGoogleドライブにアップし、インポートすれば、毎月の銀行口座の残高が自動で家計簿に表示されるようになりました。
次の記事では、インポートしたクレジットカードの明細情報と支出として入力したデータの紐付け方法についてご紹介いたします。
コメント