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.

taller · Accepted Answer · 2024-07-12 12:30:35Z

0

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.`)
    }
}

answered Jul 12, 2024 at 12:30

taller

19.7k2 gold badges9 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mean_ Over a year ago

Thank you @taller ! This is exactly what i am looking for.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related