Google Apps Script automatic (on edit) sort (speci

Question

I used to have a script for my Sheet, that was based on https://webapps.stackexchange.com/questions/7211/how-can-i-make-some-data-on-a-google-spreadsheet-auto-sorting this here to automatically sort a specific sheet whenever it is edited. However, I can't fixate the date though, it seem to have stopped working. I already did some research and apparently the sorting method was changed some while ago.

Now basically the thing I need is the following: I have a google spreadsheet that has a hell lot of different sheets (20+) I used to have only one sheet that needs sorting, but the whole thing has grown over time, and by now it is already two sheets I need sorting, and it is highly likely that the number will increase even further - and it is crucial, that the sorting only applies to these specific sheets. And to complicate the issue even more, the order of the sheets is not fixated, so I cannot work with sheet IDs, as they are likely to change, so it has to work with the sheet names (which can also change, but it's not very likely - and if it happens, the script is adapted rather quickly).

The Sheets that need sorting contain a lot of data in about 6-7 columns, has a header row and should be sorted by the first column, smallest number on top.

What I came up with as of now is the following:

function AutoSortOnEdit(){
  // select active sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // select correct sheet
  var sheet = ss.getSheetByName("testsheet456");

  // select range (exclude top row)
  var range = sheet.getRange("A2:ZZ999");

  // actually do the sorting
  range.sort({column: 1, ascending: true});

 Browser.msgBox("sort done"); 
}

The Message box is just there so I see that the thing was executed, as soon as it works properly, this will be removed. I also added a custom trigger with from spreadsheet on edit.

What is funky is, when I change the range.sort to just "(1, true)" instead of what I have now, it throws an error "method sort(number,boolean) not found"...

But the bad news actually is: with that code up there a) I get no error b) the message box appears, so it actually went through the whole thing, c) it also works when I actually edit that thing (which is good) but d) no sorting whatsoever is done...

So, can anyone help me out with this? I see no real reason why that shouldn't work (but then again, I'm not a programmer, so that's why I came here for help. :P). Additionally, I would then still need - as stated in the introduction - a way to apply that script to two (or more) specific sheets (by name), as it currently is just for one sheet, but I wanted to make it work for one first, and it even fails with this right now....

Thanks in advance,

Peter

Answer 1

This also works pretty well:

function AutoSortOnEdit() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  sheet.getLastColumn());
    ss.sort(1, false);
  });
}

Answer 2

Your range is invalid. A2:ZZ999 doesn't work if there are is no cell with address ZZ999 in your sheet. You can make it work by using sheet.getLastRow() and sheet.getLastColumn(). To sort several sheets you need an array with all the sheets in it and then loop through that. For example with a forEach loop.

function AutoSortOnEdit() {
  var sheetNames = ["testsheet456", "testsheet457", "testsheet458"];

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