Office Scripts – Macros on the Web

This post will serve as an introduction to Office Scripts and show you how to create your first script.

Learning to use Office Scripts reaps many rewards.  The primary reward is the ability to automate boring, repetitive tasks.  These can be tasks in Excel (which this demonstration will focus on) as well as other Office and Microsoft applications.

Office Scripts for Excel is available for Excel Online as well as when using Excel in Microsoft Teams.

Because VBA and Macros don’t work in the Web version of Excel or Microsoft Teams, Office Scripts is a welcomed addition to the world of Excel online automation.

Let’s see how Office Scripts can make your life a whole lot easier.

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.

Excel Dynamic Arrays Course Leila Gharani

Master Excel Functions in Office 365 - Complete Course

Excel has Changed Forever! DON'T MISS OUT!

GET ACCESS

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.

Excel VBA macros course Leila Gharani

Unlock Excel VBA & Excel Macros Course

Achieve More. Save time

Real-World Projects & Workbooks included in the complete course.

Automate Complex Tasks with Excel VBA & Macros.

GET ACCESS

“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.

Learn Excel from a Microsoft MVP

Check out my bestselling Excel Courses

Learn anytime that fits your schedule.

Download files. Practice. Apply.

Visit Courses
Free Tutorials