How can expand all PivotTable of Google SpreadSheet by Google Script?

Question

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?

Answer 1

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); to pr.valueMetadata.forEach(vm => vm.collapsed = true);.

References:

• Class PivotGroup
• Method: spreadsheets.get
• Method: spreadsheets.batchUpdate
• PivotTable