Sheets Data Validation In Colum "O" is updated when Colum "P" values are +7

Question

I have in Colum "O", on a dropdown list of two values (Yes, No). I got a scrip running that every time I tick it to "Yes", in Colum "P" I get a timestamp of todays date. Now, what I'm looking for is, after 7 days of this timestamp, the Value in the dropdown list in Colum "O" goes back to "No", and while doing so, it clears out the timestamp in Colum "P". This way I could restart the process, of clicking "Yes", get a new timestamp, and after 7 days, it resets itself.

I've been digging deep, but cant find anything helpful.

Regards!

Answer 1

Try this:

function onEdit(e) {
  e.source.toast('Entry');
  Logger.log(JSON.stringify(e));
  var s = e.range.getSheet();
  if (s.getName() == "Sheet0" && e.range.columnStart == 14) {
    e.source.toast('Flag1');
    let dt = new Date();
    if (e.range.offset(0, 1).getValue() === '') {
      e.range.offset(0, 1).setValue(dt);
    }
    let thv = new Date(dt.getFullYear(), dt.getMonth(), dt.getDate() - 7).valueOf();
    s.getRange(1, 15, s.getLastRow()).getValues().flat().forEach((el, i) => {
      if (new Date(el).valueOf() <= thv) {
        s.getRange(i + 1,14,1,2).setValues([["No",""]]);
      }
    })
  }
}