VBA & Macro Problems

How to Copy Excel Rows by Priority Using Office Scripts and Power Automate

Maira Mehtab Sep 28, 2026 869 views

Question details

The user needs an Office Script to automatically copy selected fields from an Excel worksheet to a dashboard based on priority, keeping it updated via Power Automate.

Product: Microsoft Excel / Power Automate
Device & OS: not provided
Scenario: Managing a dynamic work order dashboard where rows are continuously added or deleted and must be sorted by priority.
Observed behavior: Requires a custom script to read column priorities, group data, clear or resize destination ranges, and update seamlessly.

Before you start

Ensure your source data is formatted as an official Excel Table (Insert > Table) so that Office Scripts and Power Automate can read dynamic row ranges accurately without hardcoding.

Solution 1Recommended

Build the Office Script and Power Automate Flow

Define your workbook layout and build an Office Script that handles data grouping and dynamic dashboard updates.

Because this automation requires a custom Office Script integrated with Power Automate, you must first define clear rules for your destination ranges and column mappings. If you run into complex script errors, the Microsoft Office Development community is the best resource for tailored code solutions.

Prepare the Source Table

Ensure your source data is inside an Excel Table. Document the column mappings and destination ranges on your WIP Overview worksheet.

Write the Office Script

Create a script via the Automate tab > New Script that reads the source table, groups rows based on the priority found in Column E, and clears or resizes the destination ranges on the dashboard.

Write the Results

Ensure the script is programmed to write the filtered, priority-based results into the Work Order in Progress worksheet.

Integrate with Power Automate

Set up a Power Automate flow triggered by a specific event (like a scheduled time or row addition) and use the 'Run script' Excel action to execute your Office Script automatically.

Community Support: For specialized Office Script coding, it is highly recommended to post your workbook layout, column mappings, and destination ranges in the Microsoft Office Development community for expert assistance.

Free Microsoft Office alternative

Need a Lightweight Alternative for Advanced Spreadsheet Management?

If complex cloud automations like Power Automate and Office Scripts are too heavy, expensive, or complex for your daily needs, WPS Office offers a free, lightweight, and powerful alternative. Enjoy robust local automation via built-in JS Macros and a seamless experience for all your document tasks.

1. Download WPS Office: Install WPS Office for free from the official website.
2. Open Your Spreadsheets: Open your existing .xlsx workbooks directly in WPS Spreadsheets without losing any formatting.
3. Automate with JS Macros: Navigate to the Tools tab and use the JS Macro Editor to write local automation scripts without needing external cloud flows.

100% compatible with Microsoft Excel formats (.xlsx, .xls, .csv).Built-in JS Macro support for powerful local task automation.Lightweight software that runs smoothly on Windows, Mac, and Linux.Familiar user interface requiring zero learning curve for Excel users.

Frequently Asked Questions

Can I use Power Automate with standard Excel VBA macros?

Power Automate cloud flows primarily interact with Office Scripts for Excel Online. Standard VBA macros (.xlsm) cannot be run directly by cloud-based Power Automate without using workarounds like Power Automate Desktop.

Why does my Office Script fail to copy newly added rows?

This usually happens if the script references a static cell range instead of an Excel Table. Ensure your script uses table references (e.g., workbook.getTables()) to dynamically capture newly added rows.

How do I clear destination ranges before pasting new data in an Office Script?

You can use the clear() method on your destination range object (e.g., sheet.getRange("A2:E100").clear()) at the beginning of your script to remove old data before writing the updated priority groups.

Excel & Data Problems