Automatically Move Completed Tasks to an Archive (with Excel Office Scripts & Power Automate)

[@LeilaGharani]

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.

[@johnfromireland7551]

Office Scripts also works in Excel Desktop and even has its own macro recorder button.

[@ozdesueslue7324]

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

[@johnfromireland7551]

@@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.

[@fsdesloge]

Hi Leila - I love your Xelplus courses and KZbin videos! I've taken your VBA course and have been a subscriber to the KZbin 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!

[@Djblois1]

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.

[@srul76]

Great video and explanation as always but it looks very complex

[@heliosAJ]

Unfortunately, I think at some point, the last % of productivity gains come at the expense of complexity of the automation process.

[@KevinBuchanan66]

@@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!!

[@iamthemoss]

Leila, you are simply, "the bomb" you have helped endless people. Thank you.

[@joep9617]

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 ;)

[@Afrikan221]

Office script, my next lesson to take.

[@EricTheBroBean]

Im putting Leila's videos into my ASMR playlist because of the calming voice 😴

[@danschroeder8008]

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!

[@faucillon]

@danschroeder8008 She has courses on excel too. KZbin 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

[@LuzFraterno]

Thanks for sharing this information! I see this helping me in my 2023 office tasks!

[@mindkorner]

Another great tips from the grandmother of excel, love your content... It always help make our works easier.

[@weyikunfekadike]

your presentation is beautiful and clear, thank you!

[@abugeda5]

you are so different Leila, thanks for capacitating us dear, apricated love to u

[@해피데이즈-r1v]

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!

[@mjbah]

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.

[@faucillon]

getColumnByName("Status") does exactly that.

[@banzabest6027]

@@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.

[@faucillon]

@@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.

[@banzabest6027]

@@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.

[@abhishekkuradia898]

Thank you, I can relate this more with Javascript syntax finally.

[@santik555]

You are fantastic! Thank you! You saved my weeks 😉

[@YvesAustin]

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.

[@johnfromireland7551]

Office Scripts now works in Desktop too.

[@CathleenPrata]

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?

[@azizurrehman6296]

MS leila...you have done a greate job....but i was thinking there will be a practice file in the description

[@LeilaGharani]

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 😊

[@rathindranarayanpal586]

Very informative grand daughter 😊🙏🇮🇳

[@sapisoftware1942]

Hi Leila, please do a course for Office Scripts!!! regards!

[@TheHellis]

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

[@LeilaGharani]

Thanks for the feedback!

[@CDSeater]

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]); }); }

[@jeanpoulin8787]

Hi Leila, When are you going to create a course related to Office Scripts?

[@charlieg9559]

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

[@miguelsaab4977]

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??

[@LeilaGharani]

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.

[@McKaySavage]

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!

[@vladn4321]

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.

[@vladn4321]

@@HamaraDiin what are you talking about? I am asking for the Excel script created in the video.

[@Shreysanthu]

Subscribed from india

[@tifouraamina1761]

🎉 well explained Thanks 🙏 Leila you're my best 😊

[@DallasRabot]

Looking forward to Leila's take on the new Microsoft Loop beta release.

[@theworldbroker]

good programer❤❤

[@MedelNapa]

Hi Leila, I'm currently looking on ways to automating cell value recording when certain date comes. I dont have VBA coding skills. Do you have a tutorial for this? Thanks a lot!

[@chrism9037]

Great explanation Leila, thanks!

[@LeilaGharani]

Glad you liked it, Chris!!

[@prathameshbile5865]

Thank You ! Best wishes.👍

[@gowthaman3264]

Hi Leila, Thanks For wonderful Video, Is it possible to use other workbooks in office script?

[@johnfromireland7551]

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.

[@jugoanata6717]

it would be great if u teach about vba

[@LeilaGharani]

Hi - yes I have videos on KZbin and a full course on VBA

[@hemvaidya]

so well explained

[@chh8860]

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.

[@LeilaGharani]

Thanks for your feedback, Chris! Based on the messages we receive it seems to be used a lot for project management.

[@GerhardHolzer911]

Danke für das tolle Beispiel, auch wenn es nicht direkt für „Anfänger“ geeignet scheint 😂

[@smbs47]

Great video once again! Does Office Spripts have interactive debug tools like VBA with things like the immediate window, watch, local variables and breakpoints?

[@johnfromireland7551]

No. It has a console window. Global variables can be managed by using the worksheet to store them or using a Power Automate Flow.

[@Rajuthunder]

HI, your videos are awesome, I have tried to refresh connection through automate but while run code nothing happened could you guide me

[@einoconsult5563]

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 ?

[@ErikAlMunawarah]

Really, I like your T-shirt

[@JJ_TheGreat]

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).

[@petergeromeudang2482]

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.

[@ExcelTutorials1]

This is awesome!!

[@arseniKo3791]

Number one Leila!

[@vida1719]

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.

[@vida1719]

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

[@johnfromireland7551]

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!

[@vijethgatty869]

You are awesome!

[@nandkishoreyadav401]

Leila, The explanation was very clear, like the new method and will try this once.. I liked the video editing as well, May I know what tool do u use to edit videos?

[@LeilaGharani]

Glad you like it. We mainly use Camtasia.

[@rahan6013]

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.

