0

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?

1 Answer 1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.