Forum Discussion
EXCEL ONLINE script filter pivottable with Power Automate data
Perhaps like
function main(workbook: ExcelScript.Workbook) { const filterField = workbook .getWorksheet("Sheet1") .getPivotTable("ptSample") .getHierarchy("Category") .getPivotField("Category") const filter: ExcelScript.PivotLabelFilter = { condition: ExcelScript.LabelFilterCondition.contains, substring: "Microsoft", exclusive: false }; filterField.applyFilter({ labelFilter: filter }); }Just an idea, but works on attached file
I was looking for something different as I wanted the search filter category for "*Microsoft*" to be in the Filter section as per image here:
Not sure if this is possible?
However, your solution works as well if I get one more step:
First, I want to get this layout which I managed to get:
Secondly: I want to collapse all fields to get to the following field:
Thanks!
If for manual filter as here
script could be
function main(workbook: ExcelScript.Workbook) {
const pivot =
workbook
.getWorksheet("Sheet1")
.getPivotTable("ptSample")
const filterFiled =
pivot
.getHierarchy("Category")
.getFields()[0]
const filterBy =
filterFiled
.getItems()
.map( x => x.getName() )
.filter(x => x.includes("Microsoft") )
filterFiled
.applyFilter( {
manualFilter: { selectedItems: filterBy }
} )
}it also works if move Category in rows
If finally you would like to have collapsed fields as
we may add to the script the block at the bottom
function main(workbook: ExcelScript.Workbook) {
const pivot =
workbook
.getWorksheet("Sheet1")
.getPivotTable("ptSample")
const filterFiled =
pivot
.getHierarchy("Category")
.getFields()[0]
const filterBy =
filterFiled
.getItems()
.map( x => x.getName() )
.filter(x => x.includes("Microsoft") )
filterFiled
.applyFilter( {
manualFilter: { selectedItems: filterBy }
} )
pivot.
getHierarchy("Product")
.getFields()[0]
.getItems()
.map( x => x.setIsExpanded(false))
}Thank you!
I found that the code from the first option is best usable for my situation. As it is a standard sheet which will be updated when other category names filtering, I don't need to add the collapse code.
KR,
Goudriaan , you are welcome.
More options we know the better, everything in Excel could be done by several different ways. What to use depends on concrete scenario.