How to Automate Excel Pivot-Style Summaries Using Office Scripts
Question details
The user wants to create automated pivot-style summaries, such as grouping records and totaling sales, in Excel using Office Scripts instead of traditional VBA.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Automating data summarization and reporting in Excel for the Web or Microsoft 365 without relying on desktop-bound VBA macros.
- Observed behavior
- A need to programmatically read a specific range, group data by a target field, calculate sum totals, and output the summarized data to a new or cleared worksheet.
Ensure your Microsoft 365 plan supports Office Scripts and that you have access to the Automate tab in Excel. Verify that your source data is organized in a clear tabular format with headers.
Create an Office Script for Pivot-Style Summaries
Use the Automate tab in Excel to write a cloud-based Office Script that reads, groups, and summarizes your data.
Office Scripts provide a modern, cloud-based alternative to VBA for automating tasks in Excel. You can use JavaScript/TypeScript to loop through records, calculate totals, and dynamically output the results to a summary sheet.
Navigate to the 'Automate' tab on the Excel ribbon and click 'New Script' to open the Code Editor panel.
Define the source worksheet and data range in your script. Ensure you account for zero-based indexing (e.g., if your range starts at column A, row[0] is column A).
Write the script logic to loop through the identified range, validate numeric values, group the records by your desired field, and sum the sales data.
Program the script to check for an existing 'Summary' sheet. Have the script clear existing data or create a new worksheet, and then write a header row.
Set the calculated summary values to populate below the header row. Save your script and click the 'Run' button to generate your automated summary.

Automate Pivot-Style Summaries with WPS Spreadsheet
While Office Scripts require specific Microsoft 365 plans, WPS Spreadsheet offers powerful built-in PivotTables and robust macro support to quickly group records and total sales, allowing you to automate complex data tasks completely for free.
- 1. Open your data file: Launch WPS Spreadsheet and open your dataset.
- 2. Insert a PivotTable: Select your data range, navigate to the 'Insert' tab, and click 'PivotTable'.
- 3. Configure the summary: Choose to place the PivotTable in a new 'Summary' worksheet. Drag your grouping field into the 'Rows' area and your sales field into the 'Values' area.
- 4. Automate with macros: To fully automate this process for future files, use the 'Developer' tab to record a macro of these steps, enabling instant summary generation.

Frequently Asked Questions
Can I use Office Scripts on the desktop version of Excel?
Yes, Office Scripts can be run on Excel for Windows and Mac, but you must have a qualifying Microsoft 365 subscription and an active internet connection, as the scripts are stored and executed in the cloud.
How is an Office Script different from a VBA macro?
Office Scripts use TypeScript/JavaScript and are stored in the cloud, making them ideal for Excel for the Web and cross-platform automation. VBA macros use Visual Basic, are bound to the desktop version of Excel, and are stored locally within the workbook file.
Why is my Office Script summary showing data from the wrong column?
Office Scripts use zero-based indexing for arrays. Ensure your script logic accounts for this; for example, if your data starts in column A, row[0] represents column A, and row[1] represents column B.
How do I clear an existing summary sheet before running my script?
You can program your Office Script to check if a worksheet named 'Summary' exists. If it does, use the 'getRange().clear()' method to empty previous data before writing the new totals and headers to ensure clean results.




