3

The following function successfully sorts my file (containing 3 sheets) individually. When I hit the "play" button on the script, it properly sorts (as expected) the current (active) sheet. Since my file has 3 sheets, I'd like it to sort all three. The sheets are named "MV", "PL", and "SM". See below.

/**
* Sorts sheet on 1st column (except header rows) in descending order
*/
function AutoSortOnEdit() {
  var sheetNames = ["MV", "PL", "SM"];

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  sheetNames.forEach(function(name) {
    var sheet = ss.getSheetByName(name);
    var range = sheet.getRange(3, 1, sheet.getLastRow() - 1, sheet.getLastColumn());
    range.sort({column: 1, ascending: false});
  });
}

Following this, I'd like each individual sheet to be able to auto sort in the same manner, except only when being edited. For example, when a number is entered into column A, I'd like the sheet to be immediately sorted, so that the number entered moves to the correct spot in the list. The user would enter a number at the bottom row, and immediately that entire row should move into its numerically logical position based on column A. I'm assuming that to achieve this, I would have to run a separate function using onEdit() ... and the most important restriction is that I cannot have this function move sorted data to a secondary sheet. The onEdit() auto-sort function MUST happen within the active sheet.

1

If you want to sort only the active sheet, it's simpler:

function onEdit() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange(3, 1, sheet.getLastRow() - 2, sheet.getLastColumn());
  range.sort({column: 1, ascending: false});
}

You had sheet.getLastRow() - 1 for the number of rows in the range, which doesn't seem correct if you start the range from the 3rd row. I put -2.

  • Thanks Sally, this works beautifully for the individual sheets. Is there another way to modify the script above (not your submission) that auto-sorts all three sheets in my file? – Joel Feb 22 '16 at 16:33
  • It already sorts things correctly, as far as I can tell. The function must be named onEdit if you want it to run on every edit; this is a reserved name for this purpose. AutoSortOnEdit will not run this way, though it can be made to run automatically by creating a trigger. – user79865 Feb 22 '16 at 16:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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