CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Peleke
Peleke

Reputation: 961

Add a cell value for duplicate rows

I have created a script that deletes duplicate rows in a Google Sheet:

function removeDuplicates() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var newData = new Array();
  for(i in data){
    var row = data[i];
    var duplicate = false;
    for(j in newData){
      if(row[1] == newData[j][1]){
        duplicate = true;
      }
    }
    if(!duplicate){
      newData.push(row);
    }
  }
  sheet.clearContents();
  sheet.getRange(1, 1, newData.length, newData[0].length).setValues(newData);
}

Now I want to modify it to NOT delete the duplicate rows but instead adding "DUPLICATE: " at the beginning of the detected duplicates in both rows/cells (the script is scanning only column 2 (B)) and color them orange but I cannot find out how to do that.

Upvotes: 0

Views: 208

Answers (1)

user6655984
user6655984

Reputation:

You still need a double loop, but it's not necessary to have newData array; the data array can be modified in place. If two rows match in column B, both get "Duplicate" in Column A. Explicit bounds in for loops make it easy to avoid comparing a row with itself. 

var data = sheet.getDataRange().getValues();
for (i = 0; i < data.length; i++) {
  for (j = 0; j < i; j++) {
    if (data[i][1] == data[j][1]) {
      data[i][0] = "Duplicate";
      data[j][0] = "Duplicate";
    }
}
sheet.clearContents();
sheet.getRange(1, 1, data.length, data[0].length).setValues(data);

By the way, this logic could be implemented without a script, using Google Sheets formulas such as 

=if(countif(B:B, B2) > 1, "Duplicate", )

(This is placed in A2, and copied down the column)

Or, as an array formula in A2:

=arrayformula(if(countif(B:B2, B:B2) > 1, "Duplicate", ))

Upvotes: 2

Related Questions