0

I have a google spreadsheet which contains the multiple data spread across various columns (like 6 columns - Phone number, IMEI, URL, Id, Reg No and Time) and i have 15K rows of data.

I have to find/show all the duplicate phone number(Column 0) and data associated with them in a new sheet.

However when i try to sort the data, it throws me error like

"Comparison method violates its general contract. (line 6, file "Code")"

The code is shown below for your reference:

function myFunction() {

  var values  = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('DUP').getDataRange().getValues()

  var dupData = new Array();

  values.sort(function sortFunction(a, b) {
    if (a[0] === b[0]) {
        return 0;
    }
    else {
        return (a[0] < b[0]) ? -1 : 1;
    }
});

  for(i=1;i<values.length;i++)
  {
    if (values[i][0] == values[i+1][0]) {
        dup.push(values[i]);
    }
  }

   var new1 = dpnum.getSheetByName('Test123');
  new1.getRange(1,1,dup.length,dup[0].length).setValues(dup);

}
Benami
  • 1
  • 1
  • 3
  • Your script works correctly in my spreadsheet. Please give a link to a shared spreadsheet in which the problem occurs. –  Jul 20 '16 at 19:24
  • https://docs.google.com/spreadsheets/d/1qvd2rxwu_ZxMd_FBCoItIKvvAJStadmHmr3SwtSkco0/edit#gid=0 – Benami Jul 22 '16 at 06:49
  • https://docs.google.com/spreadsheets/d/1qvd2rxwu_ZxMd_FBCoItIKvvAJStadmHmr3SwtSkco0/edit#gid=0 @PeterSmith can i get your mail ID so that i can share similar spreadsheet – Benami Jul 22 '16 at 06:51

1 Answers1

0

Your code can be simplified like this (comments in code) :

function myFunction() {
  var values  = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('DUP').getDataRange().getValues()  
  var dupData = new Array();  
  values.sort(function(x,y){  // sort on first column
    var xp = x[0];
    var yp = y[0];
    return xp < yp ? -1 : xp > yp ? 1 : 0;// return 0 if equal, 1 if > and -1 if <
  });  
  for(var i=1 ; i<values.length ; i++){
    if (values[i-1][0] == values[i][0]) { // I changed the index as well to keep only valid data
      dup.push(values[i]);
    }
  } 
  var new1 = dpnum.getSheetByName('Test123');
  new1.getRange(1,1,dup.length,dup[0].length).setValues(dup);  
}
Serge insas
  • 45,904
  • 7
  • 105
  • 131
  • thank you so much for your time..i tried the following codes but still these are not working..my main purpose is to find the duplicate phone numbers along with other five associated data with phone number..please help me with the code – Benami Jul 21 '16 at 17:27
  • error message // Comparison method violates its general contract. (line 7, file "Code")Dismiss – Benami Jul 21 '16 at 17:28
  • function myFunction() { var values = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('DUP').getDataRange().getValues() var dupData = new Array(); values.sort(function(x,y){ var xp = x[0]; var yp = y[0]; return xp < yp ? -1 : xp > yp ? 1 : 0; }); for(var i=1 ; i – Benami Jul 22 '16 at 08:17