0

If i want to sort different columns in every sheet, what do i have to change?

function onEdit(event){
var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var editedCell = sheet.getActiveCell();

  var columnToSortBy = 9;
  var tableRange = "B4:J948";

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

There are several ways that you could adapt your script to sort a different column for each sheet.

For example, you might create a series of IF statements that combine the sort column and the sheet name. Another option, and the one used in this script, is to use the Javascript switch statement (docs).

Though an IF statement is still required, the value of columnToSortBy is modified according to the sheetname (var sheetname =); the sheet name drives the range via getSheetbyName().

Note the minor change in lines 1 and 2; var ss =, and its impact on var editedCell =.


function onEdit(event){

  //wa13669601
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var editedCell = ss.getActiveCell();

  var sheetname = event.source.getSheetName()


  switch (sheetname) {
    case 'Sheet1':
      var columnToSortBy = 9;
      break;
    case 'Sheet2':
      var columnToSortBy = 7;
      break;
    case 'Sheet3':
      var columnToSortBy = 4;
      break;
  }
  var sheet = ss.getSheetByName(sheetname);
  var tableRange = "B4:J948";

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

}

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.