Search code examples
javascriptgoogle-apps-scriptgoogle-sheetsgoogle-apps

How do I pre-sort numbers and not screw up the formatting?


I have a custom function I am writing that will return an array of arrays:

function tester(col){
  var rows = [ // this gives me an error on sorting, so turn numbers to strings
    [4,2,3,"Tom"],
    [0,8,9,"Bill"],
    [5,7,1,"Bob"],
    [1,2,3,"Charlie"]
  ];

  rows = [ // turning numbers to strings screws up formatting
    ["4","2","3","Tom"],
    ["0","8","9","Bill"],
    ["5","7","1","Bob"],
    ["1","2","3","Charlie"]
  ];
   rows.sort(function(a, b) {
   return a[col].localeCompare(b[col]); // sort by column passed by user
  });
  return rows;
}

As noted, if I pass in numbers I get an error:

TypeError: Cannot find function localCompare in object

If I turn those numbers into strings I can sort but then the user isn't able to format the numbers...show more decimals, commas, turn them into percentages, etc. How do I resolve this?

EDIT:

I've tried the suggestion by Buzinas/Kriggs but it seems to do a lexical sort for numbers and negatives and others don't sort properly. I now have (notice the negative 4 and 750):

function tester(col){
  var rows = [
    [-4,2,3,"Tom"],
    [0,8,9,"Bill"],
    [5,750,1,"Bob"],
    [1,2,3,"Charlie"]
  ];

  rows.sort(function(a, b) {
     return a[col].toString().localeCompare(b[col].toString());
  });

  return rows;
}

Solution

  • UPDATE

    Since you want to sort by number if they are numbers, and by string if they are strings, you can do:

    function tester(col){
      var rows = [
        [4,2,3,"Tom"],
        [0,8,9,"Bill"],
        [5,7,1,"Bob"],
        [1,2,3,"Charlie"]
      ];
    
      rows.sort(function(a, b) {
        if (typeof a[col] === 'number')
          return a[col] > b[col];
        return a[col].localeCompare(b[col]);
      });
    
      return rows;
    }