How to Move Completed Excel Table Rows Using Office Scripts
Question details
The user needs to move rows marked with 'Y' in a 'Complete' column from a source 'Job' table to a destination 'CompletedJobs' table using Office Scripts.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Automating task management by transferring completed tasks between two Excel tables using a script.
- Observed behavior
- The current script returns no rows and fails to move the data, likely due to invalid APIs, incorrect table names, or legacy Excel types.
Ensure that both your source table (e.g., 'Job') and destination table (e.g., 'CompletedJobs') are explicitly defined in your workbook with identical column structures before running the script.
Update to the Correct ExcelScript Namespace and APIs
Office Scripts require specific TypeScript namespaces and strict typing. Using older VBA logic or legacy Excel types will cause the script to fail or return no data.
Office Scripts use the `ExcelScript` namespace instead of the legacy `Excel` namespace used in Office Add-ins. Properly defining types like `ExcelScript.Table` and processing row deletions in reverse order ensures the script correctly handles data movement without skipping rows.
Use the exact string names of your tables in the script. Call `workbook.getTable('Job')` and `workbook.getTable('CompletedJobs')` to explicitly declare your source and destination tables.
Avoid using explicit `any` types in your TypeScript code. Strongly type your variables using `ExcelScript.Table`, `ExcelScript.Range`, and two-dimensional arrays.
Retrieve the range values from the 'Job' table and loop through them to identify rows where the 'Complete' column value strictly equals 'Y'.
Add the matched rows to the 'CompletedJobs' table. Then, delete the matching rows from the 'Job' table by looping backwards (in reverse order). This prevents index shifting from affecting subsequent deletions.
Try WPS Office for Powerful Spreadsheet Automation
While Office Scripts is specific to Microsoft 365 cloud environments, WPS Office provides a highly compatible and lightweight desktop alternative with robust support for JS Macros and VBA to automate your local data movement tasks efficiently.
Frequently Asked Questions
Why does my Office Script skip rows when deleting them from the source table?
When you delete a row, the rows below it shift up, changing their index. If you loop forward, you will skip the row immediately following a deleted row. To fix this, always loop through and delete rows in reverse order (from bottom to top).
Can I use standard Excel VBA commands in Office Scripts?
No, Office Scripts and VBA use different languages and environments. Office Scripts are based on TypeScript/JavaScript and use the ExcelScript namespace on the web, whereas VBA is a desktop scripting language.
What happens if I use 'any' types in my Office Script?
Using explicit 'any' types bypasses TypeScript's strict type checking, which can lead to runtime errors or methods failing silently, such as returning no rows. Always strongly type variables like ExcelScript.Table or ExcelScript.Range.
How do I correctly reference an Excel table in an Office Script?
You must use the `workbook.getTable('TableName')` method in your script, replacing 'TableName' with the exact name of your table as defined in the Excel Worksheet Name Manager.
