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