Today, I'm going to introduce you to Office
Scripts and show you how easy it is to create your first script. The reason you want to create
a script is to automate any boring, representative tasks that you do in Excel. So, Office Scripts is
for Excel Online or Excel files that you use in Teams because VBA macros don't work on the web and
they don't work in Teams because Teams uses Excel on the web as well. Now, if you're curious about
the differences between VBA and Office Scripts, check out this video. For now, let's go
ahead and create our first Office Script. First step is to log into office.com and let's
open up this workbook. Okay, so I've opened this up on Excel Online and I have a list of different
positions in different departments here. So, each department has a separate tab and I have the
salaries of the people and what I'd like to do is record a macro here that's going to give
me a column chart for the position and salary. Now, notice this is not formatted as an Excel
table and also my data has different ranges. We want to account for this later in our recorded
macro. Let's see how we can set this up. First step is to go to the Automate tab. Now, if you
don't see Automate, that's either because you don't have a Work or Enterprise account or
you need to get your admin to activate it for you because they can control that on the
admin side. When I go to the Automate tab, I have the ability to record my actions. So, just
like in Excel where we have a Macro Recorder, we can do that using Office Scripts. You'll be able
to see your recorded macros in the gallery here. Now, Microsoft has also added some samples for you
to get you started so you can feel free to select any of these scripts. You're going to see the
code editor pop up here. You can view the scripts and learn from these scripts. I'm just going to
close this and let's go ahead and create our first macro. When I click on this, I get the code window
opening up here and I can see it's recording. So, any changes I make to this file is going to be
recorded. So, for example, let's just type in "I'm here". Notice edit D3 shows up on the recorded
actions and we're going to go later and take a look at what is actually being recorded. But
here, we get a hint that something is happening. Now, what I want to happen is to create a
column chart. So, I'm going to select this range, go to Insert and insert a chart. So,
this is a clustered column chart. Select it, insert it. Let's make some changes to this. I'm
going to click on the columns here. Go to format, let's change the fill color to a gray color.
I also want to get rid of this legend, so I'm going to select it and delete it. I
want to get rid of the vertical axis as well, because I want to activate the data label. So,
I'm going to select the bars, go to the menu here, under more options, data labels. I want them
to be outside. And now, for the title here, I'd actually want this to be dynamic and take
it from these cells that's in A1, because each chart is going to have its own title, but for
now, I'm going to add a placeholder to this. And for the chart title here, let's put in 'Salary
Department'. Now, this is just a placeholder, we're going to change this later and make it
dynamic. Another thing I want to adjust is the grid lines here. I want to get them to be lighter.
Let's go back to format, scroll down under major grid lines, for outline, let's change it
to a light gray. Okay, so this looks better now. I'm done with the recording, I want to stop
this. So, notice here, I'm in the chart options, to go back to my recording, I can click on
this icon and go back to the recorded actions. Notice, all of this was being recorded. I can see
a friendly text here, but I'm going to stop this to actually see the script. That's my script,
it's called script one, I'm going to change the name and call it 'Create Column Charts'.
Let's go to edit and take a look at what was recorded. It starts off with a function
main, now this is how all scripts start off. Then it creates a variable for the active sheet. Now,
we don't really need to understand everything, so notice it's actually not that long. What can help
us in understanding the script is all the comments that were automatically added. So, take a look at
this, it says 'Set range D3 on selected sheet', remember I clicked in D3 and then I put 'I'm
here' in that cell. What was recorded was this. Now, if you're familiar with VBA, you're going
to notice a slight difference in typescript. We have to get the range before we set a range.
In VBA, we can directly write a value in a cell. Now, I'm going to get rid of this later, because
I don't want this to be a part of the script. So, anything that you record that you don't want, it's
easy to tell where that happened because of this commentary here and then you can just remove it
by highlighting and pressing delete. I'm going to leave that in for a second, we're going to get rid
of it later. Let's take a look at the next steps here. This is where the chart was created, so we
get this code here and take a look at the range, it's fixed to A3 to B8. So, this part wasn't
dynamic, even though I used shortcut keys to select my range, it hard coded it to this range.
Now, this is something I would like to be dynamic, so that I can apply the script to different data
ranges. Another thing I want to fix is the title, so that's where we set the title. Remember I just
put a placeholder here, but this is something that I want to make dynamic as well. Okay, so before
we make small tweaks to this, let's go and test the script. So I'm gonna go to department two
in this tab. I have the salaries for the finance department. I'm gonna go to my script here and I'm
gonna run it. So let's see what we get. I get, I'm here. Let me just collapse this so we can see it
better. We get accountant all the way to financial analyst. So it went from A3 to B8, and I get my
title that I put in before. Right, so the script works, but it's not dynamic. Okay, so now let's
go and adjust the script and make it dynamic. Let's bring up the script editor here, go and edit
our script and start by changing the chart title to be what is in A1. Now, here what we can do
is to create a variable that keeps the text that we have in cell A1. And notice this text
here, where we set the range D3, to I'm here. The code here is selectedsheet.getRange(D3). Now,
that's the part that I can use, but instead of D3, I'm gonna go with A1. Right, so we're going to
change this, instead of set range D3, let's say get the chart title. Let's also create a variable
for this. I'm going to go with let myTitle = this, but I don't want to set the value, so I'm going to
get rid of this. Instead, I want to get the text, so .getText(). And this is the great
thing about Office scripts is that we get a list of possible methods, and we can
find out more about them if we click on this. In this case, I won't get text, and I'm going to
open and close the bracket. Now, at this point, I get the text, but I want to use it in the chart
titles. If I scroll down here to where we added that chart title manually, that was right here.
I'm just gonna carefully remove this and paste in my title here. Okay, so far so good. Now we can
go ahead and quickly test if this part works. It tells me I have unsaved changes. Let's save
the script first, and now I'm going to run this. Let's take a look at our chart "Salary for finance
department", that's what I have in cell A1, right? So this part works. Now let's go ahead
and make the range dynamic. So I'm going to scroll up here. The range was used right here,
where it added the chart. Now, before this step, I also want to create a variable that keeps
my range address. So let's be consistent and add a comment for this. I'll go with "get dynamic
chart range". Now we're going to create a variable again, so let's say let myRange =. It's very
similar to what we have above here. We want to go with selectedSheet.getRange(A3). Now, here comes
the change. I want to get the surrounding region, that's called getSurroundingRegion(). So we can
see that method right here, you can select it with tab, now open and close bracket. Then what
we want to do with this range object is to just grab the address and that's .getAddress(). Let's
go with tab open and close the bracket. Okay, so that should do your job. Now, we can also check
whether something works or not by debugging this. The way it's done here is using `console. So,
console.log if I open bracket and put in my range, then I will see it here. Now, before we take
a look, let's also copy this my range here to this part, right? Because that's the part that
we want to make dynamic. Okay, so far so good. I'm going to save the script. Let's remove the old
chart here and run it again on this data set. Okay, so let's run this script. It's running now.
Notice here on the output, it gives me the range, so "Department 2", that's the name of
the sheet, "A3:B11", right? That's the address that works. I don't need this part
anymore. I'm going to get rid of that. Now, does everything else work? It goes all the way to
"Tax Professional Salary for Finance Department". That looks good. So, let's just collapse this, so
that you can see it better. Okay, so everything is dynamic and all I did is to make two small
changes to the script. Okay, so to make sure it really works, let's take another data set. This is
the salaries for the marketing department. Notice now I can see my script up here, "Create Column
Chart". I'm gonna click on it and run the script and let's take a look "Salaries for Marketing
Department" and I see it all here. The great thing about Office Scripts is that they aren't
restricted to a specific file. You can use them on your other files. So, if I go back to the
office.com page and open another Excel file, so this time I have "Sales by Region" and let's
say I want to create the same column chart but on this data set. Well, that's no problem. I'm just
going to jump to the "Automate" tab and now I see my script here, "Create Column Chart". Select it
and run it and as long as the layout is similar to my other column chart, I get the correct
visual here. So, it's "Sales by Region", it picked up the correct range and it automatically
inserted the chart with my preferred formatting. Now, where is the script saved? The script is
saved separately in your OneDrive. So, if we bring back the script here, notice this is the
location where it's saved under "My Files". So, if I jump to my OneDrive, under "My Files" here,
I'm gonna go to "Documents" and you're gonna see a folder for your Office Scripts and you'll have all
your Office Scripts there. That's the one we just created in Excel. You also have some options when
it comes to managing your script. So, if we go to "More Options" here, you can make a copy, you can
rename it, you can share it. This way, those in your organization who have access to this workbook
are going to be able to view and run the script. You can also create a flow, so you can use Power
Automate to automatically run your scripts. Now, that's a topic for another video. You
can also delete your script from here. Now, in "Edit Mode", you also have some
other options. If we go to "More Options", we get some similar options to what we saw before,
but we also get the ability to go to "Logs", which is helpful when it comes to debugging your code.
It's going to flag any problems here and give you some advice down here. Now, in addition to this,
you have some other "Editor Settings". You can select your theme, your font size. So, default
was smaller. I think it was 12. I increased mine to 14. You can adjust the word wrap, tab size,
and so on. Now, one last thing I want to show you is that you can also run this in Teams. So, I'm
just going to remove the chart that we just did, I'm going to go open up Teams, go to files, open
the file, let's bring up the Automate tab so we can see it here under more tabs, Automate. That's
the script that I wrote, so let's click on it and let's run it here from within Teams. Okay,
so let's take a look, and that's our Sales by Region graph. So, that wraps up our basic
example for Office Scripts. As you can see, there is a lot of potential there. I'm curious
what you think about this, let me know in the comments. I hope you enjoyed this video. If
you did, give it a like and do subscribe if you aren't subscribed already. Many thanks for
watching and I'm gonna see you in the next video.
