OnOpen/OnEdit Pop-Up Based on Cell Value-CodePudding

I'm new to Google Scripts and what I'm basically trying to do is: if the word "LATE" appears anywhere in the spreadsheet, I want a pop-up to appear with the phrase "Please check the master tracker" and I want to send an email to a list of specified people.

I've experimented with the onOpen function, but as that runs the script literally every time someone opens the spreadsheet that's not what I'm looking for.

    function onOpen() {
       var ui = SpreadsheetApp.getUi();
       ui.alert('Please check the master tracker');
       }""

I've also played around with the onEdit function because the word "LATE" is set in the Google Sheets function bar to appear based on a specified number of days per task. For example, an item request has a 5 day lag time:

      =IF(TODAY()>=C21 5, "LATE","")

A review request has a 2 day lag time:

      =IF(TODAY()>=C21 2, "LATE","")

And an executive review request has a 14 day lag time:

      =IF(TODAY()>=C21 14, "LATE","")

However, the onEdit function only runs when a user makes changes, not when a function makes changes.

So how can I get a popup to run whenever the word "LATE" appears in any of the sheets, when the word "LATE" is part of an automatic function? Any help or guidance would be tremendously appreciated, thank you so much for your time.

CodePudding user response：

Polling once a minute

function myfunk {
  const ss = SpreadsheetApp.getActive();
  if(ss.createTextFinder("LATE").findAll().length > 0) {
    SpreadsheetApp.getUi().alert("Please check the master tracker");
    let sh = ss.getSheetByName("Sheet1");
    let recipients = sh.getRange(2,1,sh.getLastRow() - 1).getValues().flat().join(",");
    GmailApp.sendEmail(recipients,"Subject","Message");
  }
}

Polling trigger:

function createMyTrigger() {
  if(ScriptApp.getProjectTriggers().filter(t => t.getHandlerFunction() == "myfunk").length == 0) {
    ScriptApp.newTrigger("myfunk").timeBased().everyMinutes(1).create();
  }
}

ScriptApp