概要

タイトルまんまです。

某クレジットカードを使っていますが、カード利用後すぐにメールで通知してくれるサービスがあります。
参考：https://www.jcb.co.jp/security/my-anshin/usage-notice/

これを利用してスプレッドシートにカードの利用履歴を残そうという計画です。

背景

元々私はマネーフォワードというアプリで家計簿をつけていました。
マネーフォワードではクレジットカードを登録しておけば、カード会社から利用履歴を自動で取得し家計簿として記録してくれます。

しかし、そもそもクレジットカードの利用履歴がカード会社のサービスに登録されるのにラグがあります。

そのため月半ばに家計簿をチェックして予算に余裕があると思っていても、クレジットカード利用履歴が登録されていなかっただけで予算オーバーしていたという経験が何度かありました。

特に食費については、予算を見てお金を使うか使わないかの判断をしていたので、「今月以外と余裕あるから食べ放題行っちゃおう！」と調子に乗って赤字という月もありました。

その結果、リアルタイムでの予算残高を計算したいと思うように。

そこで、利用時にリアルタイムでメール通知を送信してくれる（1時間以内に大体メールが来ます）サービスを利用し、スプレッドシートで直近の利用状況を確認できるようにすることに。

実装とか

集計したスプレッドシートはこちら

月毎に集計する想定で、タブを月毎に分けています。

ソースコードはこちら

個人的な内容も含まれていたので実際の実装とは一部変えています。

GAS

// 検索条件指定
const query = 'from:〇〇カード subject:カード利用の通知';

// 30分前～現在の新着メールを取得
var get_interval = 30; 

function main() {
  var writeItems = [];

  // ご利用のお知らせメールをリストで取得
  newUseMails = fetchMails(query)
 
  // 書き込む内容を整形
  if(newUseMails.length > 0){
    for(var i = newUseMails.length-1; i >= 0; i--){
      var item = trimUseMail(newUseMails[i]);
      writeItems.push(item)
    }
  }
  
  // 日付順でソート
  writeItems.sort(compareDates)

  // 書き込むシートを取得
  tergetSheet = fetchSheet()

  // 書き込む
  writeToSheet(tergetSheet, writeItems)
}

function createTimeQuery(){
  // 取得間隔
  var now_time= Math.floor(new Date().getTime() / 1000) ;// 現在時刻を変換
  var time_term = now_time - (60 * get_interval); // 変換

  var query = "after:" + time_term;

  return query
}

function fetchMails(query) {
  // メール取得
  var query = "(" + createTimeQuery() + " " + query + ")"

  var threads = GmailApp.search(query);
  var msgs = GmailApp.getMessagesForThreads(threads);

  var mailBodys = [];
  var mailIndex = 0
  for (var i = 0 ; i < msgs.length; i++) {
    for (var j = 0; j < msgs[i].length; j++) {
      mailBodys[mailIndex] = msgs[i][j].getPlainBody()
      mailIndex++;
    }
  }
  
  return mailBodys;
}

function trimUseMail(plainTextBody) {
  console.log('plainTextBody : ' + plainTextBody );

  var dateMatch = plainTextBody.match(/利用日(.*)/);
  var date = dateMatch[1].match(/\d{4}\/\d{1,2}\/\d{1,2}/);
  console.log('利用日 : ' + date );

  var placeMatch = plainTextBody.match(/利用先(.*)/);
  var place = placeMatch[1].substring(1);
  console.log('利用先(名目) : ' + place );

  var amountMatch = plainTextBody.match(/利用金額(.*)/);
  var amount = amountMatch[1].substring(1).replace(',', '').replace('円', '');
  console.log('利用金額 : ' + amount );

  return new UsageHistory(date, amount, classification, null, place, null);
}

