財テク：Googleスプレッドシートで家計簿をつける(第3回目)

こんにちは、はな夫（@logicalifer）です。

今回は、Googleスプレッドシートで作成した家計簿にクレジットカードの明細を自動で入力する方法をご紹介します。

我が家では、毎月のクレジットカードの明細を確認し、家計簿に入力されたデータとの整合性を確認しています。

自動で引き落とされる月額料金やAmazon等のネット通販はお店と違って財布にレシートが溜まっていくわけではないので、入力を忘れがちです。

そこで、毎月明細と支払額が確定するタイミングで内容を確認しています。

けれども、明細の項目が大量にあると、一件ずつ確認するのも大変です。

そこで、カードの明細のファイルを家計簿に取り込んで、家計簿の入力データと整合性を自動で確認する仕組みにしました。

カードの明細をCSVファイルでダウンロードする
CSVファイルをGoogleドライブへアップロードする
家計簿ファイルにカード明細シートを追加する
項目名が各カードの明細の何列目かを確認する
明細情報をインポートするスクリプトの作成
テスト
まとめ

カードの明細をCSVファイルでダウンロードする

まず、お使いのカードの明細をCSVファイルとしてダウンロードできるか確認します。

我が家では楽天カードとイオンカードを利用していますが、どちらもCSVファイルで毎月の明細をダウンロードすることができます。

各銀行のWebsiteからCSVファイルをダウンロードし、ファイル名が年と月が簡単に分かるものか確認します。

もし、分かりづらいものだった場合は年月がわかるようにファイル名を変更します。

このあと紹介する方法では、毎回同じ文字列が前後にある場合は自動で取り込みますが、たまにダウンロードした日付がファイル名になるものもあるため、その場合は手動で変更します。

CSVファイルをGoogleドライブへアップロードする

ダウンロードが完了したら、Googleドライブにそれぞれのカード会社の名前のフォルダを作成し、アップロードします。

ファイルを右クリックして、Googleスプレッドシートで開きます。

ここで、各カード会社のフォーマットを確認します。

確認するポイントは、項目名には何があるか、明細の項目名が何行目から始まっているのか、明細の最後に明細以外のデータが入っているか、我が家のように複数のカードを使っている場合は、それぞれの明細で何が違うのか、項目名の順番の差異があるかを確認します。

我が家が使用している楽天カードとイオンカードでは、下記のようになっています。

楽天カード：

利用日
利用店名・商品名
利用者
支払方法
利用金額
支払手数料
支払総額
○月支払金額
△月繰越残高
新規サイン

イオンカード

ご利用日
利用者区分
ご利用先
支払方法
空白
空白
ご利用金額
備考

この違いを整理したところで、次のステップに進みます。

家計簿ファイルにカード明細シートを追加する

まず、前回作成した家計簿スプレッドシートにカード明細シートを追加します。

新しいシートにカードの明細として最低限必要な情報を項目名として1列目に記載します。

我が家の場合は

カード会社名
CSVファイル名
日付
支払元
支払先
金額
備考

しています。日付から金額までの項目は、先程確認したとおり、使用しているカードどちらにも含まれる情報です。

項目名が各カードの明細の何列目かを確認する

リストアップした項目はカードの明細の項目の順番と一対一ではない可能性が高いため、各項目と列を整理します。

また、ファイル名のプリフィックスとサフィックス、必要なデータが始まる行をまとめます

楽天カードとイオンカードを持つ我が家の例では下記となります。

項目名	楽天カード	イオンカード
日付	1	1
支払元	3	2
支払先	2	3
支払い方法	4	4
金額	5	7
プリフィックス	enavi	meisai
サフィックス	(カード番号)	無し

ここまてで、スプレッドシートの準備ができました。

明細情報をインポートするスクリプトの作成

次にスクリプトを作成します

スプレッドシートのメニューからツール＞スクリプトエディタを開きます。

我が家のソースコードは下記になります。

/** 
 * カード情報
 */
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
};

/**
 * 月定義
 */
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で読み場所を変えていく
    }

    //イオンカード対策
    //もし価格欄が空欄なら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;
}

スクリプトにおいて、各々変更する必要がある値は「」で囲まれた部分です。

「フォルダID」は、Googleドライブにて、カードの明細が格納されたフォルダのURLの/folders/よりも後ろの文字列になります。

「サフィックス」は、先程まとめたサフィックスを入力します。

readArrについては、先程まとめたCSVの各項目列の値-1を代入しています。

それぞれの処理の概要はコメントを見てもらうしかないです。。。