Google Spreadsheetで非常用持ち出し袋の在庫管理をする

背景

我が家で数年前に非常用持ち出し袋というものを作ったのですが、中身の乾パンだとか長期保存できる羊羹だとかがそろそろ古くなってきています。
また、最近新しく家族が増えたのもあって色々と中身の増強が必要そうなのと、賞味期限とか使用期限とかの管理も今までよりしっかりやりたいなーというのがありました。

そこで、Google Spreadsheetを使って在庫の表を管理し、Google Apps Scriptが週一で期限を自動でチェックして期限が切れそうなものをメールで通知するような仕組みを作りました。

実装

Spreadsheetを作る

シンプルにこんな感じのものを作りました。

単純にGoogle Spreadsheetの機能を使っただけなのでなんてことはないのですが、、

• 分類はちゃんとマスタ（別シートで分類の一覧を用意した）から選択するような入力制御をかけた
• チェックボックスが使えたので破棄したフラグをチェックボックスで表現してみた
• 期限が近いものには「期限アラート」が出るように関数を組んだ

というのが工夫した点です。

期限アラートを出す関数は下記のような感じです。

=IF(ISBLANK(F2),,IF(OR(DAYS(F2, TODAY()) > 30, G2 = TRUE), , "❗"))

実際には30とか❗とかは変更しやすいよう設定シートを作って外出ししていますが。

Spreadsheetができたので期限切れをチェックしてメールを送るスクリプトを書きます。

Google Apps Scriptを書く

Spreadsheetに紐付いたスクリプトを作成するのは簡単で、下記のように[ツール] > [スクリプト エディタ]をクリックするだけです。

期限切れが近いレコードを抽出する部分の実装

var inventory = {};
inventory.SHEET = '物品一覧';
inventory.COLUMNS = {
  CATEGORY : '分類',
  NAME : '物品名称',
  QUANTITY : '数量',
  SELLER : '購入元',
  AQUISITION_DATE : '購入日',
  EXPIRY_DATE : '使用期限／賞味期限',
  DISPOSED : '処分済',
  EXPIRY_ALERT : '期限アラート'
};

inventory.getSheet = function () {
  if (!inventory.sheet) {
    inventory.sheet = SpreadsheetApp.getActive().getSheetByName(inventory.SHEET);
  }
  return inventory.sheet;
};

inventory.getData = function () {
  var rawData = inventory.getSheet().getDataRange().getDisplayValues();
  var keys;
  var data = [];
  for (var i = 0; i < rawData.length; i++) {
    var row = rawData[i];
    
    if (i === 0) {
      keys = row;
    } else if (row[0] && row[1]) {
      var record = {};
      for (var j = 0; j < keys.length; j++) {
        record[keys[j]] = row[j];
      }
      data.push(record);
    } else {
      // when empty row is detected
      break;
    }
  }
  return data;
};

Google Apps ScriptではSpreadsheetAppというオブジェクトが用意されており、このオブジェクトを使ってSpreadsheetの操作ができます。
inventory.getDataでは表をまるごと{列名 : 値}の連想配列の配列として取得できるようにしました。

inventory.getExpiringItems = function() {
  return inventory.getData().filter(function(record) {
    return !!record[inventory.COLUMNS.EXPIRY_ALERT]
  });
};

inventory.getDataで取得した情報をフィルターして「期限アラート」列に何らかの値（❗ですね）が入っているデータのみを抽出しています。
これで期限が切れそうな物品のみを表から抽出できるようになりました。

メール通知の実装

function checkInventory(isDebug) {
  var expiringItems = inventory.getExpiringItems();
  if (expiringItems.length > 0) {
    var DEVELOPER = ['hogehoge@dummy.com']; // 私のメールアドレス
    var USER = ['fugafuga@dummy.com']; // 妻のメールアドレス
    var recipient = isDebug ? DEVELOPER : DEVELOPER.concat(USER);
    
    var subject = '備蓄品の期限切れ通知 (' + expiringItems.length + '件)';
    var body = '下記の備蓄品の期限が近くなっています。\n\n';
    
    for (var i = 0; i < expiringItems.length; i++) {
      var item = expiringItems[i];
      body = body + '・' + item[inventory.COLUMNS.NAME] + ' (' + item[inventory.COLUMNS.EXPIRY_DATE] + ')\n';
    }
    
    body = body + '\n詳細はスプレッドシートで確認してください。\nhttps://docs.google.com/spreadsheets/d/dummy/edit#gid=0';
    
    MailApp.sendEmail(recipient.join(','), subject, body);
    Logger.log('sent e-mail : ' + {recipient : recipient, subject : subject, body : body}.toString());
  } else {
    Logger.log('no expiring items');
  }
}

function test() {
  checkInventory(true);
}

Spreadsheetのときと同様にMailAppというオブジェクトが用意されており、こいつのsendEmailというメソッドを叩くことでメールを送ることができます。
一応デバッグモードを用意し、isDebugがtrueのときには妻にメールが行かないようにしてあります。こういう気遣いはエンジニアとして大事ですよね。
特筆すべきことはないのですが、備蓄品の期限切れ通知 (3件)のようなタイトルで、本文には期限切れ間近の物品名とSpreadsheetのURLが書いてあるメールが飛びます。

ここまで書いてエディタからtest()を実行すると私のところにメールが飛びます。

そして、エディタのメニューで[編集] > [現在のプロジェクトのトリガー]と選ぶと下記のように週一でcheckInventory()を実行する設定ができます。

まとめ

きっと探せばスマホアプリがある気がしますが、個人的にこういうたまにしか使わないものは専用アプリをごちゃごちゃ入れるよりもGoogle Spreadsheetかなにかに集約しておいたほうが無くさないし共有できるし楽です。
今回は非常用持ち出し袋を対象にしているので在庫の増減（特に減の方）が頻繁には発生せず、Spreadsheetでもそれほど煩雑ではないのでこの仕組が長く活躍してくれるはずです。

ちなみに、ずっと冷蔵庫の在庫を管理したいと思っているのですが、冷蔵庫レベルで増減が頻繁に発生するとSpreadsheetではすぐに実態と記録が乖離することが容易に予想できるので同じやり方はダメそう。。
バーコード読み取って入出庫登録できるようなデバイスが冷蔵庫に磁石でくっついてたりしたら行けるんじゃないかという気がするんですが、誰か作ってくれないだろうか。
入庫の周期とか賞味期限とかからネットショップで注文しろってサジェストしたりとか儲かる要素はある気がするので、冷蔵庫の在庫管理というサービスは既にありそう。