How to use Office Scripts and Power Automate to do Boring Excel Tasks for You

Рет қаралды 373,066

Күн бұрын

Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
Learn how to effortlessly run Office Scripts in Excel online, without even opening your files! Discover how to automate tasks across multiple Excel sheets and files seamlessly.
📌 What You'll Learn:
▪️ Create and Run Office Scripts: Discover how to record a script in Excel for tasks like formatting. We'll show you how to script for multiple sheets in a file.
▪️ Adjust Scripts for Broader Use: Modify your script to work on every sheet of your Excel file.
▪️ Seamless Integration with Power Automate: Learn to use Power Automate to run your script on all files in a folder, schedule tasks, and more.
In this video you'll learn how to trigger an Office Script with Power Automate in Microsoft Excel. Office Scripts in Excel on the web lets you automate your day-to-day tasks. It's like running a macro on the web. You can repeat your recorded Excel actions on different workbooks and worksheets without ever opening the files. You can run your script from your mobile, based on a schedule, or if something happens in another app. As we setup this Office Script, I'll show you how you can apply your script to each worksheet. So we'll use Office Scripts to loop through each sheet in the file.
In this step by step tutorial we'll create an office script that applies s special formatting to all sheets in an Excel file. The script should run not just on one file but on any file in a specific folder. With Power Automate we will loop though all files in a folder and if we come across an Excel file it will apply the script.
00:00 How to Run an Office Script in Excel With Power Automate
01:27 Recording an Office Script
03:17 How to Edit and Adjust an Office Script
06:36 How to Setup the Flow in Power Automate
10:49 Testing the Flow to Trigger the Office Script
11:34 Wrap Up
⯆ Link to complete blog post: www.xelplus.com/office-script...
LINKS to related videos:
Introduction to Office Scripts: • Introduction to Office...
Power Automate Beginner's Guide: • Learn to Use Power Aut...
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
Note: 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!
#MsExcel #officescripts #powerautomate

Пікірлер: 247

@LeilaGharani 4 ай бұрын

Stay Ahead with Our Weekly Newsletter. Get the latest insights and updates straight to your inbox 👉 link.xelplus.com/yt-c-newsletter

@jchanning72 Жыл бұрын

I love your honesty, even with the awkward comments and questions. Thank you for the content, it has taught me a lot!

@wayneedmondson1065 2 жыл бұрын

Hi Leila. Office Scripts and Power Automate together looks very powerful! Thanks for sharing this example. Thumbs up!!

@mistofstars 2 жыл бұрын

Thanks Leila! I'm just getting into Power Automate and your videos are really helpful

@derekeano 7 ай бұрын

Fantastic video, I learned so much. Beautifully presented and you have such a nice voice to listen to! Very pleasing to the ear.

@shaundicker 2 жыл бұрын

Great video, I learnt so much, thanks Leila! I love how you explain things in your videos!

@jeffkerr7038 2 жыл бұрын

You have some of the best productivity content. Thanks so much

@chh8860 2 жыл бұрын

For me ... a lot to unpack here. But a flawless delivery (once again) ... and Leila's encouragement makes me curious. But I still have a long road to travel before I get "there". As always ... thank you ... thank you ... thank you.

@bobhutchins557 2 жыл бұрын

Wow! I've been out of the Excel/Office loop for a couple of years and what a great surprise - a lot of really neat features have been added! Thanks for a wonderful tutorial!

@LeilaGharani 2 жыл бұрын

You're very welcome, Robert!

@mehdid.1022 2 жыл бұрын

I learnt something new today, thanks a lot! I use Power Automate quite a lot but never used it with Excel scripts. Good to know!

@elvisosei9766 2 жыл бұрын

As usual.... Always delivering the best tutorial. Thanks for this. Forever grateful

@LeilaGharani 2 жыл бұрын

My pleasure!

@dc1858 2 жыл бұрын

I did not realise how macros could be integrated with power animate. I’ve been “stuck” in my personal exploration of power animate and watching you set up the script really helped a few things click. This knowledge makes me hopeful. Thank you!

@LeilaGharani 2 жыл бұрын

I’m glad this helped. It takes time to get used to PA but once we have some patterns we’ll be able to reuse them easily.

@prof.code-dude2750 2 жыл бұрын

Wow!!! You changed your workplace! And as you said in your Q and A you made a video on this

@IvanCortinas_ES 2 жыл бұрын

