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
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):
getLastColumn() and getLastRow() grab all the data. You then use 2 arrays. 1 is current and 1 is sorted.data[i][0] matches the rank, output it to the sorted arrayrank[n])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
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:
Same example for column 2:
sort