Google Apps脚本：在脚本中排序时可以指定标题行吗？ [英] Google Apps Script: Can I specify a header row when sorting in a script?
问题描述
我有一张电子表格，当我处理文档时，我想多次排列多列。这是一个使用GUI的多步骤过程，看起来太耗时。我必须检查标题行的框，设置第一个排序参数，然后再添加一个。唷！它变得很快。
I have a spreadsheet that I'd like to sort by multiple columns many times as I work on the document. This is a multi-step process using the GUI, and seemed far too time-consuming. I have to check the box for a header row, set up the first sorting parameter, and then add another one. Phew! It gets old quick.
排列表单没有问题，但标题行也是排序的！我没有找到任何方法来指定我有一个标题行作为sort（）函数的参数，我也没有看到任何方法来全局定义将被sort（）自动发现的标题行。
Sorting the sheet was no problem, but the header row was also sorted! I don't find any way to specify that I have a header row as a parameter to the sort() function, nor do I see any way to globally define a header row that will be automatically noticed by sort().
取而代之，我保存了标题行，删除了第一行，对表单进行了排序，在最上面插入了一个新行，重新创建了标题行并格式化了标题行。哇！编写它并没有太长的时间，它运行良好，但它非常复杂！
In lieu of that, I saved the header row, deleted the first row, sorted the sheet, inserted a new row at the very top, re-created the header row, and formatted the header row. Wow! It didn't take that long to write and it works well, but it's awfully complicated!
从用户角度来看，标题行在弹出备份之前会短暂消失。这并不是太糟糕，但显然有些事情正在发生。
From the user perspective, the header row briefly disappears before popping back up. It's not too bad, but it's plainly obvious something is going on.
所以最后我解决了我的问题：我是否忽略了承认头部存在的能力当我排序时排？或者排序工作表中的标题行还排序标题行，无追索权？
So finally I get around to my question: Have I overlooked the ability to acknowledge the presence of a header row when I'm sorting? Or does sorting a sheet with a header row present in a script also sort the header row, with no recourse?
如果我的方法或类似方法需要避免在排序过程中包含标题行，是否有某处我可以通知Google Apps团队，以便他们可以考虑添加此功能？由于用一个简单的复选框指定了标题行的存在，我希望它存在或可以在脚本中排序时添加。
If my method, or one like it, is required to avoid including the header row in the sort process, is there somewhere that I can notify the Google Apps team so they can consider adding this feature? Since specifying the existence of a header row exists in the GUI with a simple checkbox, I hope it's present or can be added when sorting within a script.
编辑
我的原始代码（已删除文档/注释）：
My original code (with documentation/comments removed):
sheet.deleteRow(1);
rows.sort([<1st col>, <2nd col>]);
sheet.insertRowsBefore(1, 1);
for (var j = 0; j <= (numCols - 1); j++) {
sheet.getRange(1, (j + 1)).setValue(firstRow[j]);
}
sheet.getRange(1, 1, 1, numCols).setFontWeight("bold");
sheet.getRange(1, 1, 2, numCols).setBorder(true, true, true, true, true, true);
感谢Thomas的建议，我试过代替上面的代码：
What I've tried in place of the above code thanks for Thomas' suggestion:
sheet.setFrozenRows(1);
sheet.sort(<2nd col>);
sheet.sort(<1st col>);
sheet.setFrozenRows(0);
不幸的是，这只是按列而不是按行排序。用第一个代码块中的 rows.short（）; 行代替2 sheet.sort（）; 不起作用。尝试这样的结果会导致我在我的评论中报告的最初问题，其中标题行与其他数据一起排序，即使第一行在排序之前被冻结。
Unfortunately, this just sorts by column, not by row. Replacing the 2 sheet.sort(); calls with the rows.short(); line in the first code block doesn't work. Trying this results in the initial problem I reported in my comment where the header row is sorted along with the other data, even though the first row is frozen before sorting.
另外，除非在下面添加另一行代码，否则我会在电子表格的顶部获得永久的正在工作...通知。然而，它似乎没有影响任何东西。
Also, unless another line of code is added below this I get a perpetual "Working..." notification at the top of the spreadsheet. It doesn't seem to affect anything, however.
在任何情况下， var sheet = SpreadsheetApp.getActiveSheet（）; ， var rows = sheet.getDataRange（）; 和 firstRow 是标题行数据的数组。
In all cases, var sheet = SpreadsheetApp.getActiveSheet();, var rows = sheet.getDataRange();, and firstRow is an array of the header row data.
推荐答案
在所有情况下，var sheet = SpreadsheetApp.getActiveSheet（）;, var rows =
sheet.getDataRange（）;, firstRow是标题行
data的数组。
In all cases, var sheet = SpreadsheetApp.getActiveSheet();, var rows = sheet.getDataRange();, and firstRow is an array of the header row data.
使用getDataRange（），你可以得到你想要排序的范围（也就是排除标题行）：
Rather than using getDataRange(), you can just get the range you want to sort (that is, exclude the header row):
var sheet = SpreadsheetApp.getActiveSheet();
var rows = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn());
下一部分只是一个答案/观察到次要问题：
$ b
This next part is just an answer/observation to a "secondary" question:
脚本完成后，标题行与所有$一起排序
After the script is finished, the header row was sorted along with all the other rows although the first row is frozen.
我认为这对GAS来说有点限制，其中一些行是冻结的这些电子表格工具方法不能很好地互相发挥作用 - 此问题与您的不一样，但我认为它是（种）相关的。
I think this is a bit of a limitation in GAS where some of these "spreadsheet tool" methods don't play nice with one another - this issue is not the same as yours but I think it's (kind of) related.
因此，使用冻结头文件解决方法，我会感兴趣的是看看 SpreadsheetApp.flush（）; 紧接在 sheet.setFrozenRows（1）; 之后插入它。
So with the "frozen header" workaround, I'd be interested to see if SpreadsheetApp.flush(); inserted just after sheet.setFrozenRows(1); makes it work.
这篇关于Google Apps脚本：在脚本中排序时可以指定标题行吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
