Class Note: Automating Task Archiving in Excel Using Office Scripts & Power Automate

Overview

• Demonstrated how to automate the process of moving completed tasks ("done") from an active tasks table to an archive table in Excel using Office Scripts and Power Automate.
• Motivation: Inspired by a real project management need to remove "done" tasks daily, a process previously done manually.
• Goal: At the end of each day, completed tasks are automatically moved to the archive table—no more manual updates required.

---

Tools & Features Used

• Automate Tab in Excel for Desktop (Office 365 Enterprise)
• Office Scripts: For scripting the logic to move and delete rows.
• Power Automate: For scheduling the script to run automatically at specified times (e.g., nightly at 10 PM).

---

Step-by-Step Process

1. Setting Up Tables in Excel

• Active tasks table (Tasks) with columns: Responsibility, Priority, Task, Status, etc.
• Archive table (Archives) located on "Archive" sheet.

2. Starting Office Scripts

• Go to Automate tab → New Script or use Record Actions if coding experience is limited.
• Script editor records manual actions for further tweaking.

3. Recording Actions (Initial Approach)

• Filter "Status" column to show only "done" tasks.
• Copy visible cells (filtered tasks).
• Paste at the bottom of the "Archive" table.
• Delete "done" rows from the tasks table.
• Result: The recorder-generated code partially works but is not fully dynamic or error-proof.

4. Tweaking the Script (Advanced Steps)

• Key Issues Identified in Recorder Output:
  • Code was copying all rows, not just filtered ones.
  • Deletion was not robust—could affect data outside the table.

Key Script Adjustments:

• Define visible range using getVisibleView().
• Calculate paste location dynamically at bottom of archive table.
• Loop from bottom to top of tasks table to safely delete "done" rows (avoids index shift errors).
• Ensure column indices and ranges are correctly referenced (zero-based).
• Prevent script from running when there are no “done” tasks (add an if-check).

Example Pseudocode:

if (get_visible_view.getRowCount() > 0) {
    // Copy/paste rows
}
for (let i = tasks_rows - 1; i > -1; i--) {
    if (tasks_range[i][5].toString().toUpperCase() == "DONE") {
        tasks.deleteRowsAt(i, 1);
    }
}

5. Scheduling Automation with Power Automate

• Access Power Automate from the Automate tab in Excel: Automate a Task.
• Choose "Schedule an Office Script to run in Excel" template.
• Set execution frequency (e.g., daily at 10 PM).
• Configure file location (OneDrive for Business/SharePoint) and script name.

6. Testing & Verification

• Manual and scheduled trigger both supported for testing the setup.
• Confirm that “done” tasks are moved and deleted as expected.
• Script now runs automatically at scheduled time without needing the Excel file open (just saved online).

7. Error Handling & Optimization

• Add checks so script does not error out when there are no "done" tasks.
• Place all copy-paste logic inside condition checking row count.
• Further optimization and debugging possible via console logging in the script.
• Script can be revisited and improved at any time.

---

Key Takeaways

• Recorder speeds up script generation but requires knowledge for dynamic or complex tasks.
• Looping bottom-to-top is crucial when deleting rows in a table to avoid index problems.
• Power Automate + Office Scripts offers robust, schedule-based automations for Excel workflows.
• Online storage (OneDrive/SharePoint) is required for automated scripts to work without file being open.

---

Tips

• Access Office Scripts API reference for syntax and method help.
• Ensure Office Scripts are enabled by your organization’s admin.
• Test script thoroughly before relying on automation.

---

Workflow Summary

1. Script moves & deletes "done" tasks nightly.
2. Power Automate schedules the run.
3. No manual intervention required once set up—fully automated project task archiving!

---

Call to Action

• Explore the Automate tab in Excel.
• Try building and tweaking your own Office Scripts.
• Connect with Power Automate for seamless workflow automations.

---

Subscribe for more Excel automation tips!