Removing duplicate rows using Google App Scripts

Question

I have a Google Sheet with two tabs that I want to check rows and delete duplicates from. However, the requirement for both is slightly different.

On tab submittedMatches I have data imported from a Google Form into columns A:C where a script on form submit then grabs data from an API and populates D:J and finally, K:M has a custom formula inserted via a script. The range of data being A2:M

The problem I am having is that a) I'm new to all of this, and b) when I try to remove duplicates (people can submit the same thing twice from the form) the only solution I have found copies all the rows, removes the duplicates and pastes the unique rows again so I lose the formulas.

I have the script checking columns B (this just has a letter from A-Z) and C (a unique ID) for duplicates, e.g., If row 2 and 3 have an "A" in column B and "123456" in column C, then this is a duplicate. For reference, this is what I had working:

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

What I need to happen is the script check for the duplicate rows based on data in columns B and C and remove them, but the formula in K:M must be preserved. Either by just removing the data from A:J or by another method.

Additionally, I'm assuming I need to make sure this is only run on the tab 'submittedMatches' and not other tabs on the sheet.

Adam Sundown Adam Sundown · Accepted Answer · 2021-05-08T14:53:21

I solved this. Sorry. Always the way when you eventually ask for help after hours of trying.

I set a conditional format on B and C with: =countifs($B$1:$B1,$B1,$C$1:$C1,$C1)>1

I set the background colour to #fefefe and made the font red and bold. I then added the below script to check for cells with a #fefefe background and remove them.

function deleteDuplicateMatches() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getSheetByName('submittedMatches');
  
  //Only get range with data (non-empty cells)
  var firstCol = sheet.getDataRange();

  //Save data validation rules based on the first row
  var dataValRules = sheet.getRange('A2:M2').getDataValidations();

  var maxRow = firstCol.getNumRows();
  for (var row=1; row<=maxRow; row++){

    //Delete row with background color of gray #fefefe
    if(sheet.getRange(row,1).getBackground() == "#fefefe"){

      sheet.deleteRow(row);

      //since current row was deleted adjust current data set
      //this will handle scenarios with succeeding duplicates
      row--;
      maxRow--;

      //insert new row at the end and set data validation rules
      sheet.insertRowAfter(maxRow);
      sheet.getRange(maxRow,1,1,13).setDataValidations(dataValRules);
    }
  }
};

Removing duplicate rows using Google App Scripts

1 Answers