Google Apps Script – Loop Through All Pages Except the First

google sheetsgoogle-apps-script

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

Best Answer

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.

Related Topic