function fetchSheet() {
  var targetSpreadsheet = SpreadsheetApp.openById('スプレッドシートのID');

  // スプレットシート中のシート一覧を取得
  var sheetNames = [];
  targetSpreadsheet.getSheets().forEach(
    function (sheet) {
      sheetNames.push(sheet.getSheetName());
      console.log('シート : ' + sheet.getSheetName() );
    }
  );

  // 本日の日付を取得
  var now = new Date();
  var thisMonth = Utilities.formatDate(now, "JST", "yyyyMM");

  // 該当月シートが無かったらテンプレートからシートを生成
  if (!sheetNames.includes(thisMonth)) {
    // テンプレートをコピーして作成
    var templateSheet = targetSpreadsheet.getSheetByName("テンプレート");
    templateSheet.copyTo(targetSpreadsheet);

    // 名前を変更
    var newSheet = targetSpreadsheet.getSheetByName("テンプレート のコピー");
    newSheet.setName(thisMonth)
  }

  // 書き込むシートを取得
  var sheet = targetSpreadsheet.getSheetByName(thisMonth);

  return sheet
}

function writeToSheet(tergetSheet, writeItems){
  // 追加したいセル範囲内の最終行を取得
  var lastRow 
  for(let i = 3; ; i++){
    var cellValue = tergetSheet.getRange('B' + i).getValue();

    // 書き込み対象のセルのうち、最下行を取得
    if (cellValue === "") {
      lastRow = i
      break
    }

  }

  // Noを入力するための変数
  var inputNum = lastRow - 2

  // 追加するデータをセル範囲に書き込む
  writeItems.forEach(function(item) {
    tergetSheet.getRange('B' + lastRow).setValue(inputNum);
    tergetSheet.getRange('C' + lastRow).setValue(item.date);
    tergetSheet.getRange('D' + lastRow).setValue(item.amount);
    tergetSheet.getRange('E' + lastRow).setValue(item.classification);
    tergetSheet.getRange('F' + lastRow).setValue(item.paymentMethod);
    tergetSheet.getRange('G' + lastRow).setValue(item.useTarget);
    tergetSheet.getRange('H' + lastRow).setValue(item.note);

    lastRow++
    inputNum++
  });
}

class UsageHistory {
  constructor(date, amount, classification, paymentMethod, useTarget, note) {
    this.date = date; // 日付
    this.amount = amount; // 金額
    this.classification = classification; // 項目
    this.paymentMethod = paymentMethod; // 支払い方法
    this.useTarget = useTarget; // 名目(利用先)
    this.note = note; // 備考
  }
}

function compareDates(a, b) {
    const dateA = new Date(a.date);
    const dateB = new Date(b.date);
    return dateA - dateB;
}

補足とか

かいつまんでの解説です。

検索クエリ

// 検索条件指定
const query = 'from:〇〇カード subject:カード利用の通知';

// 30分前～現在の新着メールを取得
var get_interval = 30;

検索クエリ

function createTimeQuery(){
  // 取得間隔
  var now_time= Math.floor(new Date().getTime() / 1000) ;// 現在時刻を変換
  var time_term = now_time - (60 * get_interval); // 変換

  var query = "after:" + time_term;

  return query
}

上記の部分ではGmailの検索クエリを作成。

queryは取得したいメールアドレスの検索条件を指定しています。
https://blog.synnex.co.jp/google/gmail-gas/

get_intervalはスクリプトの実行トリガーに合わせて設定します。

登録情報の整形

function trimUseMail(plainTextBody) {
  console.log('plainTextBody : ' + plainTextBody );

  var dateMatch = plainTextBody.match(/利用日(.*)/);
  var date = dateMatch[1].match(/\d{4}\/\d{1,2}\/\d{1,2}/);
  console.log('利用日 : ' + date );

  var placeMatch = plainTextBody.match(/利用先(.*)/);
  var place = placeMatch[1].substring(1);
  console.log('利用先(名目) : ' + place );

  var amountMatch = plainTextBody.match(/利用金額(.*)/);
  var amount = amountMatch[1].substring(1).replace(',', '').replace('円', '');
  console.log('利用金額 : ' + amount );

  return new UsageHistory(date, amount, classification, null, place, null);

}

