How can I highlight duplicate rows in my sheet based on column value?

Question

I am trying to highlight duplicate rows in my sheet by checking for the same email address entered under the 'Email Address' column.

I have some code (below) that does this - it looks for duplicate rows based on repeated values under 'Email Address' and highlights them red. However, once I revisit the sheet, manually remove the duplicate row and rerun the script, the same row is highlighted again. Why is this happening and what can I do to ensure that when I update the sheet, the (now) unique row is not highlighted again?

function findDupes() {
  var CHECK_COLUMNS = [3];

  var sourceSheet = SpreadsheetApp.getActiveSheet();
  var numRows = sourceSheet.getLastRow();
  var numCols = sourceSheet.getLastColumn();

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var newSheet = ss.insertSheet("FindDupes");


  for (var i = 0; i < CHECK_COLUMNS.length; i++) {
    var sourceRange = sourceSheet.getRange(1,CHECK_COLUMNS[i],numRows);
    var nextCol = newSheet.getLastColumn() + 1;
    sourceRange.copyTo(newSheet.getRange(1,nextCol,numRows));
  }


  var dupes = false;
  var data = newSheet.getDataRange().getValues();
  for (i = 1; i < data.length - 1; i++) {
    for (j = i+1; j < data.length; j++) {
      if  (data[i].join() == data[j].join()) {
        dupes = true;
        sourceSheet.getRange(i+1,1,1,numCols).setBackground("crimson");
        sourceSheet.getRange(j+1,1,1,numCols).setBackground("crimson");
      }
    }
  }

  ss.deleteSheet(newSheet);

  if (dupes) {
    Browser.msgBox("Possible duplicate(s) found. Please check for repeat attendees.");
  } else {
    Browser.msgBox("No duplicates found.");
  }
};

I want to be able to run the script again once I have manually removed the rows and have it reflect the updated nature of the sheet.

Answer 1

Try adding this before the for loops. It will turn all the rows into white, and then the rest of your code will turn the duplicates red:

 sourceSheet.getRange(2,1,numRows,numCols).setBackground("white");

Also, you can add an onEdit() function to check for duplicates in real time, when you add or edit an email address:

function onEdit(e){

  if (e.range.getColumn() == 3){
    var sourceSheet = SpreadsheetApp.getActiveSheet(); 
    var numRows = sourceSheet.getLastRow();
    var numCols = sourceSheet.getLastColumn();

    var data = sourceSheet.getRange("C2:C"+numRows).getValues();
    var editedCell = e.value;

    for (var i = 0; i < data.length;i++){ 
      if (editedCell == data[i] && (i + 2) != e.range.getRow()){
        sourceSheet.getRange(e.range.getRow(),1,1,numCols).setBackground("crimson");
        sourceSheet.getRange(i+2,1,1,numCols).setBackground("crimson");
      }

    }
  }
}

Answer 2

Try this:

Sorry, but there were so many things I couldn't understand why you were doing them that I find it easier just to show you how I'd do it.

function findAndHighlightDupesInColumn(col) {
  var col=col||3;//I think you wanted to check column 3
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getActiveSheet();
  var rg=sh.getDataRange();
  var vA=rg.getValues();//gets all data
  var uA=[];//this is the array that hold all unique values
  for(var i=1;i<vA.length;i++) {//assumes one header row
    if(uA.indexOf(vA[i][col-1])==-1) {//if it's unique then put it in uA
      uA.push(vA[i][col-1]);
    }else{//if it's not unique then set background color
      sh.getRange(i+1,1,1,sh.getLastColumn()).setBackground('crimson');
    }
  }
}

The following code will remove duplicates in column 3:

This method assumes that the first occurrence of any row is the row that you wish to keep. All other duplicate rows are deleted.

function removeColumnDupes(col) {
  var col=col||3
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getActiveSheet();
  var rg=sh.getDataRange();
  var vA=rg.getValues();
  var uA=[];
  var d=0;
  for(var i=1;i<vA.length;i++) {
    if(uA.indexOf(vA[i][col-1])==-1) {
      uA.push(vA[i][col-1]);
    }else{
      sh.deleteRow(i+1-d++);
    }
  }
}