Automatically Move Completed Tasks to an Archive
with Excel Office Scripts & Power Automate
Learn how to use the “record actions” button in Excel Automate tab and record an Office Scripts. With Office Scripts we’ll create a macro that works on both Excel desktop and Excel online. In this example, I’ll show you how to create a script to automatically move rows from one sheet to another sheet every single day.
Many of us have to maintain and update task or to-do lists in Excel. Wouldn’t it be nice if tasks marked as “Complete” or “Done” are moved to an Archive automatically at the end of each day. So, next time you open the task tracker only open tasks are shown on your to-do list?
For example, you’ve created an Excel table to collect all the tasks your team is working on. At the end of the day, you want to move all the “Complete” or “Done” tasks to another Excel table. Instead of doing it yourself every single day, you can fully automate it. I’ll show you how to create an Office Script to move rows from one table and append it to the bottom of another table based on a logic. I’ll also show you how to let the script run automatically at a scheduled time using Power Automate. We’ll set up a flow that runs the script every day. This way you don’t need to worry about running the script yourself. Power Automate will run automatically.
With this tutorial you’ll get to learn both Office Scripts and get an introduction to Power Automate and how to create a flow from Excel.
In this Video
- 00:00 Move All Rows with Status Finished Into Another Worksheet
- 02:00 The task tracker
- 03:40 Record Actions with Office Scripts
- 05:23 Testing the Recorded Script
- 06:20 Understanding Office Scripts Code
- 07:14 Office Scripts to copy visible range in table
- 08:28 Append rows to the bottom of a table with Office Scripts
- 11:17 Loop through a table and delete table rows with Office Scripts
- 15:12 Power Automate to run the script every day
- 18:18 Optimize and Debug Code
- 19:31 Wrap Up
More links on about
Nov 16, 2022 — A sample that uses Power Automate and Adaptive Cards automate task reminders in a project management spreadsheet.
22 answers · 0 votes: Hi - if you do this and it copies it to the next worksheet, which it does, but then for some ...
It's easy to archive a whole task folder in Outlook. However, normal archiving method deletes tasks by modified date, which may delete in-processing or not ...