user6415669 user6415669 - 6 months ago 23
Javascript Question

Delete Duplicate rows on Google sheet by comparing only certain values on certain columns

Sheet 1

Timestamp |Name | Shift.....| Date....| Letter..|
19/05/2016 15:44:46|Mark | Swap | 02/AUG | S |
Timestamp2 |Ivon | Give away | 25/ AUG | G |
Timestamp3 |Laura| Take | 01/AUG | A |
Timestamp4 |Ann | Take | 02/AUG | A |
Timestamp5 |Ann | Give away | 03/AUG | G |
Timestamp6 |Laura| Delete | 01/AUG | D |


As this is a google sheet, I am trying to run the below script to delete rows which matches Name and Date Columns. E.G. Laura has 2 entries on the same date of 01AUG. Therefore I want both rows to be deleted. I am trying the following script but it doesn't work.

function removeDuplicates() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Form responses 2");
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[0] == newData[j][1] && row[1] == newData[j][3]){
duplicate = true;
}
}
if(!duplicate){
newData.push(row);
}
}
sheet.clearContents();
sheet.getRange(1, 1, newData.length, newData[0].length).setValues(newData);
}

Answer

v2.

function duplDel()
{//if Bx === By and Dx === Dy, delete row x && y

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Form responses 2");

  var bob = []; //colB values
  var dod = []; //colD values
  var nummel = [0]; //rows to be deleted. dunno if shorter variables are faster

  for (i=2 ; i<sheet.getLastRow()+1 ; i++){
    var bi = sheet.getRange(i, 2).getValue();
    var di = sheet.getRange(i, 4).getValue();

    for (g=0 ; g<bob.length;g++){ //i is row in question, g is known row + 2

      if(String(bi)==String(bob[g])){ //short-circuit?
        var b = true;
        if(String(di)==String(dod[g])){ //don't check if b is not true
          var d = true;
        }else{var d = false}
      }else{
        var b=false;
        var d=false;
      }

      /*the next if(){} would push even though the value is already in nummel
          meaning, nummel.sort() could be [0,1,1,1,1,1,1,1,2,2,2,...etc]
            but I think it's better than running another loop.
          But, nummel.sort() and another if(condition){nummel.push()}
            might be able to pull it off.
      */
      if(b&d){
        nummel.push(i);
        nummel.push(g+2);
        Logger.log(i + "i and g+2" +(g+2))
      }
    }
    Logger.log(bi+" bi and di "+di);
    bob.push(bi);
    dod.push(di);
  }
  nummel.sort();
  Logger.log(nummel);
  for (i=nummel.length-1;i!=0;i--){if(nummel[i-1]!==nummel[i]){sheet.deleteRow(nummel[i])}}
}