Google Apps脚本自动（编辑时）排序（仅限特定工作表） [英] Google Apps Script automatic (on edit) sort (specific sheets only)

问题描述

我曾经为我的工作表提供了一个脚本，它基于 https://webapps.stackexchange.com/questions/7211/how-can-i-make-some-data-on-a-google-spreadsheet-auto-sorting 这里可以在编辑时自动对特定工作表进行排序。但是，我不能固定日期，但似乎已停止工作。我已经做了一些研究，显然排序方法前一段时间被改变了。

现在基本上我需要的是以下内容：我有一个谷歌电子表格，有一个地狱很多不同的纸张（20+）我以前只有一张需要分页的纸张，但整件事情随着时间的推移而增长，现在已经是两张我需要分类的纸张了，而且这个数字很可能会增加甚至更进一步 - 而且至关重要的是，分类仅适用于这些特定工​​作表。而且这个问题更加复杂，纸张的顺序并没有固定，所以我不能使用纸张ID，因为它们可能会改变，所以它必须使用纸张名称（它也可以改变，但它不是很可能 - 如果发生了，脚本会很快适应）。

需要排序的表格包含大约6-7列的大量数据，标题行，应该按第一列排序，最小的数字在上面。

我现在提出的是以下内容：

 函数AutoSortOnEdit（）{
 //选择活动工作表
 var ss = SpreadsheetApp.getActiveSpreadsheet（）; 
 
 //选择正确的表格
 var sheet = ss.getSheetByName（testsheet456）; 
 
 //选择范围（排除顶行）
 var range = sheet.getRange（A2：ZZ999）; 
 
 //实际执行排序
 range.sort（{column：1，ascending：true}）; 
 
 Browser.msgBox（sort done）; 
} 
  

消息框就在那里，所以我看到这个东西被执行了，一旦它正常工作，这将被删除。我还在编辑电子表格中添加了自定义触发器。

当我将range.sort更改为（1，true）而不是我现在拥有的东西，它会抛出一个错误方法排序（数字，布尔）没有找到...

但实际上坏消息是：在那里的代码a）我没有错误b）消息框出现，所以它实际上完成了整个事情，c）当我实际编辑那个东西时（这很好），但是d）没有完成任何分类......

那么，有谁能帮我解决这个问题吗？我没有看到没有真正的原因，为什么这不应该工作（但是，我再次，我不是一个程序员，所以这就是为什么我来这里寻求帮助。：P）。此外，我还需要（如介绍中所述）将该脚本应用于两个（或更多）特定工作表（按名称），因为它目前仅用于一个工作表，但我希望使其适用于一个第一，它甚至失败，现在这个...... ....

在此先感谢，

彼得

解决方案

您的范围无效。 A2：如果工作表中没有地址 ZZ999 的单元格，则ZZ999 不起作用。您可以使用 sheet.getLastRow（）和 sheet.getLastColumn（）来使其工作。要对几张纸进行排序，您需要一个包含所有纸张的数组，然后循环执行。例如使用forEach循环。

 函数AutoSortOnEdit（）{
 var sheetNames = [testsheet456，testsheet457 ，testsheet458]; 
 
 var ss = SpreadsheetApp.getActiveSpreadsheet（）; 
 sheetNames.forEach（function（name）{
 var sheet = ss.getSheetByName（name）; 
 var range = sheet.getRange（2，1，sheet.getLastRow（） -  1， sheet.getLastColumn（））; 
 range.sort（{column：1，ascending：true}）; 
}）; 
} 
  

I used to have a script for my Sheet, that was based on https://webapps.stackexchange.com/questions/7211/how-can-i-make-some-data-on-a-google-spreadsheet-auto-sorting this here to automatically sort a specific sheet whenever it is edited. However, I can't fixate the date though, it seem to have stopped working. I already did some research and apparently the sorting method was changed some while ago.

Now basically the thing I need is the following: I have a google spreadsheet that has a hell lot of different sheets (20+) I used to have only one sheet that needs sorting, but the whole thing has grown over time, and by now it is already two sheets I need sorting, and it is highly likely that the number will increase even further - and it is crucial, that the sorting only applies to these specific sheets. And to complicate the issue even more, the order of the sheets is not fixated, so I cannot work with sheet IDs, as they are likely to change, so it has to work with the sheet names (which can also change, but it's not very likely - and if it happens, the script is adapted rather quickly).

The Sheets that need sorting contain a lot of data in about 6-7 columns, has a header row and should be sorted by the first column, smallest number on top.

What I came up with as of now is the following:

function AutoSortOnEdit(){
  // select active sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // select correct sheet
  var sheet = ss.getSheetByName("testsheet456");

  // select range (exclude top row)
  var range = sheet.getRange("A2:ZZ999");

  // actually do the sorting
  range.sort({column: 1, ascending: true});

 Browser.msgBox("sort done"); 
}

The Message box is just there so I see that the thing was executed, as soon as it works properly, this will be removed. I also added a custom trigger with from spreadsheet on edit.

What is funky is, when I change the range.sort to just "(1, true)" instead of what I have now, it throws an error "method sort(number,boolean) not found"...

But the bad news actually is: with that code up there a) I get no error b) the message box appears, so it actually went through the whole thing, c) it also works when I actually edit that thing (which is good) but d) no sorting whatsoever is done...

So, can anyone help me out with this? I see no real reason why that shouldn't work (but then again, I'm not a programmer, so that's why I came here for help. :P). Additionally, I would then still need - as stated in the introduction - a way to apply that script to two (or more) specific sheets (by name), as it currently is just for one sheet, but I wanted to make it work for one first, and it even fails with this right now....

Thanks in advance,

Peter

解决方案

Your range is invalid. A2:ZZ999 doesn't work if there are is no cell with address ZZ999 in your sheet. You can make it work by using sheet.getLastRow() and sheet.getLastColumn(). To sort several sheets you need an array with all the sheets in it and then loop through that. For example with a forEach loop.

function AutoSortOnEdit() {
  var sheetNames = ["testsheet456", "testsheet457", "testsheet458"];

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  sheetNames.forEach(function(name) {
    var sheet = ss.getSheetByName(name);
    var range = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn());
    range.sort({column: 1, ascending: true});
  });
}

这篇关于Google Apps脚本自动（编辑时）排序（仅限特定工作表）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！