こちらでは取得したメールから登録情報を抽出しています。
通知メールはHTML形式で送られてくるのでタグで取得しようとしていましたが、難航したので平文で取得後に無理矢理抽出する形を取りました。

登録クラス

class UsageHistory {
  constructor(date, amount, classification, paymentMethod, useTarget, note) {
    this.date = date; // 日付
    this.amount = amount; // 金額
    this.classification = classification; // 項目
    this.paymentMethod = paymentMethod; // 支払い方法
    this.useTarget = useTarget; // 名目(利用先)
    this.note = note; // 備考
  }
}

普段Javaでプログラムを書いているので、登録情報もクラスで扱いたいなと思ってこのような形に。いい形なのかはわからん...。
スプレッドシートのカラムとフィールドが対応しています。

シート取得

function fetchSheet() {

  var targetSpreadsheet = SpreadsheetApp.openById(スプレッドシートのID);

  // スプレットシート中のシート一覧を取得
  var sheetNames = [];
  targetSpreadsheet.getSheets().forEach(
    function (sheet) {
      sheetNames.push(sheet.getSheetName());
      console.log('シート : ' + sheet.getSheetName() );
    }
  );

  // 本日の日付を取得
  var now = new Date();
  var thisMonth = Utilities.formatDate(now, "JST", "yyyyMM");

  // 該当月シートが無かったらテンプレートからシートを生成
  if (!sheetNames.includes(thisMonth)) {
    // テンプレートをコピーして作成
    var templateSheet = targetSpreadsheet.getSheetByName("テンプレート");
    templateSheet.copyTo(targetSpreadsheet);

    // 名前を変更
    var newSheet = targetSpreadsheet.getSheetByName("テンプレート のコピー");
    newSheet.setName(thisMonth)
  }

  // 書き込むシートを取得
  var sheet = targetSpreadsheet.getSheetByName(thisMonth);

  return sheet

}

このメソッドでは書き込み先のスプレッドシート返却しています。
対象のスプレッドシートがない場合（月初めで該当月のシートがない場合など）はテンプレートからコピーして作成します。

書き込み

function writeToSheet(tergetSheet, writeItems){

  // 追加したいセル範囲内の最終行を取得
  var lastRow 
  for(let i = 3; ; i++){
    var cellValue = tergetSheet.getRange('B' + i).getValue();

    // 書き込み対象のセルのうち、最下行を取得
    if (cellValue === "") {
      lastRow = i
      break
    }

  }

  // Noを入力するための変数
  var inputNum = lastRow - 2

  // 追加するデータをセル範囲に書き込む
  writeItems.forEach(function(item) {
    tergetSheet.getRange('B' + lastRow).setValue(inputNum);
    tergetSheet.getRange('C' + lastRow).setValue(item.date);
    tergetSheet.getRange('D' + lastRow).setValue(item.amount);
    tergetSheet.getRange('E' + lastRow).setValue(item.classification);
    tergetSheet.getRange('F' + lastRow).setValue(item.paymentMethod);
    tergetSheet.getRange('G' + lastRow).setValue(item.useTarget);
    tergetSheet.getRange('H' + lastRow).setValue(item.note);

    lastRow++
    inputNum++
  });

}

最終行の取得にはgetLastRowメソッドを使用しようと思いましたが、シート全体の最下行となるので一行一行チェックする形に変更。
書き込みについて、ヘッダー行から取得したカラム名とUsageHistoryクラスのフィールドを紐づけて列数を取得したかったのですが、うまくいかず直接指定する形となりました。

感想とか

お仕事では長年Javaをやっているので、動的型付け言語に慣れず大変でした。
特にSheetとSpreadsheetが紛らわしい。名前変えて欲しい。

今回作成したスクリプトとか直接関係ないですが、知識のアウトプットに偏っているので今後もQiita記事を投稿したいです。