楽天カードの【速報版】をGASを使って取得した

Dellのパソコンを購入した話は前回書きました。
ところで、少し値の張る商品をクレジットカードで購入するときに、今月はいくらくらい使ったか気になることがあります。

オンラインの利用明細に表示されているのは、確定分だけ。
昨日使ったクレジット代金が利用明細に反映されるのは、少し先になります。

楽天カードでは、カードを使った日の翌日、または翌々日に「速報版」がメールで送られてきます。

この速報版を集計すれば、今月の利用金額がわかるはずです。
Google Apps Script（GAS）で自動化したので、その紹介です。

Google Apps Script(GAS)

GASは、Googleの各種サービスと連携するプログラムのことです。
Microsoftでいえば、エクセルのマクロのようなものです。
私は業務でマクロを書くことが多かったのですが、GASはあまり馴染みがありません。
仕事ではGoogleとの連携業務はありませんでしたので。

とはいえ、Gmailのデータを取得するにはGASが便利なはず。
今回は、慣れないプログラミングを試してみました。

今回参考にした本はこちら。
「詳解! Google Apps Script完全入門 [第3版]」　https://amzn.to/4fgvL2f

GASの詳しい内容については、長くなるので今回は省略します。

Gmailの内容を確認する

まず、すべてのプログラムに共通ですが、データを取り込む前に速報版のメール内容を詳細に確認します。
よく見ないで始めてしまうと、プログラムの流れが固まりません。
もちろんプログラムは作りながらのトライアンドエラーはつきもの。
都度修正することは避けられません。

まず、楽天カードで買い物をした翌日くらいに昨日のカード支払金額がメールで届きます。

タイトルは「【速報版】カード利用のお知らせ(本人ご利用分)」
本文は、次のようになっています。

以上の内容から、プログラムでは

タイトルが「【速報版】カード利用のお知らせ(本人ご利用分)」
となっているメールを選び出しし、そこから

• ご利用日
• ご利用金額

を抽出してスプレッドシートに渡せばいいことが分かります。

タイトルから抽出

まず、数あるメールの中から楽天カード利用のお知らせ（速報版）だけを選びます。

スクリプトの書き方は、こんな感じで、
　差出人が「楽天カード株式会社」
　タイトルが「【速報版】カード利用のお知らせ」
となっているメールの０番目から100番目までのメールを取得します。

私は欲張って100までとしましたが、１か月分なのでもっと少なくても良いかと思います。