Tremendous video!! Thank you Leila! Superb!!!

@jeremy8968 2 жыл бұрын

The excel videos are really great and easy to follow. I wish you did videos on access. Everyone else is so dry or hard to understand.

@kaushiksekar845 2 жыл бұрын

Congratulations Leila for Your 1 Million Subscribers Milestone. Keep Going

@toughslaut 2 жыл бұрын

I'm going to apply this to my daily/weekly/monthly work routine. Hope this will simplify my work which currently I think taking too much of my precious time.

@ajitdembla 2 жыл бұрын

Brilliant. Your explanations are so easy 😎. Super cool 👍

@krishnavarkala4736 2 жыл бұрын

Thank you Leila. This video is vey helpful for me.

@2JSinc 2 жыл бұрын

Hello Ms. Leila, Thank you for another great tutorial. I'm wondering if you can make a video on how to make a simple CRUD using Microsoft Forms or if its not possible maybe you can give some advice on how to make a formatted and ready to print webpage generated from an excel table that was populated from MS forms... Sorry for my bad English, hope you understand what I meant and want to achieve. thanks again...

@cshahed 2 жыл бұрын

Thanks a good example of how to combine Power Automate and Office scripts

@garynagle3093 2 жыл бұрын

Wow. So much power in the palm of your hands. Thank you

@LeilaGharani 2 жыл бұрын

👍

@garyhanson4133 2 жыл бұрын

Wow you have so much information. Now to put it in practice :-) thanks

@MohammadAshooryan 2 жыл бұрын

Very excited. Thank you for sharing.

@ricardopinxt212 2 жыл бұрын

Clear and nicely explained Leila! Thank you.

@LeilaGharani 2 жыл бұрын

You're very welcome!

@bentad1 2 жыл бұрын

Tks Leila. Very helpful. That's give a lot of idea. After formatting, send à email, do the same but on the power automate desktop

@autobusesMexico 2 жыл бұрын

I love your voice, and your knowledge

@lwinsoemar7405 2 жыл бұрын

Subscribed to your channel is one of the best things I did in my life 😁

@FRANKWHITE1996 2 жыл бұрын

Another great video! Thanks for sharing!!

@emmafeng55 2 жыл бұрын

Thank you Leila for another amazing video!🥰

@LeilaGharani 2 жыл бұрын

You are so welcome!

@mattschoular8844 2 жыл бұрын

Very interesting. I don't often use Excel online however these are certainly some time saving methods that I can incorporate into my work flows. Thanks Leila.

@LeilaGharani 2 жыл бұрын

Glad it was helpful!

@hollydunne2687 2 жыл бұрын

Thank you Leila for yet another brilliant video. As a newbie to the data world i am wondering when i would use Office Script vs when to use Power Query? Any direction you ( or anyone else reading this) could offer would be gratefully received. Thank you

@susannem6302 2 жыл бұрын

Hello, thanks for all the very helpful videos. I was wondering if you also have some tutorials about Visio? Every time you come up with a new video I learn so much exciting stuff.

@SaulEduardoo2 Жыл бұрын

Hi Leila, I just want to say that your videos are so enjoyable! 😄🤗

@LeilaGharani Жыл бұрын

Oh thank you!

@KatlegoMasego 2 жыл бұрын

Thabk you for another informative video.

@Div0068 2 жыл бұрын

Very informative video, Leila. Thank you. Would you please make a video for automating new form submissions uploaded to a onedrive file which can save a lot of time opening main file and take data. Please go a bit slow when automating video is prepared for learners to understand how does it work. Thank you once again 👍

@Interesting-AF 2 жыл бұрын

Lila, awesome video! Any idea when or if refresh connections will be available in Office Scripts?

@georgechitechi 2 жыл бұрын

Thanks Leila, my productivity increased since I found your channel. I have a question, is it possible to fill down in excel on the web or using script. I want to fill down blanks until next value and fill down that as well. Similar to the go to special in excell desktop app. Will really appreciate

@yulinliu850 2 жыл бұрын

Wonderful teacher, wonderful tutorial.

@LeilaGharani 2 жыл бұрын

Thank you! 😃

@saradhee 2 жыл бұрын

That’s awesome Leila ! Nice tutorial

@LeilaGharani 2 жыл бұрын

Glad you like it.

@anthonyv3050 2 жыл бұрын

