If you are new to the Office Scripts universe and want a quick overview of its capabilities and differences when compared to Excel macros and VBA, check out this post for a write-up and video showcasing these topics.
Excel VBA vs Office Scripts
Creating Our First Office Script
Begin by logging into your Office 365 account on the Web and open an Excel file.
My sample file has three sheets, all with small tables containing Position and Salary information.
Our objective is to create a column chart that displays the different positions and salaries.
Issues with the Data
The catch is that the list of items varies from table to table. One sheet has 5 items while another has 8 items, and another has 3 items.
The lists are also not formatted as proper Excel Tables, but rather “plain tables”; simple typed data.
Recording the Script
The first step is to select the Automate tab …
… then click the Record Actions button.
NOTE: If you do not have the Automate table it is because either your network administrator has deactivated this feature via a policy, or you don’t have a Microsoft 365 Business or Enterprise account.
Just like Excel’s Macro Recorder, Excel for the Web’s Automate has a Record Actions button.
Microsoft has provided a few sample Office Scripts to get you started as well as consult for learning purposes (can you say “reverse engineering”?)
After we click Record Actions, a panel opens to the right where we can see that we are currently recording. We can also restart or stop the script recording at any time.
When recording, any changes made to the file will be recorded for later playback.
The actions are recorded as steps that are displayed with a mostly user-friendly description.
Later, we will examine the auto-generated code that resides behind these steps to get a glance “behind the curtain” as it were.
Featured Course
Master NEW Excel Functions in Office 365 & Office 2021
Creating the Chart
We begin by selecting the range (A3:B8) of data on the first sheet.
Next, select the Insert tab and click the Clustered Column chart from the chart library.
Customize the Chart
Let’s make the chart a bit more informative with the following chart modifications:
- Change the bar colors to medium-dark gray color.
- Remove the legend.
- Remove the Y (vertical) axis.
- Add data labels to the outside end of the bars.
- Change the chart title to read “Salary Department”.
- Set the gridlines color to a light gray color.
Finally, we need to stop the script recorder, less we record all future steps for the rest of time.
Click the Record Actions panel selector on the far right to return to the list of recorded steps.
Click the STOP button at the top of the panel to cease the recording process.
We are presented with a completed script named “Script 1” that we can run or edit.
Click where it displays the name “Script 1” and rename the script to “Create Column Chart”.
Testing the Script
We will test our script by select a different sheet in the workbook and click RUN.
We see that the chart is created but do you notice the problem?
We appear to be only charting the first 5 positions. This is because the original script hardcoded the range to be A3:B8. We will fix this issue very soon. For now, it’s nice to see that the code is executed without much complaint.
Peeking Behind the Curtain
To see the technical representation of those nicely presented steps above, click the EDIT button to display the raw code.
The script begins with the statement “function main”. This is how all scripts begin.
Next, it creates a variable for the active sheet.
Our goal is not to understand all the underlying code. That’s a whole different conversation. One of the most appreciated features of the script recorder is the automatic generation of user-friendly comments, seen here in green text.
All comments begin with two forward slash characters ( // ).
(Typographic Tidbit: If you italicize a forward slash character, it turns into a SUPER forward slash character.)
If the script contains unwanted steps, the comments make the act of step discovery easier. You can just highlight the unwanted step and delete it from the script.
Making the Code Dynamic
The shortcoming of the Script Recorder is that it notes the exact cells and selected ranges during the recording session.
Notice how the range selected for the char is fixed to cells A3:B8.
We want this range reference to be dynamic so we can apply the script to tables of various sizes.
While we’re at it, let’s make the title dynamic. Instead of always displaying “Salary Department”, we want the title to display the text located in cell A1 of the respective sheet.
Dynamic Chart Titles
First, we need to capture the contents of cell A1 and store those contents in a variable. We will name our variable “myTitle”.
After the first LET line of code, add the following lines of code to perform the text capture step.
// Get the Chart Title
let myTitle = selectedSheet.getRange(“A1”).getText();Update the line of script that sets the chart title from this …
chart_1.getTitle().setText(“Salary Department”)… to this …
chart_1.getTitle().setText(myTitle)Dynamic Range Selection
As the range selector is fixed to cell A3:B8, we need to create a variable to capture the range of data for each processed sheet.
After the previously created LET line of code, add the following lines of code to perform the range capture step.
// Get Dynamic Chart Range
let myRange = selectedSheet.getRange(“A3”).getSurroundingRegion().getAddress();Let’s update the existing code from this …
column.Clustered, selectedSheet.getRange(“A3:B8”));… to this …
column.Clustered, selectedSheet.getRange(myRange));If we delete the existing chart and re-run the script, we see the updated chart results.
Observe the dynamic title taken from cell A1 and the expanded data range (A3:B11).
Testing the script on a smaller dataset that occupies cells A3:B6, we get the following results by clicking our listed script on the Automate ribbon and select RUN.
The Wonders of Portability
A great feature of Office Scripts is that they are not limited to only use in the authoring file. Switching to a different file with a similar structure allows us to activate the script and reap the rewards.
Featured Course
Unlock Excel VBA & Excel Macros
“Where are Office Scripts saved?”
Good question.
The scripts are saved in your OneDrive account separately from the application files.
You can see in the Code Editor panel that the script save location is in a folder named “My Files” in a subfolder named “OfficeScripts”.
If you open your OneDrive folder and browse to the Documents folder, you can see the Office Scripts subfolder. Inside that subfolder lies the “Create Column Chart.osts” file.
Changing Script Options
Back in Excel, clicking the Options button (three dots) reveals several administrative options that can be exercised on the script.
If you select SHARE as an option, those users who have been granted access to this workbook will also have access to the script.
You can also leverage the power of Power Automate to create a flow and automate the execution of the script.
Edit Mode Options
When viewing the underlying code, you can select the same Options button to display the available scripting options.
The LOGS option is useful when debugging code problems.
There are also options behind Editor Settings for customizing the look and behavior of the Code Editor.
Running an Office Script in Microsoft Teams
If we switch to Microsoft Teams, browse to our Files section, and open the Excel workbook, we can select the Automate tab just like in Excel for the Web and execute the “Create Column Chart” script.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
