[GAS×LINE] シェアハウスのLINEグループにゴミ収集日を通知してみた②

Teddyです。

以前公開したゴミ収集日通知botの記事にて、スプレッドシート部分についてどう作ったかお問い合わせいただきました。なので、今回はスプレッドシート部分について説明しようと思います。

[GAS×LINE] シェアハウスのLINEグループにゴミ収集日を通知してみた

フラグ機能

まず、このスプレッドシートには以下のフラグ機能を持たせました。

  1. 除外フラグ
    次の日がゴミ収集の除外日かどうかを表しています。僕の住んでいる地域では、年末年始はゴミ収集がお休みのため、年末年始がゴミ収集日であっても通知を除外させるために使用します。
  2. 通知フラグ
    そのゴミ種別が次の日に収集日であるかどうかを表しています。第何週目かと曜日から通知するかどうかを判断させています。例えば、「不燃ごみ」は「第1週、第3週」の「金曜日(数字でいうと6)」の場合、通知フラグが「1」になります。

明日の日付と除外日を定義する

除外フラグを計算するために必要な「明日の日付」と「除外日」をセルに定義します。ここでのポイントは、「年」の情報を除外する点です。
除外フラグは、対象の日付が除外日と一致するかどうかで判断させています。そのため「年」の情報が含まれていると、「年」まで同じでないと一致していると単純に判断ができません。
計算時に省くことも考えましたが、シンプルイズベストにするために、日付を定義する時点から「年」の情報を除外しています。

除外フラグを計算する

除外フラグは「明日の日付」が「除外日リスト」に含まれているかどうかを見て値を変化させています。COUNTIF関数は、条件に合うセルの個数を求めることができます。以下のように記述することで、緑の値紫の範囲の値のうちいづれかと一致する場合は1、そうでなければ0を返します。この仕組みを利用して、「明日の日付」が「除外日リスト」に含まれているかどうかを1と0で表現しています。

その月の第何週かと曜日を定義する

通知フラグを計算するために必要な「その月の第何週か」と「曜日」をセルに定義します。曜日はWEEKDAY関数で計算することができます。しかし、「その月の第何週か」はちょっと複雑な計算をします。

対象日がその月の第何週なのかは次の式で表現することができます。

=WEEKNUM(対象日)-WEEKNUM(その月の1日)+1

WEEKNUM関数はその年の第何週かを計算することができるので、「年の 週数」ー「月初日の週数」+ 1 でその月の第何週なのかを割り出すことができます。

通知フラグを計算する

通知フラグは、「その月の第何週か」と「曜日」がゴミ収集スケジュールと一致するかどうかを見て値を変化させています。除外フラグの時と同様に、COUNTIF関数を使用しています。
除外フラグとの違いは条件が2つある点です。以下2つの条件が両方当てはまる場合のみ、フラグが1となります。

  • ゴミ収集スケジュールの第X週かどうかと、明日の日付の第何週かが一致すること
  • ゴミ収取スケジュールの収集曜日と、明日の日付の曜日が一致すること

まとめ

駆け足に説明してしまったのですが雰囲気は理解いただけましたでしょうか。このあたりの計算部分をスプレッドシートにて表現することで、エクセルのように複雑な規則性も表現でき、引っ越しなどで曜日が変更になっても対応しやすいくなっています。今回はゴミ収集日の通知という目的で作成しましたが、まだまだ別の用途に流用できると思います。こんな通知アプリがあると便利だなーなどアイデアいただけると嬉しいです。

 

コメントを残す

メールアドレスが公開されることはありません。