Here’s a sample OfficeScript that demonstrates how to read various properties of a pivot table in Excel:
In this example, the readPivotTableProperties function performs the following steps:
It gets the active worksheet using the
getActiveWorksheetmethod.It retrieves the first pivot table in the worksheet using the
getPivotTables().getAt(0)method. If you have multiple pivot tables, adjust the index as needed.It reads various properties of the pivot table, such as the name, source range, column count, row count, and value count.
The pivot table properties are stored in variables:
pivotTableName,pivotTableSourceRange,pivotTableColumnCount,pivotTableRowCount, andpivotTableValueCount, respectively.Finally, it logs the pivot table properties to the console.
You can call the readPivotTableProperties function to see the pivot table properties in the console output.
Please note that this example assumes that you have a workbook open and a worksheet with at least one pivot table in Excel for the web or Excel Online, where you can execute the OfficeScript.
Additionally, remember to set up the necessary context and load the OfficeScript runtime to ensure the script executes successfully.