在脚本中排序时可以指定标题行吗? [英] Can I specify a header row when sorting in a script?
问题描述
我有一个电子表格，在处理文档时，我想按多列多次对其进行排序.这是使用GUI的多步骤过程，似乎太耗时.我必须选中标题行的框，设置第一个排序参数，然后添加另一个. ！它很快就变老了.
排序工作表没有问题，但标题行也已排序！我找不到任何方法可以指定将标头行作为sort()函数的参数，也找不到任何全局定义标头行的方法，该行会被sort()自动注意到.
代替它，我保存了标题行，删除了第一行，对工作表进行了排序，在最顶部插入了新行，重新创建了标题行，并格式化了标题行.哇！编写并不需要花很长时间，而且效果很好，但是非常复杂！
从用户的角度来看，标题行短暂消失，然后弹出.虽然还算不错，但是显然正在发生什么事情.
所以最后我解决了我的问题:我是否忽略了排序时确认标题行存在的能力?还是对脚本中存在标题行的工作表进行排序是否也对标题行进行了排序，而没有任何追索权?
如果需要我的方法或类似的方法来避免在排序过程中包含标题行，是否可以通知Google Apps小组以便他们考虑添加此功能?由于在GUI中使用一个简单的复选框指定了标题行的存在，因此希望它存在或可以在脚本内排序时添加.
编辑
我的原始代码(已删除文档/注释):
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的建议:
sheet.setFrozenRows(1);
sheet.sort(<2nd col>);
sheet.sort(<1st col>);
sheet.setFrozenRows(0);
不幸的是，这只是按列而不是按行排序.在第一个代码块中用rows.short();行替换2个sheet.sort();调用不起作用.尝试执行此操作会导致出现最初的问题，我在我的评论中报告了该问题，即使在排序之前冻结了第一行，标题行仍与其他数据一起进行排序.
此外，除非在该代码下方添加另一行代码，否则电子表格顶部将始终显示正在工作..."通知.但是，它似乎没有任何影响.
在所有情况下，var sheet = SpreadsheetApp.getActiveSheet();，var rows = sheet.getDataRange();和firstRow是标题行数据的数组.
在所有情况下，var sheet = SpreadsheetApp.getActiveSheet();， var rows = sheet.getDataRange();，并且firstRow是标题行的数组 数据.
您无需获取getDataRange()，就可以获取要排序的范围(即，不包括标题行):
var sheet = SpreadsheetApp.getActiveSheet();
var rows = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn());
下一部分只是对次要"问题的回答/观察:
脚本完成后，标题行与所有 其他行，尽管第一行已冻结.
我认为这在GAS中有一定的局限性，其中某些电子表格工具"方法不能很好地配合使用-sheet.setFrozenRows(1);之后插入的SpreadsheetApp.flush();是否可以正常工作.
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.
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?
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.
EDIT
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);
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);
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.
In all cases, var sheet = SpreadsheetApp.getActiveSheet();, var rows = sheet.getDataRange();, and firstRow is an array of the header row data.
In all cases, var sheet = SpreadsheetApp.getActiveSheet();, var rows = sheet.getDataRange();, and firstRow is an array of the header row data.
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());
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.
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.
So with the "frozen header" workaround, I'd be interested to see if SpreadsheetApp.flush(); inserted just after sheet.setFrozenRows(1); makes it work.
这篇关于在脚本中排序时可以指定标题行吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
