AppSScript Code

Copy Code

function checkForDuplicates() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange();
  var values = range.getValues();
  var newRow = sheet.getActiveRange().getRow();
  var newCol = sheet.getActiveRange().getColumn();
  var newValue = sheet.getActiveRange().getValue().toString().toLowerCase();
  var duplicateCells = [];
  
  for (var row = 0; row < values.length; row++) {
    for (var col = 0; col < values[row].length; col++) { if (values[row][col].toString().toLowerCase() === newValue && (row+1) != newRow && (col+1) != newCol) { var duplicateRow = row + 1; var duplicateCol = col + 1; duplicateCells.push(String.fromCharCode(64 + duplicateCol) + duplicateRow); } } } if (duplicateCells.length > 0) {
    var response = Browser.msgBox("Duplicate value found!", "The value '" + sheet.getActiveRange().getValue() + "' already exists in the following cells:\n" + duplicateCells.join(", ") + ".\n\nDo you still want to add it?", Browser.Buttons.YES_NO);
    if (response == "no") {
      sheet.getRange(newRow, newCol).clearContent();
    }
  }
}