やろうと思ったきっかけ

生活費は夫婦でほとんど折半しているのですが、固定費とか雑費とかを支払う際に使用する家族共有カードを楽天カードで作成してました。

毎月お互い決めている一定金額を入金し、さらに雑費分はその都度入れてたつもりだったんですけど、ふと残額を確かめてみると

「あれ！？結構余裕持って入れてたつもりなのに結構ギリギリになってるやん、、！」

という事態に遭遇しました。
確かめてみたところ雑費も折半しているのですが、とりあえず買っちゃってお金は後から振り込む〜みたいな感じでそのまま忘れるケースがお互い多々あり、それがちりつもでギリギリになっていたみたいです。

これちょっと改善せないかんな〜と思い、とりあえずカード使用したらスプレッドシートに記載していこう！ということになった訳ですが、そもそも毎回毎回書き込むのも面倒すぎるから自動化できないかな〜と考え、ググってみるとGoogle Apps Scriptとかいうやつがあることを知り試しにつくってみるか〜となったのでした。
(前置き長くてすみません笑)

Google Apps Script の始め方

スプレッドシートから直接作るやり方もあるらしいですが、今回はGoogle Driveから作成しました。

Google Drive -> 新規 -> その他 -> Google Apps Script

するとこんな画面になります。

Google Apps ScriptはJavaScriptで記述できるので、JavaScript触ったことある人は特に問題なく書けると思います！
ちなみに最近自分は業務でTypeScriptばかり触っているもんで、動的型付けのJavaScriptにかなり違和感と気持ち悪さを感じてしまいました、、w

後から知ったのですが、Google Apps ScriptをTypeScriptで書くやり方もあるみたいですね。次やるならこっちでやりたい、、！！

書き込みたいスプレッドシート

今回はGoogle Apps Scriptで新しくスプレッドシートを作るのではなく、既存のスプレッドシートの指定のシートに書き込んでいく実装になります。

現在使用しているスプレッドシートはこちら ↓

このシートの名前は「共有カード運用管理」にしてあります。

実装内容

まずは実装したコードです。

const SPREAD_SHEET = SpreadsheetApp.openById('スプレッドシートID');
const SHARED_CARD_MANAGEMENT_SHEET = SPREAD_SHEET.getSheetByName('共有カード運用管理');

function addCardUseDetail() {
  /** メール検索クエリを作成 */
  const SUBJECT = 'カード利用のお知らせ(本人ご利用分)'; // 利用お知らせメールの件名
  const ADDRESS = 'info@mail.rakuten-card.co.jp'; // 楽天カードの明細メールアドレス

  /** 検索期間の初めと終わりを昨日と明日にする事で今日のみのMailを検索できる */
  let afterDate = new Date();
  afterDate.setDate(afterDate.getDate() - 1);
  let beforeDate = new Date();
  beforeDate.setDate(beforeDate.getDate() + 1);
  const DATE_AFTER = Utilities.formatDate(afterDate, 'JST', 'yyyy/M/d');
  const DATE_BEFORE = Utilities.formatDate(beforeDate, 'JST', 'yyyy/M/d');

  const QUERY = 'subject:' + SUBJECT + ' from:' + ADDRESS + ' after:' + DATE_AFTER + ' before:' + DATE_BEFORE;

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

  /** 該当メールがあった場合 */
  if(threads.length > 0) {
    const msgs = GmailApp.getMessagesForThreads(threads);

    /** テーブルの左端 */
    const TALBE_LEFT_MOST = 1;
    /** テーブルの右端 */
    const TALBE_RIGHT_MOST = 6;

    /**
     * 検索ヒットしたMailを一つずつ処理する
     */
    for(let i=0; i < msgs.length; i++) {
      /** 最終行番号取得 */
      let lastrow = SHARED_CARD_MANAGEMENT_SHEET.getLastRow();
      /** 新規で追加する行番号 */
      let newrow = lastrow + 1

      /** 元となるデータがある範囲 */
      const sourceRange = SHARED_CARD_MANAGEMENT_SHEET.getRange(
        `${getColName(TALBE_LEFT_MOST)}${lastrow}:${getColName(TALBE_RIGHT_MOST)}${lastrow}`
      );

      /** 本文を取得 */
      const plainBody = msgs[i][0].getPlainBody();
      console.log(`メール本文: \n${plainBody}`);

      /** 受信日を取得 */
      const mailDate = msgs[i][0].getDate();

      /** テーブルデータ取得 */
      const tableData = SHARED_CARD_MANAGEMENT_SHEET.getRange(
        `${getColName(TALBE_LEFT_MOST)}6:${getColName(TALBE_RIGHT_MOST)}${lastrow}`
      ).getValues();

      /** 利用先の配列を取得 */
      let useTargets = plainBody.match(/[0-9]{4}\/[0-9]{2}\/[0-9]{2}.*本人/g);
      if (useTargets && useTargets.length) {
        useTargets.forEach((val, index) => {
          useTargets[index] = val.replace(/[0-9]{4}\/[0-9]{2}\/[0-9]{2}\t|\t本人|本人/g, '');
        })
      }
      
      /** 明細日付の配列を取得 */
      let histories = plainBody.match(/[0-9]{4}\/[0-9]{2}\/[0-9]{2}/g);
      if (histories && histories.length){
        histories.forEach((history, index) => {
          const [year, mouth, day] = history.split('/')
          histories[index] = new Date(Number(year), Number(mouth - 1), Number(day))
        })
      }

      /** 金額の配列を取得 */
      let prices = plainBody.match(/1回.*円/g)
      if (prices && prices.length){
        prices.forEach((price, index) => {
          prices[index] = price.replace(/1回\t|円|,|\s/g, '')
        })
      }

      /**
       * データ登録処理
       */
      if (useTargets && useTargets.length && useTargets[0]) {
        for(let j=0; j < useTargets.length; j++) {

          /** 比較用データ生成 */
          const compareData = [
            mailDate ?? new Date(),
            histories[j] ?? new Date(),
            useTargets[j] ?? '', 
            '共有', 
            -Number(prices[j]) ?? 0, 
            '未支払'
          ];

          /** 受信日時、購入品名もしくは金額が一緒の場合は処理をスキップ (重複を防ぐため) */
          if (tableData.find((val => {
            // 受信日時
            return formatDate(val[0]) === formatDate(compareData[0]) &&
             // 購入品名
             (val[2] === compareData[2] ||
             // 金額
             val[4] === compareData[4])
            })
          ) !== undefined){
            continue;
          }

          /** 固定費の場合は金額に入れたくないのでスキップ */
          if (useTargets[j] && isFixedCost(useTargets[j])) {
            continue;
          }

          /** オートフィルを反映させたい範囲 */
          const destination = SHARED_CARD_MANAGEMENT_SHEET.getRange(
            `${getColName(TALBE_LEFT_MOST)}${newrow + j}:${getColName(TALBE_RIGHT_MOST)}${newrow + j}`
          );

          /** 元のデータを新規で追加する行にコピーする */
          sourceRange.copyTo(destination);

          console.log(`
            受信日時: ${mailDate}, 
            履歴: ${histories[j]}, 
            購入品名: ${useTargets[j]}, 
            金額: ${-Number(prices[j])}
          `)

          /** 受信日時: メール受信時間を設定 */
          const dateSell = SHARED_CARD_MANAGEMENT_SHEET.getRange(`A${newrow + j}`);
          dateSell.setValue(mailDate ?? new Date());

          /** 履歴: 明細日付を設定 */
          const historySell = SHARED_CARD_MANAGEMENT_SHEET.getRange(`B${newrow + j}`);
          historySell.setValue(histories[j] ?? new Date());

          /** 購入品名: 利用先を設定 */
          const purchaseProductNameSell = SHARED_CARD_MANAGEMENT_SHEET.getRange(`C${newrow + j}`);
          purchaseProductNameSell.setValue(useTargets[j] ?? '');

          /** 支払者: デフォルトは「共有」に設定 */
          const payerSell = SHARED_CARD_MANAGEMENT_SHEET.getRange(`D${newrow + j}`);
          payerSell.setValue('共有');

          /** 金額: 利用金額を負の数で設定 */
          const priceSell = SHARED_CARD_MANAGEMENT_SHEET.getRange(`E${newrow + j}`);
          priceSell.setValue(-Number(prices[j]) ?? 0);

          /** 支払状況フラグ設定 */
          const paymentStatusSell = SHARED_CARD_MANAGEMENT_SHEET.getRange(`F${newrow + j}`);
          paymentStatusSell.setValue('未入金');
        }
      }

      /** 履歴を降順にに並び替え */
      const historyRow = SHARED_CARD_MANAGEMENT_SHEET.getRange(`B6:B${lastrow}`);
      historyRow.sort({column: 2, ascending: false});
    };
  }
}

/** 時間のフォーマット */
function formatDate(date) {
  const yyyy = date.getFullYear(),
  mm = toDoubleDigits(date.getMonth() + 1)
  dd = toDoubleDigits(date.getDate())
  hh = toDoubleDigits(date.getHours())
  mi = toDoubleDigits(date.getMinutes())
  se = toDoubleDigits(date.getSeconds());

  return yyyy + '/' + mm + '/' + dd + ' ' + hh + ':' + mi + ':' + se;
}

/** 日付の0埋め */
function toDoubleDigits(num){
  num += "";
  if (num.length === 1) {
    num = "0" + num;
  }
 return num;     
};

/** 固定費かどうかの判定 (金額に入れたくないものを随時追加する) */
function isFixedCost(useTarget){
  if (
  useTarget.match(/^(?=.*ﾄｳｷﾖｳﾃﾞﾝﾘﾖｸ).*$|^(?=.*東京ガス).*$/)
  ){
    return true;
  }

  return false;
};

/** セルの列名取得 */
function getColName(num) {
  let result = SHARED_CARD_MANAGEMENT_SHEET.getRange(1, num);
  result = result.getA1Notation();
  result = result.replace(/\d/,'');
 
  return result;
}

コード解説

メールからカード明細情報を取得してくるaddCardUseDetailメソッドと、あとはそれに使用するヘルパーメソッドに分かれます。解説はaddCardUseDetailのみになります。

下準備

まず対象のスプレッドシートを特定する必要があるので、スプレッドシートIDを設定します。
スプレッドシートIDってどこにあるかというとURLのspreadsheets/後に続く文字列がスプレッドシートIDになってます。

https://docs.google.com/spreadsheets/:id <- これ

対象のスプレッドシートを特定できたら書き込みたいシート名を設定します。今回は「共有カード運用管理」シートでしたね。

const SPREAD_SHEET = SpreadsheetApp.openById('スプレッドシートID');
const SHARED_CARD_MANAGEMENT_SHEET = SPREAD_SHEET.getSheetByName('共有カード運用管理');

次は、Gmailからメールを探してくる処理になります。

  /** メール検索クエリを作成 */
  const SUBJECT = 'カード利用のお知らせ(本人ご利用分)'; // 利用お知らせメールの件名
  const ADDRESS = 'info@mail.rakuten-card.co.jp'; // 楽天カードの明細メールアドレス

  /** 検索期間の初めと終わりを昨日と明日にする事で今日のみのMailを検索できる */
  let afterDate = new Date();
  afterDate.setDate(afterDate.getDate() - 1);
  let beforeDate = new Date();
  beforeDate.setDate(beforeDate.getDate() + 1);
  const DATE_AFTER = Utilities.formatDate(afterDate, 'JST', 'yyyy/M/d');
  const DATE_BEFORE = Utilities.formatDate(beforeDate, 'JST', 'yyyy/M/d');

  const QUERY = 'subject:' + SUBJECT + ' from:' + ADDRESS + ' after:' + DATE_AFTER + ' before:' + DATE_BEFORE;

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

GmailApp.search(...) に検索ワードを渡せるのでそのデータを作成しています。
実はこれ、実際のGmailの検索フォームにも同じキーワードで検索できるのでここで設定する前に一度Gmailの方で検索ワード試してみてから設定したほうが確実かもしれません。

Utilities.formatDate()というのは最初からGoogle Apps Scriptに組み込まれているメソッドで日付を指定のフォーマット文字列に変換してくれます。

次からは該当メールがあった場合の処理ですね。

const msgs = GmailApp.getMessagesForThreads(threads);

このmsgsには、検索ヒットしたメールが配列で入ります。
その後の処理で、このmsgsを一つずつ取り出して処理を行っています。

