Office Scripts is like running a macro on the web.
So, for example, you can record some tasks that you usually do in Excel, and then you just run
it on other data sets or on other online files. With Power Automate, you can run your script
without ever opening your file. You can run your script from your mobile, you can run it based
on a schedule, or you can run it if something happens in another app. So, you've seen Office
Scripts on the channel before, and you've also seen Power Automate. Links to these videos are
going to be available below or in the cards. Now, what you haven't seen is how these two can
work together. Don't worry if you're completely new to both of these tools because you can
still watch this video, and by the end, you're going to see the possibilities of
what you can achieve with both of these, and hopefully, you'll have some ideas on
how you can apply it to your own processes. So, here's a scenario that we're dealing with
here. I want to create a script that applies a special formatting to all my Excel sheets. So,
not just one sheet, but all the sheets in the Excel file. Now, I want the script to run not
just on one file, but on any file that I have in a folder. So, basically, the script will loop
in the folder, if it's an Excel file, it's going to run it on every sheet of that Excel file. Sounds
complicated, but really, it's not that bad. Let's start off with our script. So, I've logged
into office.com, and I want to create my script based on some data that I have in this file called
'To Format.' I'm just going to open this up. The data that I want to format is right here.
Now, the length of the data is going to change, but the thing that's going to stay fixed
is that I will always have two columns, and my header is going to be on the first row.
I will also always have text in the 'A' column and numbers in the 'B' column, but they can
be different things. It doesn't have to be position and salary. What I want to do is apply
the formatting to this, and I want to create my macro in a way that it would automatically
run for every single sheet. So, no matter how many sheets this file has, it would run on this
data set as well and apply the same formatting. Okay, so let's go ahead and record our steps.
I'm going to go to the Automate tab and record the actions. Recording has started. The first
thing I'm going to do, as you saw in here, for example, we have a different background color.
So, first thing I'm going to do is to remove any existing formatting that might be there. So,
I'm just going to select these two columns, go to 'Home' under 'Editing' here, 'Clear'
and 'Clear the current format.' Next thing I'm going to do is to apply number formatting to
the 'B' columns. I'm just going to highlight this, go to the formatting options here, more
number formats, 'Number" "Use thousands separator' and 'Zero decimal places' and click on 'OK.'
Next, let's add a background color to this, make it bold, and add a bottom border. Last, I
want to autofit these columns. I'm just going to double-click to make sure they fit well. Okay,
we're done. Let's stop the actions here. My script was recorded. Before we take a look at what
is inside, let's give this a name. I'll call it "Formatting." Now, let's go to "Edit." Okay,
so this is all that was recorded. Now, you don't need to understand all of this, but it's easy to
identify what each step is. So, here ".clear" is where I cleared the formatting and here, we also
have a comment or a description of this macro that was applied. Down here, we added a bottom border,
we autofitted the columns in the end. All of this is being applied to the active worksheet. We can
see that here. We don't want that. Instead, we want it to apply to every single worksheet that we
have in this file. To figure out how to do this, I take a look at the documentation that we have
available for Office Scripts. You're going to find it in docs.microsoft.com. It starts off with
the basics of scripts, and then down here, we have collections. So, this is what we need because
we're dealing with a collection of worksheets. So, we're going to need a different syntax, and the
syntax we need is right here. This one gets all the worksheets in the workbook. That's
what we need. So, I'm going to copy this, go back to my script, and instead of "let selected
sheet equals the active worksheet," I'm going to change that. Just replace it with what I copied
from the demo. This time, "let sheets equals workbook.getWorksheets()." Now, after getting the
worksheets, I need this macro to loop through each of the worksheets. So, there is another part
of code that I need, and if we go back here, we can see the code right here. It says "this
sets the tab color for each worksheet to a random color." Now, we don't want to set the tab color,
but we do want to deal with some code that gives us each worksheet, and that's this one. Notice
this syntax is similar to VBA. We have for each here. Here we have "for (let sheet off sheets)."
So, "sheets" is the name that was given here, and "sheet" is each single object in the collection.
Notice we also have the open curly bracket, so it means we need to add the closing curly bracket to
this as well. So, I'm going to copy this, go back to the code here, and right before all of this
is executed, I'm going to paste what I copied. And to make sure I don't forget that closing curly
bracket, I'm going to add it to the end here. Okay, so now we have "let sheet off sheets," but
this one is referencing "selected sheet," which we don't have anymore. Instead of "selected sheet," I
need "sheet" because I need each single object in that collection. So, I'm going to press "Ctrl+H,"
replace "selected sheet" with just "sheet" ("sheet" from this one), and replace all. And
that's it, we're done. Let's save the script. And just to make sure it runs, let's just take a look
at this sheet. It has this ugly formatting. I'm going to go to the first sheet here and run this
just to make sure the macro is working properly. Now, when I go to "Report2" tab, everything
looks great. Okay, so we're done with the first part of this challenge, which was to create a
macro that runs for every single sheet. Now, we want to go to Power Automate and run this macro
for every single file that we have in a folder. And we want to be able to schedule this so it
can run at a specified time without us having to worry about it. This file is saved on my OneDrive
for Business in a folder called "Reporting." Now, in this "Reporting" folder, I have other files as
well that I want to apply the formatting to. So, for example, this "Salaries" file it currently
looks like this. I have different sheets here, but I'm missing that formatting, so I want to schedule
a script that automatically opens these files and applies the formatting. But, I need to be careful
because I don't want the script to run on anything that's not an .xlsx file. So, let's set that up
with Power Automate. I'm going to go back to the Office page here and go and find Power Automate.
Let's now create a new flow. You can create an automated cloud flow so it's triggered when a
specific event occurs. You can have an instant cloud flow here, you can run the flow
automatically from your mobile. You can also schedule a flow. So, this time let's go and
schedule this. I'll call it "Monthly Formatting." You can decide when you want the flow to start to
run and then how often you want it repeated. So, I'm going to go with once a month and click on
create. Now, let's add a new step. The first thing I need to do is to get the list of files that I
have in my OneDrive folder. So, let's search for OneDrive, OneDrive for Business, list files in
folder. Right, so that's what I need because I want to loop through each single file and execute
my script. I need to browse for the folder. Mine is called "Reporting." Next step is to run my
script. My script is for Excel Online and we see it right here, run script. First thing I need to
do is the location, mine is OneDrive for Business, document library. This is just OneDrive. The file
itself, well, it's not a specific file instead it's each single file that is listed here. So,
I need dynamic content here. Now, this is a bit difficult to see so let's expand this a bit more.
I need to grab the unique identifier of the file. So, I'm going to go with "id" and check this out.
The moment I selected that, it added an automatic "Apply to each" control right here because it's
smart enough to identify that I have multiple files here and I probably want to apply the script
to each file in this folder. So, I don't need to do anything here, but I didn't have a chance
to pick the script which I can do right now. Notice here, this is what I mentioned before. It's
telling me that we've added a "For Each" container for you. "For Each" enables you to perform actions
for each individual item in a set of values. Great, I got it. Now, let's go ahead and select
the name of the script, and I call the script "Formatting." Okay, so so far so good. Right now,
when I save this, it's going to apply the script to each file in a folder, but it's not going
to check whether it's an Excel file or not. So, remember that's something that we wanted
to add. So, I'm going to add an action here, that action is a control action and what I need
is a condition here. Because I need to check whether a file extension or file name ends
with .xlsx or not, so my condition here is basically the name of the file. Because check
this out, we have name and we have name without extension, so this means that name must include
extension. Instead of 'is equal to', I need 'ends with', and for choose a value I'm going to go with
.xlsx. You can add different extensions here as well, whatever you need. Now, the condition
should be the first thing that's checked, and if this condition is met, then we want
the script to run. So, I have to grab this and drag it and drop it in the 'if yes' box.
If no, I can leave it empty because I don't want anything to happen if this condition isn't
met. Now, let's save this. We can test the flow from here and see if it works. So, I'm going
to trigger this manually right now. Let's test and let's run the flow. Flow has started. Let's
click on 'done'. Okay, I can see it running here and it completed successfully. So now, let's
check. Well, nothing should have changed here. Let's bring up the 'Salaries' file. Let's
check that when I open it up, I have my new formatting applied to each single sheet. Now,
anytime I add a new file to that Excel folder, it's going to run whenever my flow is triggered
and currently is triggered on a monthly basis. I can update that or change it to a manual
trigger and run that from my mobile app. Okay, so as you can see, that's an example of how
Power Automate can work together with Office Scripts. I hope this video gave you some
ideas that you can apply to your own work. If you enjoyed this, please give it a
thumbs up, hit that subscribe button if you aren't subscribed to our channel yet,
and I'm gonna catch you in the next video.
