CODEWITHSUNDEEP
sortinggoogle-apps-scriptgoogle-appsgoogle-sheets-api
Dogsaysmeow
Dogsaysmeow

Reputation: 1

How do I sort a column in both alphabetical and numerical order

So I've been trying to create a google app script that would allow me to simply order column A2 and downwards, first alphabetically and then numerically. I would like the end outcome to for example, sort: A2, B5, H1, A3, A1, B4, B2. To become: A1, A2, A3, B2, B4, B5, H1, and etc.

So currently my code looks like the following: 

    function onOpen(){
    var ui = SpreadsheetApp.getUi();
    ui.createMenu('Order Adresses')
    .addItem('Order', 'orderNumber')
    .addToUi();
}

function orderNumber() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var editedCell = sheet.getActiveCell();

  var columnToSortBy = 1;
  var tableRange = "A2:A500"; // What to sort.

  if(editedCell.getColumn() == columnToSortBy){   
    var range = sheet.getRange(tableRange);
    range.sort( { column : columnToSortBy, ascending: true } );
  }
}

This code allowed me to sort the column in either alphabetical or numerical. But not both. For example it would sort it in alphabetical but then list the following numbers in the order of 1, 10, 11, 12, 2, 3, 4 and etc.

Upvotes: 0

Views: 1000

Answers (1)

Vytautas
Vytautas

Reputation: 2286

The problem is how sort actually works. In essence you will always be sorting a string and sort evaluates each symbol on it's own. So if you have A1, B4, B10 the sorting algorithm will look at A, B and B, so it will put A1 at the top. Then it will look at B4 and B1, in which case B1 wins and already we get B10, B4.

You would need to re-do the sorting algorithm to suit your needs. If the only format allowed is 1 letter and then any amount of digits then it's relatively easy. First of all you need to use .getValues() to get yourself the array of data. Simply split the string after the 1st position and you will end up with letters and integers in two columns.

Now it's just a matter of sorting a 2d array first by letter and then by digit. Once that is done, join the string with the number and .setValues() back into the sheet.

Upvotes: 1

Related Questions