Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – Custom sort order according to cell value (GAS)

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 ?

Tags:

2

Answers


  1. 0

    You can use a Map object to assign a numerical value to each role and sort based on those values. Hopefully this works as intended.

    var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName(a);
var dataRange = sheet.getDataRange();
var data = dataRange.getValues();

var roleOrder = {
  'intern': 1,
  'junior': 2,
  'senior': 3,
  'lead': 4,
  'manager': 5
};

data.sort(function(row1, row2) {
  var role1 = row1[role - 1]; 
  var role2 = row2[role - 1];
  
  if (roleOrder[role1] < roleOrder[role2]) {
    return -1;
  } else if (roleOrder[role1] > roleOrder[role2]) {
    return 1;
  } else {
    return row2[cases - 1] - row1[cases - 1];
  }
});

sheet.getRange(1, 1, data.length, data[0].length).setValues(data);
    Login or Signup to reply.
  2. 0

    Sorts the role level using ForEach

    By sorting the level from intern to manager, all of the data from your dataRange will be retrieved, and the setValues will be returned to the designated range.

    Sample Table:
    intern a
    junior b
    manager c
    lead d
    senior e
    intern f
    junior g
    manager h
    lead i
    senior j

    Output Table:
    intern a
    intern f
    junior b
    junior g
    senior e
    senior j
    lead d
    lead i
    manager c
    manager h

    Script:

    function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("a");
  var dataRange = sheet.getDataRange();
  var data = dataRange.getValues();
  var output = []
  var hier = ['intern', 'junior', 'senior', 'lead', 'manager'];
  hier.forEach(x => {
    var temp = [];
    data.forEach(y => (y[0] == x) ? temp.push(y) : null);
    temp.forEach(z => output.push(z))
  });
  dataRange.setValues(output);
}

    Reference:

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search