4

I have successfully been able to sort the first sheet in my Google Spreadsheet onOpen, but I'd like to sort all four spreadsheets onOpen and I can't make this work. I've tried quite a few variations in Script Editor; here's my latest:

function onOpen(){
   var ss = SpreadsheetApp.getActiveSpreadsheet();

  // Sorts the first sheet by the first column, ascending
  var sheet = ss.getSheets()[0];
  var tableRange = sheet.getRange("A3:L999"); // What to sort.
  tableRange.sort({column: 1, ascending: true});

  // Sorts the second sheet by the first column, ascending
  var sheet1 = ss.getSheets()[1];
  var tableRange1 = sheet1.getDataRange("A3:AC999"); // What to sort.
  tableRange1.sort({column: 1, ascending: true});

  // Sorts the third sheet by the first column, ascending
  var sheet2 = ss.getSheets()[2];
  var tableRange2 = sheet2.getDataRange("A3:N999"); // What to sort.
  tableRange2.sort({column: 1, ascending: true});

  // Sorts the fourth sheet by the first column, ascending
  var sheet3 = ss.getSheets()[3];
  var tableRange3 = sheet3.getDataRange("A3:AC999"); // What to sort.
  tableRange3.sort({column: 1, ascending: true});
}
  • i believe that you are assigning var ss with get active spreed sheet value and there is possible to provide only the currently displaying sheet and not the entire pages opened. did you check whether it is providing the entire list in array – Vembu Nov 21 '14 at 18:08
4

Try this instead.

Code

function onOpen() {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();  
  for(var i = 0, iLen = sheets.length; i < iLen; i++) {
    var sheet = sheets[i], range = sheet.getDataRange();
    range.sort({column: 1, ascending: true});
  }
}

If you want to sort from a certain row or column, then use this code

function onOpen() {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  for(var i = 0, iLen = sheets.length; i < iLen; i++) {
    var sheet = sheets[i], lastRow = sheet.getLastRow();
    var lastColumn = sheet.getLastColumn();    
    var range = sheet.getRange(3,1,lastRow, lastColumn);
    range.sort({column: 1, ascending: true});
  }
}

or this

function onOpen() {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  var dataRange = ["A3:L999","A3:AC999", "A3:N999", "A3:AC999"];
  for(var i = 0, iLen = sheets.length; i < iLen; i++) {
    var sheet = sheets[i], range = sheet.getRange(dataRange[i]);
    range.sort({column: 1, ascending: true});
  }
}
  • 1
    THANK YOU. This worked wonderfully - exactly what I needed. So appreciated. – Sarah Nov 25 '14 at 14:15

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.