明細データを追加する前に、追加したい対象の表を特定する必要があります。

/** テーブルの左端 */
const TALBE_LEFT_MOST = 1;
/** テーブルの右端 */
const TALBE_RIGHT_MOST = 6;
...

/** 最終行番号取得 */
let lastrow = SHARED_CARD_MANAGEMENT_SHEET.getLastRow();
/** 新規で追加する行番号 */
let newrow = lastrow + 1

/** 元となるデータがある範囲 */
const sourceRange = SHARED_CARD_MANAGEMENT_SHEET.getRange(
`${getColName(TALBE_LEFT_MOST)}${lastrow}:${getColName(TALBE_RIGHT_MOST)}${lastrow}`
);

ちょっとややこしいですが、スプレッドシートの最終行の番号を取ってきて、現在の表の最終行
をsourceRangeに格納しています。

このgetRange()はGoogle Apps Scriptでよく使うメソッドで、スプレッドシートの対象のセルを取得、変更する際に使用します。
例えば今回の場合は、表最終行のA11:F11のデータをとってきたいわけですから
SHARED_CARD_MANAGEMENT_SHEET.getRange('A11:F11')
とも指定可能です。
ただ、これからこの表にデータを追加していくので当然表の最終行が変わってきます。
ですので都度、getLastRow()を使って最終行を取得しに行っているわけです。

/** 本文を取得 */
const plainBody = msgs[i][0].getPlainBody();
console.log(`メール本文: \n${plainBody}`);

/** 受信日を取得 */
const mailDate = msgs[i][0].getDate();

/** テーブルデータ取得 */
const tableData = SHARED_CARD_MANAGEMENT_SHEET.getRange(
`${getColName(TALBE_LEFT_MOST)}6:${getColName(TALBE_RIGHT_MOST)}${lastrow}`
)
.getValues();

getPlainBody()はメール本文,getDate()はメール受信時間を取得できます。
getRange()で現在のテーブルデータ全体を取得しているのは後で重複データを作成しないようにするために判定に使用しています。getRange()の後にgetValues()をつけるとそのデータを取得できます。

メールの明細データ取得

次にメールの明細データ取得です。
楽天カードの利用明細は二種類あって、速報版とその後にくる通常版がありまして、今回は明細情報が多い通常版を取得する実装にしています。

楽天から届くメールはこちら ↓

このショッピングご利用分の中身を今回抽出してスプレッドシートに書き込んでいきます。

/** 利用先の配列を取得 */
let useTargets = plainBody.match(/[0-9]{4}\/[0-9]{2}\/[0-9]{2}.*本人/g);
  if (useTargets && useTargets.length) {
      useTargets.forEach((val, index) => {
        useTargets[index] = val.replace(/[0-9]{4}\/[0-9]{2}\/[0-9]{2}\t|\t本人|本人/g, '');
      })
    }
      
/** 明細日付の配列を取得 */
let histories = plainBody.match(/[0-9]{4}\/[0-9]{2}\/[0-9]{2}/g);
  if (histories && histories.length){
     histories.forEach((history, index) => {
       const [year, mouth, day] = history.split('/')
       histories[index] = new Date(Number(year), Number(mouth - 1), Number(day))
     })
   }

/** 金額の配列を取得 */
let prices = plainBody.match(/1回.*円/g)
  if (prices && prices.length){
    prices.forEach((price, index) => {
      prices[index] = price.replace(/1回\t|円|,|\s/g, '')
    })
  }

plainBodyにはメールの本文が文字列で入っているのでその中から各データを正規表現で取り出しています。(正規表現の内容については長くなるので割愛します)

データ登録

次はスプレッドシートに各データを入れ込む処理です。
実は、このメール検索には通常版の他に速報版も取ってきてしまうので、速報版のデータは弾く必要があります。
速報版には明細情報に「利用先」の項目がないため必然的にuseTargetsはnullになるため、useTargetsで通常版と速報版の判定を行なっています。

