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