VBA & Macro Problems

Fix Office Scripts Error When Splitting Excel Worksheet by Column

Adam Davis Oct 1, 2026 868 views

Question details

The user needs to resolve an Office Scripts execution failure that occurs when attempting to group or split worksheet rows based on a specific column's value.

Fix Office Scripts Error When Splitting a Worksheet by Column

Product: Microsoft Excel
Device & OS: not provided
Scenario: Using an Office Script to iterate through worksheet data and group rows by a specific value, such as the contents of column 4.
Observed behavior: The script fails and throws the error 'Cannot read properties of undefined (reading 'push')' because the target array or object was not initialized before the push method was executed.

Before you start

Review your script's variable declarations and identify the exact line number where the `.push()` method is being called to verify the state of your arrays before execution.

Solution 1Recommended

Initialize the Array Before Executing the Push Method

Resolve the 'undefined' error by adding a conditional check to ensure the target group or array is properly initialized before appending new row data.

In TypeScript and JavaScript, which Office Scripts rely on, you cannot use the `.push()` method on a variable that does not yet exist as an array. When grouping rows dynamically by column values, the script must first check if an array for that specific value exists. If it does not, it must be created as an empty array before data is pushed into it.

Locate the error in your script

Open the Code Editor in Excel and find the specific line in your loop where the script attempts to group the row data using the `.push()` method (e.g., `myGroup[columnValue].push(row);`).

Add an initialization check

Immediately above the `.push()` statement, insert an `if` condition to verify the existence of the array key. For example: `if (!myGroup[columnValue]) { myGroup[columnValue] = []; }`.

Run and test the script

Save the modifications and click 'Run'. The script will now properly instantiate the array for each unique column value before appending the row data, resolving the undefined property error.

Initialize the Array Before Executing the Push Method

Code Structure Verified: Properly initializing arrays prevents runtime object errors and ensures your grouped data is structured correctly for subsequent processing.

Solution 2

Consult the Microsoft Office Development Forum

If the script contains complex logic or modifying the array initialization does not resolve the problem, requesting help from specialized developers is the most effective approach.

Free Microsoft Office alternative

Use WPS Office for Powerful Data Processing and Automation

If you are struggling with complex Office Scripts in Microsoft Excel, consider switching to WPS Office. It provides an intuitive environment for data management and features powerful built-in JS Macros that are highly accessible, making it a robust and lightweight alternative for your spreadsheet automation needs.

1. Download WPS Office: Visit the official WPS website to download and install the free WPS Office suite.
2. Open Your Spreadsheet: Launch WPS Spreadsheet and open your existing .xlsx file directly without any format conversion.
3. Explore JS Macros: Navigate to the 'Tools' tab and select 'JS Macro' to start automating your worksheet tasks in a highly compatible JavaScript environment.

Fully compatible with Microsoft Excel formats (.xlsx, .xls, .csv).Free, lightweight, and fast alternative to Microsoft Office.Built-in JS Macros and VBA support for robust spreadsheet automation.Familiar user interface requiring zero learning curve for Excel users.

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get a 'Cannot read properties of undefined' error in Office Scripts?

This error is thrown when you attempt to access a property or execute a method (like `.push()`) on a variable that has not been defined or assigned a value in JavaScript/TypeScript. In worksheet splitting scripts, it typically happens when trying to push data into a category array that hasn't been initialized.

How do I group rows by a specific column value using scripts?

You can group rows by reading the data range using `getValues()`, iterating through each row with a `for` loop, extracting the value from your target column index, and storing the rows in a dictionary or Map object using that extracted value as the key.

Are Office Scripts the same as VBA macros?

No. Office Scripts are based on TypeScript/JavaScript and are primarily designed to run in Excel on the web and Microsoft 365 cross-platform environments. VBA is an older, distinct programming language heavily used for macros in desktop versions of Office.

Where can I find examples of Office Scripts for Excel?

Sample scripts and extensive documentation can be found on the Microsoft Learn platform under the Office Scripts section. You can also find tailored solutions and community discussions in the Microsoft Q&A Office Development forum.

Excel & Data Problems