Google Spreadsheetで災害用備蓄を管理し、賞味期限をメール通知する

災害用の備蓄を管理する必要性

近年の日本では大規模な地震、台風や水害が多発している。土砂災害や暴風によって交通網やライフラインが遮断されることで、避難所に行けなかったり公共の支援を受けられなかったりするという事態が発生する可能性がある。その場合、災害発生から公的支援が受けられるようになるまでの間の生活は、自宅での備蓄に頼らなければならない。

近年は「ローリングストック法」を用いて備蓄を管理することが推奨されている (参考: 農林水産省「ローリングストックについて知りたい方へ」)。ある程度多めに備蓄を購入し余裕をもって消費することで、賞味期限切れを防止するとともに、普段から非常食になれておくという方法である。

しかし、私のようにズボラな人間は、どんぐりの隠し場所を忘れるリスのように、買ってきた非常食の存在を忘れてしまい、毎年台風の季節に賞味期限の切れたカップラーメンを発掘してうんざりすることになる。

そこで、Google Spreadsheetを用いた備蓄管理リマインダーを準備して対策にあたった。以下のように運用することを想定する。

①購入した品物をスプレッドシートで管理する。

②期限切れの数日前と期限切れした日にメールでリマインダーが来るようにする

こうすれば、最低限スプレッドシートへの入力さえ怠らなければ賞味期限の管理ができる。

Step0: 備蓄を購入する

まずは各自必要なものを備蓄しよう (参考: 東京都防災 「「日常備蓄」を進めましょうリーフレット　日本語版」)。

Step1: 備蓄をスプレッドシートに入力する

Google Spreadsheetにアクセスし、空のシートを作る (Googleアカウントが必要なのでない人は作ってください)。

以下の画像のように、品名(A列)、数量(B列)、期限(C列)、保管場所(D列)、通知したい期日(F2セル)、送信先のメールアドレス(F3セル)を入力する。(列を増やすなどのレイアウト変更を行う場合、後述のようにスクリプトを書き換える必要がある。)

これでスプレッドシートの準備は完成。次はスクリプトの準備に入る。

Step2: スクリプトを貼り付ける

(スクリプトの作成にあたって、Google Apps Scriptを使って締め切りを通知する方法 と  Google Apps Script で毎日決まった時刻にスクリプトを実行するトリガー設定 を大いに参考にした)

スプレッドシート上部にある「ツール / Tools」から「スクリプトエディタ / Script editor」を選択する。すると新しいタブでスクリプト管理画面が開く。

↓

ここに、

function myFunction() {

}

とだけ表示されているので、これらをすべて削除し、以下のコードをコピーして貼りつける。

function mailSend() {
 var mySs = SpreadsheetApp.getActiveSpreadsheet(); //スプレッドシートを取得
 var mySheet = SpreadsheetApp.getActiveSheet(); //シートを取得
 var url = mySs.getUrl(); //スプレッドシートのURL
 var today = new Date(); //今日の日付を取得
 var strTo = mySheet.getRange(3,6).getValue(); //To
 var strFrom = mySheet.getRange(3,6).getValue(); //From
 var strSender = "備蓄管理"; //差出人
 var dateBuffer = mySheet.getRange(2,6).getValue(); //賞味期限の何日前に通知するか
 
 
/* 繰り返し計算 */
 for (var i = 2; i <= 200; i++) {
   var goodsName = mySheet.getRange(i, 1).getValue(); //Ai列を取得
   var exDt = mySheet.getRange(i, 3); //Ci列を取得
   var place = mySheet.getRange(i, 4).getValue(); //Di列を取得
   var exDate = new Date(exDt.getValue()); //Ci列のDateを作成
   var dt = exDate.getTime() - today.getTime(); //exDateから今日の日付を引く
   var day = Math.ceil(dt / 1000 / 60 / 60 / 24);　 //日付に変換
   
   if (0 <= day && day <= dateBuffer) { //通知する期日を過ぎたか　
     /* メール本文を準備 */
     var strSubject2 = goodsName + "の賞味期限が迫っています (備蓄管理Googleスプレッドシートから)";
     var strBody2 = "備蓄管理シートからのお知らせです。\n" + goodsName + " (保管場所: " + place + ") の賞味期限が" + String(dateBuffer) + "日前に迫っています。ストックを入れ替えましょう\n\n スプレッドシートへのリンク: " + url;
     /* メールを送信 */
     var send = GmailApp.sendEmail(
       strTo,
       strSubject2,
       strBody2, {
         from: strFrom,
         name: strSender
       }
     );
     
   } else if (day <= 0) { //今日の日付と締切日を比較
     /* メール本文を準備 */
     var strSubject1 = goodsName + "の賞味期限が切れています (備蓄管理Googleスプレッドシートから)";
     var strBody1 = "備蓄管理シートからのお知らせです。\n" + goodsName + " (保管場所: " + place + ") の賞味期限が切れました。ストックを入れ替えましょう。\n\nスプレッドシートへのリンク: " + url;
     /* メールを送信 */
     var send = GmailApp.sendEmail(
       strTo,
       strSubject1,
       strBody1, {
         from: strFrom,
         name: strSender
       }
     );
   }
 }
}

(※ getRange()で行と列を取得しているので、新たな列を挿入して位置がずれた場合、getRange()のカッコ内の数字を書き換える必要がある。例えばD列に「種別」という列を設けるとすると、それより後の列は一つずつ列番号が増えるので、六行目でアドレスをしている箇所では、var strFrom = mySheet.getRange(3,7).getValue(); //From と書き換える必要がある。同様に、行と列の指定をずれた分だけ修正する。)

スクリプトに適当な名前をつけて保存し、実行する (実行するにはCtrl + R/ Cmd + Rを押下するか、上側にある▶︎ボタンを押す)。このスクリプトにアクセスを許可するか尋ねる画面に遷移するので、許可する。すると、期限切れ間近の食品があった場合、F3セルに入力したアドレスにメールが行くはず。t試しに期限切れの食品を登録してテストしてみよう。

Step3: リマインドを自動化する

今の状態では手動で▶︎ボタンを押さないと通知が来ないので、毎日決まった時刻に自動でメールが来るようにする。

スクリプト編集画面で、下の画像で赤囲みになっている「吹き出しに時計のマーク」をクリックすると、「トリガー管理画面」に移動する。

トリガーとは、「満たされた時にスクリプトが自動で実行するされるような条件」のことだ。今回は「毎日一定の時間を迎える」ことをトリガーにする。

画面右下の「+トリガーを追加」ボタンをクリックするとトリガー編集画面に移動する。下の方へスクロールし、下の画像のように「時間主導型」「日付ベースのタイマー」「午前11~午後12時」を選択し、保存ボタンをクリックする。最後の時刻の項目は自分の好みに変えて良い。

これで全ての行程が完了した。もし期限切れの備蓄があれば、毎日決まった時間に通知される。期限切れの備蓄を消費したら、シートからその行を削除し、新たに備蓄を購入し、シートへ書き加えよう。