Forum Discussion

Goudriaan's avatar
Goudriaan
Copper Contributor
May 30, 2023
Solved

EXCEL ONLINE script filter pivottable with Power Automate data

Hi all, 

 

I have a script where I have a pivot table (PivotTable1) on worksheet Transportkosten_klant. I want to use the customer specific name using a Power Automate flow which runs every quarter (for costs indication). 

 

My idea is using a template pivot table and filling the customer name in the filter option using a Excel Online Script. The thing is, I want to use a partial match in my PA flow -> (e.g. if I enter "Microsoft", I want the excel online script filter the pivot table to "Microsoft Apps", "Microsoft Power Automate", and "Microsoft Excel"). After that I copy the pivot table to a new Excel file on a SharePoint location with a different script. 

 

My script is found beneath and I tried multiple manners to check this without any success.

function main(workbook: ExcelScript.Workbook, custName:string, pivotName:string) {
    const custSheet = workbook.getWorksheet("Transportkosten_klant");  /* pivotName */
    const custPivot = custSheet.getPivotTable("PivotTable1");
    const classificationField = custPivot.getFilterHierarchy("customernames").getFields()[0];
    classificationField.applyFilter({
        manualFilter: {
            selectedItems: ["customer1"] /* custName */
       }
    });
}

 

any idea's how to tackle this?
Excel for web
Office Scripts
  • SergeiBaklan's avatar
    SergeiBaklan
    May 30, 2023

    Goudriaan 

    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

  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    May 30, 2023

    Goudriaan 

    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

    • Goudriaan's avatar
      Goudriaan
      Copper Contributor
      May 31, 2023

      SergeiBaklan 

       

      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!

      • SergeiBaklan's avatar
        SergeiBaklan
        MVP
        May 31, 2023

        Goudriaan 

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

}

Resources