How to Get Range From Pivot To Copy to Another Sheet Using Office Scripts?

Question

I want to get the range object of the Pivot based on a certain column. For example, I want to get the data for the pivot column 'Food'. On VBA, it is ptPivot.PivotFields("Food").DataRange

I have tried using the getLayOut() method, but I am not able to get my needed range.

Answer 1

Get the data range of a pvt row field.

function main(workbook: ExcelScript.Workbook) {
    const fldName = "Food"
    let selectedSheet = workbook.getActiveWorksheet();
    let pvt = selectedSheet.getPivotTables()[0];
    // console.log(pvt.getLayout().getBodyAndTotalRange().getAddress())
    const rowLable = pvt.getLayout().getRowLabelRange()
    const rowFld = pvt.getRowHierarchy(fldName)
    if (rowFld) {
        const fldIndex = rowFld.getPosition()
        console.log(rowLable.getColumn(fldIndex).getAddress())
    } else {
        console.log(`[${fldName}] is not a row field.`)
    }
}