if (useTargets && useTargets.length && useTargets[0]) {
  for(let j=0; j < useTargets.length; j++) {
    /** 比較用データ生成 */
    const compareData = [
      mailDate ?? new Date(),
      histories[j] ?? new Date(),
      useTargets[j] ?? '', 
      '共有', 
      -Number(prices[j]) ?? 0, 
      '未支払'
    ];

    /** 受信日時、購入品名もしくは金額が一緒の場合は処理をスキップ (重複を防ぐため) */
    if (tableData.find((val => {
      // 受信日時
      return formatDate(val[0]) === formatDate(compareData[0]) &&
        // 購入品名
        (val[2] === compareData[2] ||
        // 金額
        val[4] === compareData[4])
      })
      ) !== undefined){
      continue;
    }

    /** 固定費の場合は金額に入れたくないのでスキップ */
    if (useTargets[j] && isFixedCost(useTargets[j])) {
      continue; 
    }

    /** オートフィルを反映させたい範囲 */
    const destination = SHARED_CARD_MANAGEMENT_SHEET.getRange(
      `${getColName(TALBE_LEFT_MOST)}${newrow + j}:${getColName(TALBE_RIGHT_MOST)}${newrow + j}`
    );

    /** 元のデータを新規で追加する行にコピーする */
    sourceRange.copyTo(destination);

    console.log(`
      受信日時: ${mailDate}, 
      履歴: ${histories[j]}, 
      購入品名: ${useTargets[j]}, 
      金額: ${-Number(prices[j])}
    `)

    /** 受信日時: メール受信時間を設定 */
    const dateSell = SHARED_CARD_MANAGEMENT_SHEET.getRange(`A${newrow + j}`);
    dateSell.setValue(mailDate ?? new Date());

    /** 履歴: 明細日付を設定 */
    const historySell = SHARED_CARD_MANAGEMENT_SHEET.getRange(`B${newrow + j}`);
    historySell.setValue(histories[j] ?? new Date());

    /** 購入品名: 利用先を設定 */
    const purchaseProductNameSell = SHARED_CARD_MANAGEMENT_SHEET.getRange(`C${newrow + j}`);
    purchaseProductNameSell.setValue(useTargets[j] ?? '');

    /** 支払者: デフォルトは「共有」に設定 */
    const payerSell = SHARED_CARD_MANAGEMENT_SHEET.getRange(`D${newrow + j}`);
    payerSell.setValue('共有');

    /** 金額: 利用金額を負の数で設定 */
    const priceSell = SHARED_CARD_MANAGEMENT_SHEET.getRange(`E${newrow + j}`);
    priceSell.setValue(-Number(prices[j]) ?? 0);

    /** 支払状況フラグ設定 */
    const paymentStatusSell = SHARED_CARD_MANAGEMENT_SHEET.getRange(`F${newrow + j}`);
    paymentStatusSell.setValue('未入金');
  } 
}

先ほど作ったtableDataと新規作成予定のデータをcompareDataに格納し、もし既にデータがあった場合はスキップするように設定しています。

isFixedCostのメソッドは、固定費等は別で管理していてこのスプレッドシートに記入したくない場合用に作成しました。

また、データを登録する際に、前回のセルの設定を新しいセルにも適応させたかったため、

sourceRange.copyTo(destination);

このcopyToを使うと対象のセルを引数に渡したセルにコピーすることができて、一緒に設定もコピーされます。

あとは各対象セルにsetValueで値を入れていけば 🆗 です。

トリガー設定

実装は完成したので実行するトリガーを設定します。
サイドバーの「トリガー」をクリックすると、トリガー一覧が表示されます。
その右下のトリガーを追加を押すと下のようなモーダルが出てきます。

この実行する関数に先ほど作成した関数一覧が表示されるので、その中から実行したいaddCardUseDetailを選択します。
時間は色々設定できますが、今回は一時間おきに設定しました。

これで設定は全て完了です！
いっとき放置しといて、実行数を確認してみると、

正常に一時間ごとに実行されていますね！

まとめ

ふとしたきっかけで初めてGoogle Apps Scriptに触れてみたのですがかなり便利で使いやすい印象でした！
自動化ってPythonとかでしかできないイメージだったのですが、こういったツールを使えばいちいちアプリを作る必要もなくJavaScriptの知識だけでサクッとできてしまうので凄いですよね。

今後も何か自動化したいときにはGASを使ってみようと思います!