MotoJapan's Tech-Memo

技術めも

【お家IT#1】 googleスプレッドシートで管理した賞味期限リストをGASでメールで定期通知してみた

GAS(Google Apps Script)という、Google製サービス上で動くJavascript互換のスクリプトを初めて触ってみた。

家の課題を解決するために。

課題:家の調味料や保存食の賞味期限がいつの間に切れていく

こんなことありますよね。(え、ないですか?)
私と妻で掃除してみたところ65品目ありましたが、この期限を全て把握することは記憶力が鶏レベルの私には無謀です。

調味料だけでもよくわからないほどあった。

写真は調味料棚の一部
f:id:motojapan:20170918224818p:plain
実際棚はこの9倍くらいの容積があるので覚えられるわけがない。

これに対して、賞味期限を定期通知することで、家の中の賞味期限食品を管理し、ゴミを減らし、地球温暖を防ぎ、さらに世界の家庭で蔓延しているらしい「夕食何にする?」という問いを「今晩あれを使って何か作ろう」という積極的な思考へ変換することで、世の中全ての家庭が円満になることを妄想して作ってみた。

概略図
f:id:motojapan:20170919232912p:plain

やったこ

0.夫婦共有のスプレットシートを作る

夫婦共有のGoogle Driveフォルダにスプレットシートを作る。

1.スプレットシートに賞味期限を書く

大掃除がてらキッチン周りの掃除と一緒にやる。
15分程度で書き込み終わり。

こんな感じ。
f:id:motojapan:20170919222810p:plain

最低限、B列とC列があればOK。

説明
A列 今日の日付(A1セル)
B列 品目名
C列 賞味期限
D列 残り日数

今回はスクリプトで完結するので使わないが
A1セルは、[=TODAY]で今日の日付を取得。
D2セルは、[=C2-$A$1]で残り日数を取得。

2.スプレットシートの情報をemailで送るスクリプトを書く

スプレットシートの[ツール]->[スクリプトエディタ]を選択。

ここで空プロジェクトができるので下記を記載。
ソース自体は30行くらいでできた。

ソースコード全体は下にあるが重要なところだけ先んじて覚書。

getRangeについて
  var MAX_LINES = 200
  //シート情報を取得
  var sheet = SpreadsheetApp.getActiveSheet();  
  //B,C列の情報を、配列で取得
  var lines = sheet.getRange(2, 2, MAX_LINES, 2).getValues();  

getRangeでシート情報を配列として取得できるが、多重定義されてるのでいろいろなデータの取り方がある。
本家API referenceを参考にする。

今回の場合下図の対応。
getRange(row, column, numRows, numColumns)

f:id:motojapan:20170919235816p:plain


ここ以外特にハマったとこはなく終わった。

ソースコード全体

下記の通り。

function myFunction() {
  var MAX_LINES = 200
  var today = new Date();

  var sheet = SpreadsheetApp.getActiveSheet();  
  var lines = sheet.getRange(2, 2, MAX_LINES, 2).getValues();  
  var text = ""
  var flag = true
  
  for (var idx in lines) {
      var date      = lines[idx][1]
      var item_name = lines[idx][0]
      if(date == null || date == "") {
        continue;
      }    
      if(item_name == null || item_name == "") {
        continue;
      }
      
      if(flag && date > today) {
        today_info = Utilities.formatDate(today, 'Asia/Tokyo', 'yyyy年M月d日')
        text +=  "------- today : " + today_info + " ------- \n"
        flag = false
      }

      var dt = date.getTime() - today.getTime();
      var diff_day = dt / (1000 * 60 * 60 * 24) + 1;
      
      day_info = Utilities.formatDate(date, 'Asia/Tokyo', 'yyyy年M月d日')
      text +=  day_info + "(" + Math.floor(diff_day) + ") \t" + item_name + "\n"
  }
  
  GmailApp.sendEmail("xxxxxx@gmail.com", "notify", text);  // 私のアドレス
  GmailApp.sendEmail("yyyyyy@gmail.com", "notify", text);  // 妻のアドレス
}

3.定期配信のためのスケジュール設定する

[スクリプトエディタ] → 時計マークの[現在のプロジェクトのトリガー]を選択
月曜の夜と金曜の夜に計画的な買い出しをするため、月曜と木曜に定期配信設定。
f:id:motojapan:20170919224700p:plain

結果

通知されたメールはこんな感じ(やったぜ)。
f:id:motojapan:20170919225936p:plain

雑感

  • GASはかなり簡単にかけた、必要な部分だけで済むので実装が楽
  • デバックやログも簡単に出力可能
  • 翌日だったが、apps-scripts-notifications@google.com からエラーレポートが届くので監視が楽 (下図参考)

f:id:motojapan:20170919225532p:plain


そのうち画像認識と文字認識を組み合わせて、賞味期限リストへの登録を半自動化したいと思う。
それ以外であっても業務効率化で使えそう。

では、はっぴー食べ物らいふを!(今晩はマグロの漬け丼になりました)