google app script array delete duplicate value from top

Question

I've got this working code that removes duplicates by comparing values in column B. If row 2 and 3 have the same values in column B then row 3 is deleted.

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);
}

How can I change this code to build the array from edit this so that instead of row 3 being deleted, row 2 is deleted?

Cooper · Accepted Answer · 2017-09-09 17:28:07Z

1

I think this will do it.

  function removeDuplicates() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getActiveSheet();
  var rg=sh.getDataRange();
  var row=rg.getRow();
  var col=rg.getColumn();
  var vA=rg.getValues();
  var nA=[];
  var duplicate=true;
  for(var i=0;i<vA.length;i++)
  {
    duplicate=false;
    for(var j=0;j<nA.length;j++)
    {
      if(vA[i][1]==nA[j][1])
      {
        duplicate=true;
        nA[j]=vA[i];
      }
    }
    if(!duplicate)
    {
      nA.push(vA[i]);
    }
  }
  rg.clearContent();
  sh.getRange(row, col, nA.length, nA[0].length).setValues(nA);
}

The outer loop is iterating through all of the rows of the active sheet and each time through it sets duplicate to false. The inner loop searches through nA[] looking for columnB matches if it finds one it sets duplicate to true. If duplicate is true then it doesn't get added to nA[]. The first time through nA.length is 0 so the inner loop doesn't do anything duplicate is false and so that element gets added to nA[]. It keeps doing this until there are no more rows and the rows that are in nA become the unique row. That's the way it use to run when I first did it. But since you wanted to keep the last duplicate instead of the first then I added nA[j]=vA[i]; which replaces the current element with the current match.

Just setup some fake data and play with it and you'll start to see how it works.

edited Sep 9, 2017 at 17:28

answered Sep 8, 2017 at 23:48

Cooper

64.2k7 gold badges29 silver badges65 bronze badges

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

3 Comments

testing123 Over a year ago

That doesn't seem to do anything at all.

Cooper Over a year ago

Sorry about that. Sometimes I fail to read the entire question. But I think this is what you want. Now the last duplicate is the one that is kept.

testing123 Over a year ago

Can you explain in plain English what's happening with the code? Code works well. Apparently my brain does not. I've added comments to what I think is going on. Thanks.

Collectives™ on Stack Overflow

google app script array delete duplicate value from top

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related