In Google Sheets, how can I set up an alert if a cell has NOT been updated?

Question

I want to get an alert from Google Sheets if, once a date in a cell has passed, another cell is still blank. In other words, if no one has filled out a cell by a certain date, I want to get an alert. I believe this would have to be separate from the sheet in question, since I'm looking for a lack of activity in the sheet. I hope I'm making sense.

I can create alerts, calendar entries, etc., from a spreadsheet based on cell data. But that only works if the sheet is open. I need something that will basically tell me that no one has opened the sheet and entered data. Along the lines of, "Hey, team, it's xx/xx/xxxx and nobody has updated sheet y."

Answer 1

It's not clear if you want to get the last time the sheet was modified or only a certain cell. If it's the full spreadsheet, you can try with an approach like this. Adapt the tolerance as you need:

function lastupdate(){
  tolerance_in_hours = 24
  const current = new Date()
  id = "put_your_id"
  var last_update = DriveApp.getFileById(id).getLastUpdated()
  var difference = (current - last_update)
  var difference_in_hours = difference/1000/60/60
  Logger.log(difference_in_hours)

  if(difference_in_hours > tolerance_in_hours){
    //place your code here
  }
}

If you just need to check if certain cell is empty, you can try with:

function checkcell(){
 
 if(SpreadsheetApp.openById(put_your_id).getSheetByName(put_your_name).getRange(put_your_range) == "")
  { insert your code}
}