Avoid the same date in a column for maximum 10 times in Google Sheet-CodePudding

I would to find a way to avoid to insert in the column A of a spreadsheet the same date for maximum 10 times, and so if in the column A there is already the same date 10 times, if a user try to insert another time the same date, a popup will alert that it is not possible.

I am trying with Apps Script writing the code below, but it works only with alphabetical values and not with dates.

Maybe I have to format something?

function onEdit(e) {
  var r = e.range;
  var s = r.getSheet();
  if(s.getName()==='Foglio1' && r.getColumn()===1) {
    var newValue = e.value;
    var b = s.getRange('A1:A');
    var bv = b.getValues();
    
    var count = 0;
    var flag = false;
    for(var i=0;i<bv.length;i  ) {
      if(bv[i][0]===newValue)
        count  ;
      if(count>10) {
        flag = true;
        break;
      }
    }
    
    if(flag) {
      r.setValue(e.oldValue);
      SpreadsheetApp.flush();
      SpreadsheetApp.getUi().alert('This date is already inserted 10 times');
    } 
  }
}

CodePudding user response：

For example, as a simple modification, how about using getDisplayValue as follows?

Modified script 1:

function onEdit(e) {
  var r = e.range;
  var s = r.getSheet();
  if (s.getName() === 'Foglio1' && r.getColumn() === 1) {
    var newValue = r.getDisplayValue();
    var b = s.getRange('A1:A');
    var bv = b.getDisplayValues();
    var count = 0;
    var flag = false;
    for (var i = 0; i < bv.length; i  ) {
      if (bv[i][0] === newValue) {
        count  ;
      }
      if (count > 10) {
        flag = true;
        break;
      }
    }
    if (flag) {
      r.setValue(e.oldValue);
      SpreadsheetApp.flush();
      SpreadsheetApp.getUi().alert('This date is already inserted 10 times');
    }
  }
}

Modified script 2:

As other method, in this modification, filter is used for counting the values.

function onEdit(e) {
  var r = e.range;
  var s = r.getSheet();
  if (s.getName() === 'Foglio1' && r.getColumn() === 1) {
    var newValue = r.getDisplayValue();
    if (newValue == "") return;
    var count = s.getRange('A1:A').getDisplayValues().filter(([a]) => a === newValue).length;
    if (count > 10) {
      r.setValue(e.oldValue);
      SpreadsheetApp.flush();
      SpreadsheetApp.getUi().alert('This date is already inserted 10 times');
    }
  }
}

Modified script 1:

Modified script 2:

References: