How to automate pivot table filtering with Office Scripts?

Question

i'm trying to rebuild some VBA-Code in Office-Scripts, because I want to automate the execution via Microsoft Power Automate (former Flow). Now I have problems with a section of my code dealing with pivot tables. In the VBA-Code, i use:

    Dim oWSHilfsPivot As Worksheet
    Set oWSHilfsPivot = ActiveWorkbook.Sheets("Hilfs_Pivots")

    With oWSHilfsPivot.PivotTables("PivotTable1").PivotFields("Projekt")
         .PivotItems("(blank)").Visible = True
    End With

to automate the filtering of a pivot table:

How can I rebuild the filtering in Office Scripts? When I try to record it with the integrated Office Scripts Recorder, I get:

function main(workbook: ExcelScript.Workbook) {
   // Unknown event received with eventId:153
}

So it seems like Office Scripts doesn't support this functionality yet by Default. Of course the definition of the variable works, the filtering is the Problem. ;)

I appreciate any help.

Ethan · Accepted Answer · 2020-11-24T16:22:40.673

Within a PivotTable, you need: .getRowHierarchy & .getPivotItem and then set the visibility to false(shown below).

Example of the code below

newPivotTable.getRowHierarchy("Work Center")
  .getPivotField("Work Center").getPivotItem("DIM_INSP").setVisible(false);

function main(workbook: ExcelScript.Workbook) {
    // Add a new worksheet
    let sheet1 = workbook.addWorksheet();
    let selectedSheet = workbook.getWorksheet("New Sheet");
    // Add a new pivot table
    let newPivotTable = workbook.addPivotTable("PivotTable1", selectedSheet.getTable("MSRLoop"), sheet1.getRange("A3:C20"));
    // Add pivot field to a hierarchy
    newPivotTable.addRowHierarchy(newPivotTable.getHierarchy("Work Center"));
    // Change pivot position in a hierarchy
    newPivotTable.getRowHierarchy("Work Center")
      .setPosition(0);
    // Add pivot field to a hierarchy
    newPivotTable.addRowHierarchy(newPivotTable.getHierarchy("Order Type"));
    // Change pivot position in a hierarchy
    newPivotTable.getRowHierarchy("Order Type")
      .setPosition(1);
    // Add pivot field to a hierarchy
    newPivotTable.addDataHierarchy(newPivotTable.getHierarchy("Required Capacity"));
    // Change pivot position in a hierarchy
    newPivotTable.getDataHierarchy("Sum of Required Capacity")
      .setPosition(0);
    // Add pivot field to a hierarchy
    newPivotTable.addColumnHierarchy(newPivotTable.getHierarchy("Prdn Week"));
    // Change pivot position in a hierarchy
    newPivotTable.getColumnHierarchy("Prdn Week")
      .setPosition(0);


        //HERE IS HOW YOU FILTER IN A PIVOT TABLE

    newPivotTable.getRowHierarchy("Work Center")
      .getPivotField("Work Center").getPivotItem("DIM_INSP").setVisible(false);
    newPivotTable.getRowHierarchy("Work Center")
      .getPivotField("Work Center").getPivotItem("FIN_INSP").setVisible(false); 
    newPivotTable.getRowHierarchy("Work Center")
      .getPivotField("Work Center").getPivotItem("FLAT_INSP").setVisible(false);
    newPivotTable.getRowHierarchy("Work Center")
      .getPivotField("Work Center").getPivotItem("WELD_INSP").setVisible(false);
    newPivotTable.getRowHierarchy("Work Center")
      .getPivotField("Work Center").getPivotItem("(blank)").setVisible(false);
  }

Thanks a lot Ethan! Your answer was very helpful. For further use here our code to make blank Pivot items visible again: pivotTable.getRowHierarchy(hierarchies[index].getName()) .getPivotField(hierarchies[index].getName()).setShowAllItems(true); — ediordna, Dec 10 '20 at 12:41

How to automate pivot table filtering with Office Scripts?

1 Answers1