GASにて、事前に記入した日にチェックが入り、特定セルに記入が無ければメールで通知するようにしたい。

Question

前提

公開されている備品管理のGASを活用させて頂いています。
B列の指定の日・特定の時間までにスマホでQRコードを読み込んでいるかを判定し、読み込んでいなければメールで通知が飛ぶようにしたいです。

実現したいこと

例えばB3で、時間が8時45分あれば、7/19の8時45分にそのチェックが動き、Eに記入がなければメール通知が飛ぶ、といった形をイメージしています。

定期的な通知であればトリガーで出来そうだったのですが、日付が毎月変わる必要があり、そちらの作り方がわからない状況です。

チェックの時間は8時45分固定で良いのですが、以下のソースコードにどのように追加をして行けば上手くB列の日にちとE列の記入有無判定できるでしょうか。

どうぞよろしくお願い致します。

該当のソースコード

ソースコード
function bihinlabel(){
  var sht = SpreadsheetApp.getActive().getSheetByName('備品');
  var lastRow = sht.getLastRow();
  for (var i=3; i<=lastRow; i++) {
      sht.getRange(i, 3).clear();
      sht.getRange(i, 4).clear();
      var qrc1 = '=\"https://script.google.com/macros/s/\"&$B$1&\"/exec?no=\"&A' + i;
      var qrc2 = '=image(\"https://chart.apis.google.com/chart?chs=250x250&cht=qr&chl=\"&C' + i +')';
      sht.getRange(i, 3).setValue(qrc1);
      sht.getRange(i, 4).setValue(qrc2);
  }
}

コード
 function doGet(e) {
    var no = e.parameter.no;
    var datetime = new Date();
    var today = Utilities.formatDate(datetime,'JST', 'yyyy/MM/dd');
    var userid = Session.getActiveUser().getEmail();
    var sht = SpreadsheetApp.getActive();
    var lastRow = sht.getLastRow();
    const values = sht.getRange(3, 1, lastRow - 2).getValues().flat();
    var r = values.indexOf(no) + 3;
    sht.getRange(r, 5).setValue(datetime);
    sht.getRange(r, 6).setValue(userid);
    return ContentService.createTextOutput(today + "\n" + sht.getRange(r, 2).getValue() + "\n" + userid);
  }

試したこと

定期的な通知であればトリガーで出来そうだったのですが、日付が毎月変わる必要があり、そちらの作り方がわからない状況です。

補足情報（FW/ツールのバージョンなど）

ここにより詳細な情報を記載してください。

Comments

[退会済みユーザー]

・要件でいくつか不明な点があります。
①「Eに記入がなければメール通知が飛ぶ」ということだが、具体的に、どの宛先に対してどのような内容の通知メールを送るのか。
a. あて先は固定[たとえば管理者]なのか、それとも状況ごとに異なる送信先なのか。

b..通知すべき行が複数ある場合、複数まとめて送信するのか、個別に送信するのか。

②画像ではB列に月と日しか記載されていないように見えるが、年は内部で持っていて実際は内部で持っている年と合わせて年月日で判断するのか？
それとも年は持っているが無視してよく「当年の」月/日で判断するのか。
それともB列は文字列書式であり、「当年の」月/日で判断するのか。

例）
a、B列は書式なしテキストで「7月9日」という文字であり、年を持っていない。
2022年の7月9日にチェックを行う。
2023年になったら2023年のの7月9日にもチェックを行う。
2024年になったら2024年の7月9日にもチェックを行う。

ｂ、B列の書式は日付であり「7月9日」という表示であるが、内部では「2022年」という年を持っている。
b-1：チェックを行うのは、2022年の7月9日だけである。（年月日で判断）
b-2：内部で持っている年は無視し、2023年以降毎年の7月9日にもチェックを行う。

③チェックするのは、
[a]B列が当日の月日となっている行だけでよいのか。
それとも
[b]当日以前の月日となっている行すべてをチェックする必要があるのか。
例）
7月29日と7月31日の2行あり、いずれもE列が空欄状態であるとする。
チェック日時を7月31日の8時45分とする。
[a]ならば、7月31日の行についてのみ通知すればよい。
[b]ならば、7月31日以前、すなわち7月29日と7月31日の両方通知する必要がある。

④確認時刻を経過したあとに、その日以前を確認日とするデータが追記されるような場合はありえるのか？
例）「チェック時刻を7月31日の8:45とする。
7月31日の8:45時点では、データとして7月29日の1行しかなく、かつその行はE列にメールアドレスが入力済みの状態であったので、チェックの時点では通知する必要はない。
しかし、同じ日（7月31日の）10:00に、「7月31日」と「7月30日」の行が追加された」
というようなシチュエーションはありえるのか。

[madarakko]

ご返信ありがとうございます。
ご不明な点ですが、

①「Eに記入がなければメール通知が飛ぶ」ということだが、具体的に、どの宛先に対してどのような内容の通知メールを送るのか。
a. あて先は固定[たとえば管理者]なのか、それとも状況ごとに異なる送信先なのか。
⇒宛先は固定で、１つのメールアドレスに飛ばしたいです。
b..通知すべき行が複数ある場合、複数まとめて送信するのか、個別に送信するのか。
⇒「当日」入力が無かったものを通知したいので、当日の1件のみ送信したいです。

