Auto Sort not working on Multiple Sheets within one Google Sheet

Question

I want Sheet 2 (Titled "Deals in Escrow") to auto sort by date and Sheet 1 (Titled "Loan Inquiries") to auto sort by last name. Both Sheets are within a single Google Sheet. I have found the following script, and I modified it slightly and it works great on Sheet 2 to auto sort by date;

//Updates sort for range automatically
function onEdit(event){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var editedCell = sheet.getActiveCell();
  var columnToSortBy = 7;
  var tableRange = "A3:T99"; //range to be sorted
  if(editedCell.getColumn() == columnToSortBy){   
    var range = sheet.getRange(tableRange); 
    range.sort( { column : columnToSortBy, ascending: true } );
  }
}

However, I want the 1st Sheet to auto sort by Last Name which is in Column 2, rather than Column 7 that the script refers to.
For what it's worth, I made the following changes to the script and added in the following script which works well to sort by last name in column 2 on Sheet 1;

//Updates sort for range automatically
function onEdit(event){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var editedCell = sheet.getActiveCell();

  var columnToSortBy = 2;
  var tableRange = "A3:T99"; //range to be sorted

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

But the problem is that by doing so, the other script to auto sort by date in Sheet 2 has now been disabled.

I've also tried creating a Name Range in each sheet and putting NamedRange1 and NameRange2 in the table range section of the script. It hasn't changed anything. Sheet 1 is still sorting by last name and Sheet 2 is not sorting.

//Updates sort for range automatically
function onEdit(event){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var editedCell = sheet.getActiveCell();

  var columnToSortBy = 2;
  var tableRange = "NamedRange1"; //range to be sorted

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

Sorry no it comes after getActiveSheet as an additional method e.g. getActiveSheet().getSheetByName - im on an airplane lol so i can take a look once we hit wifi capable elevation :) — Aurielle Perlmann

Ed Nelson Ed Nelson · Accepted Answer · 2016-12-23T16:49:19

Try:

function onEdit(event){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = event.source.getActiveSheet().getName()
  var editedCell = event.range.getSheet().getActiveCell();
if(sheet=="Loan Inquiries"){
  var columnToSortBy = 2;
  var tableRange = "A3:T99"; //range to be sorted
  if(editedCell.getColumn() == columnToSortBy){   
    var range = ss.getActiveSheet().getRange(tableRange); 
    range.sort( { column : columnToSortBy, ascending: true } );
  }}
  else if(sheet=="Deals in Escrow"){
    var columnToSortBy = 7;
    var tableRange = "A3:T99"; //range to be sorted
  if(editedCell.getColumn() == columnToSortBy){   
     var range = ss.getActiveSheet().getRange(tableRange); 
    range.sort( { column : columnToSortBy, ascending: true } );
  }
  else{return}
}}

Test spreadsheet:

 https://docs.google.com/spreadsheets/d/13vveA0n4w84yQ71no7LgGonz_wLE7H_RA1ocDOtLZPQ/edit?usp=sharing

Auto Sort not working on Multiple Sheets within one Google Sheet

2 Answers