Hello Leila. This is a great video. Keep up the great work. You demonstrated how to program a folder (using a script) so that excel files within it are formatted with the same style. If the file is copied into a different folder, without the script, will the formatting remain?

@Shannyfrizz 2 жыл бұрын

i wish i found your videos like 10 years ago. this was an awesome tutorial.

@LeilaGharani 2 жыл бұрын

Glad you like them!

@dataisfun4964 2 жыл бұрын

This is awesome, can you do a course on PowerAPP (PowerAutomate).

@CideeTV 2 жыл бұрын

Thanks for this video…this is what im looking to help my monthly reporting

@KennethBaker53 2 жыл бұрын

Hi Leila. I have a report that I build every Monday that starts with data in a CSV file. I import and transform the data using Power Query and then build a pivot table and another table with summary data. I do all of this manually. This video gives me the idea to automate all of that. Thanks for the overview of this!

@jackfleming7137 10 ай бұрын

Couldn’t you potentially do all this with either macros in excel or power bi reports

@tifouraamina1761 2 жыл бұрын

Really interesting, I'm wondering if I can apply this to get standard report format in word 🤔 great idea 😃

@pandamyfb186 2 жыл бұрын

Greetings Guru Ms. Leila, Thank you for another superb video once again. If I may ask for your guidance on what would be a great formula, scripts, forms etc. Hope I explained the question well. Column A given numbers (5,12,15,20,37,50) Column B number to be input let say (99) Column C will display (12, 37, 50) it got from Column A 12+37+50. Thank you for reading.

@Emdy97 2 жыл бұрын

Can u do a video on PowerPoint? I would really appreciate it. Example; changing the headings and text formatting to a specified design thats been prepared ahead of time

@chrism9037 2 жыл бұрын

Super cool, thanks Leila!

@LeilaGharani 2 жыл бұрын

Glad you like it!

@samueljordan584 2 жыл бұрын

You're lessons have helped me out a lot. My team runs a report that is 93 pages worth of stuff even adding filters we still get a lot of stuff in the report. How can I build a query that links all these pages? Any guidance is much appreciated! you rock

@EmanuelSantos_ELS 2 жыл бұрын

i am brazilian. excellent content 👏👏👏

@sureshbabu-tj8fl 2 жыл бұрын

Thanks for the video 🙏🙏🙏

@ramneeksharma4226 2 жыл бұрын

Why we love Leila gharani channel.....easy asian accent English and ability to articulate so well.

@MaheshSharma-sv4kl 2 жыл бұрын

Very Nice And Very Informative.

@suketudeliwala7673 2 жыл бұрын

Great!! Thanks for video on innovative idea…

@MelianusSapari 2 жыл бұрын

Good steps Leila thanks

@5uysal 7 ай бұрын

This is the power automate flow I have been looking for! Thank you Leila! If anyone is like me and not a guru on these things, I just want to explain how I overcome an issue with this flow as it didn’t work for me initially when I was testing it. This flow only sends 20 files/emails at a time (I haven’t figured out how to disable the limit) and therefore if you have more than 20 files, and your flow fails, you maybe wondering why. I was testing it with a couple of lines from my spreadsheet (which had 100s of lines and therefore 100s of files in the folder). The two lines I was testing were not in the first 20 files that the flow was searching and that’s why my flow was failing. I removed all of the files and left the two files that I was testing and the flow worked! Just be aware of this as I was going mental 😅 trying to figure it out!! Hope this is helpful!

@LeilaGharani 7 ай бұрын

Thanks for sharing your experience!

@baliflorent6971 Жыл бұрын

Great work. Very clear. Please which software do you use to record your screen?

@dcpowered 2 жыл бұрын

Please post more videos with excel, automate and office scripts!!!

@DanielRamirez-wz7gk 2 жыл бұрын

Holy sheet this is amazing, thank you

@OCCmorrow 2 жыл бұрын

Hi Leila - great video once again! I immediately thought this would be perfect for a process that I go through each quarter - printing a dozen client earning statements to PDFs. I tried to record the script to "Print to PDF", but it doesn't seem to capture any steps. The "stop recording" button never becomes active. Any thoughts? Also, each time someone opens one of the files, there could be warnings such as, updating links to external sources, or that the file was saved as Read-Only. Would these kinds of notifications affect an automated script?

@AlThePal78 2 жыл бұрын

Always amazing you go girl

@tahashaukatali7694 2 жыл бұрын

