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