Google Spreadsheet How to delete duplicate rows when first column is different but all other are same?

Question:

( by champ_12345 )

I am trying to delete rows from Google spreadsheet. But my 1st column in spreadsheet is Timestamp which is based on when entry got added in sheet. So that field is always different and I need that field there. I want to delete rows when there are two rows same except timestamp.

Solution:

Have a look at the following screenshots:

Before Executing Script function "DeleteDuplicates()":

After Executing Script function "DeleteDuplicates()":

Have a look at the following code:

///////////////////////////////////////

// This script has been developed by Kishan.
// For more visit iGoogleDrive.blogspot.com

function DeleteDuplicates() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Form Responses 1");
  var dataRange = sheet.getDataRange();
  var v = dataRange.getValues();
  
  var data = [];
  for(var i=0;i<v.length;i++) {
    var temp="";
    for(var j=1;j<v[0].length;j++)
      temp = temp+v[i][j].toString();
    data.push( [temp]);
  }
  
  var dups=[];
  for (var k=0;k<data.length-1;k++)
    for (var l=k+1;l<data.length;l++)
      if (data[k].toString() == data[l].toString())
      dups.push([l+1]);
  
  dups.sort(sortFunction);
  var duplicates = dups.getUnique();
  
  for( var d=duplicates.length-1;d>=0;d--)
    sheet.deleteRow(duplicates[d]);
};

var sortFunction = function(a,b) {
  try{x=a[sortCol].toLowerCase();
      y=b[sortCol].toLowerCase();}
  catch(e){x=a[0];y=b[0];}
  return (x>y)?1:(x<y)?-1:0
};

Array.prototype.getUnique = function(){
  var u = {}, a = [];
  for(var i = 0, l = this.length; i < l; ++i){
    if(u.hasOwnProperty(this[i])) {
      continue;
    }
    a.push(this[i]);
    u[this[i]] = 1;
  }
  return a;

};

///////////////////////////////////////

put the above code in your script editor., and then you can delete all the duplicate rows from Sheet "Form Responses 1"

Change the Sheet name in code if required.


And If you are not much familiar with scripts then check out the following link:
http://igoogledrive.blogspot.com/2012/08/how-to-write-script-in-google.html 

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan.

iGoogleDrive