vbaoffice365pivot-tablemicrosoft365office-scripts

How to automate pivot table filtering with Office Scripts?


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: Excel_screenshot_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.


Solution

  • 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);
    

    Pivot Table Fields

    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);
      }