CODEWITHSUNDEEP
javascriptsortinggoogle-apps-scriptgoogle-sheets
Dunkstormen
Dunkstormen

Reputation: 105

Sort data in google sheets (with custom sort order) using google scripts

As the title says, I need to sort my data depending on my first column which contains som rank names. I do not wan't it sorted alphabetically but rather in a order I define myself.

Example:

Admin_contact
Commissioner
Battalion Chief
Paramedic

I want them listed in that order using google scripts so it automatically sorts when data gets edited.

Hope someone can help me achieve this

Upvotes: 2

Views: 3472

Answers (2)

Max Makhrov
Max Makhrov

Reputation: 18707

Try this code:

function customSort(array, arrayOrder, numColumn) {
    var order = [];

    // transpose vertical 2d array into 1d array
    for (var i = 0; i < arrayOrder.length; i++) {
      order.push(arrayOrder[i][0]);    
    }

    // sort
    return array.sort(function (a, b) {
        return order.indexOf(a[numColumn-1]) - order.indexOf(b[numColumn-1]);
    });
}

Here's the use like custom function: enter image description here

Same example for column 2: enter image description here

Upvotes: 1

Vytautas
Vytautas

Reputation: 2286

Absolutely, you can create a google script function onEdit() in the spreadsheet which will fire each time you edit something.

There are a few ways to do it. One of the most obvious ones (probably not the best as it loops through the data a lot):

  1. Using getLastColumn() and getLastRow() grab all the data. You then use 2 arrays. 1 is current and 1 is sorted.
  2. Loop through the current array and if data[i][0] matches the rank, output it to the sorted array
  3. After you finish one loop, change to the rank you expect next (keep them in an array and you can use a for loop with rank[n])
  4. Loop the array until you run out of the ranks.lenght

So it would be something along the lines of 

function onEdit()
//Get the data
rank = [rank1, rank2, rank3]

for (n=0 ; n < rank.lenght; n++) {
  for (i = 0; i < data.lenght; i++)
    if (data[i][0] === rank[n]) {
      sorted[x] = data[i];
      x++;
    }
  }
}

Though I would recommend to make it so you can manually run it instead of an onEdit. Or you can set it so only if the last row is edited, then to fire it (make a return at the start of the script if the edited row is less than getLastRow()+1

Upvotes: 2

Related Questions