Exception based on condition on Apps Script in Google Sheet-CodePudding

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');
    }
  }
}

CodePudding user response：

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:

CodePudding user response：

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);
    }
  }
}