Sorting a certain range on Edit()

Question

I am trying to automatically sort a certain range of my sheet onEdit in a way that on the top is an empty cell and towards the bottom are the oldest entries. I have gotten this code from here and tried to modify it for my needs:

function onEdit(e)
{
  var sheet = ss.getActiveSheet();
   if( sheet != ss.getSheetByName('cover sheet'))
   {
      var editRange = { // B4:J6
         top: 29,
         bottom: sheet.getLastRow()+1,
         left: 4,
         right: 11
      };
      // Exit if we're out of range
      var thisRow = e.range.getRow();
      if (thisRow < editRange.top || thisRow > editRange.bottom) return;

      var thisCol = e.range.getColumn();
      if (thisCol < editRange.left || thisCol > editRange.right) return;

      // We're in range; timestamp the edit
      var ss = e.range.getSheet();
      ss.getRange(editRange).sort({column: 1, ascending: true});
   }
}

But that does not work. Any Ideas? Here is the link to a sample sheet.

Regards

What about it doesn't work? Can you provide a sample to show what it does and what it should do? — Robin Gertenbach, Sep 29 '16 at 14:17
It should sort the specific rows in a sheet if the sheet is not the coversheet. It does nothing. I´ll create a sample-spreadsheet — , Sep 29 '16 at 14:42
Try running the function from the code editor with some `Logger.log('some text here: ' + variableName)` statements, then VIEW the LOGS to see what it printed to the logs. For example. Put `Logger.log('it ran!')` at the very top of the function, to determine if the function is running or not. Change the first `if` statement to: `if( sheet.getName() === 'cover sheet')` — Alan Wells, Sep 29 '16 at 15:34
Did a logger right after on edit(e){ and that didnt prog, so i guess for some reason the script does not automatically trigger. Any idea why? the script is bound to the sheet. — , Sep 29 '16 at 15:52

score 0 · Answer 1 · answered Sep 29 '16 at 17:56

0

Well,

what you need should be something like this:

function onEdit(e) {
  var thisSS = e.source,
      editedRow = e.range.getRow(),
      editedCol = e.range.getColumn();

  if(editedCol === 4){
    thisSS.sort(editedCol, false);
  }
  thisSS.insertRowBefore(2);
}

then paste it to your sheet's script, then go to "Resources >> Current project's triggers" and setup the trigger to run the function "onEdit" when the onEdit Event occour.

best regards.

answered Sep 29 '16 at 17:56

LeandroP

337
2
8

Unfortunately i need another solution than insert row, since on the left side of this area is a region that should not be changed. – Sep 29 '16 at 17:58
Also the sort should not leave the boundaries of the box, but be sorted inside the box. – Sep 29 '16 at 18:33

score 0 · Answer 2 · edited May 23 '17 at 12:33

Please make sure that there are cells in ranges that you declared. As mentioned by @SpiderPig in this SO question, a range will be invalid if there is no cell within the given range.

For this, you may also try using bottom: sheet.getLastRow() -1 like the solution given:

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});
    });
}

Hope that works for you. Happy coding!

I am a little bit worried about the runtime: if i see that right it always sorts all the sheets in that area even if the change wasn´t made in that area. tecnically it would be enough to only sort the sheet that got edited if the edit was in that area or am i wrong? — , Oct 03 '16 at 18:48

Sorting a certain range on Edit()

2 Answers2