英文:
How can expand all PivotTable of Google SpreadSheet by Google Script?
问题
我想通过脚本展开所有在电子表格中折叠的行:
我有这段代码:
var pivotTables = sheet.getPivotTables();
for (var i = 0; i < pivotTables.length; i++) {
var pivotTable = pivotTables[i];
var rows = pivotTable.getRowGroups();
for (var j = 0; j < rows.length; j++) {
rows[j].expand();
}
}
但出现错误:TypeError: rows[j].expand is not a function
如何通过Google Script展开Google电子表格的所有透视表?
英文:
I want expand all rows had Collapse in a SpreadSheet by Script:
I had this code:
var pivotTables = sheet.getPivotTables();
for (var i = 0; i < pivotTables.length; i++) {
var pivotTable = pivotTables[i];
var rows = pivotTable.getRowGroups();
for (var j = 0; j < rows.length; j++) {
rows[j].expand();
}
}
But occur error: TypeError: rows[j].expand is not a function
How can expand all PivotTable of Google SpreadSheet by Google Script?
答案1
得分: 2
修改点:
- 不幸的是,似乎 SpreadsheetApp.PivotGroup 没有
expand
方法。链接 - 我之前在寻找展开和折叠行列组的方法,但不幸的是,我找不到。但幸运的是,我确认了当使用 Sheets API 时,可以实现展开和折叠数据透视表的行列组。
基于以上情况,我想建议使用 Sheets API 来实现。示例脚本如下。
示例脚本:
请复制并粘贴以下脚本到电子表格的脚本编辑器中。在使用此脚本之前,请启用高级 Google 服务中的 Sheets API。
并且,请设置您的工作表名称并保存脚本。
function myFunction() {
var sheetName = "Sheet1"; // 请设置您的工作表名称。
var ss = SpreadsheetApp.getActiveSpreadsheet();
var ssId = ss.getId();
var sheet = ss.getSheetByName(sheetName);
var sheetId = sheet.getSheetId();
// 使用 Sheets API 检索数据透视表。
var obj = Sheets.Spreadsheets.get(ssId, { ranges: [sheetName], fields: "sheets(data(rowData(values(pivotTable))))" }).sheets[0];
// 通过展开行组创建请求主体。
var requests = obj.data[0].rowData.reduce((ar, r, i) => {
if (r.values) {
r.values.forEach((c, j) => {
if (c.pivotTable) {
var pivotTable = c.pivotTable;
["rows"].forEach(e => { // 如果要展开行和列,请使用 ["rows", "columns"].forEach(e => {
if (pivotTable[e]) {
pivotTable[e].forEach(pr => {
if (pr.valueMetadata) {
pr.valueMetadata.forEach(vm => vm.collapsed = false);
}
});
ar.push({
updateCells: {
range: { sheetId, startRowIndex: i, endRowIndex: i + 1, startColumnIndex: j, endColumnIndex: j + 1 },
rows: [{ values: [{ pivotTable }] }],
fields: "pivotTable"
}
});
}
});
}
});
}
return ar;
}, []);
if (requests.length == 0) return;
// 请求 Sheets API 的 batchUpdate 方法。
Sheets.Spreadsheets.batchUpdate({ requests }, ssId);
}
- 运行此脚本时,将展开所有数据透视表的行组。
- 如果您想展开行组和列组,请将
["rows"].forEach(e => {
修改为["rows", "columns"].forEach(e => {
。 - 如果您想折叠所有行组,请将
pr.valueMetadata.forEach(vm => vm.collapsed = false);
修改为pr.valueMetadata.forEach(vm => vm.collapsed = true);
。
参考:
英文:
Modification points:
- Unfortunately, it seems that SpreadsheetApp.PivotGroup has no method of
expand
. Res - I was looking for the method for expanding and collapsing the groups of rows and columns before. But, unfortunately, I couldn't find it. But, fortunately, I confirmed that when Sheets API is used, expanding and collapsing the groups of rows and columns of the pivot table can be achieved.
From the above situation, in this answer, I would like to propose using Sheets API. The sample script is as follows.
Sample script:
Please copy and paste the following script to the script editor of Spreadsheet. Before you use this script, please enable Sheets API at Advanced Google services.
And, please set your sheet name and save the script.
function myFunction() {
var sheetName = "Sheet1"; // Please set your sheet name.
var ss = SpreadsheetApp.getActiveSpreadsheet();
var ssId = ss.getId();
var sheet = ss.getSheetByName(sheetName);
var sheetId = sheet.getSheetId();
// Retrieve pivot table using Sheets API.
var obj = Sheets.Spreadsheets.get(ssId, { ranges: [sheetName], fields: "sheets(data(rowData(values(pivotTable))))" }).sheets[0];
// Create request body by expanding groups of rows.
var requests = obj.data[0].rowData.reduce((ar, r, i) => {
if (r.values) {
r.values.forEach((c, j) => {
if (c.pivotTable) {
var pivotTable = c.pivotTable;
["rows"].forEach(e => { // If you want to expand both rows and columns, please use ["rows", "columns"].forEach(e => {
if (pivotTable[e]) {
pivotTable[e].forEach(pr => {
if (pr.valueMetadata) {
pr.valueMetadata.forEach(vm => vm.collapsed = false);
}
});
ar.push({
updateCells: {
range: { sheetId, startRowIndex: i, endRowIndex: i + 1, startColumnIndex: j, endColumnIndex: j + 1 },
rows: [{ values: [{ pivotTable }] }],
fields: "pivotTable"
}
});
}
});
}
});
}
return ar;
}, []);
if (requests.length == 0) return;
// Request the batchUpdate method of Sheets API.
Sheets.Spreadsheets.batchUpdate({ requests }, ssId);
}
- When this script is run, all row groups of all pivot tables are expanded.
- When you want to expand both row groups and column groups, please modify
["rows"].forEach(e => {
to["rows", "columns"].forEach(e => {
. - If you want to collapse all row groups, please modify
pr.valueMetadata.forEach(vm => vm.collapsed = false);
topr.valueMetadata.forEach(vm => vm.collapsed = true);
.
References:
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论