2

I'm very new to all this. I'm trying to write a script that sorts sheets by descending date order each time a new row is entered. I need the script to run on all pages but the first (the summary page). I've gotten close (I can get the script to run on a specified page), but don't know how to

  • exclude a page or
  • have it start running the script on the second page.

Here's the code so far. It appears there is a TypeError in the line that reads, "var editedCell = shs.getActiveCell();"

function onEdit(event){  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var shs = ss.getSheets(), num = shs.length;
  var sheetName = [] 

  for(var i = 1; i<num; i++);
    var editedCell = shs.getActiveCell();
    var columnToSortBy = 1;
    var tableRange = "A2:G99"; // What to sort.

    if(editedCell.getColumn() == columnToSortBy){   
      var range = sheet.getRange(tableRange);
      range.sort( { column : columnToSortBy, ascending: false });
    }
  }
}
1

First, the error: shs is an array, since you populate it with ss.getSheets(), so it doesn't have a method getActiveCell(). However, the elements of the array has that method.

You are also referencing a variable sheet, which you haven't defined anywhere.

Currently, your code is more suitable for going through a single sheet, you need to iterate through the shs array to have it operate on all sheets.

The following function will go through all cells in all sheets:

function onEdit(event){  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var shs = ss.getSheets(), num = shs.length;
  var sheetName = [] 

  for (var sheetIdx = 0; sheetIdx < shs.length; sheetIdx++) {
      var sheet = shs[sheetIdx];

      for(var i = 1; i<num; i++) {
      ...
   }
}

But you wanted to skip the first sheet, so let's start with element 1 in the array (since the array is 0-based, element 1 is the second sheet):

function onEdit(event){  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var shs = ss.getSheets(), num = shs.length;
  var sheetName = [] 

  for (var sheetIdx = 1; sheetIdx < shs.length; sheetIdx++) {
      var sheet = shs[sheetIdx];

      for(var i = 1; i<num; i++) {
        var editedCell = shs.getActiveCell();
        var columnToSortBy = 1;
        var tableRange = "A2:G99"; // What to sort.

        if(editedCell.getColumn() == columnToSortBy){   
          var range = sheet.getRange(tableRange);
          range.sort( { column : columnToSortBy, ascending: false });
        }
      }

   }
}

As for your comment, your code

function onEdit(event) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    for (var n in ss.getSheets()) {
        var sheet = ss.getSheets()[n];
        var name = sheet.getName();
        if (name == 'Dashboard') 
           return;
        var editedCell = ss.getActiveCell();
        var columnToSortBy = 1;
        var tableRange = "A2:G99";
        if (editedCell.getColumn() == columnToSortBy) {
            var range = sheet.getRange(tableRange);
            range.sort({column: columnToSortBy, ascending: false});
        }
    }
}

could work, if you replace return with continue. You don't want return, it ends the script. continue skips to the next element in the for loop.

  • Yes, here is the link to the spreadsheet. I'm running it in the scripts editor. docs.google.com/spreadsheets/d/… – MacKenzie Feb 19 '15 at 16:39
  • That's because of name != 'Dashboard'. That means that only if the name of the sheet is Dashboard, the rest of the for loop will run. You probably want name == 'Dashboard'. I'll update my answer. – Vidar S. Ramdal Feb 19 '15 at 16:52
  • That makes sense. And what you said about the Play button makes sense, too. I was able to make edits on both the 2nd and 3rd pages and see the sheets reorder themselves. You've been so helpful. There is a non-profit in NYC that is singing your praises. Thanks, again. – MacKenzie Feb 19 '15 at 16:59

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.