I'm managing a tracker in google sheets, where users should enter new IDs in column A. I need to prevent them, as well as alert them, whenever their entry is a duplicate (so the ID already exists in column A). Data validation isn't a good option. How should I go about this?

CodePudding user response：

we do with data validation with formula

=countif($B$2:$B,B2)>1

CodePudding user response：

Try this code:

var ss = SpreadsheetApp.getActiveSpreadsheet();
function onEdit(e) {
  var sheet = ss.getActiveSheet()
  var value = sheet.getActiveCell().getValue()
  var r = sheet.getActiveRange().getRow()
  var c = sheet.getActiveRange().getColumn()
  var sheetName = sheet.getName();
  if(value!=="" && c==1 && r>2 && sheetName == "Sheet2"){ // change condition before testing
    var data = sheet.getRange("A2:A").getValues().filter(String).flat() // change rage according to requrments
    if(data.indexOf(value)>-1){
      e.range.setValue("")
    }
  }
}

CodePudding user response：

Clear and alert duplicates

function onMyEdit(e) {
  e.source.toast('Entry');
  const sh = e.range.getSheet();
  if(sh.getName() == "Sheet0" && e.range.columnStart == 1 && e.value !== null) {
    e.source.toast("Flag1")
    let u = [];
    sh.getRange(2,1,sh.getLastRow() - 1).getDisplayValues().flat().forEach(el => {
      if(!~u.indexOf(el)) {
        u.push(el)
      } else {
        e.source.toast("Your have created a duplicate");
        e.range.clearContent();
      }
    }); 
  }
}

Demo: