こんにちは、はな夫（@logicalifer）です。
今回はこれまでにインポートしたカードの情報と支出のデータを紐づけるための処理をします。
我が家では1ヶ月に一度カードの明細の確認をしていますが、家計簿に入力した支出の一覧と照らし合わせるのはかなり時間がかかります。そこで機械的に確認が取れるものは計算に任せることで、この確認作業の手間を減らします。
比較の方法について
カードの明細として入力した項目と支出に入力した項目を照らし合わせます。
全てが一対一で紐づくようにできれば良いのですが、クレジットカードで支払った支払先については自分たちが認識している名前とカード会社で明細書に記載される名前がなかなか一致しません。
例えばショッピングセンター内のテナントに対してクレジットカードで支払った場合、明細書にはショッピングセンターの名前だったり、テナントの名前だったりバラバラだったりするので、これを事前に把握するのは困難です。
そこで日付、金額、クレジットカードの種類をキーとして一意になるような文字列で比較することにします。
一度文字列を生成するのは同じ日に同じ金額で複数回の支払いをした場合に対応できるように連番の番号をつける必要があるためです。
入力フォームデータシートの追加項目
以前テスト入力した項目の横に関数を追加していきます。
- A～G列はフォームから入力した項目
- H列：日付、金額、クレジットカードの種類を連結した文字列
- I列：H列の文字列が同じシートに何回現れたかカウントした数字
- J列：H列とI列を連結した文字列
- K列：カードの明細に現れたかを示す
日付、金額、クレジットカードの種類を連結した文字列
この文字列は以下のコードで生成しています。
=TEXT(indirect("RC[-7]",false),"yyyymmdd")&"_"&indirect("RC[-4]",false)&"_"&indirect("RC[-2]",false)TEXT(indirect(“RC[-7]”,false),”yyyymmdd”)はyyyy/mm/dd形式で表している日付を、yyyymmddの文字列に変換しています。
indirect(“RC[-4]”,false)は支払金額を表しています。
indirect(“RC[-2]”,false)は支払い方法を表しています。
あえて、セルをRC形式で指定しているのは、後からGASを利用して入力するためです。
H列の文字列が同じシートに何回現れたかカウントした数字
下記の計算式を使います。こちらも、RC形式で指定しています。
また、該当の行までに現れた数をカウントすることで、重複した文字列が何個目かを表示できるようにしています。
=COUNTIF($I$2:indirect("RC[-1]",false),indirect("RC[-1]",false))H列とI列を連結した文字列
これは2セル左と１セル左の文字列を連結しています。
=indirect("RC[-2]",false)&"_"&indirect("RC[-1]",false)カードの明細に現れたかを示す列
カードの明細に同じ文字列があるかをカウントします。
1セル左の文字列がCardというシートのK列にあるかチェックします。
=countif(Card!$K:$K,indirect("RC[-1]",false))以上で、支出の入力シートに追加する項目ができました。
フォーム入力時に自動で追加されるようにする
先程の追加項目は、手動で入力するのもありですが、フォームに入力したときに自動で挿入されたほうがスマートです。
そこで、GASを用いて、自動で追加されるようにします。
参考にさせていただいたのは、下記です。
スクリプトのソースコードは以下となります。
var formConsts =[ '=TEXT(indirect("RC[-7]",false),"yyyymmdd")&"_"&indirect("RC[-4]",false)&"_"&indirect("RC[-2]",false)',
'=COUNTIF($I$2:indirect("RC[-1]",false),indirect("RC[-1]",false))',
'=indirect("RC[-2]",false)&"_"&indirect("RC[-1]",false)',
'=countif(Card!$K:$K,indirect("RC[-1]",false))'
];
/**
* フォーム投稿時文字列追加関数
* @param {Object} e
*/
function submitForm(e) {
var range = e.range; // Rangeオブジェクトで取得
var sheet = range.getSheet(); // シートを取得
var arr = []; // 2次元配列として利用するために一度宣言する
arr.push(formConsts); // 固定文字列を取得
//9列目＝I列から文字列を追加する
sheet.getRange(range.getRow(),9,1,formConsts.length).setValues(arr);
}formConstsの部分に先程Formシートに追加した計算式を定義しておき、フォームに投稿されたら、この文字列をI列からL列に追加するようにしておきます。
作成したスクリプトが、支出を登録時に自動で実行されるように設定します。
スクリプトエディタの左側の時計マークをクリックし、
画面が切り替わったら右下のトリガーを追加をクリックします。
トリガーの追加ダイアログが表示されたら、実行する関数を先程作成したsubmitFormに変更し、イベントの種類をフォーム送信時に変更し、保存をクリックします。
テスト
フォームに入力したら、期待する関数が追加されるかテストします。入力フォームにテスト用のデータを入力し、送信します。
送信完了後にスプレッドシートにテスト用のデータとともに関数も追加されているか確認します。
追加されていれば成功です。
カード明細データシートの追加項目
カードの明細についても、同様に項目を追加します。
- A～H列はフォームから入力した項目
- I列：日付、金額、クレジットカードの種類を連結した文字列
- J列：H列の文字列が同じシートに何回現れたかカウントした数字
- K列：H列とI列を連結した文字列
- L列：支出のフォームに現れたかを示す
列が１つずつずれていますが、追加する内容は支出のフォームで入力したものとと同じです。
日付、金額、クレジットカードの種類を連結した文字列
この文字列は以下のコードで生成しています。
=TEXT(indirect("RC[-6]",false),"yyyymmdd")&"_"&indirect("RC[-2]",false)&"_"&indirect("RC[-8]",false)計算式は支出のシートと比べて、対象の列が少し違いますが、構造は同じです。
G列の文字列が同じシートに何回現れたかカウントした数字
こちらも支出のシートとまったく同じです。
=COUNTIF($I$2:indirect("RC[-1]",false),indirect("RC[-1]",false))H列とI列を連結した文字列
こちらも支出のシートとまったく同じです。
=indirect("RC[-2]",false)&"_"&indirect("RC[-1]",false)カードの明細に現れたかを示す列
支出のフォームに同じ文字列があるかをカウントします。
1セル左の文字列がFormというシートのK列にあるかチェックします。
=countif(Form!$K$2:$K,indirect("RC[-1]",false))以上で、カードの明細シートに追加する項目ができました。
スクリプトの変更
第3回、第4回で作成したスクリプトを変更し、先程の関数が追加されるようにします。
定数として下記を追加します。１つ目に、備考として空白文字列を追加することで、入力フォームと列数が揃うようにしています。
/**
* カード固有の情報
*/
var cardConsts =[ '',
'=TEXT(indirect("RC[-6]",false),"yyyymmdd")&"_"&indirect("RC[-2]",false)&"_"&indirect("RC[-8]",false)',
'=COUNTIF($I$2:indirect("RC[-1]",false),indirect("RC[-1]",false))',
'=indirect("RC[-2]",false)&"_"&indirect("RC[-1]",false)',
'=countif(Form!$K$2:$K,indirect("RC[-1]",false))'
];
また、8列目以降に文字列を追加する処理を追加します。
//8列目～12列目
if ('Card' == property){
for ( idx = 0; idx < cardConsts.length; idx++ ){
val[columnIdx + idx] = cardConsts[idx];
}
}else 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'];
/**
* カード固有の情報
*/
var cardConsts =[ '',
'=TEXT(indirect("RC[-6]",false),"yyyymmdd")&"_"&indirect("RC[-2]",false)&"_"&indirect("RC[-8]",false)',
'=COUNTIF($I$2:indirect("RC[-1]",false),indirect("RC[-1]",false))',
'=indirect("RC[-2]",false)&"_"&indirect("RC[-1]",false)',
'=countif(Form!$K$2:$K,indirect("RC[-1]",false))'
];
/**
* データ生成関数
* @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列目～12列目
if ('Card' == property){
for ( idx = 0; idx < cardConsts.length; idx++ ){
val[columnIdx + idx] = cardConsts[idx];
}
}else 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;
}
テスト
第3回でインポートしたデータを一度全て削除し、再度インポートしてみます。
期待するように関数が追加されれば完成です。
まとめ
今回は、Googleスプレッドシートで作成した我が家の家計簿にインポートしたカードの情報と支出のデータを紐づけるための処理が簡単にできるように関数を追加した方法をご紹介しました。
これで、毎月の明細の確認処理が少し低減するかと思います。
ただし、クレジットカードの明細はお店で支払った日が、カード会社に売上が計上される日と異なる場合があるので、注意が必要です。その項目については、手動で確認をする必要があります。
次の記事では、家計簿に必須ではない内容ですが、住宅ローンの把握に便利な機能をGoogleスプレッドシートで作成した家計簿に取り込む方法についてご紹介します。
コメント