Hi Leila, thank you for a great video. I was wondering whether it was possible to design a workflow that used multiple scripts across multiple files? The process I am thinking about involves around 3 files (let's call them A, B & C). Script-1 would do something like this: Step -1: Clean up file A Step -2: Look up two fields from file B into file A Step-3: Clean up file A some more Step-4: Save file A Script-2 would: Step-1: Clean up file C Step -2: Do a lookup between files A and C Step-5: Copy + paste relevant records from file C to file A Step-6: Run a pivot on the final file A I know this is more complicated then the task you displayed in the video but assuming I can capture the steps above in multiple macro scripts. Is it possible to design a workflow with multiple macro scripts applied across files with specific naming conventions??? Thanks in Advance.

@shraddhamane2385 Жыл бұрын

hi Leila, you have nicely explained, thank you for video .can u tell me can we use function in Automate.

@mikeymack6852 7 ай бұрын

👍👍👍 Awesome - thank you

@vijayarjunwadkar 2 жыл бұрын

Hmmm, nice video as usual, but need to spend some more time in understanding Office Script and Power Automate before applying them practically. Thanks Leila! 👍

@LeilaGharani 2 жыл бұрын

Thanks, Vijay. Yes, I'll also have to learn much more about these tools. But they are exciting :)

@herbertravens7786 2 жыл бұрын

@@LeilaGharani Marc

@jh_jlo4449 2 жыл бұрын

Thank you!

@ralucaioanagheorghe1701 2 жыл бұрын

Hello! I am a fan of your excel videos. It helps me a lot on my work. Now I need your advise: I have to automaticaly import an .json file with some data and create some charts that should get in a power point presentation. I work a lot with vba, so if I succed to have my data in excel all other steps are easy. My question is: to automaticaly import the json file is it easier with office scripts (with or without power automate) or should I use vba? Notice that I am a novice in office scripts, I only repoduced the scripts tha I saw in your videos. Thank you a lot.

@murattasova7598 2 жыл бұрын

Thanks Leila!

@dhiyaneshwarchess12Pro 2 жыл бұрын

Hii Leila! Glad to see you! How are you?

@blackm9063 2 жыл бұрын

Amazing channel 👏

@LeilaGharani 2 жыл бұрын

Thank you 🙌

@peterspencer9149 2 жыл бұрын

Leila, thank you for yet another really helpful video. I note that there is also one dislike - just guessing, but probably from a LOTUS-123 user.

@LeilaGharani 2 жыл бұрын

😂

@johnjoeffreyagustin8677 2 жыл бұрын

Hi Ms. Leila, I am watching your videos every day after my shift. I am looking for an excel tutorial regarding how to match 2 files with addresses. I want to look if they have the same building name. Do you have a tutorial for that?

@rodrigoalban7335 2 жыл бұрын

Hi Leila, thanks for this video. Do you know if the automation will work with opening the excel and run run a VBA scrip that connects and log on in SAP for extracting files ? Thank you in advance ,

@abtotheku Жыл бұрын

Very Powerful

@GigaBuzzStore 2 жыл бұрын

I love it how helpful for the workflow this is. But I am on Gsuite. Should I turn to Ms?..

@cpsandeepgowda 2 жыл бұрын

Love your t-shirt quote👌

@LeilaGharani 2 жыл бұрын

Thanks!

@funzonekerala 2 жыл бұрын

Excellent

@claudecormane9373 2 жыл бұрын

Hi Leila, love your vids and the concise explanations you give. Here at our company we use many Excel spreadsheets used by multiple personnel through SharePoint. These contain multiple queries to our ERP system. I currently have to remember to manually "Open in Desktop app" and then "Refresh All" twice (not sure why some require twice) and then close the app. Is there a way to automate this through Automate?

@andrestaquechel8921 Жыл бұрын

Use a Power automate desktop flow!

@abdulhalimkolko 2 жыл бұрын

great Stuff Wonder if we can run a script on "workbook open" or "Workbook Close" similar to VBA functions

@StratosLaspas 2 жыл бұрын

Please so more on power automate!

@allanhallin855 2 жыл бұрын

Hi Laila, Thanks for making all these interesting and very useful videos. I have a sheet to monitor my stocks. I've set it to update every 2 minutes into a dashboard. Everything works fine. But (there's always a but) my screen timeout turns on after 15 minutes, so I made a Power Automate flow to change settings to 'Alway on' and another to reverse at end of the day. I would like to be able to trigger these flows from buttons in Excel. Is that possible? Br. Allan

