Multiple Filter Criteria -Excel Table

Question

I am trying to filter the following table with multiple Criteria which are passed from PowerAutomate. I want to filter the Product column for "Product-1", "Product-3", "Product-5", Location column for "East", "West" and Colour column for "Black", "White".

I am passing three variable from Power-Automate to office script as product_Name , product_Location and product_Color. Where

product_Name = "Product-1", "Product-3", "Product-5"

product_Location = "East", "West"

product_Color="Black", "White"

I am using the following office script to apply the filter, however with this the filter can not be applied as it in only filtering the column as "Product-1", "Product-3", "Product-5" not a row specific.

function main(workbook: ExcelScript.Workbook,
    product_Name?: string,
    product_Location?: string,
    product_Color?: string,
) {
    let table2 = workbook.getTable("Table2");
    // Apply checked items filter on table table2 column Product
    table2.getColumnByName("Product").getFilter().applyValuesFilter([product_Name]);

    // Apply checked items filter on table table2 column Location
    table2.getColumnByName("Location").getFilter().applyValuesFilter([product_Location]);

    // Apply checked items filter on table table2 column Color
    table2.getColumnByName("Color").getFilter().applyValuesFilter([product_Color]);
}

Table:

Product	Location	Colour
Product-1	East	Black
Product-2	West	Red
Product-3	South	Blue
Product-4	West	Blue
Product-5	East	Yellow
Product-1	West	White
Product-2	East	Black
Product-3	West	Red
Product-4	South	Blue
Product-5	West	Blue
Product-1	East	Yellow
Product-2	West	White
Product-3	South	Black
Product-4	West	Red
Product-5	South	Blue
Product-1	West	Blue
Product-2	East	Yellow
Product-3	West	White

how can I use the variables as proper filter? Can any one help on this ?

Answer 1

applyValuesFilter takes an array of strings as a parameter. The simplest way you could do this is by changing your parameter to an array of strings -

function main(workbook: ExcelScript.Workbook,
    product_Names?: string[],
    product_Locations?: string[],
    product_Colors?: string[],
) {
    let table2 = workbook.getTable("Table2");
    // Apply checked items filter on table table2 column Product
    table2.getColumnByName("Product").getFilter().applyValuesFilter(product_Names);

    // Apply checked items filter on table table2 column Location
    table2.getColumnByName("Location").getFilter().applyValuesFilter(product_Locations);

    // Apply checked items filter on table table2 column Color
    table2.getColumnByName("Color").getFilter().applyValuesFilter(product_Colors);
}

The values for Power Automate would have to be changed to arrays as well

product_Name = ["Product-1", "Product-3", "Product-5"]

product_Location = ["East", "West"]

product_Color= ["Black", "White"]