Currently I sort my sheet via this function
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName(a);
var dataRange = sheet.getDataRange();
dataRange.sort([
{column: role, ascending: true},
{column: cases, ascending: false}
]);
This sorts the by role first in alphabetical order. Is it possible to customise this sort ? For example, ideally the sort is by role level and not aplhabetical
Eg. if the roles are [intern, junior, senior, lead, manager] can the sheet be sorted in that order ?
Here is my sample data. My table doesn't show it but column a is a drop down selection
A | B | C | D | E | |
---|---|---|---|---|---|
1 | COL1 | COL2 | COL3 | COL4 | COL5 |
2 | intern | B2 | C2 | D2 | E2 |
3 | junior | B3 | C3 | D3 | E3 |
4 | senior | B4 | C4 | D4 | E4 |
5 | lead | B5 | C5 | D5 | E5 |
6 | manager | B6 | C6 | D6 | E6 |
7 | intern | B7 | C7 | D7 | E7 |
8 | junior | B8 | C8 | D8 | E8 |
9 | senior | B9 | C9 | D9 | E9 |
10 | lead | B10 | C10 | D10 | E10 |
11 | manager | B11 | C11 | D11 | E11 |
12 | intern | B12 | C12 | D12 | E12 |
13 | junior | B13 | C13 | D13 | E13 |
14 | senior | B14 | C14 | D14 | E14 |
15 | lead | B15 | C15 | D15 | E15 |
16 | manager | B16 | C16 | D16 | E16 |
17 | intern | B17 | C17 | D17 | E17 |
18 | junior | B18 | C18 | D18 | E18 |
19 | senior | B19 | C19 | D19 | E19 |
20 | lead | B20 | C20 | D20 | E20 |
21 | manager | B21 | C21 | D21 | E21 |
22 | intern | B22 | C22 | D22 | E22 |
23 | junior | B23 | C23 | D23 | E23 |
24 | senior | B24 | C24 | D24 | E24 |
25 | lead | B25 | C25 | D25 | E25 |
26 | manager | B26 | C26 | D26 | E26 |
My Script:
function myFunction() {
var ss = SpreadsheetApp.getActive();
var sh = ss.getSheetByName("Sheet0");
var rg = sh.getRange(2,1,sh.getLastRow() - 1, sh.getLastColumn());
var vs = rg.getValues();
var list = ['intern', 'junior', 'senior', 'lead', 'manager'];
vs.sort((a,b) =>{
return list.indexOf(a[0]) - list.indexOf(b[0]);//switch a and b to sort descending
} )
Logger.log(JSON.stringify(vs).replace(/],/g,'],\n'))
//rg.setValues(vs);
}
Here's the logger output:
[["intern","B2","C2","D2","E2"],
["intern","B7","C7","D7","E7"],
["intern","B12","C12","D12","E12"],
["intern","B17","C17","D17","E17"],
["intern","B22","C22","D22","E22"],
["junior","B3","C3","D3","E3"],
["junior","B8","C8","D8","E8"],
["junior","B13","C13","D13","E13"],
["junior","B18","C18","D18","E18"],
["junior","B23","C23","D23","E23"],
["senior","B4","C4","D4","E4"],
["senior","B9","C9","D9","E9"],
["senior","B14","C14","D14","E14"],
["senior","B19","C19","D19","E19"],
["senior","B24","C24","D24","E24"],
["lead","B5","C5","D5","E5"],
["lead","B10","C10","D10","E10"],
["lead","B15","C15","D15","E15"],
["lead","B20","C20","D20","E20"],
["lead","B25","C25","D25","E25"],
["manager","B6","C6","D6","E6"],
["manager","B11","C11","D11","E11"],
["manager","B16","C16","D16","E16"],
["manager","B21","C21","D21","E21"],
["manager","B26","C26","D26","E26"]]
Just learn how to use the Javascript Array.sort() with the compare function. Use your favorite javascript reference.
Regarding your question about sorting multiple columns here is a snippet of a sorting routine that sorts bible verses in biblical canonical order. As you can see it begins to get a little more complicated. But the idea is still the same.
vs.sort((a, b) => {
let tA = [a[1], a[2]];
let tB = [b[1], b[2]];
let ia = vers.indexOf(a[3].toString());
let ib = vers.indexOf(b[3].toString());
if (~ia && ~ib) {
let vA = (vers.indexOf(a[3]) * 1000);
vA += (a[3] == b[3]) ? (booknames.indexOf(a[0]) * 100) : 0;
vA += (a[0] == b[0]) ? (Number(tA[0]) * 10) : 0;
vA += (tA[0] == tB[0]) ? (Number(tA[1]) * 1) : 0;
let vB = (vers.indexOf(b[3]) * 1000)
vB += (a[3] == b[3]) ? (booknames.indexOf(b[0]) * 100) : 0;
vB += (a[0] == b[0]) ? (Number(tB[0]) * 10) : 0;
vB += tA[0] == tB[0] ? (Number(tB[1]) * 1) : 0;
Logger.log("vA: %s vB: %s VersionA: %s versionB: %s NameA: %s NameB: %s verseA: %s:%s verseB: %s:%s", vA, vB, a[3], b[3], a[0], b[0], tA[0], tA[1], tB[0], tB[1])
return vA - vB;
}
})