I'm trying to collapse the rows of a pivot table within the sheet using @Tanaike's method: , but for some reason, the rows won't collapse if I don't manually collapse them first. Rows that I did not manually collapse will stay expanded. I'm using the exact same script here:
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 = true);
}
});
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);
}
Am I missing something in the code? Here's the sample sheet: #gid=2100307022
I'm trying to collapse the rows of a pivot table within the sheet using @Tanaike's method: https://stackoverflow/a/76227150, but for some reason, the rows won't collapse if I don't manually collapse them first. Rows that I did not manually collapse will stay expanded. I'm using the exact same script here:
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 = true);
}
});
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);
}
Am I missing something in the code? Here's the sample sheet: https://docs.google/spreadsheets/d/1Zsj-EcUY2G0n8fgJtgzz0kTwNDTU8ZqQ_W40s8SD3cM/edit?gid=2100307022#gid=2100307022
Share Improve this question edited 2 days ago vk26 asked Feb 17 at 15:47 vk26vk26 2281 silver badge9 bronze badges 1- @Patsytalk I've added the sample sheet. Thanks for the advice. – vk26 Commented 2 days ago
1 Answer
Reset to default 1Tanaike's script, as quoted in the question, doesn't collapse rows. It expands collapsed rows.
From Tanaike's answer:
If you want to collapse all row groups, please modify
pr.valueMetadata.forEach(vm => vm.collapsed = false);topr.valueMetadata.forEach(vm => vm.collapsed = true);
To toggle between expanded and collapsed, use this:
pr.valueMetadata.forEach(vm => vm.collapsed = !vm.collapsed);
The execution log of the script project in the sample spreadsheet has several errors like this:
"Exception: Service Google Sheets API has not been enabled for your Apps Script-managed Cloud Platform project."
The Sheets API seems to now have been enabled therein, but for subsequent readers: you need to click Services and enable the Sheets API for the code to work.