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

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

今回はGoogleスプレッドシートで作成した家計簿に銀行の取引明細を取り込む方法をご紹介します。

我が家では、毎月末に銀行口座の残高を確認し、立て替えた支払いの精算を実施しています。
第3回でご紹介したカードの明細と合わせて月末に実施することで、家計チェックを行います。

今までご紹介した家計簿への入力も、第3回のカード明細情報とほとんど同じ方法で取り込むことができちゃいます。

銀行口座の明細をCSVファイルでダウンロードする

まず、家計を管理する口座がネットバンク対応かを確認します（最近はほとんどの銀行が対応しているかと思いますが）

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

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

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

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

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

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

ここで、各銀行のフォーマットを確認します。

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

我が家が使用している三菱UFJ銀行とイオン銀行では、下記のようになっています。

三菱UFJ銀行：

• 日付
• 摘要
• 摘要内容
• 支払い金額
• 預かり金額
• 差引残高

イオン銀行

• 日付
• お取引内容
• お引出し
• お預入れ
• 残高

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

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

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

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

我が家の場合は

• 種類（口座の種類）
• CSVファイル名
• 日付
• 取引内容
• 引き出し
• 預け入れ
• 残高

日付から残高までの項目は、先程確認したとおり、使用している銀行のどちらにも含まれる情報です。

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

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

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

三菱UFJ銀行とイオン銀行の口座を持つ我が家の例では下記となります。

項目名	三菱UFJ銀行	イオン銀行
日付	1	1
取引内容	3	2
引き出し	4	3
預け入れ	5	4
残高	6	5
プリフィックス	無し	無し
サフィックス	無し	無し

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

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

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

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

前回作成した銀行口座用のソースコードに、銀行口座分を追加すればOKです。我が家は下記になります。

追加分は

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

    //イオンカード対策
    //もし価格欄が空欄なら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回の銀行口座の情報と同じフォーマットでitemを追加しています。

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

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

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

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

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

テスト

Googleドライブに、各銀行からダウンロードしたCSVファイルを格納し、スクリプトの実行をします。

Bankシートに明細のデータが入ってくれば成功です。

ちなみに我が家では同じシートにまとめて取り込み、フィルタリングにより各口座の明細を確認していますが、もし口座ごとに異なるシートにしたい場合は、各口座ごとのシートを作成し、ソースコードのpropertyをシート名に変更すれば対応できると思います。

まとめ

今回は、Googleスプレッドシートで作成した我が家の家計簿に銀行口座の明細を取り込む方法をご紹介しました。

第3回のカードの明細とほとんど同じフォーマットで取り込むことができました。

我が家では三菱UFJ銀行とイオン銀行を使用していますので、もし同じ組み合わせの方がいましたら、そのままお使いいただけると思います。

次の記事では、家計簿ファイルに取り込んだ口座の情報を家計簿の情報と連携させる方法についてご紹介いたします。