Avoid Duplicate Entries in Google Sheets using Google Apps Script Automation

function checkForDuplicates() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange();
  var values = range.getValues();

  var activeRange = sheet.getActiveRange();
  var newRow = activeRange.getRow();
  var newCol = activeRange.getColumn();
  var newValue = activeRange.getValue();

  // ✅ Stop if cell is empty
  if (!newValue || newValue.toString().trim() === "") {
    return;
  }

  var newValueLower = newValue.toString().toLowerCase();
  var duplicateCells = [];

  // ✅ Loop through all cells to find duplicates
  for (var row = 0; row < values.length; row++) {
    for (var col = 0; col < values[row].length; col++) {
      var cellValue = values[row][col];

      if (cellValue && cellValue.toString().toLowerCase() === newValueLower) {
        var isSameCell = (row + 1 === newRow && col + 1 === newCol);
        if (!isSameCell) {
          // Convert column number to letter (A, B, C…)
          var colLetter = columnToLetter(col + 1);
          duplicateCells.push(colLetter + (row + 1));
        }
      }
    }
  }

  // ✅ Show alert if duplicates found
  if (duplicateCells.length > 0) {
    var message =
      "⚠️ Duplicate value found!\n\n" +
      "The value '" + newValue + "' already exists in the following cells:\n" +
      duplicateCells.join(", ") + "\n\n" +
      "Do you still want to keep it?";

    var response = Browser.msgBox("Duplicate Detected", message, Browser.Buttons.YES_NO);

    if (response === "no") {
      sheet.getRange(newRow, newCol).clearContent();
    }
  }
}

/**
 * Helper function: Convert column number to letter (e.g., 1 → A, 27 → AA)
 */
function columnToLetter(column) {
  var letter = "";
  while (column > 0) {
    var temp = (column - 1) % 26;
    letter = String.fromCharCode(temp + 65) + letter;
    column = Math.floor((column - temp - 1) / 26);
  }
  return letter;
}