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.

Answer 1

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

Answer 2

Here is how I apply a manual filter in a pivot table based in a range of values:

function main(workbook: ExcelScript.Workbook) {

const clientPT = workbook.getPivotTables()[0];
const client = clientePT.getHierarchy("Client").getPivotField("Client");
const sheet = workbook.getWorksheet("ClientBase");
const range = sheet.getRange("A2:A100");

let array = [""];

  for (let i = 0; i < range.getTexts().length; i++) {
    array[i] = range.getTexts()[i].toString();
    //console.log(JSON.stringify(i, null, 4));
  }
//console.log(JSON.stringify(array, null, 4));  
  client.applyFilter ({
    manualFilter: {
      selectedItems: array
    }
  })
}