Apps Script not deleting rows

Question

When I run the following function in my sheet:

    function removeDuplicateRows() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var column = sheet.getRange('D580:D');
  var data= column.getValues();
  var newData = new Array();
  for(i in data){

    var row = data[i];
    var duplicate = false;
    for(j in newData){

      if(row.join() == newData[j].join()){

        duplicate = true;

      }

    }
    if(!duplicate){
      newData.push(row);

    }
}
sheet.clearContents();
sheet.getRange(1, 1, newData.length, newData[0].length).setValues(newData);

}

Nothing happens, it just times out. It seems to be due to my 'column' variable when I identify the range. If I leave it blank it runs but I don't want it to hit the first 580 rows. Anybody out there know why this happens?

Thanks for any help,

Review your algorithm. Double nested for is bad. Especially when you are missing short-circuit logic steps, like stopping the inner loop once you know you have a duplicate. But, there's much better methods than storing things in an array and traversing that array every time. See some of my answers about duplicate detection. — tehhowch
– tehhowch, Commented Mar 12, 2019 at 18:19
PS: this code snippet can't possibly delete rows - you never call Sheet#deleteRow — tehhowch
– tehhowch, Commented Mar 12, 2019 at 18:21
It works on my test spreadsheet (less data). I didn't put in Sheet#deleterow? — Bunny
– Bunny, Commented Mar 12, 2019 at 18:28

Cooper · Accepted Answer · 2019-03-12 18:31:30Z

1

Try this:

function removeDuplicates() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getActiveSheet();
  var rg=sh.getRange(580,4,sh.getLastRow(),1);
  var vA=rg.getValues();
  var uA=[];
  var dC=0;
  for(var i=0;i<vA.length;i++) {
    if(uA.indexOf(vA[i].join())==-1) {
      uA.push(vA[i].join());
    }else{
      sh.deleteRow(i + rg.getRow() - dC);
      dC++;
    }
  }
}

answered Mar 12, 2019 at 18:31

Cooper

64.2k7 gold badges29 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Apps Script not deleting rows

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related