Google-sheets – How to make some data on a Google Spreadsheet auto-sorting using onOpen

google sheetsgoogle-apps-script

I was working with @W0lf here
but I am unable to comment/continue the conversation anymore.

I am trying to use autosort function onOpen but is not working:

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

  var columnToSortBy = 4;
  var tableRange = "A2:F99";

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

@w0lf's code for onEdit is working:

function onEdit(event){
  var sheet = event.source.getActiveSheet();
  var editedCell = sheet.getActiveCell();

  var columnToSortBy = 4;
  var tableRange = "A2:F99";

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

Best Answer

You need to rename your variable ss to sheet:

function onOpen(event) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();

  var columnToSortBy = 4;
  var tableRange = "A2:F99";

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

If that does not work, try to use @w0lf's approach with the event argument:

var sheet = event.source.getActiveSheet();

UPDATE: To sort by several columns just put the sorting options into an array, like so:

range.sort( [4, 1] ); // or
range.sort( [{ column: 4, ascending: false }, 1] ); // or
range.sort( [{ column: 4, ascending: false }, { column: 1, ascending: false }] );

See the google dev docs for more details: https://developers.google.com/apps-script/class_range#sort .

Related Topic