Exception based on condition on Apps Script in Google Sheet

Question

With this script I can exclude to insert the same value column in Google Sheet for maximum 100 times.

But I am trying to exclude (with if statement) some values from this script, in particular the date "25/12/2022" and the date "12/01/2012".

How could I proceed?

function onEdit(e) {
  var r = e.range;
  var s = r.getSheet();
  if (s.getName() === 'New Rise 2022' && r.getColumn() === 27) {
    var newValue = r.getDisplayValue();
    if (newValue == "") return;
    var count = s.getRange('AA1:AA').getDisplayValues().filter(([a]) => a === newValue).length;
    if (count > 99) {
      r.setValue(e.oldValue);
      SpreadsheetApp.flush();
      SpreadsheetApp.getUi().alert('Questa data è stata già inserita 100 volte');
    }
  }
}

Update:

function onEdit(e) {
  var r = e.range;
  var s = r.getSheet();
  if (s.getName() === 'New Rise 2022' && r.getColumn() === 27) {
    var newValue = r.getDisplayValue();
    if (newValue == "") return;
    var count = s.getRange('AA1:AA').getDisplayValues().filter(([a]) => a === newValue).length;
    if (count > 99 || e.range.getDisplayValue() == "25/12/2012" || e.range.getDisplayValue() == "12/01/2012") {
      r.setValue(e.oldValue);
    r.setNumberFormat('dd/mm/YYYY'); 
      SpreadsheetApp.flush();
      SpreadsheetApp.getUi().alert('Questa data è stata già inserita 100 volte');
    }
  }
}

Answer 1

How about this?

function onEdit(e) {
  const sh = e.range.getSheet();
  const x = ["25/12/2022","12/01/2012"];
  const idx = x.indexOf(e.value);
  if (sh.getName() === 'New Rise 2022' && e.range.columnStart == 27 && e.value && !~idx) {
    var count = sh.getRange('AA1:AA' + sh.getLastRow()).getDisplayValues().flat().filter(e => e == e.value).length;
    if (count > 99) {
      e.range.setValue(e.oldValue);
    }
  }
}

Answer 2

You can get the newly entered display value and compare it against the "forbidden" values

Therefore, retrieve the latest modified cell with e.range:

    ...
    if (count > 99 || e.range.getDisplayValue() == "25/12/2022" || e.range.getDisplayValue() == "12/01/2012") {
      ...
    }
   ...

Note:

I understood that what you are interested in is the displayed value (date in this case), but depending on your date formatting the display value will be different from the value you typed in.

If it is the typed in value you are after, you can retrieve it with e.value:

    ...
    console.log("e.value: " + e.value)
    console.log("e.range.getDisplayValue(): " + e.range.getDisplayValue())
    if (count > 99 || e.value == "25/12/2022" || e.value == "12/01/2012") {
      ...
    }
   ...

References:

• Event Objects
• getDisplayValue()

UPDATE:

If you have problems with number formatting you can use the method setNumberFormat().

Modify your code block in the if statement to

r.setValue(e.oldValue);
r.setNumberFormat('dd/mm/YYYY');