ไม่สามารถเล่นวิดีโอนี้
ขออภัยในความไม่สะดวก
Automatically Move Completed Tasks to an Archive (with Excel Office Scripts & Power Automate)
ฝัง
- เผยแพร่เมื่อ 16 ส.ค. 2024
- Join 400,000+ professionals in our courses here 👉 link.xelplus.c...
Discover how to streamline your Excel workflow with this tutorial on automating task management! Perfect for project managers or anyone who needs a more efficient way to handle task lists.
⬇️ Download the script and file here: pages.xelplus....
🔍 What's Inside:
▪️ Automate Tab in Excel: Explore the Automate tab in Excel for Desktop, a powerful feature for scripting and automation.
▪️ Office Scripts & Power Automate: Learn how to use Office Scripts combined with Power Automate to move completed tasks from one table to another automatically.
▪️ Practical Example: Follow a step-by-step guide to automate moving "finished" tasks from an active task list to an archive table.
▪️ Custom Script Creation: See how to create and tweak a script using Office Scripts' Record Actions feature.
▪️ Scheduled Automation: Set up your script to run automatically every night using Power Automate, ensuring your task list is always up-to-date.
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.
🌍 My Online Courses ► www.xelplus.co...
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
🎬 LINKS to related videos:
Introduction to Office Scripts: • Introduction to Office...
How to use Office Scripts and Power Automate to do Boring Excel Tasks for You: • How to use Office Scri...
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creato...
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.co...
🎥 RESOURCES I recommend: www.xelplus.co...
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
👉 This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#officescripts #powerautomate #excel
Grab the script and file I used in the video from here 👉 pages.xelplus.com/automate-scripts-file
Remember, the main advantage of Office Scripts over VBA is the macro works in the browser as well - i.e. Excel online or Excel in Teams.
Office Scripts also works in Excel Desktop and even has its own macro recorder button.
Hey Leila,
I watched the previous video regarding chat gpt integration to excel. Although my api key is valid I get an error message in my office script. I have also created an issue in LG expriment page. I would highly appreciate your support as I terribly want l to use this function. Thanks in advance
@@ozdesueslue7324 What is "LG experiment page"? There might be autoprotection functionality preventing the usage of the API key. The organisation protection policies (including specifically for Office Scripts) might be blocking it. See the relevant info page on MSFT docs for that. You might be able to change one of the settings to get it work but I do recall reading something about this not being possible. though is the API is a string I think it would work.
There are 2 videos I would love if you would make (if you haven't already). 1) Show us things you can do in Office Scripts that you cannot do in Excel 2) Show us how to integrate office scripts with Excel if possible. I am very experienced with VBA already but I haven't touched office scripts yet.
Great video and explanation as always but it looks very complex
Unfortunately, I think at some point, the last % of productivity gains come at the expense of complexity of the automation process.
@@heliosAJ thought the same. I’ld probably opt for a filtered view in this instance.
But there are probably some use cases where would be preferred behavior. The demo was very well explained and (ask always) enjoyable!!
Hi Leila - I love your Xelplus courses and TH-cam videos! I've taken your VBA course and have been a subscriber to the TH-cam channel for several years. You do a fantastic job of presenting material in a clear, concise, and enjoyable manner. Keep up the great work - Thank you!
Office script, my next lesson to take.
It is the most intricated thing I have seen
Im putting Leila's videos into my ASMR playlist because of the calming voice 😴
Leila, you are simply, "the bomb" you have helped endless people. Thank you.
Thanks for sharing this information! I see this helping me in my 2023 office tasks!
Looking forward to Leila's take on the new Microsoft Loop beta release.
you are so different Leila, thanks for capacitating us dear, apricated love to u
Another great tips from the grandmother of excel, love your content... It always help make our works easier.
your presentation is beautiful and clear, thank you!
Thank you, I can relate this more with Javascript syntax finally.
Your task must've been long before 'Scripts' since you stated 'I lacked Excel skills' @1:11
I find this very hard to believe coming from our Excel queen ;)
Very informative grand daughter 😊🙏🇮🇳
Hi Leila, I was really impressed by your video on office scripts in Excel! I noticed that you not only recorded the script but also knew how to modify the code extensively. I'm really interested in learning how to write and understand office scripts at an advanced level like you do. Can you please recommend any resources or courses that you found helpful for this? Thank you in advance!
@danschroeder8008 She has courses on excel too. TH-cam is part of her 'product'. Basically the short version of it.
No idea of how good the courses are and if they are worth whatever price she asks for, but she certainly knows her stuff.
I for one don't follow any courses because the material out there is plentiful, but to each their own way of improving. Hope this helps
You are fantastic! Thank you! You saved my weeks 😉
Hi Leila. Good video. I was thinking instead of hard coding the column number, why not loop through the columns as well to determine which column number has 'Status' as title. This is to avoid the code breaking if a column is added/removed.
getColumnByName("Status") does exactly that.
@@faucillonit will be good to have such func with regexp. It will depend on the data, and did you tried proposed by you func. with enters (alt+enter) in column name? and sometimes files are not yours, so the owner can add/update description of column name at any time and there will be needed not equal to name function but for e.g. copmparator for the same as right part of the column name.
@@banzabest6027 it all depends on the situation off course. In this case Mohamed asked for something to get the column by means of the name. I think the code does that perfectly as is. Different situations, ask for different solutions.
@@faucillon yes, for sure solution are better instead of column name cells looping. But what about ranges that not associated with a tables, will it also work with just sheets cells ranges? I haven't started using power automate to be able t check it.
Since there is a loop at the end I would remove the filtering and just do it all in the loop.
That way you make the code more efficient and you wouldn't need to worry about the error
Thanks for the feedback!
Hi Leila, please do a course for Office Scripts!!! regards!
Leila, at the end you added some code so that if there is no DONE the script will still work. However, not having DONE makes the flow fails in Power Automate, any clue to fix this ?
MS leila...you have done a greate job....but i was thinking there will be a practice file in the description
Yes. I will be adding the link to the script once I get back from holidays. Check back next week and you should see it in the description of the video 😊
Hello Leila, thank you for another great video. I just wonder if you have a plan to launch Power Automate course. Also launching new version of Macro VBA course for the latest. I've already completed your Power Query course(currently going through both Power Pivot and Power BI course) and came to realize how awesome the automation actually works for me. Personally my ultimate goal is just to automate almost 100% of my tasks at work. Thank you!
HI Leila, it looks like MS have added a script into excel as an example of this. "Move Rows Between Tables"
/*
This script does the following:
Selects rows from the source table where the value in a column is equal to some value (FILTER_VALUE in the script).
Moves all selected rows into the target table in another worksheet.
Reapplies the relevant filters to the source table.
*/
function main(workbook: ExcelScript.Workbook) {
// You can change these names to match the data in your workbook.
const TARGET_TABLE_NAME = "Archive";
const SOURCE_TABLE_NAME = "Current";
// Select what will be moved between tables.
const FILTER_COLUMN_INDEX = 3;
const FILTER_VALUE = "Done";
// Get the Table objects.
let targetTable = workbook.getTable(TARGET_TABLE_NAME);
let sourceTable = workbook.getTable(SOURCE_TABLE_NAME);
// If either table is missing, report that information and stop the script.
if (!targetTable || !sourceTable) {
console.log(
`Tables missing - Check to make sure both source (${TARGET_TABLE_NAME}) and target table (${SOURCE_TABLE_NAME}) are present before running the script. `
);
return;
}
// Save the filter criteria currently on the source table.
const originalTableFilters = {};
// For each table column, collect the filter criteria on that column.
sourceTable.getColumns().forEach((column) => {
let originalColumnFilter = column.getFilter().getCriteria();
if (originalColumnFilter) {
originalTableFilters[column.getName()] = originalColumnFilter;
}
});
// Get all the data from the table.
const sourceRange = sourceTable.getRangeBetweenHeaderAndTotal();
const dataRows: (
| number
| string
| boolean
)[][] = sourceTable.getRangeBetweenHeaderAndTotal().getValues();
// Create variables to hold the rows to be moved and their addresses.
let rowsToMoveValues: (number | string | boolean)[][] = [];
let rowAddressToRemove: string[] = [];
// Get the data values from the source table.
for (let i = 0; i < dataRows.length; i++) {
if (dataRows[i][FILTER_COLUMN_INDEX] === FILTER_VALUE) {
rowsToMoveValues.push(dataRows[i]);
// Get the intersection between table address and the entire row where we found the match. This provides the address of the range to remove.
let address = sourceRange
.getIntersection(sourceRange.getCell(i, 0).getEntireRow())
.getAddress();
rowAddressToRemove.push(address);
}
}
// If there are no data rows to process, end the script.
if (rowsToMoveValues.length < 1) {
console.log(
"No rows selected from the source table match the filter criteria."
);
return;
}
console.log(`Adding ${rowsToMoveValues.length} rows to target table.`);
// Insert rows at the end of target table.
targetTable.addRows(-1, rowsToMoveValues);
// Remove the rows from the source table.
const sheet = sourceTable.getWorksheet();
// Remove all filters before removing rows.
sourceTable.getAutoFilter().clearCriteria();
// Important: Remove the rows starting at the bottom of the table.
// Otherwise, the lower rows change position before they are deleted.
console.log(
`Removing ${rowAddressToRemove.length} rows from the source table.`
);
rowAddressToRemove.reverse().forEach((address) => {
sheet.getRange(address).delete(ExcelScript.DeleteShiftDirection.up);
});
// Reapply the original filters.
Object.keys(originalTableFilters).forEach((columnName) => {
sourceTable
.getColumnByName(columnName)
.getFilter()
.apply(originalTableFilters[columnName]);
});
}
it would be great if u teach about vba
Hi - yes I have videos on TH-cam and a full course on VBA
how on earth is that the task of a project manager?
I am one and for the life of me I can't figure out when this would ever be usefull.
Even if you only use excel itself you can literally filter out any task which is 'Done' so you can have your meeting.
Hi Leila, this is very helpful. This is exactly what I'm trying to do in a sheet I have. I followed the However, for some reason excel wouldn't record my keyboard actions. Do you have any advice?
good programer❤❤
At the 19:45 mark - "... Let me know what you think about this. Comment below ..."
If Leila is presenting, "Record Actions in Office Scripts" then it must be important topic for many. But it is waaaaay over my head.
Candidly, I don't understand the premise. Why did the LinkedIn user use a spreadsheet to track tasks in the first place'? Spreadsheets are for numbers. Use the 'Tasks' function in Outlook to track 'tasks' for a project. It provides the flexibility to create a Project Name, Activities, and Sub-Activities, Due Dates, set Deadlines and create Reminders. Tasks can be assigned team members with level of importance. When the task is done, right click and select "Mark Complete" and 'strikethrough' the task. Personally, I like that all the team's tasks, completed and pending, are in the same place.
Nonetheless, thank you ... well done presentation as always.
Thanks for your feedback, Chris! Based on the messages we receive it seems to be used a lot for project management.
🎉 well explained
Thanks 🙏 Leila you're my best 😊
Would it be possible to have the script code in the description of the video? or in the comments? Would be useful to be able to copy-paste it and adapt it directly. Thanks.
@@Diin_Digest what are you talking about? I am asking for the Excel script created in the video.
Some of these really helpful features seem to only apply to the Enterprise versions of Excel. Having Microsoft 365/Office 365 online versions won't get you there. Now the confusion part is what really is the the Enterprise version. Microsoft makes the purchase of this extremally confusing. At lest to me anyway..
Hi Leila,
When are you going to create a course related to Office Scripts?
Subscribed from india
Hi Leila, great video. Can you tell me what syntax I'd have to add to run the script as soon as the value is changed in column #5? I want the row to be moved as soon as I update the status.
Thanks
Why would you want to take out records from a database? You could use them run some analytics. Or would you write another script to put all together again at a later stage?
Thank You ! Best wishes.👍
"I'd be at dinner with friends when I suddenly remembered, OMG I forgot to move those rows!" -- my life every day before I was treated for inattentive ADHD.
This is pretty impressive, but I’m having trouble thinking of a reason to use it? Why wouldn’t you use a task-management, todo-list, or project management application for tracking the tasks for your team or project? This seems like a _lot_ of work just to reinvent basic task-management functionality?
5:32 I don't think it is going to dynamically work all the time, because it cites the range as rows 2:10. I believe that if you add rows, they won't be picked up (according to the script).
That is true, however, the script provided what are need to be removed and it mentioned the "Visible view with value"("Done"). Even if keeps adding the rows and soon, as long that value exists on those rows, it will work to auto-add those criteria to archive sheets.
Great Video Leila. As an exercise I changed it to be a To-Do-List for me.
I also added a sheet that has repetitive tasks that are automatically added to task list.
Each item on this list has a date for when the tasks are to be added.
I am in Australia, so I use the Australian date format dd/mm/yyyy
The script works fine when I run it but when it is scheduled and run from a 'flow' the dates are interpreted as mm/dd/yyyy it adds tasks based on that date format.
I've changed every date setup/option on OneDrive and Sharepoint that I can find, and it masks no difference.
Does anyone have any suggestions?
Danke für das tolle Beispiel, auch wenn es nicht direkt für „Anfänger“ geeignet scheint 😂
HI, your videos are awesome, I have tried to refresh connection through automate but while run code nothing happened could you guide me
Really, I like your T-shirt
What is the benefit to do this archiving with Office Scripts? Same work will be done with VBA with before close start. And do I understand correctly that if the Excel file is not in OneDrive or Sharepoint then Office Script cannot do the archiving?
One benefit as I understand it is the use of JavaScript. VBA is getting old and ms are moving towards using JavaScript as far as I know. I believe it is due to them not having to develop the language themselves as they did with VBA. Not that I know, this is a guess.
The scripts, it think, will run anywhere, the power automate thing is related to some cloud infrastructure.
Edit: VBA don't run on the web, which the scripts do. There are also some security benefits from using JavaScript in terms of what rights the script has compared to VBA. VBA does however still have the ability to handle things at the 'application' level, and the scripts are limited to the workbook itself.
so well explained
Great explanation Leila, thanks!
Glad you liked it, Chris!!
Hi Leila, I hope you can see this question: how to create different directories referring to the same file folder?
You can program the file folders(in SharePoint) by integrating your script with Power Automate and the SharePoint actions.
Should advise the client to use Microsoft planner.
Cool tip, but i just hide the done tasks using a filter!
Number one Leila!
How about just filtering the table in the morning to remove done? Sometimes we overthink the problem.
Super cool trick, I have some queries. For example if my formula is A2+Sheet2!A1+Sheet3!B5, CTRL +[ will take me to A2 cell but then how do i move to Sheet2!A1 and Sheet3!B5?
This is awesome!!
Great video Leila, and nice work figuring this out! So, it looks like people who want to automate tasks and do not know VBA end up automating them using...hmm...well.. something that looks like VBA!! Now, I am curious to see how some more complex tasks can be written in both Automate and VBA and check which of the two codes is more efficient. Does Microsoft have some hidden agenda to eliminate VBA at some point? Ouch! that would hurt... From my side, I will continue to play in my VBA sandbox for the foreseeable future :) Thank you again for this great content.
Office Scripts now works in Desktop too.
VBA macro would be more straightforward. I think these tools are just to avoid VBA but it’s not less complex.
I find it more straightforward too. The advantage with OS is that the macro can run on the web too. So if someone is using Excel in Teams or Excel online it will work as well whereas VBA will not work.
@@LeilaGharani thanks for your answer, if it works on mobile and on another apps, then it may be really interesting. Regards
Great example of how Office Scrips can work together with Power Automate. I was wondering if Office Scrips have MsgBox as in VBA. For example, when user clicks Delete button, I would like to show message asking if they really want to delete data or when user clicks Paste button without copying new data, they would see message ''No data has been copied''. I tried to search on such features in office scrips, but couldn't find.
I just found an answer to my question on ChatGPT. They advised that there is ui.confirm and ui.alert methods, but they only work in the web-based version of Excel, but not in the desktop version of Excel
There are no forms at this time. Office Scripts is being developed. It is only about 2 years old. Many more features and functionality to come!
Good afternoon!
Please tell me if I can make a script to update all the queries that are in the current book, and make it automatically updated daily?
Thank you!
Yes!! I’ve been trying to figure out if this is possible as well!
You are awesome!
Great voice
❤ thanks for video 📷
good video, i dont think this is easier than VBA
Ms are slowly moving away from VBA as far as I know, so the javascript is likely going to be the replacement
This is altogether new school of learning and I got automate tab but no record button on desktop version. All are afraid of automation
I love you leila ❤
Thank you leila so muchhhhhhhhhh!!! My company do flayers for party as an invitation. we usually send by emails. is there any way that we can do it online where they can view the information. will you please, do tutorial on Microsoft Sway on Microsoft 360 on how to do a flyer. Thanks in advance!!!
Hi Leila, how to add the Atomate option to top row ribbon on Excel? Can’t find it in desktop nor web version of Excel for Win10. MS search also has not helped on it. Thx for info about it.
Everyone making video on How to use office scripts, but not found a single video on "How to enable automate tab in excel"?
I could easily do this same process by using =countif() to create a numerical value for each "done" instance. Then use a =vlookup() on the other sheet to create individual tables by status. A pivot table would also do the same. The original table would only be there for reference. Not sure about this chatGPT "automate" stuff.
But they don't want to reference it on the task list. So vlookup wouldn't work as requested. That's why this requires some coding.
I am getting this error while running the script Line 5: Cannot read properties of undefined (reading 'getColumnByName') can you please help
Hi! could you please help out. I'm trying to automate some thing in excel, but I can't get around VBA, I'm just an average joe btw. no idea on programming or such.
the steps involves duplication the the sheet which contain data and those data are in sets,
then based on given number, it should remove sets that do not contain that number.
Automate tab is not available in my Excel
I am using LTSC version
Need help with using vba in Mac for save file to pdf and email out, keep getting the runtime error 429, activeX component can’t create object (I’m trying to set outlook as object) …
Do you have a way to take screen shots and save from multiple sites?
Awesome
We can done this by using VBA
It's easy in VBA. I would program it to automatically move a row whenever Done is selected, so it's always right up to date.
Many of the tools this channel covers are awesome, but we cannot use them in HIPAA regulated, or IT restricted networks. Having alternatives would be amazing!
I'm a big VBA fan, but office scripts are easier to integrate with power automate, power apps, and other cool tools that can take automation to a whole new level.
@Jackson Aquino can not use this in some environments! The involvement if web-based services can violate security protocols for some organizations.
You can create vba macro to do the same thing.
You can create another workbook that on open will open the original workbook and run the code.
Then you can set task scheduler in settings and have the second workbook open at a certain time.
No websites and it works for me.
it appears that this process will only work work or school versions and not personal 365. Is that correct?
While i used it it doesn't delete the data from the souce... Don't know why
Why not VBA? I think it would be easier
VBA is 20th century technology and does not work online.
Will this work? On the sheets saved in local disk as compared to One drive showed in the video
Hi Liela,
I have downloaded recently powerbi but the visualization feature is not seen in top right side how do I get enable this feature on top right side
It seems complicated for me with no experience with coding. How to start to learn about coding?
Hi Leila, thanks for this great video. I don't know anything about coding and I believe this is a must if you want to use this tool to automate any task. Where can I learn this coding skills in order to use this tool??
I’d say any type of coding skill is easily transferrable from one language to another. You cam start with the one that would be most appropriate for your work. Something you can use right away.
I would agree with Leila. I’ve learned Apps Scripts for example and then moved on to more general JavaScript and I find that I can easily follow what she’s doing and adapting the JS/Apps Script knowledge to Office Scripts quite easy. So I’d recommend you do one of the “Beginner to Advanced 200 days” type Javascript course because there are thousands of high-quality courses on that available, often for cheap. After even a few weeks come back and you’ll find following Leila’s steps here easy.
Office Scripts doesn’t have a robust course ecosystem yet since it’s new, still quite niche and still in development. But as Leila says, the coding skills and patterns are very similar to other languages. Best of luck!
Difficult for me
awesome!😀
Hi Leila, Thanks For wonderful Video, Is it possible to use other workbooks in office script?
The Script Leila wrote will operate on any workbook as long as the sheet names match. You can, also, alter the code so that it will operate on a sheet based on its position.
My question has always been:
*Does Leila even lift bro?*
1:43 question finally answered 😂
Hi Leela did y tech class privately
can i total every page when printting
please help in excel
as I am from excel background having okay knowledge of vba but not other programming language , office script (I am not if it is java or java script or something else). can you help people like me understand what is going on. do I have to leave java or Java script or any other language and is it easy someone who is in 40s?
Great video once again! Does Office Spripts have interactive debug tools like VBA with things like the immediate window, watch, local variables and breakpoints?
No. It has a console window. Global variables can be managed by using the worksheet to store them or using a Power Automate Flow.
Do you mind sharing what subscription level of Microsoft you have? It’s always sad to see such incredible features but not have access to them.
We have a Microsoft 365 enterprise license.
huhu i wanna learn all this but where to start? help
Was this not already in the macro
Thanks for sharing this video with us. As microsoft has no future plan for VBA. Is there any plan of you would launch an office script course in near future.
Keenly waiting to learn this. But there is no course of office sript.
Microsoft has tried several times to ditch VBA but business has prevented it because VBA is embedded in so many critical systems. I think it will be around for a while yet, and (like JavaScript in web pages) it is extremely useful. So worth learning, yes. I have used it for 30 years and it has helped create some marvellous tools.
Great👌🏾
Hi Leila, please do a series on Libre office. Thanks.
The AUTOMATE option is not available for OFFICE HOME EDITION. atleast i tried to installed it but apparently is just for BUSINESS EDITION.
Same here, I am upset
thank you for explaining why I can't find it in my excel 🙏 really sad though
I don't have it yet :( Since I am the data cruncher (not the PM, but the person the PM tasks with this (sad face)) at my work.
I have a question. What if your data is filtered from columns that are linked from other workbooks. In most instance you need to open it and update the data. Is there any option for automatization with that issue in mind?
I don't think the functionality to "filter from columns from other workbooks" exists in Excel Online. Office Scripts works in Excel Desktop too.