自动排序不适用于一张Google表格中的多张表格 [英] Auto Sort not working on Multiple Sheets within one Google Sheet

问题描述

我希望工作表2（标题为托管中的交易）按日期自动排序，并将工作表1（标题为贷款查询）自动按姓氏排序。两张表格都在一张Google表格中。我发现了下面的脚本，并且稍微修改了它，它在工作表2上按照日期自动排序很有效;

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

但是，我希望1st Sheet自动排序姓氏在列2中，而不是脚本引用的列7.

对于它的价值，我对脚本进行了以下更改，并添加到以下脚本中，该脚本适用于排序姓氏在表1中列2;

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

但问题是，通过这样做，在工作表2中按日期自动排序的脚本现在已被禁用。

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

我也尝试在每个表格中创建一个名称范围，并将NamedRange1和NameRange2放入脚本的表格范围部分。它没有改变任何东西。

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

推荐答案

：

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}
}}

测试电子表格：

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

这篇关于自动排序不适用于一张Google表格中的多张表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！