Sorting columns using custom order in google script

Question

I am sorting two columns in google sheets using a script.

Currently, both columns use the sort function and I was wondering whether it was possible to use a custom order for the first column (in that case ['OPEN','YES','NO'])

Here is a link to a sample spreadsheet

function autoSort() {

  /** Begin sorting function **/

  var activeSheet = SpreadsheetApp.getActiveSheet();
  var sheetName = activeSheet.getSheetName(); //name of sheet to be sorted
  var sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
  var range = sheet.getRange(headerRows+1, 1, sheet.getMaxRows()-headerRows, sheet.getLastColumn());
  var sortOrder = ['OPEN','YES','NO'];
  range.sort([{column: sortFirst, ascending: sortFirstAsc}, {column: sortSecond, ascending: sortSecondAsc}]);
}

Answer 1

• You want to run the 2 kinds of sorts in your shared Spreadsheet.
  • You want to sort the date of the column "A".
  • For the column "C", you want to sort with the custom order using the keys.
• You want to achieve this using Google Apps Script.

If my understanding is correct, how about this sample script? In this modification, the values retrieved from Spreadsheet are sorted by the sort method of Javascript, and then, the sorted values are put to the Spreadsheet using setValues(). Please think of this as just one of several answers.

Flow:

The flow of this sample script is as follows.

1. Retrieve values from Spreadsheet.
2. Sort the date of column "A".
3. Sort the values of column "C" with the custom sort using the keys.

Sample script:

function autoSort() {
  var headerRows = 1;
  var sortFirst = 1; // 1 is Column "A"
  var sortFirstAsc = false; // When it's "true", the order is ascending.
  var sortSecond = 3; // 3 is Column "C"
  var sortSecondAsc = true; // When it's "true", the order is ['OPEN','YES','NO'].

  // Retrieve values from Spreadsheet.
  var activeSheet = SpreadsheetApp.getActiveSheet();
  var sheetName = activeSheet.getSheetName(); //name of sheet to be sorted
  var sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
  var range = sheet.getRange(headerRows+1, 1, sheet.getLastRow()-headerRows, sheet.getLastColumn());
  var values = range.getValues();

  // Sort the date of column "A".
  var s1 = sortFirstAsc ? 1 : -1;
  values.sort(function(a, b) {return (a[sortFirst - 1] < b[sortFirst - 1] ? -s1 : s1)});

  // Sort the values of column "C" with the custom sort using the keys.
  var sortOrder = ['OPEN','YES','NO'];
  var s2 = sortSecondAsc ? 1 : -1;
  values.sort(function(a, b) {
    var i1 = sortOrder.indexOf(a[sortSecond - 1]);
    var i2 = sortOrder.indexOf(b[sortSecond - 1]);
    var vlen =  values.length;
    return s2 * ((i1 > -1 ? i1 : vlen) - (i2 > -1 ? i2 : vlen));
  });
  sheet.getRange(2, 1, values.length, values[0].length).setValues(values);
}

• If you want to use the variables of headerRows, sortFirst, sortFirstAsc, sortSecond and sortSecondAsc as the global, please put them to the outside of the function of autoSort().
• If you want to sort the date with the ascending order, please modify to var sortFirstAsc = true;.
• If you want to sort the values of the column "C" with the order of ['NO', 'YES', 'OPEN'], please modify to var sortSecondAsc = false;.

Result:

When the script is run under the following input situation, the following output can be retrieved.

Input:

Output:

Note:

• In this answer, your shared Spreadsheet was used. So if you want to use this script for other Spreadsheet, please be careful the order of columns.

• Of course, in order to sort the date, you can use range.sort(). In this case, please modify the script as follows.

  function autoSort() {
    var headerRows = 1;
    var sortFirst = 1; // 1 is Column "A"
    var sortFirstAsc = false; // When it's "true", the order is ascending.
    var sortSecond = 3; // 3 is Column "C"
    var sortSecondAsc = true; // When it's "true", the order is ['OPEN','YES','NO'].
  
    // Retrieve values from Spreadsheet.
    var activeSheet = SpreadsheetApp.getActiveSheet();
    var sheetName = activeSheet.getSheetName(); //name of sheet to be sorted
    var sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
    var range = sheet.getRange(headerRows+1, 1, sheet.getLastRow()-headerRows, sheet.getLastColumn());
  
    // Sort the date of column "A".
    range.sort([{column: sortFirst, ascending: sortFirstAsc}]);
    SpreadsheetApp.flush();
  
    // Sort the values of column "C" with the custom sort using the keys.
    var values = range.getValues();
    var sortOrder = ['OPEN','YES','NO'];
    var s2 = sortSecondAsc ? 1 : -1;
    values.sort(function(a, b) {
      var i1 = sortOrder.indexOf(a[sortSecond - 1]);
      var i2 = sortOrder.indexOf(b[sortSecond - 1]);
      var vlen =  values.length;
      return s2 * ((i1 > -1 ? i1 : vlen) - (i2 > -1 ? i2 : vlen));
    });
    sheet.getRange(2, 1, values.length, values[0].length).setValues(values);
    Logger.log(JSON.stringify(values));
  }
  

Reference:

• sort()

If I misunderstood your question and this was not the result you want, I apologize.