删除电子表格中所有分组的行/列 [英] Remove all grouped rows / columns in a spreadsheet
问题描述
我有一个Google表格可以对脚本进行动态分组.我正在寻找一种功能，该功能可以消除其中的所有 Groups 再次工作表.
I have a Google Sheet that does dynamic grouping with a script. I am looking for a function that gets rid of all those Groups in the sheet again.
类似于 expandAllColumnGroups ，我想使用一个名为 removeAllColumnGroups 的函数-但似乎没有可用的函数.
Similar to expandAllColumnGroups, I would like to have a function called removeAllColumnGroups - but it seems there is no such function available.
我当前的方法非常缓慢且麻烦.我做了相当多的研究，但是甚至找不到一种方法来获取工作表中的所有 columnGroups 或至少是start-column-ID，因此我遍历每列并按字面意义 try (如果有)删除该组，因为无法判断是否退出某个组.不幸的是，对于大约90列，这需要很长时间(实际上是几分钟)...
My current approach is very slow and cumbersome. I did quite some research but could not even find a way to get all the columnGroups or at least the start-column-IDs in a sheet, so I iterate over every column and literally try to remove the group if there is one, as there is no way to tell if a group exits. Unfortunately for about 90 columns this takes ages (minutes, really)...
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sh = ss.getActiveSheet();
//remove all column-groups
var group = null;
var maxCol = sh.getMaxColumns();
for(var c = 1; c <= maxCol; c++) {
try {
group = sh.getColumnGroup(c, 1); // Try to get group
group.remove(); // Remove the group
} catch (e) {
//Logger.log("No Group at ColIndex "+c);
}
}
任何想法都将不胜感激.预先感谢！
Any ideas would be really appreciated. Thanks in advance!
推荐答案
在我的评论中扩展:使用Google Sheets REST API访问和修改行/列分组:
Expanding on my comment re: use of the Google Sheets REST API to access and modify row/column groupings:
行/列组是与 Sheet 资源相关联的元数据，因此可以通过向
Row/Column groups are metadata associated with a Sheet resource, and as such can be obtained for all sheets in a workbook with a single HTTP request to spreadsheets.get, with the appropriate fields specification:
GET https://sheets.googleapis.com/v4/spreadsheets/{YOUR_SPREADSHEET_ID}?fields=sheets(columnGroups%2Cproperties(sheetId%2Ctitle)%2CrowGroups)&key={YOUR_API_KEY}
上述请求返回一个具有 sheets 属性的对象，该对象是具有3个属性的对象数组(电子表格中每张纸1个): rowGroups ， columnGroups 和 properties .群组属性是 DimensionGroup 的数组对象，而 properties 对象包含工作表的gridId/sheetId(在各种API请求中标识工作表所需的信息)及其名称(可能对您自己的脚本逻辑有用)
The above request returns an object with a sheets property, which is an array of objects (1 per sheet in the spreadsheet) having 3 properties: rowGroups, columnGroups, and properties. The group properties are arrays of DimensionGroup objects, while the properties object contains the sheet's gridId/sheetId, which you need for identifying the sheet in various API requests, and its name (which may be useful for your own script logic).
要删除每个行/列组，您需要发出 DimensionRange ，在您的请求中，这被解释为电子表格的整个范围(所有行/所有列，具体取决于方向).
To delete every row/column group, you need to issue as many DeleteDimensionGroupRequests to do so as the maximum depth returned in your query's groups. If you do not specify the indices of the DimensionRange in your request, this is interpreted as the whole range of the spreadsheet (all rows / all columns, depending on direction).
一个示例请求(需要OAuth身份验证，而不仅仅是API密钥):
An example request (requires OAuth authentication, not just API key):
POST https://sheets.googleapis.com/v4/spreadsheets/{YOUR SPREADSHEET ID}:batchUpdate?fields=replies%2FdeleteDimensionGroup
{
"requests": [
{
"deleteDimensionGroup": {
"range": {
"sheetId": "{SHEET 1 ID}",
"dimension": "COLUMNS"
}
}
},
{
"deleteDimensionGroup": {
"range": {
"sheetId": "{SHEET 2 ID}"
"dimension": "COLUMNS",
}
}
},
...
]
}
每个删除请求都有一个回复a>，该响应将与您从初始查询中获得的行/列组的初始响应非常相似.如果您事先知道gridIds，则可以放弃初始查询，并使用while循环在响应包含维度组时继续发送删除请求.
Each delete request has a reply response, and that response will be very similar to the initial response you got for the row/column groups from the initial query. If you knew the gridIds beforehand, you could forgo the initial query and use a while loop to keep sending delete requests while the response contains a dimension group.
要将这些方法与Google Apps脚本一起使用，可以将 UrlFetchApp 与原始URL资源一起使用，也可以利用可用的客户端库 Sheets (必须先启用).这两种方法都要求您从脚本的 Google Cloud中启用表格API.平台项目页面.
To use these methods with Google Apps Script, you can either use UrlFetchApp with raw URL resources, or take advantage of the available "advanced service" client library Sheets (which must first be enabled). Both methods require you to enable use of the Sheets API from your script's Google Cloud Platform project page.
使用启用的客户端库 Sheets :
function removeAllGroups() {
const wb = SpreadsheetApp.getActive(),
wbId = wb.getId();
const initial = Sheets.Spreadsheets.get(wbId, {
fields: "sheets(columnGroups,properties(sheetId,title),rowGroups)"
});
// Determine the maximum depth of row & column groups on each sheet in the workbook.
const maxDepths = {row: {}, col: {}};
initial.sheets.forEach(function (s) {
// if (s.properties.title ... (could write logic to do this only for certain sheets)
var sId = s.properties.sheetId;
if (s.columnGroups && s.columnGroups.length)
maxDepths.col[sId] = s.columnGroups.reduce(dgMaxDepth_, 0);
if (s.rowGroups && s.rowGroups.length)
maxDepths.row[sId] = s.rowGroups.reduce(dgMaxDepth_, 0);
});
// Add all delete requests to an array
const rqs = [];
for (var rqType in maxDepths) {
for (var sheetId in maxDepths[rqType]) {
addDeleteDGRequests_(rqs, rqType, sheetId, maxDepths[rqType][sheetId]);
}
}
// Send all requests.
if (rqs.length) {
const replies = Sheets.Spreadsheets.batchUpdate({requests: rqs}, wbId);
console.log({message: "Batch response", response: replies});
}
}
// Callback for Array#reduce
function dgMaxDepth_(val, dg, i, allDGs) {
return Math.max(val, dg.depth);
}
function addDeleteDGRequests_(requests, rqType, sheetId, num) {
const dim = rqType === "col" ? "COLUMNS" : "ROWS";
while (num > 0) {
var rq = {
deleteDimensionGroup: {
range: { sheetId: sheetId,
dimension: dim }
}
};
requests.push(rq);
--num;
}
}
资源:
这篇关于删除电子表格中所有分组的行/列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
