クレジットカード利用明細を自動で作るGoogle Apps Script

みなさんはクレジットカードを利用する際、
「利用お知らせメール」機能は利用されていますか？

カード会社によっては決済するとリアルタイムに
利用金額をメールでお知らせしてくれるサービスがあるので安心・便利ですよね！

本記事では、そのメールを利用して
スプレッドシートに月々の利用明細を自動 & リアルタイムに作ってくれる Google Apps Script を紹介します。

明細シートの完成イメージはこんな感じ。

まずはコードから

function getPayments() {
  // メール検索クエリを作成
  const SUBJECT = 'ご利用のお知らせ【〇〇カード】'; // 利用お知らせメールの件名
  const ADDRESS = '[e-mail]'; // お知らせメールの送信元
  const LABEL_NAME = 'credit'; // ラベル名
  const QUERY = 'subject:' + SUBJECT + ' from:' + ADDRESS + ' -label:' + LABEL_NAME;

  // メールを検索
  threads = GmailApp.search(QUERY);

  // 該当メールがあった場合
  if(threads.length > 0) {
    const KEYWDS  = ['利用日：', '利用金額：'];
    const LABEL = GmailApp.getUserLabelByName(LABEL_NAME);

    msgs = GmailApp.getMessagesForThreads(threads);
    sheet = SpreadsheetApp.getActiveSheet();

    for(i=0; i<msgs.length; i++){
      // 本文を取得
      plainBody = msgs[i][0].getPlainBody();
      sheet.appendRow([
        plainBody.match(/利用日：.*/)[0].replace(KEYWDS[0], ''),
        plainBody.match(/利用金額：[\d,]+/)[0].replace(KEYWDS[1], ''),
      ]);
     // 処理の完了後、ラベルを付与
      threads[i].addLabel(LABEL);
    };

    // 料金列に通貨表示を適用
    amounts = sheet.getRange('B:B');
    setCurrencyLayout(amounts);
  }
}

function monthlyFormat() {
  const ZONE = 'Asia/Tokyo';
  const FORMAT = 'yyyy/MM';
  sheet = SpreadsheetApp.getActiveSheet();
  // 現在の月を取得
  date = new Date();
  month = Utilities.formatDate(date, ZONE, FORMAT);

  // 現在明細で利用中の列を取得
  activeCell = sheet.getRange(1,1);
  activeMonth = null;
  if(activeCell.isBlank()===false){
    activeMonth = activeCell.getValue();
    activeMonth = Utilities.formatDate(activeMonth, ZONE, FORMAT);
  }
  // 現在月と異なる（空または前月）の場合は当月分の列を挿入
  if(activeMonth === null || month != activeMonth) {
    sheet.insertColumnsBefore(1,2);
    activeCell.setValue(month);
    sheet.getRange(1,1,1,2).merge();

    const SUM = '=SUM(B:B)';
    sumCell = sheet.getRange(2,1);
    sumCell.setValue(SUM);
    setCurrencyLayout(sumCell);

    activeCell.offset(2,0).setValue('date');
    activeCell.offset(2,1).setValue('amount');
  }
  // ヘッダのレイアウト調整
  headers = [sheet.getRange('1:1'),sheet.getRange('3:3')];
  for(i=0; i<headers.length; i++){
    headers[i].setHorizontalAlignment('center').setFontWeight(('bold'));
  }

// 列ごとのレイアウト調整
  amounts = sheet.getRange('B:B');
  setCurrencyLayout(amounts);
  amounts.setBorder(null, null, null, true, null, false, 'black', SpreadsheetApp.BorderStyle.DASHED);
  sheet.setColumnWidth(1,120);
  sheet.setColumnWidth(2,70);
}

// 選択セルを通貨表示にフォーマット
function setCurrencyLayout(range) {
  range.setNumberFormat('[$¥-411]#,##0');
}

コード解説

大きくは 明細情報を取得してくるgetPaymentメソッドと、
当月分の明細を載せる列を作る montlyFormat メソッドの２つに分かれます。

明細情報を取得 (getPayment)

上記の例では、「件名」「送信元アドレス」を元に利用お知らせメールを検索しています。
この時、「取得処理済みか未処理か」を判定するラベルをあらかじめ作っておき、
そのラベルが付与されていないこと -label: をもう１つの検索条件にしています。

  // メール検索クエリを作成
  const SUBJECT = 'ご利用のお知らせ【〇〇カード】'; // 利用お知らせメールの件名
  const ADDRESS = '[e-mail]'; // お知らせメールの送信元
  const LABEL_NAME = 'credit'; // ラベル名
  const QUERY = 'subject:' + SUBJECT + ' from:' + ADDRESS + ' -label:' + LABEL_NAME;

  // メールを検索
  threads = GmailApp.search(QUERY);

