Here’s an example of an OfficeScript that creates a pivot table using the range A1:Z200 in Sheet1 and adds sample rows, columns, and values to the pivot:
function main(workbook: ExcelScript.Workbook) { // Get the source sheet and range let sourceSheet = workbook.getWorksheet("Sheet1"); let sourceRange = sourceSheet.getRange("A1:Z200"); // Create a new sheet for the pivot table let pivotSheet = workbook.addWorksheet("Pivot"); // Create the pivot table on the pivot sheet let pivotTable = pivotSheet.addPivotTable( sourceRange, pivotSheet.getRange("A1") ); // Add sample rows to the pivot table pivotTable.addRowHierarchy(pivotTable.getHierarchy("Row"), "Column1"); // Add sample columns to the pivot table pivotTable.addColumnHierarchy(pivotTable.getHierarchy("Column"), "Column2"); // Add sample values to the pivot table pivotTable.addValue( pivotTable.getHierarchy("Value"), "Sum of Values", ExcelScript.PivotTableCalculation.Sum ); }
To use this OfficeScript, follow these steps:
- Open Excel Online or Excel for the web.
- Open an existing workbook or create a new workbook.
- Click on the “Automate” tab in the ribbon.
- Click on the “Script Lab” button.
- In the Script Lab pane, create a new script or open an existing one.
- Replace the default code in the script editor with the code provided above.
- Click the “Run” button to execute the script.
This script creates a new sheet named “Pivot” and creates a pivot table using the range A1:Z200 in Sheet1. It then adds sample rows, columns, and values to the pivot table. You can modify the range, the sample rows/columns/values, and their names according to your specific data and requirements.
Please note that OfficeScripts are currently only supported in Excel for the web and Excel Online. They are not available in the desktop version of Excel.