Avoid Duplicate Entries in Google Sheets using Google Apps Script Automation

Avoid duplicate entries in Google Sheets using Google Apps Script by setting up a script that checks new data against existing rows before adding it. This automation compares key columns (like email or ID) and only inserts unique values, keeping your data clean, organized, and free from repetition.

Step 1: Click on the Copy button to copy the code snippet.
Step 2: Paste the copied code into your project’s script editor.

Apps Scripts Blog

Read Blog

Apps Scripts 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();
    }
  }
}