3

I found this script from How can I apply a script to only one specific sheet within a spreadsheet to auto sort a column:

    function onEdit(){
  var sh0 = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
  var editedCell = sh0.getActiveRange().getColumnIndex();

  if(editedCell == 1) { 
    var range = sh0.getRange("B2:B7");
    range.sort({column: 2, ascending: false});
  }
}

However it's sorting by a single column and not keeping the data in other columns in line by row, so mixing all the data up.

I think I need to sort by sheet rather than range and found this page "Sorts a sheet by column", so I tried replacing:

range.sort({column: 2, ascending: false});

with

sheet.sort(2, false);

However now the script doesn't work.

How can I auto sort a single sheet by the values in one column, but keeping all the data in line?

1

The sort method belongs to the class Range, not to the class Sheet. Simply put, it always operates on a range. If you want that range to include all the data in a sheet, use the method getDataRange of the class Sheet.

var range = sh0.getDataRange();
range.sort({column: 2, ascending: false});

For completeness, I present a customizable script for automatic sorting.

  function onEdit() {
    var sheetToSort = "Sheet Name"; // enter name of sheet 
    var columnEdited = 1;           // sort when this column is edited
    var columnSort = 2;             // sort by this column
    var sortAscending = false;      // true or false here

    var sheet = SpreadsheetApp.getActiveSheet();
    var editedCell = sheet.getActiveRange().getColumnIndex();
    if (sheet.getSheetName() == sheetToSort && editedCell == columnEdited) { 
      var range = sheet.getDataRange();
      range.sort({column: columnSort, ascending: sortAscending});
    }
  }

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.