🎃

利用通知メールが来るクレカをスプレッドシートで自動記録する

2024/08/01に公開

GAS

Google Sheets

tech

目的

クレカの利用情報をスプレッドシートで管理して、明細を直接確認できるようにしたい

前提

利用金額が即時メールで送信されるクレカを使用.
メールが届いたらその情報を使ってスプレッドシートに記録する.
GAS と Google Spread Sheet を使う.

イメージ

支払い

↓
メールが届く

↓
スプレッドシートに自動記録

実装

構造

GoogleDrive/
    └ MoneyPortal/
        ├ MoneyPortal # Google Spread Sheet
        └ CheckMail # GAS

Google Spread Sheet に必要なシートを作成

message というシートを作成し、A1 から H1 に次の値を入力する.

セル番地	値	目的
A1	`message_id`	Gmail のメッセージID
B1	`date`	カードの使用日時
C1	`value`	カードの使用金額
D1	`shop`	カードの使用店名
E1	`card_name`	カード名
F1	`date_timestamp`	カードの使用日時 (`date`) のタイムスタンプ
G1	`updated`	記録時のタイムスタンプ
H1	`is_notified`	record シートへの書き込みフラグ

GAS セットアップ

スクリプトプロパティを設定

新しい GAS のファイルを作成し、以下のスクリプトプロパティを作成.
キー: SPREAD_SHEET_ID
値: 先ほど作ったスプレッドシートの ID

ライブラリをインポート

以下のライブラリを使用する.

BetterLog: https://github.com/peterherrmann/BetterLog
SpreadSheetsSQL: https://github.com/roana0229/spreadsheets-sql

GAS のコード

Logger.gs

BetterLog の基本設定を行う.

Logger.gs

const Logger = (() => {
    const ssId = PropertiesService
        .getScriptProperties().getProperty('SPREAD_SHEET_ID');
    // ログ出力先は SPREAD_SHEET_ID
    const _logger = BetterLog.useSpreadsheet(ssId);
    // ログのフォーマットを指定
    _logger.DATE_TIME_LAYOUT = "yyyy-MM-dd kk:mm:ss.SS Z '[CheckMail]'";
    return _logger;
})();

checkMail.gs

新規メールを検知して、その内容を message シートへ記録する.
環境ごとに設定が必要な値がある.
その設定項目は以下.

GAMIL_APP_QUERY
Gmail で利用通知メールだけを取得するためのクエリ. 例えば、Gmail で利用通知メールを「XXXXカード」というラベルに振り分けしている場合、この値は「label:XXXXカード」という文字列になる.
CARD_NAME
対象となるカードの名前. message シートの card_name に記録されることになる. 例えば、「XXXXカード」という文字列.
利用日取得用のパターン
メール内の文字列から、利用日を取得する正規表現. 例えば、「利用日：2024/08/11 16:50」という文字列がメール内にあるとき、「/利用日：(\d{4}/\d{2}/\d{2} \d{2}:\d{2})/」という正規表現になる. メール内の文字列は、メール内容確認用プログラムを使って調べる.
利用金額取得用のパターン
メール内の文字列から、利用金額を取得する正規表現. 例えば、「利用金額：192円」という文字列がメール内にあるとき、「/(?<=利用金額：)(\b\d{1,3}(,\d{3})*\b)(?=円)/」という正規表現になる. メール内の文字列は、メール内容確認用プログラムを使って調べる.
利用先取得用のパターン
メール内の文字列から、利用先を取得する正規表現. 例えば、「利用先：SEVEN-ELEVEN」という文字列がメール内にあるとき、「/利用先：(.+)/」という正規表現になる. メール内の文字列は、メール内容確認用プログラムを使って調べる.

checkMail.gs

function checkMail() {
    const ssId = PropertiesService
        .getScriptProperties().getProperty('SPREAD_SHEET_ID');
    const SheetSQL = SpreadSheetsSQL.open(ssId, 'message');

    // <設定項目1> Gmail クエリ
    // Gmail で利用通知メールを取得するためのクエリ
    const GAMIL_APP_QUERY = 'label:XXXXカード';
    // <設定項目2> カードの名前
    // message シートの card_name や record シートの method に入力するカード名称
    const CARD_NAME = 'XXXXCard';
    // 取得するメール検索結果のスレッド数
    const GMAIL_SREAD_LIMIT = 3;

    const threads = GmailApp.search(GAMIL_APP_QUERY, 0, GMAIL_SREAD_LIMIT);
    // message_id の配列. メールが新しいものかを判断するために使う
    const msgIdList = SheetSQL.select(['message_id']).result()
        .map((obj) => obj.message_id);

    for (let thread of threads) {
    for (let message of thread.getMessages()) {
    // メールごとの処理 -----------------------------
    // ===== メール内容確認用プログラム =====
    // 設定項目3 ~ 5 を設定するために使う. 設定後削除.
    console.log(message.getPlainBody()); continue;
    // =====================================
    // メッセージ ID
    const msgId = message.getId();
    console.log(msgId);
    // すでに書き込み済みなら無視
    // msgId は新しいものほど msgIdList の後ろに来る
    if (msgIdList.lastIndexOf(msgId) !== -1) continue;

    // メール本文
    const mBody = message.getPlainBody();

    // 情報の取得
    // メール本文から、必要な情報をパターンマッチで取り出す
    const trnsDate = (function (t) {
        // <設定項目3> 利用日取得用のパターン
        const regexp = new RegExp(/利用日：(\d{4}\/\d{2}\/\d{2} \d{2}:\d{2})/);
        const match = regexp.exec(t);
        if (!match) return '';
        // このメールには、YYYY/MM/DD hh:mm と分までしかないので
        if (match !== null) return match[1] + ':00';
        else return '';
    })(mBody);

    const trnsValue = (function (t) {
        // <設定項目4> 利用金額取得用のパターン
        const regexp = new RegExp(/(?<=利用金額：)(\b\d{1,3}(,\d{3})*\b)(?=円)/);
        const match = t.match(regexp);
        // カンマで数値が区切られているので、取り除いて数値に変換
        if (match !== null) return parseInt(match[0].replace(",", "")); 
        else return 0;
    })(mBody);

    const trnsShop = (function (t) {
        // <設定項目5> 利用先取得用のパターン
        const regexp = new RegExp(/利用先：(.+)/);
        const match = regexp.exec(t);
        if (match !== null) return match[1];
        else return "";
    })(mBody);

    // trnsDate がカラのとき現在時刻を入れる
    const now = new Date();
    const trnsDateObj = (trnsDate === '') ? now : new Date(trnsDate);

    // 書き込み
    SheetSQL.insertRows([{
        message_id: msgId,
        date: trnsDate,
        value: trnsValue,
        shop: trnsShop,
        card_name: CARD_NAME,
        date_timestamp: trnsDateObj.getTime(),
        updated: now.getTime(),
        is_notified: 'no',
    }]);

    // メッセージを既読にする
    message.markRead();
    // -----------------------------------------
    } // End of for (let thread of threads)
    } // End of for (let message of thread.getMessages())
}

起動設定

前項の checkMail.gs に適切な設定をする.
初回は checkMail を起動して、権限等の割り当てをする.
あとは checkMail 関数を５分おきに起動するように設定すれば完了.

目的

前提

イメージ

実装

構造

Google Spread Sheet に必要なシートを作成

GAS セットアップ

スクリプトプロパティを設定

ライブラリをインポート

GAS のコード

Logger.gs

checkMail.gs

起動設定

Discussion