CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Michał
Michał

Reputation: 61

Delete both duplicated row using Google Scripts app

Is it possible to perform excel sheets formula by google scripts?

I want to delete every single duplicate data in particular column. In this case it will be Col A. So for example if on A16 and A 189 is the same number I want my script to delete both whole row. And to be clear data from B to the end Column are not the same. I want to do it by google script. I was thinking if I could perform exel's functions like countif and where the output is 2 automatically delete both. If not how else could i perform such action

Upvotes: 0

Views: 60

Answers (1)

Cooper
Cooper

Reputation: 64042

Delete all rows that have duplicates and the duplicates

function removeDuplicatesAndRowsThatHaveDuplicatesInColumnA() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getActiveSheet();
  var rg=sh.getDataRange();
  var vA=rg.getValues();
  var dObj={uA:[]};//unique array
  var d=0;//deleted row counter
  //This removes the duplicates
  //skipped first row ... it is a header
  vA.forEach(function(r,i){
    if(i>0) {
      var idx=dObj.uA.indexOf(r[0]);
      if(idx==-1){
        dObj.uA.push(r[0]);
        dObj[r[0]]={row:i+1,value:1};
      }else{
        dObj[r[0]].value+=1;
        sh.deleteRow(i+1-d++);
      }  
    }
  });
  //Logger.log(JSON.stringify(dObj));
  //This sorts the unique array in row descending order
  dObj.uA.sort(function(a,b){return dObj[b].row-dObj[a].row;});
  //this  deletes the unique array elements that had duplicates
  dObj.uA.forEach(function(e,i){if(dObj[e].value>1){sh.deleteRow(dObj[e].row)}});
}

Upvotes: 1

Related Questions