②画像ではB列に月と日しか記載されていないように見えるが、年は内部で持っていて実際は内部で持っている年と合わせて年月日で判断するのか？
それとも年は持っているが無視してよく「当年の」月/日で判断するのか。
それともB列は文字列書式であり、「当年の」月/日で判断するのか。
⇒こちらはb-1の「年は持っているが無視してよく「当年の」月/日で判断する」です。

③チェックするのは、
[a]B列が当日の月日となっている行だけでよいのか。
それとも
[b]当日以前の月日となっている行すべてをチェックする必要があるのか。
⇒[a]です。その当日だけで大丈夫です。他の日付は事前に入力しているだけのイメージです。

④確認時刻を経過したあとに、その日以前を確認日とするデータが追記されるような場合はありえるのか？
例）「チェック時刻を7月31日の8:45とする。
7月31日の8:45時点では、データとして7月29日の1行しかなく、かつその行はE列にメールアドレスが入力済みの状態であったので、チェックの時点では通知する必要はない。
しかし、同じ日（7月31日の）10:00に、「7月31日」と「7月30日」の行が追加された」
というようなシチュエーションはありえるのか。
⇒これはありません。抜け漏れチェックのイメージですので、可能性として7月31日の7：00に7月31日と入力され、8:45にチェックが走る可能性はありますが、ここは特別準備をしなくても良い部分かと思われます。

どうぞよろしくお願い致します。

Answer 1

下記のようなコードを追加します。
別途、check 関数を毎日8:45に実行するようにトリガーを使って設定します。

const DATASHEET_ID = 'スプレッドシートのID'
const MAIL_ADDRESS = '****@example.com';  // 送信先メールアドレス

function check() {
  const sht = SpreadsheetApp.openById(DATASHEET_ID).getSheetByName("備品");
  const lastRow = sht.getLastRow();
  const values = sht.getRange(3, 1, lastRow - 2, sht.getLastColumn()).getValues();
  const today = new Date();
  // 当日で未確認の行を抽出（日付列＝B列、空欄チェック列＝E列）
  const records = getUncheckedRecords(values, today, 2, 5);
  if (records.length === 0) {
    console.log('条件にあてはまる行はありませんでした。');
    return;
  }
  const items = records.map(e => e[0]);
  const title = `${Utilities.formatDate(today, 'Asia/Tokyo', 'M月d日')}：未確認通知`;
  const body = `次の備品が未確認です。\n${items.join('\n')}`;
  MailApp.sendEmail(MAIL_ADDRESS, title, body);
}

/**
 * 指定した２次元配列データから、指定した日付にあてはまる行のうち、未確認のもの（確認列が空欄であるもの）を返します。
 * @param  {Number[][]} sourceData ：検索対象となるスプレッドシートのデータ
 * @param  {Date} targetDate ：検索する日付
 * @param  {Number} dateColumn ：sourceData のうち日付列が左から何列目か（1 始まり）
 * @param  {Number} checkColumn ：sourceData のうち空欄を確認する列が左から何列目か（1 始まり）
 * @return　{Number[][]} 指定した日付（targetDate ）にあてはまるレコードのうち、
 *  確認列が空欄であるデータ（2次元配列）
*/
function getUncheckedRecords(sourceData, targetDate, dateColumn, checkColumn) {
  console.log(`targetDate=${targetDate}`);
  console.log(`Type of targetDate= ${Object.prototype.toString.call(targetDate)}`);
  console.log(`dateColumn=${dateColumn}`);
  console.log(`checkColumn=${checkColumn}`);
  console.log(sourceData[0].length);
  return sourceData.filter((row, i) => {
    let srcMonth = '';
    try{
     srcMonth = row[dateColumn - 1].getMonth();
    }catch(e) {
      console.log(`i= ${i}`);
      console.log(`row[dateColumn - 1]= ${row[dateColumn - 1]}`);
      console.log(`Type of row[dateColumn - 1]= ${Object.prototype.toString.call(row[dateColumn - 1])}`);
      return;
    }

    const srcDate = row[dateColumn - 1].getDate();
    const orgMonth = targetDate.getMonth();
    const orgDate = targetDate.getDate();
    if (srcMonth === orgMonth && srcDate === orgDate &&
      row[checkColumn - 1] === '') {
      return true;
    }
  });
}

Comments

[madarakko]

ありがとうございます。非常に助かります。
頂いたコードを追記してみたのですが、3カ所エラーとなります。
エラー
TypeError: row[(dateColumn - 1)].getMonth is not a function
(匿名) @ コード.gs:45
getUncheckedRecords @ コード.gs:44
check @ コード.gs:23

どのように対応すればよろしいでしょうか。
よろしくお願い致します。

[退会済みユーザー]

コードを、エラーがおきたときにエラー内容が確認できるように修正しました。
こちらのコードで試しに実行していただき、ログに表示される内容を省略せず全部記載してください。

[madarakko]

ありがとうございます！通知メールが届くようになりました！感謝致します。