[@dermotbalson]

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.

[@izainonline]

Great voice

[@JohnM51982]

How about just filtering the table in the morning to remove done? Sometimes we overthink the problem.

[@jyotiprasadchaudhary8527]

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?

[@natbarmore]

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?

[@boucharebyasmine9908]

I love you leila ❤

[@ficfab5252]

Hi Leila, I hope you can see this question: how to create different directories referring to the same file folder?

[@johnfromireland7551]

You can program the file folders(in SharePoint) by integrating your script with Power Automate and the SharePoint actions.

[@AccroExcel]

❤ thanks for video 📷

[@nairobi203]

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?

[@Catelynmeow]

"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.

[@bssimmonds]

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?

[@alexxvlg]

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!

[@tessmcgraw]

Yes!! I’ve been trying to figure out if this is possible as well!

[@kaaa3485]

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!!!

[@ahaha731]

Should advise the client to use Microsoft planner.

[@paulhoward1493]

I think filtering done tasks, copying, transferring and then deleting is probably quicker. Sure it’s one less thing to do with the automation, but this went from record script to coding very quickly

[@MrSupernova111]

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.

[@prestonplambeck4297]

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.

[@alexcool879]

good video, i dont think this is easier than VBA

[@IGqy]

Ms are slowly moving away from VBA as far as I know, so the javascript is likely going to be the replacement

[@tarenflores]

awesome!😀

[@kkaurkify]

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?

[@IGqy]

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.

[@Bhavik_Khatri]

Awesome

[@alex3890]

VBA macro would be more straightforward. I think these tools are just to avoid VBA but it’s not less complex.

[@LeilaGharani]

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.

[@alex3890]

@@LeilaGharani thanks for your answer, if it works on mobile and on another apps, then it may be really interesting. Regards

[@outandabout4812]

Do you have a way to take screen shots and save from multiple sites?

[@adamshults9810]

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.

[@LeilaGharani]

We have a Microsoft 365 enterprise license.

[@gabcossa86]

Great👌🏾

[@MrWanted000]

I’m really curious how you manage your content creation calendar. Is it also using Microsoft 365?

[@LeilaGharani]

We use Teams in combination with Planner. Works great for us :)

[@MrWanted000]

@@LeilaGharani you use planner for that? I'm very curious to see how 😅 how maybe do a vid about that topic.

[@richardleslie410]

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..

[@ashubiyala]

Will this work? On the sheets saved in local disk as compared to One drive showed in the video

[@kebincui]

awesome

[@sybamunki]

Cool tip, but i just hide the done tasks using a filter!

[@faucillon]

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.

[@piotrvsa317]

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.

[@fluffybasket5674]

Hi Leila! Does this replace VBA?

[@ewankho6154]

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.

[@shamieseenarine3086]

Hi Leela did y tech class privately

[@prasantha1972]

We can done this by using VBA

[@dermotbalson]

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.

[@damionkoger]

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!

[@jacksonaquino7024]

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.

[@damionkoger]

@Jackson Aquino can not use this in some environments! The involvement if web-based services can violate security protocols for some organizations.

[@topten2828]

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.

[@aaayusharmaa]

Everyone making video on How to use office scripts, but not found a single video on "How to enable automate tab in excel"?

[@leophrix]

huhu i wanna learn all this but where to start? help

[@georgegray2712]

My question has always been: *Does Leila even lift bro?* 1:43 question finally answered 😂

[@saiprasannaalapati6091]

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

[@patrickkinbonso1809]

Office Script + Automate or VBA? I can't believe I say this. VBA seems easier for once. 😮

[@LeilaGharani]

I find VBA simpler too at this point but the advantage of OS is it works on Desktop and online - you can also combine it with PA.

[@patrickkinbonso1809]

@@LeilaGharani what is PA?

[@einoconsult5563]

@@patrickkinbonso1809 Power Automate

[@OntarioAndrews415]

[ Like ] ...a lot.

[@carlrigefsky3380]

it appears that this process will only work work or school versions and not personal 365. Is that correct?

[@canirmalchoudhary8173]

This is altogether new school of learning and I got automate tab but no record button on desktop version. All are afraid of automation

[@abdullahrajab3830]

can i total every page when printting please help in excel

[@minnguyen778]

It seems complicated for me with no experience with coding. How to start to learn about coding?

[@ryanjararios8315]

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?

[@johnfromireland7551]

I don't think the functionality to "filter from columns from other workbooks" exists in Excel Online. Office Scripts works in Excel Desktop too.

[@mnesevati]

What is the difference between macro and officescript recording?

[@IGqy]

You can record macros as well. The difference, if I am not mistaking, is the language. VBA is a separate language and the language used in these scripts look like JavaScript. Having debugged a ton of VBA I think it is nice that they are moving to a "more real" language that is easier to debug. I am used to python, so java is not far of in that sense, hence my preference to move away from VBA, but guess that would be a personal matter.

[@cavaturnagesh]

Hi Leila, please do a series on Libre office. Thanks.

[@AhMauy]

Can you make Excel shortcuts on coffee mugs to sell? So I can buy some.

[@LeilaGharani]

We have a selection of mugs already :) xelplus.creator-spring.com/

[@vineetat2]

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?