2

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

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 .

  • Thanks the following code worked: function onOpen(event) { var sheet = SpreadsheetApp.getActiveSpreadsheet(); var columnToSortBy = 4; var tableRange = "A2:G99"; var range = sheet.getRange(tableRange); range.sort( { column : columnToSortBy, ascending: false } ); } Now how would I go about adding a secondary column to sort by. Its currently by column 4, I would then like to sort secondary by column 1 – drizzt09 May 11 '12 at 5:06
  • See my update for an example. – Oliver May 11 '12 at 22:44

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.