@kaushiksekar845 2 жыл бұрын

I was thinking about Power Automate, Leila Uploaded the Video.

@LeilaGharani 2 жыл бұрын

At your service 😎

@timothyo718 11 ай бұрын

Hi Leila. Could you do more videos on OfficeScripts?! I noticed that the Automate ribbon is available on Excel desktop now. Curious if people will start using this instead of VBA.

@MsDEAD27End 2 жыл бұрын

This video was great i have a question I can read excel with extension XSLB, with power automaet? that s even possible?

@Kara.Rawson 2 жыл бұрын

Thanks!

@LeilaGharani 2 жыл бұрын

Thank you, Kara!

@SimonLant 2 жыл бұрын

I did just this exact this this week, but I have it to download a file to SharePoint then run the formatting script on the saved file but it just fails every time. Doing two separate flows to save the attachment then apply the formatting script works fine! Sometimes PA is really annoying! Great video though.

@ameture-fotografer Жыл бұрын

Hi Leila, Once again a great informative video that increases my love for Excel. I really want to use this flow as I have a formatting script that I run on a particular CSV file I receive from a client on a bi-weekly basis. However, the challenge for me has been automating the conversion of the file from CSV to .xlsx. Any ideas or videos on how to accomplish this step via script or PA flow?

@OneEyedWonder Жыл бұрын

m.kzfaq.info/get/bejne/aLp5os-X0tq7gYE.html

@johnstonewall917 2 жыл бұрын

Thank you again for a great tutorial. I have one topic that you might think worthy of a tutorial. Many years ago at work I had a data file with a huge amount of data of variables changing very (I mean very!) slowly with time which I had to analyse. I realised that if I took every tenth data set then the analysis became much easier. I found a method for doing this, but sadly I have forgotten, I am now retired and this was about 13 years ago. I now have some weather data I would like to reduce in a similar fashion. Might it be a good video to show how to pick out every nth line in Excel?

@dermotbalson 2 жыл бұрын

John, you could do this by adding a column that simply numbered the rows 1,2,3,4...., then use the recently added FILTER function to pull out all rows where that number column MOD 10 is 0

@bijaytanty8434 2 жыл бұрын

Good 👍

@turkeysandwich5543 2 жыл бұрын

Hi Leila, Do you have a Udemy/similar course for Office Scripts/JavaScript for Excel? I learned so much from your VBA course!!

@thamilanban 2 жыл бұрын

You need to do a course on Power Automate

@asmithapriyankavanapalli4688 2 жыл бұрын

Yes

@TS-yy6jb 2 жыл бұрын

I was going to say the same

@khaledsolieman1704 2 жыл бұрын

Agree

@grahamparker7729 2 жыл бұрын

Agreed

@manny1152 2 жыл бұрын

Agree!

@gatekeeper84 10 ай бұрын

This is a good and informative video. However, the foreground music is incredibly distracting. Do you have a second channel with the same videos but without the music added?

@2409anupam 9 ай бұрын

hi madam, all your videos are always motivating and inspiring to learn excel and its commands. Can you please let me know if there is a way to automate google sheets data without any 3rd party ai tool?

@jagadeeshkumar5251 2 жыл бұрын

Thanks

@LeilaGharani 2 жыл бұрын

Thank you, Jagadeesh!

@davidferrick Жыл бұрын

My 14 year old boy brain giggled every time you emphasized "sheet". :) Great introduction to this pairing.

@LeilaGharani Жыл бұрын

😁

@robertalderete6765 7 ай бұрын

Hi Leila, love your videos, how can i process a .csv file with office script? Your video shows an xls file but if file is .csv? I can't get it to work.

@raysrule81 Жыл бұрын

Looks like the version of Office that my company uses doesn't have the Automate tab in Excel yet (online or desktop). Hopefully it gets added soon, this would greatly help!

@MrAlos87 2 жыл бұрын

Hello, I wanted to ask if it is possible to check a certain line in an Excel spreadsheet and if it should be empty, to send an automatic email to the respective editor of the line. Thank you very much

@hamdisultan 2 жыл бұрын

Thank you for sharing this tutorial. Can we use these scripts to do the following senareo: students answer a quiz in MS forms and then the responses are grades and the mark is sent in a pdf report via outlook? Second: can we use power automate to import questions into a form from an excel file. Thanks