function rakuten_card_sokuho() {
  const query='from:楽天カード株式会社 in:inbox "【速報版】カード利用のお知らせ"';
  const start=0;
  const max=100;

  const threads = GmailApp.search(query, start, max);
  const messagesForThreads = GmailApp.getMessagesForThreads(threads);

  const sheet = SpreadsheetApp.getActiveSheet();

取得したメールデータをスプレッドシートに書き出す前に、前回プログラムを実行した際のスプレッドシートをクリアにします。

  const lastRow = sheet.getLastRow();
  sheet.getRange(3,1,lastRow,8).clear();
  sheet.getRange(2,1,2,3).clear();

この意味は分かりにくいのですが、完成形を先に見た方が早いかと思います。
スプレッドシートの一部には、文字数をカウントする関数をあらかじめ入力してあります。
すべてクリアするとそこも消えてしまうので、関数が入力されている２行目のD列からH列は残すようにしています。

入力してある関数については後ほど説明します。

次にメール本文のデータを取得して格納します。
まず、後でスプレッドシートに書き込むデータのために、valuesという配列を用意します。
messagesForThreadsというメッセージの配列をループ処理します。

ループ内では、messagesの要素を取得します。
そのメッセージから日付と本文を取得し、それをrecordという配列に格納します。
そのrecordをvalues配列に追加します。

    const values = [];
    for(const messages of messagesForThreads){
    const message = messages[0];
    const record = [
      message.getDate(),
      message.getPlainBody()
    ];
    values.push(record);

valuesにデータがある場合、スプレッドシートのアクティブシートのセル範囲を指定して、その範囲にvaluesのデータを書き込みます。
getRange(2, 1, values.length, values[0].length)は、シートの2行目、1列目から始まり、valuesの行数と列数に合わせて範囲を指定します。

最後の行は、最終行の取得です。

  if(values.length > 0){
    SpreadsheetApp.getActiveSheet().getRange(2, 1, values.length, values[0].length).setValues(values);
  }

  var rows = sheet.getLastRow();

この時点でA列には日付、B列にはメッセージの内容が格納されています。
B列２行目には、先ほど見たメールの本文がテキスト形式で入っていることがわかります。

１日に２回以上取引があった場合の処理

上記のメールから、利用日と利用金額部分を抽出すれば良さそうです。
ただしときには、１日に２回以上買い物をすることもあります。

この日の取引は４件です。
B列の格納データを見るとこのようになっています。

このメールから「利用日」の数をカウントすることにします。

  for(let i = 2; i <= rows; i++) {
    strformula = "=(LEN(B" +i + ")-LEN(SUBSTITUTE(B" +i +","+ "\"■利用日:\",\"\")))/LEN(\"■利用日:\")";
    sheet.getRange(i, 3).setValue(strformula) ;
  var ken = sheet.getRange(i,3).getValue()

C列には、その日の取引件数が入力されることになります。

そして、C列の取引件数をチェックし、同じ利用日に取引が２件以上ある場合には、データを下にコピーして挿入します。

    if (ken > 1) {
      sheet.insertRowsAfter(i, ken-1);
        var crange = sheet.getRange(i, 1, 1,3);
            crange.copyTo(sheet.getRange(i+1, 1,ken-1,3));
            i = i+ken-1
      var rows = rows+ken-1
    }

D列からH列までの関数

先ほど、２行目のD列からH列までは関数を入れてあるので、最初にクリアはしないと書きました。
具体的には、次の関数を入れています。

・D列　本文中の「■利用日」が何文字目に入っているかを示しています。
　（関数にあるG1セルには「■利用日」が入力されています）

・E列
　E列には、本文中の「■利用金額」が何文字目に入っているかを示しています。

・F列
　F列も同様に、本文中の「円」の文字位置です。

・G列・H列
G列は、D2に５文字を加えたところから11文字を抽出しています。
H列は、「■利用金額」から「円」の前までの部分を抽出しています。

最後にD2からH２までの計算式を最終行までコピーです。

  var crange = sheet.getRange(2, 4, 1,8);
  crange.copyTo(sheet.getRange(3, 4,rows-2,8));

本日のまとめ

一連のスクリプトを書くと、このようになります。

function rakuten_card_sokuho() {
  const query='from:楽天カード株式会社 in:inbox "【速報版】カード利用のお知らせ"';
  const start=0;
  const max=100;

  const threads = GmailApp.search(query, start, max);
  const messagesForThreads = GmailApp.getMessagesForThreads(threads);

  const sheet = SpreadsheetApp.getActiveSheet();

  const lastRow = sheet.getLastRow();
  sheet.getRange(3,1,lastRow,8).clear();
  sheet.getRange(2,1,2,3).clear();


    const values = [];
    for(const messages of messagesForThreads){
    const message = messages[0];
    const record = [
      message.getDate(),
      message.getPlainBody()
    ];
    values.push(record);
  }
 
  if(values.length > 0){
    SpreadsheetApp.getActiveSheet().getRange(2, 1, values.length, values[0].length).setValues(values);
  }

  var rows = sheet.getLastRow();

  for(let i = 2; i <= rows; i++) {
    strformula = "=(LEN(B" +i + ")-LEN(SUBSTITUTE(B" +i +","+ "\"■利用日:\",\"\")))/LEN(\"■利用日:\")";
    sheet.getRange(i, 3).setValue(strformula) ;
  var ken = sheet.getRange(i,3).getValue()
    if (ken > 1) {
      sheet.insertRowsAfter(i, ken-1);
        var crange = sheet.getRange(i, 1, 1,3);
            crange.copyTo(sheet.getRange(i+1, 1,ken-1,3));
            i = i+ken-1
      var rows = rows+ken-1
    }
  } 
  var crange = sheet.getRange(2, 4, 1,8);
  crange.copyTo(sheet.getRange(3, 4,rows-2,8));

}

もっと簡潔に書けるのだろうと思いますが、自分で使う分には問題ありません。
プログラム修正などはChat GPTなどが得意なので、尋ねてしまってもいいですし。

一度型を作ってしまえば、後は応用です。

これを工夫すれば、スターバックスでのモバイルオーダーの注文履歴など抽出することもできるはずです。