その QUERY に該当するメールが１件でもあれば取得処理に入ります。
ここでは getPlainBody() した本文を match()で正規表現にて取得します。

お知らせメールのフォーマットに合わせる必要がありますが、ここでは以下をそれぞれ取得。
1. 「利用日：」から改行まで
2. 「利用金額：」と、隣接する半角数字と「,」

そして「利用日：」と「利用金額：」は邪魔になるので replace() で消してしまいます。

（ここの抽出処理は記事最後の「参考サイト」がベースです。）

      // 本文を取得
      plainBody = msgs[i][0].getPlainBody();
      sheet.appendRow([
        plainBody.match(/利用日：.*/)[0].replace(KEYWDS[0], ''),
        plainBody.match(/利用金額：[\d,]+/)[0].replace(KEYWDS[1], ''),
      ]);

完了したら addLabel() して、このメールが再度処理されないようにラベルを付与します。

      // 処理の完了後、ラベルを付与
      threads[i].addLabel(LABEL);

最後に料金列を ¥1,000 のような通貨表示にするよう、
別に作った setCurrencyLayout メソッドを呼び出して処理は終了です。

当月分の列を挿入 (montlyFormat)

こちらは月ごとの明細を転記していく列を挿入することも自動化します。
まずは現在の年月を取得します。

  const ZONE = 'Asia/Tokyo';
  const FORMAT = 'yyyy/MM';
  sheet = SpreadsheetApp.getActiveSheet();
  date = new Date();
  month = Utilities.formatDate(date, ZONE, FORMAT);

次に挿入対象のA1セルの値がもし空でなければ、
A1セルの値も年月形式で取得します。

  activeCell = sheet.getRange(1,1);
  activeMonth = null;
  if(activeCell.isBlank()===false){
    activeMonth = activeCell.getValue();
    activeMonth = Utilities.formatDate(activeMonth, ZONE, FORMAT);
  }

この時、A1が空または現在年月と異なる（前月の想定）場合に列挿入していきます。
A列の前に２列を挿入(insertColumnsJBefore(1,2);)し、
明細のヘッダに必要な「合計」セル・日付項目・料金項目を setValue()します。

    sheet.insertColumnsBefore(1,2);
    activeCell.setValue(month);
    sheet.getRange(1,1,1,2).merge();

    const SUM = '=SUM(B:B)';
    sumCell = sheet.getRange(2,1);
    sumCell.setValue(SUM);
    setCurrencyLayout(sumCell);

    activeCell.offset(2,0).setValue('date');
    activeCell.offset(2,1).setValue('amount');

最後に、A1セルの値に関わらず
ヘッダや各列のレイアウトを調整します。
ここではヘッダ行(1・3行目)は太字中央揃えに、
料金列は通貨表示にして前月との境界に罫線を引き、
列幅も見やすい幅に調整しています。

  // ヘッダのレイアウト調整
  headers = [sheet.getRange('1:1'),sheet.getRange('3:3')];
  for(i=0; i<headers.length; i++){
    headers[i].setHorizontalAlignment('center').setFontWeight(('bold'));
  }

  // 列ごとのレイアウト調整
  amounts = sheet.getRange('B:B');
  setCurrencyLayout(amounts);
  amounts.setBorder(null, null, null, true, null, false, 'black', SpreadsheetApp.BorderStyle.DASHED);
  sheet.setColumnWidth(1,120);
  sheet.setColumnWidth(2,70);

完成したら、最初の1回は monthlyFormat を手動実行しておいて、
このあとのトリガーで実行される getPayment の貼り付け先となる列を作っておくと良いでしょう。

トリガーを設定

あとは上記2つのセルを定期実行するよう トリガー を設定します。
プロジェクト編集画面の左側「トリガー」を選択し、トリガーを追加します。

まずは montlyFormatを、
時間主導型 で 月ベースのタイマーを選び、毎月 ++1日++ を設定します。

続く getPayment はメールチェックのトリガーなので、お好みの頻度でいいでしょう。
最短で「1分おき」も設定可能ですが、
私は 1時間おき を設定しました。

これで準備は完了！
あとはお買い物をして利用お知らせメールが来るのを待つのみ。
メール受信時は付いていない credit ラベルが後ほど付いていれば、
自動取り込みは完了しているはずです。

単純なスクリプトですが、
わざわざクレジットカード会社のHPにログインして明細ページを開いて確認するよりも
はるかにお手軽かつ利用からタイムラグなく確認できる、
ちょっとしたライフハックでした。

---

参考サイト

GASでGmail本文の一部を抽出してスプレッドシートに書き出す方法