4

I have a spreadsheet that I update on a regular basis. I also have to re-sort the spreadsheet when finished because of the changes made. I need to sort with multiple criteria like the below settings. I have searched for examples but my Google search skills have failed me.

Sort range from A1:E59
[x] Data has header rows
sort by "Priority" A > Z
then by "Open" Z > A
then by "Project" A > Z
Mogsdad
  • 44,709
  • 21
  • 151
  • 275
PY_
  • 1,189
  • 8
  • 18
  • 29

4 Answers4

10

Mogsdad's answer works fine if none of your cells have values automatically calculated via a formula. If you do use formulas, though, then that solution will erase all of them and replace them with static values. And even so, it is more complicated than it needs to be, as there's now a built-in method for sorting based on multiple columns. Try this instead:

function onEdit(e) {
  var priorityCol = 1;
  var openCol = 2;
  var projectCol = 3;

  var sheet = SpreadsheetApp.getActiveSheet();
  var dataRange = sheet.getDataRange();
  dataRange.sort([
    {column: priorityCol, ascending: true},
    {column: openCol, ascending: false},
    {column: projectCol, ascending: true}
  ]);
}

Instead of making a separate function, you can use the built-in onEdit() function, and your data will automatically sort itself when you change any of the values. The sort() function accepts an array of criteria, which it applies one after the other, in order.

Note that with this solution, the first column in your spreadsheet is column 1, whereas if you're doing direct array accesses like in Mogsdad's answer, the first column is column 0. So your numbers will be different.

TheSoundDefense
  • 6,753
  • 1
  • 30
  • 42
  • Is it possible to sort a column with date values, some blank, where the blanks would be at the top? The script works for me otherwise, the blanks are just always at the bottom of the array. – TC76 Aug 07 '23 at 22:07
4

That is a nice specification, a great place to start!

Remember that Google Apps Script is, to a large extent, JavaScript. If you extend your searching into JavaScript solutions, you'll find plenty of examples of array sorts here on SO.

As it happens, much of what you need is in Script to copy and sort form submission data. You don't need the trigger part, but the approach to sorting can be easily adapted to handle multiple columns.

The workhorse here is the comparison function-parameter, which is used by the JavaScript Array.sort() method. It works through the three columns you've indicated, with ascending or descending comparisons. The comparisons used here are OK for Strings, Numbers and Dates. It could be improved with some cleaning up, or even generalized, but it should be pretty fast as-is.

function sortMySheet() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var dataRange = sourceSheet.getDataRange();
  var data = dataRange.getValues();
  var headers = data.splice(0,1)[0]; // remove headers from data
  data.sort(compare);       // Sort 2d array
  data.splice(0,0,headers); // replace headers

  // Replace with sorted values
  dataRange.setValues(data);
};

// Comparison function for sorting two rows
// Returns -1 if 'a' comes before 'b',
//         +1 if 'b' before 'a',
//          0 if they match.
function compare(a,b) {
  var priorityCol = 0;  // Column containing "Priority", 0 is A
  var openCol = 1;
  var projectCol = 2;

  // First, compare "Priority" A > Z
  var result = (a[priorityCol] > b[priorityCol] ) ? 
                    (a[priorityCol] < b[priorityCol] ? -1 : 0) : 1;
  if (result == 0) {
    // "Priority" matched. Then compare "Open" Z > A
    result = (b[openCol] > a[openCol] ) ?
                    (b[openCol] < a[openCol] ? -1 : 0) : 1;
  }
  if (result == 0) {
    // "Open" matched. Finally, compare "Project" A > Z
    result = (a[projectCol] > b[projectCol] ) ?
                    (a[projectCol] < b[projectCol] ? -1 : 0) : 1;
  }

  return result;
}
Community
  • 1
  • 1
Mogsdad
  • 44,709
  • 21
  • 151
  • 275
  • Thanks for this beautiful example of array sorting :-) +1 again. – Serge insas Dec 10 '13 at 20:06
  • I tried to copy/paste it into the script editor but when i hit save, i get this error: "Missing ; before statement. (line 43,..." – PY_ Dec 11 '13 at 11:09
  • @PY_ - there were extra brackets at the end of every ternary statement, leftovers from a final clean-up in the SO editor. Fixed. – Mogsdad Dec 11 '13 at 16:50
0

Try this using the Apps Script sort instead of the native JavaScript. I had the same issue with sorting the header row(s) and this solved the issue.

So I think something like this should work:

function onOpen() {
  SpreadsheetApp.getActiveSpreadsheet()
  .getSheetByName("Form Responses 1").sort(2);
}

Regarding sorting by multiple columns, you can chain that sort() method, with the final sort() having the highest priority, and the first sort() the lowest. So something like this should sort by Start date, then by End date:

function onOpen() {
  SpreadsheetApp.getActiveSpreadsheet()
  .getSheetByName("Form Responses 1").sort(3).sort(2);
}

Reference link:-

https://support.google.com/docs/thread/16556745/google-spreadsheet-script-how-to-sort-a-range-of-data?hl=en

Steven
  • 1
  • 1
  • While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. – tjheslin1 Apr 07 '22 at 16:09
0

Not sure if this is still relevant, but you can use the sort() function to define another tab as a sorted version of the original data.

Say your original data is in a tab named Sheet1; I'm also going to act as though your Priority, Open, and Project columns are A, B, and C, respectively.

Create a new tab, and in cell A1 type:

=sort(Sheet1!A1:E59, 1, TRUE, 2, FALSE, 3, TRUE)

The first argument specifies the sheet and range to be sorted, followed by three pairs: the first of each pair specifies the column (A=1, B=2, etc.), and the second specifies ascending (TRUE) or descending (FALSE).

mchung
  • 1