Table column data used as PivotTable filter?

Question

In a worksheet titled 'Config' I have several lists of names in a table with several columns denoting different teams, for the sake of example lets say:

Team A	Team B
Alan	Dane
Geof	Keri

This team table will be updated and change with staff changes.

I have a raw data import in another sheet title 'Raw Data', this has an extract of timesheets logged with time spent on different customer accounts, example:

USER	CUSTOMER
Alan	Cust 1
Geof	Cust 3
Alan	Cust 3
Keri	Cust 3
Keri	Cust 2
Alan	Cust 5
Dane	Cust 4
Alan	Cust 5

I then have a pivot table in a third worksheet that has 'USER' as the filter hierarchy, 'CUSTOMER' as the row hierarchy, and 'DAYS' as the data hierarchy.

What I'm wanting is to filter the 'USER's in the pivot table dynamically using a specific column (lets say 'Team A') from the table on sheet one.

I have the two pivot tables just fine, but I can't figure out how to filter each for the respective team members to show what each team has worked on...

I'm fairly new to Office Scripts and not sure where to begin with this.

I've thought maybe there's a way to convert the table columns into comma-separated lists and pass through a 'pivotManualFilter' but how to actually make that happen is still unknown to me.

Answer 1

Obtain user list from 'Config' sheet. Use that list to filter the pivot table. This approach should work.

function main(workbook: ExcelScript.Workbook) {
    const pivotSheet = workbook.getActiveWorksheet();
    let myPivot = pivotSheet.getPivotTables()[0];
    // ger page filter field
    let userField = myPivot.getFilterHierarchy("USER").getFields()[0];
    // get user list of team A from 'Config' sheet
    let userItems = workbook.getWorksheet("Config")
        .getUsedRange().getColumn(0).getTexts()
        .slice(1).map(x=>x[0])
    console.log(userItems);
    userField.applyFilter({
        manualFilter: { selectedItems: userItems }
    });
}