How to apply conditional formatting only on the top level rows in the pivot table hierarchies

I’m trying to find the addresses of all the top-level rows in hierarchies to apply conditional formatting to the pivot table. It seems that using the method below, I can only extract the definitions of the fields and not the actual locations on the sheet.

  function fn2(workbook: ExcelScript.Workbook) {

    const sheet = workbook.getWorksheet("RC PivotTable");

    // Get the PivotTable (assuming it's the first one)
    const pivotTable = sheet.getPivotTables()[0];

    // Access the row hierarchy
    const rowHierarchies = pivotTable.getRowHierarchies();

    // Iterate through each row hierarchy
    for (let i = 0; i < rowHierarchies.length; i++) {
        const hierarchy = rowHierarchies[i];

        // Get the fields
        const fields = hierarchy.getFields();
        for (let j = 0; j < fields.length; j++) {
            const field = fields[j];

            // Get the items
            const items = field.getItems();
            for (let k = 0; k < items.length; k++) {
                const item = items[k];
                console.log(`hierarchy: ${hierarchy.getName()} | field: ${field.getName()} | item: ${item.getName()}`);
            }
        }
    }
}

Is there any way to extract the address ranges of each top-level row displayed on the sheet?

There isn’t a method to directly obtain the range of a pivot field. However, there is a workaround to get it. Adjust as needed based on your pivot table layout.

function main(workbook: ExcelScript.Workbook) {
    const sheet = workbook.getWorksheets()[0];
    // Get the PivotTable (assuming it's the first one)
    const pivotTable = sheet.getPivotTables()[0];
    // Access the row hierarchy
    const rowHierarchies = pivotTable.getRowHierarchies();
    // Iterate through each row hierarchy
    let topCateList: string[] = [];
    const TARGET = "Category";
    for (let i = 0; i < rowHierarchies.length; i++) {
        const hierarchy = rowHierarchies[i];
        if (hierarchy.getName() === TARGET) {
            // Get the fields
            const fields = hierarchy.getFields();
            for (let j = 0; j < fields.length; j++) {
                const field = fields[j];
                // Get the items
                const items = field.getItems();
                for (let k = 0; k < items.length; k++) {
                    const item = items[k];
                    // extract "B" from the string "[Range].[Category].&[B]", modify as needed
                    let topCateItem = item.getName().split("&[").pop()?.split("]")[0] || null
                    topCateList.push(topCateItem)
                }
            }
        }
    }
    const pvtRng = pivotTable.getLayout().getRange(); // pivottable range
    const rowLableRng = pivotTable.getLayout().getRowLabelRange(); // row lable range
    const rowCnt = rowLableRng.getRowCount();
    for (let i = 0; i < rowCnt; i++) {
        const cellRng = rowLableRng.getCell(i, 0);
        if (topCateList.includes(cellRng.getText())) { // apply filling color
            cellRng.getEntireRow().getIntersection(pvtRng).getFormat().getFill().setColor("FFFF00")
        }
    }
}