How to use Office Scripts and Power Automate to do Boring Excel Tasks for You
How to use Office Scripts and Power Automate to do Boring Excel Tasks for You
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.
★★★ My Online Excel Courses ► https://www.xelplus.com/courses/
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
★ Get the Official XelPlus MERCH: https://xelplus.creator-spring.com/
⯆ Link to complete blog post: https://www.xelplus.com/office-script…
LINKS to related videos:
Introduction to Office Scripts: • Introduction to Office Scripts \u0026 How …
Power Automate Beginner’s Guide: • Learn to Use Power Automate with Exam…
✉ Not sure which of my Excel courses fits best for you? Take the quiz: https://www.xelplus.com/course-quiz/
EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/
GEAR I use: https://www.xelplus.com/resources/#gear
More resources on my Amazon page: https://www.amazon.com/shop/leilagharani
Let’s connect on social:
Instagram: https://www.instagram.com/lgharani
Twitter: https://twitter.com/leilagharani
LinkedIn: https://at.linkedin.com/in/leilagharani
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
Content
0.24 -> Office Scripts is like running a macro on the web.
So, for example, you can record some tasks that
4.96 -> you usually do in Excel, and then you just run
it on other data sets or on other online files.
12.08 -> With Power Automate, you can run your script
without ever opening your file. You can run your
18.16 -> script from your mobile, you can run it based
on a schedule, or you can run it if something
23.84 -> happens in another app. So, you've seen Office
Scripts on the channel before, and you've also
29.2 -> seen Power Automate. Links to these videos are
going to be available below or in the cards.
35.36 -> Now, what you haven't seen is how these two can
work together. Don't worry if you're completely
41.52 -> new to both of these tools because you can
still watch this video, and by the end,
46.4 -> you're going to see the possibilities of
what you can achieve with both of these,
51.44 -> and hopefully, you'll have some ideas on
how you can apply it to your own processes.
56.16 -> So, here's a scenario that we're dealing with
here. I want to create a script that applies a
60.96 -> special formatting to all my Excel sheets. So,
not just one sheet, but all the sheets in the
66 -> Excel file. Now, I want the script to run not
just on one file, but on any file that I have
71.6 -> in a folder. So, basically, the script will loop
in the folder, if it's an Excel file, it's going to
77.28 -> run it on every sheet of that Excel file. Sounds
complicated, but really, it's not that bad.
88.32 -> Let's start off with our script. So, I've logged
into office.com, and I want to create my script
93.44 -> based on some data that I have in this file called
'To Format.' I'm just going to open this up.
99.2 -> The data that I want to format is right here.
Now, the length of the data is going to change,
104.16 -> but the thing that's going to stay fixed
is that I will always have two columns,
108.4 -> and my header is going to be on the first row.
I will also always have text in the 'A' column
115.36 -> and numbers in the 'B' column, but they can
be different things. It doesn't have to be
118.88 -> position and salary. What I want to do is apply
the formatting to this, and I want to create
124.32 -> my macro in a way that it would automatically
run for every single sheet. So, no matter how
130.88 -> many sheets this file has, it would run on this
data set as well and apply the same formatting.
136.16 -> Okay, so let's go ahead and record our steps.
I'm going to go to the Automate tab and record
142.8 -> the actions. Recording has started. The first
thing I'm going to do, as you saw in here,
148.4 -> for example, we have a different background color.
So, first thing I'm going to do is to remove any
154.8 -> existing formatting that might be there. So,
I'm just going to select these two columns,
159.28 -> go to 'Home' under 'Editing' here, 'Clear'
and 'Clear the current format.' Next thing
166.4 -> I'm going to do is to apply number formatting to
the 'B' columns. I'm just going to highlight this,
172.4 -> go to the formatting options here, more
number formats, 'Number" "Use thousands separator'
179.04 -> and 'Zero decimal places' and click on 'OK.'
Next, let's add a background color to this,
185.44 -> make it bold, and add a bottom border. Last, I
want to autofit these columns. I'm just going
191.68 -> to double-click to make sure they fit well. Okay,
we're done. Let's stop the actions here. My script
198.88 -> was recorded. Before we take a look at what
is inside, let's give this a name. I'll call
204.48 -> it "Formatting." Now, let's go to "Edit." Okay,
so this is all that was recorded. Now, you don't
210.8 -> need to understand all of this, but it's easy to
identify what each step is. So, here ".clear" is
217.44 -> where I cleared the formatting and here, we also
have a comment or a description of this macro that
224.08 -> was applied. Down here, we added a bottom border,
we autofitted the columns in the end. All of this
231.76 -> is being applied to the active worksheet. We can
see that here. We don't want that. Instead, we
238 -> want it to apply to every single worksheet that we
have in this file. To figure out how to do this,
244 -> I take a look at the documentation that we have
available for Office Scripts. You're going to find
248.8 -> it in docs.microsoft.com. It starts off with
the basics of scripts, and then down here, we
254.72 -> have collections. So, this is what we need because
we're dealing with a collection of worksheets. So,
260.72 -> we're going to need a different syntax, and the
syntax we need is right here. This one gets all
266.48 -> the worksheets in the workbook. That's
what we need. So, I'm going to copy this,
271.76 -> go back to my script, and instead of "let selected
sheet equals the active worksheet," I'm going to
278 -> change that. Just replace it with what I copied
from the demo. This time, "let sheets equals
284.4 -> workbook.getWorksheets()." Now, after getting the
worksheets, I need this macro to loop through each
290.56 -> of the worksheets. So, there is another part
of code that I need, and if we go back here,
295.6 -> we can see the code right here. It says "this
sets the tab color for each worksheet to a random
302.24 -> color." Now, we don't want to set the tab color,
but we do want to deal with some code that gives
308 -> us each worksheet, and that's this one. Notice
this syntax is similar to VBA. We have for each
314.72 -> here. Here we have "for (let sheet off sheets)."
So, "sheets" is the name that was given here, and
322 -> "sheet" is each single object in the collection.
Notice we also have the open curly bracket, so it
327.68 -> means we need to add the closing curly bracket to
this as well. So, I'm going to copy this, go back
333.28 -> to the code here, and right before all of this
is executed, I'm going to paste what I copied.
339.52 -> And to make sure I don't forget that closing curly
bracket, I'm going to add it to the end here.
345.52 -> Okay, so now we have "let sheet off sheets," but
this one is referencing "selected sheet," which we
352.08 -> don't have anymore. Instead of "selected sheet," I
need "sheet" because I need each single object in
358.32 -> that collection. So, I'm going to press "Ctrl+H,"
replace "selected sheet" with just "sheet"
366.16 -> ("sheet" from this one), and replace all. And
that's it, we're done. Let's save the script. And
373.04 -> just to make sure it runs, let's just take a look
at this sheet. It has this ugly formatting. I'm
378.64 -> going to go to the first sheet here and run this
just to make sure the macro is working properly.
384.88 -> Now, when I go to "Report2" tab, everything
looks great. Okay, so we're done with the first
390.88 -> part of this challenge, which was to create a
macro that runs for every single sheet. Now,
396.64 -> we want to go to Power Automate and run this macro
for every single file that we have in a folder.
404 -> And we want to be able to schedule this so it
can run at a specified time without us having to
409.12 -> worry about it. This file is saved on my OneDrive
for Business in a folder called "Reporting." Now,
414.72 -> in this "Reporting" folder, I have other files as
well that I want to apply the formatting to. So,
419.52 -> for example, this "Salaries" file it currently
looks like this. I have different sheets here, but
425.6 -> I'm missing that formatting, so I want to schedule
a script that automatically opens these files and
432 -> applies the formatting. But, I need to be careful
because I don't want the script to run on anything
437.92 -> that's not an .xlsx file. So, let's set that up
with Power Automate. I'm going to go back to the
444.16 -> Office page here and go and find Power Automate.
Let's now create a new flow. You can create an
451.04 -> automated cloud flow so it's triggered when a
specific event occurs. You can have an instant
456.48 -> cloud flow here, you can run the flow
automatically from your mobile. You can
461.12 -> also schedule a flow. So, this time let's go and
schedule this. I'll call it "Monthly Formatting."
468 -> You can decide when you want the flow to start to
run and then how often you want it repeated. So,
473.92 -> I'm going to go with once a month and click on
create. Now, let's add a new step. The first thing
480.32 -> I need to do is to get the list of files that I
have in my OneDrive folder. So, let's search for
487.6 -> OneDrive, OneDrive for Business, list files in
folder. Right, so that's what I need because I
493.92 -> want to loop through each single file and execute
my script. I need to browse for the folder.
501.44 -> Mine is called "Reporting." Next step is to run my
script. My script is for Excel Online and we see
508.56 -> it right here, run script. First thing I need to
do is the location, mine is OneDrive for Business,
515.68 -> document library. This is just OneDrive. The file
itself, well, it's not a specific file instead
522.48 -> it's each single file that is listed here. So,
I need dynamic content here. Now, this is a bit
529.2 -> difficult to see so let's expand this a bit more.
I need to grab the unique identifier of the file.
536.8 -> So, I'm going to go with "id" and check this out.
The moment I selected that, it added an automatic
543.2 -> "Apply to each" control right here because it's
smart enough to identify that I have multiple
548.72 -> files here and I probably want to apply the script
to each file in this folder. So, I don't need to
554.72 -> do anything here, but I didn't have a chance
to pick the script which I can do right now.
560.64 -> Notice here, this is what I mentioned before. It's
telling me that we've added a "For Each" container
566.16 -> for you. "For Each" enables you to perform actions
for each individual item in a set of values.
572.24 -> Great, I got it. Now, let's go ahead and select
the name of the script, and I call the script
578.16 -> "Formatting." Okay, so so far so good. Right now,
when I save this, it's going to apply the script
584.24 -> to each file in a folder, but it's not going
to check whether it's an Excel file or not.
588.72 -> So, remember that's something that we wanted
to add. So, I'm going to add an action here,
593.76 -> that action is a control action and what I need
is a condition here. Because I need to check
600.8 -> whether a file extension or file name ends
with .xlsx or not, so my condition here
608.16 -> is basically the name of the file. Because check
this out, we have name and we have name without
615.84 -> extension, so this means that name must include
extension. Instead of 'is equal to', I need 'ends
622.08 -> with', and for choose a value I'm going to go with
.xlsx. You can add different extensions here as
628 -> well, whatever you need. Now, the condition
should be the first thing that's checked,
632.96 -> and if this condition is met, then we want
the script to run. So, I have to grab this
638.4 -> and drag it and drop it in the 'if yes' box.
If no, I can leave it empty because I don't
644.16 -> want anything to happen if this condition isn't
met. Now, let's save this. We can test the flow
651.68 -> from here and see if it works. So, I'm going
to trigger this manually right now. Let's test
657.2 -> and let's run the flow. Flow has started. Let's
click on 'done'. Okay, I can see it running here
664.72 -> and it completed successfully. So now, let's
check. Well, nothing should have changed here.
670.56 -> Let's bring up the 'Salaries' file. Let's
check that when I open it up, I have my new
676.64 -> formatting applied to each single sheet. Now,
anytime I add a new file to that Excel folder,
683.12 -> it's going to run whenever my flow is triggered
and currently is triggered on a monthly basis.
689.04 -> I can update that or change it to a manual
trigger and run that from my mobile app. Okay,
694.72 -> so as you can see, that's an example of how
Power Automate can work together with Office
699.92 -> Scripts. I hope this video gave you some
ideas that you can apply to your own work.
705.12 -> If you enjoyed this, please give it a
thumbs up, hit that subscribe button if
709.36 -> you aren't subscribed to our channel yet,
and I'm gonna catch you in the next video.
Source: https://www.youtube.com/watch?v=v-_joMcPpLI