クレカの利用明細メールをスプレッドシートに自動で書き起こしする

こんにちは、TRUSTDOCKのよもぎたです。

突然ですが、時代はキャッシュレス！です。
クレカや〇〇ペイなどで支払いをすると、その旨のメールが飛んでくるものが多いかと思います。そのメールをパースして、スプレッドシートに書き起こしするGoogle Apps Scriptを作成してみました。

別の言い方をすると、こちらの記事でGmailを扱うリファレンスを見たので、それをもとにした派生ネタです。

アプローチ

先程引用した記事と同じで、search()メソッドでメールを検索します。そこから今回はメール本文をパースして、スプレッドシートに書き起こししたい単位に抽出・分割して配列に格納してスプレッドシートにsetValues()メソッドでスプレッドシートに書き起こします。

今はいくつかカードを試して、三井住友カードのデビットカードに落ち着いています。サンプルではこのカードのメールをパースしていきます。

条件、制限

こちらについては先程引用した記事と同じです。

実践

メール例と出力例

カードを利用すると、次のようなメールが飛んできます。

よもぎた 様

いつもＯｌｉｖｅフレキシブルペイをご利用頂きありがとうございます。
お客様のカードご利用内容（デビットモード）をお知らせいたします。


◇利用日  ：yyyy/mm/dd HH:MM:SS
◇利用先　：shop.example.com
◇利用金額：n,nnn円
...以下省略...

これが来たら、こんなアウトプットをするようにしました。

ポイントはいくつかありまして

• メールでは1行で記載されている年月日と時刻を別のセルに入力したい
• メールでは利用日時は秒まで記載されているが、正直そこまでいらない、分まででいい
• 月の累計使用金額もF1セルで計算させたい
• 利用月別のシートにしたい。そのため、シート名をyyyy年mm月にしたい

といったところです。

コード例：メール抽出部分

Zennで行番号付きでコードブロックを記述することが出来ないようなので（少なくともこちらには記載がなかった）ので、コードブロック中にコメントで説明を記述するスタイルで行きたいと思います。

  //メールの検索条件を指定して、ヒットした結果を threads に格納
  const threads = GmailApp.search('from:[e-mail] subject:ご利用のお知らせ【三井住友カード】 is:unread');

  //利用日などを抽出するための条件の正規表現を定義
  const regex_payment_date_line = /◇利用日/g;
  const regex_payment_store_line = /◇利用先/g;
  const regex_payment_amount_line = /◇利用金額/g;
  const regex_payment_amount_data = /[0-9,]*/g;

  //支払いデータを格納する配列を宣言
  let payment_data = [];

  //ヒットした各スレッドについてループを回す
  threads.forEach((thread) =>
    {
      //スレッドからメッセージを取得
      let messages = thread.getMessages();
      //各メッセージについてループを回す
      messages.forEach((message) =>
        {
          //メッセージが未読だった場合にのみ、if文の中の処理を実行
          if(message.isUnread()){
            //メッセージ本文を取得し、1行ごとの配列に分割
            let body = message.getBody();
            let lines = body.split('\n'); 
            //支払日、支払時刻、支払先、支払金額を代入する変数を宣言
            let payment_date, payment_time, payment_store, payment_amount;
            //メッセージ本文1行ごとにループを回す
            lines.forEach((line) =>
              {
                //支払い日時の行の場合の処理
                if(line.match(regex_payment_date_line)){
                  //支払い日時を取り出し、日付と時刻に分割
                  //さらに時刻については先頭5文字だけを取得
                  let payment_date_time = line.split('：')[1];
                  payment_date = payment_date_time.split(' ')[0];
                  payment_time = payment_date_time.split(' ')[1].substring(0,5);
                }
                //支払先の行の場合の処理。支払先名を変数に代入。
                if(line.match(regex_payment_store_line)){
                  payment_store = line.split('：')[1];
                }
                //支払い金額の行の場合の処理
                if(line.match(regex_payment_amount_line)){ 
                  //：で二分割した後半を取得、さらに数値だけ取り出す
                  payment_amount = line.split('：')[1].match(regex_payment_amount_data)[0];
                }
              }
            )
            //取り出した利用日、時刻、支払先、支払金額を配列に格納
            payment_data.push([payment_date,payment_time,payment_store,payment_amount]);
          }
          //メッセージを既読に変更
          message.markRead();
        }
      )
    }
  )

コード例：スプレッドシートへの書き起こし部分

  //支払いデータがある場合のみ実行
  if(payment_data.length > 0){
    //書き起こし先のスプレッドシートを開く
    const spreadsheet_file = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/...');

    //支払いデータ1件ごとにループを回す
    payment_data.forEach((data) =>
      {
        //支払いデータから年と月を取り出し、シート名を構築
        let year = data[0].split('/')[0];
        let month = data[0].split('/')[1];
        let sheet_name = year + '年' + month + '月'; 
        
        //シートを代入する変数を宣言
        let sheet;

        //書き起こし先のシートが既に存在するときはsheet変数に代入
        if(spreadsheet_file.getSheetByName(sheet_name) != null){
          sheet = spreadsheet_file.getSheetByName(sheet_name);
        }else{
          //書き起こし先のシートが存在しない場合、新規に作成
          spreadsheet_file.insertSheet(sheet_name);
          sheet = spreadsheet_file.getSheetByName(sheet_name);
          sheet.getRange('A1:E1').setValues([['日付','時刻','利用先','利用金額','累計使用金額']]);
        }

        //支払いデータを挿入する行番号を取得
        let insert_row = sheet.getLastRow() + 1;
        //支払いデータを挿入
        sheet.getRange('A' + insert_row + ':D' + insert_row).setValues([data]);

        //累計支払額の計算式を更新
        sheet.getRange('F1').setFormula('=sum(D2:D' + insert_row + ')');
      }
    )

    //スプレッドシートをflush()
    SpreadsheetApp.flush();
  }

正直、F1セルの数式は=sum(D2:D10000)でもOKだとは思いますが、僕はそういうのは何となく気持ち悪く感じる人なので、上記のような処理になっています。

トリガー設定

こちらをご覧ください。まったく同じことをしています。

高速化

payment_dataが複数件あった場合、その長さを取得して適宜挿入先のRangeを取得してsetValues()する方が処理は速いはずです。setValues()メソッドの処理時間が挿入するセルの数にあまり影響されないようだからです。ぶっちゃけ、1セルにデータを挿入する処理を100回やるのと、100個のデータを1回で挿入するの、結果は同じ見た目になるけど、処理時間は後者の方がずっと速いです。
しかし、複数件あった場合には、月をまたいだ利用があった場合に挿入先のシートをどうするのとか、考えることが増えます。
メールから抽出したpayment_dataを一旦JSONオブジェクトにして、そこからまた月別の挿入データを構築して…なんてことも考えたりはしました。

でも、15分おきの実行で複数件の支払いがある方が稀でしょう。それがさらに月をまたぐことになる可能性なんて、僕の生活パターンからいったらまず考えられません。

そこで、上記のような処理に落ち着いています。

さいごに

素直にMoney Forwardさんとか使っておけばいいんじゃない？というご意見は、まさにおっしゃる通りです。何なら使ってるし、キャッシュレス優先にして課金してもいいな、と思ってます。

それでも、やれそうだったらやってみる、というのが僕のコンセプトと言いましょうか、何と言いましょうか、まぁそういうヤツなんです。

きっと、登山家の方が山に登るのと同じです。しらんけど。

最後までお読みいただきありがとうございました。