在谷歌应用程序脚本中分组和折叠行

Question

我正在尝试折叠数据，但我不知道如何显示需要折叠数据的分组列

var rangeStaff = staffInWork.getRange(2,1,LASTROWSTAFFINWORK-1,LASTCOLUMNSTAFFINWORK);
rangeStaff.shiftRowGroupDepth(1);  
var groupStaff = staffInWork.getRowGroupAt( ? 'I do not know what settings insert here '?);
groupStaff.collapse();

• 行分组前的表格

• 预期结果：按子类别分组的行(这就是我想要得到的(

• 我的代码运行后的当前错误结果

电子表格应用程序组类的文档

Answer 1

您可以最简洁地将行分组在一个范围内，如下所示：

rangeStaff.shiftRowGroupDepth(1).collapseGroups();

但是，您似乎要对多个行组进行分组和折叠，其中列A中的值决定是否应折叠一行。在母行中，列A为空，在子行中，它为非空。

尝试类似这样的groupRowsByCriteria_()函数：

function groupRowsStaffInWork() {
const groupRowsBy = 'staffInWork!A2:A';
groupRowsByCriteria_({
keyRange: SpreadsheetApp.getActive().getRange(groupRowsBy),
motherRow: /^()$/i, // () means blank
daughterRow: /^(.+)$/i, // (.+) means at least one character
collapse: true,
});
}
/**
* Groups all rows that meet criteria and optionally collapses the groups.
*
* @param {Object} criteria An object with this structure:
*                 {Range} keyRange The range within sheet where to find motherRows and daughterRows.
*                 {RegExp} motherRow A regular expression that identifies a value in the criteria column as a mother row.
*                 {RegExp} daughterRow A regular expression that identifies a value in the criteria column as a daughter row.
*                 {Boolean} collapse Optional. Whether to collapse the groups.
* @return {Sheet} The sheet where rows were grouped.
*/
function groupRowsByCriteria_(criteria) {
// version 1.0, written by --Hyde, 1 June 2021
const sheet = criteria.keyRange.getSheet();
const rowStart = criteria.keyRange.getRow();
const rowEnd = getLastRow_(sheet);
const keys = criteria.keyRange.getDisplayValues().flat();
const daughterRows = [];
keys.forEach((key, index) => {
if (rowStart + index <= rowEnd && key.match(criteria.daughterRow) && !key.match(criteria.motherRow)) {
daughterRows.push(rowStart + index);
}
});
const runLengths = getRunLengths_(daughterRows);
runLengths.forEach(tuple => {
const [start, numRows] = tuple;
const range = sheet.getRange(start, 1, numRows);
range.shiftRowGroupDepth(1);
if (criteria.collapse) {
range.collapseGroups();
}
});
return sheet;
}

/**
* Counts consecutive numbers in an array and returns a 2D array that
* lists the first number of each run and the number of items in each run.
*
* The numbers array [1, 2, 3, 5, 8, 9, 11, 12, 13, 5, 4] will get
* the result [[1, 3], [5, 1], [8, 2], [11, 3], [5, 1], [4, 1]].
*
* For best results, sort the numbers array like this:
* const runLengths = getRunLengths_(numbers.sort((a, b) => a - b));
* Note that duplicate values in numbers will give duplicates in result.
*
* @param {Number[]} numbers The numbers to group into runs.
* @return {Number[][]} The numbers grouped into runs, or [] if the array is empty.
*/
function getRunLengths_(numbers) {
// version 1.1, written by --Hyde, 31 May 2021
if (!numbers.length) {
return [];
}
return numbers.reduce((accumulator, value, index) => {
if (!index || value !== 1 + numbers[index - 1]) {
accumulator.push([value]);
}
const lastIndex = accumulator.length - 1;
accumulator[lastIndex][1] = (accumulator[lastIndex][1] || 0) + 1;
return accumulator;
}, []);
}

/**
* Gets the position of the last row that has visible content in a column of the sheet.
* When column is undefined, returns the last row that has visible content in any column.
*
* @param {Sheet} sheet A sheet in a spreadsheet.
* @param {Number} columnNumber Optional. The 1-indexed position of a column in the sheet.
* @return {Number} The 1-indexed row number of the last row that has visible content.
*/
function getLastRow_(sheet, columnNumber) {
// version 1.5, written by --Hyde, 4 April 2021
const values = (
columnNumber
? sheet.getRange(1, columnNumber, sheet.getLastRow() || 1, 1)
: sheet.getDataRange()
).getDisplayValues();
let row = values.length - 1;
while (row && !values[row].join('')) row--;
return row + 1;
}