javascriptgoogle-sheetsgoogle-apps-script

Custom sort order according to cell value


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 ?


Solution

  • 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;
        }
      })