How do I sort several columns by a list of values?

Question

In Google Sheets, I have several columns:

 Industry | Company | Country | Date Last Visited

And I want to order them by Country, then by Date Last Visited, then by Industry, then by Company so that they're ordered like this:

   Industry   |     Company    | Country | Date Last Visited
--------------+----------------+---------+-------------------+
 Tech         | [Alphabetical] | USA     |    [auto by date]
 Business     |                | Canada  | 
 Food Service |                | UK      | 
              |                | Japan   | 

The inputs for these are forced by validation to be the choices presented here

However, in Google Apps Script, I don't know how to do this. Here's my code so far:

function onEdit(event){
  var sheet = event.source.getActiveSheet();
  if (sheet.getName() != "Companies")
    return;
  var editedCell = sheet.getActiveCell();

  var columnsToSortBy = 
      [
         {column: 3} // C (Country)
        ,{column: 4} // D (Date Last Visited)
        ,{column: 1} // A (Industry)
        ,{column: 2} // B (Company)
      ];
  for(i in columnsToSortBy) columnsToSortBy[i].ascending = true; // add "ascending: true" to all columns
  var tableRange = "A2:Z999"; // What to sort.

  for (var columnToSortBy = 0; columnToSortBy < columnsToSortBy.length; columnToSortBy++)
    if(editedCell.getColumn() == columnsToSortBy[columnToSortBy].column){   
      var range = sheet.getRange(tableRange);
      range.sort(columnsToSortBy);
    }
}

As you can see, I have them sorting "ascending", but I have now way for Country and Industry to be sorted by my list. How do I sort all 4 columns, some automatically, some by a custom list?

Answer 1

Create a sort key, for example by combining actual values and your custom list orders, such as with lookup tables and concatenation, eg:

=VLOOKUP(C2,H:I,2,0)&"|"&D2&"|"&VLOOKUP(A2,F:G,2,0)&"|"&B2  

then sort on that column.