How to run script in Microsoft 365 Excel for the web

One thing users have clamored for is web apps that support code (macros or scripts). It’s been a long time in coming, but Microsoft Excel for the web now lets you automate some tasks using Office Scripts. You can record your actions, similar to the way you’d do so in Excel desktop, and Excel for the web turns your keystrokes into a TypeScript language script. You can edit and even share scripts with others. If you want more than recorded actions, you’ll need to learn the language, which is a superset of Java Script. In this article, we’ll launch Excel for the web and create a simple script that you can use in other workbooks in Excel for the web.

SEE: Software Installation Policy (TechRepublic)

I’m using Microsoft 365 Excel for the Web on a Windows 10 64-bit system. My files are saved in OneDrive for Business, and I have an Office 365 Business Premium license. There’s no demonstration file because you won’t need one. As usual, this feature is available to Office Insiders first and will be rolled out to everyone in stages. If you don’t have it yet, you soon will.

Who can run it scripts on Excel for the web?

Figuring out if your version of Microsoft 365 has a specific feature is a bit like a Halloween maze. So many newer features aren’t available in all versions. I’ve noticed that the web versions are receiving some interesting features that aren’t available any place else. To limit things even further, many features aren’t available across all licenses. To run Office Scripts, you’ll must meet the following requirements:

• Excel for the web
• OneDrive for Business
• One of the following Microsoft 365 licenses:
  • Office 365 Business
  • Office 365 Business Premium
  • Office 365 ProPlus
  • Office 365 ProPlus for Devices
  • Office 365 Enterprise E3
  • Office 365 Enterprise E5
  • Office 365 A3
  • Office 365 A5

If you don’t see the Automate tab in Excel for the web, and you’re part of a large organization, talk to your Microsoft 365 administrator. If you’re on your own and you don’t see it, you might need to enable third-party cookies.

About TypeScript in Excel for the web

Before we continue, you might be wondering what a TypeScript language is. Technically, it’s a programming language that’s a superset of JavaScript—a cross-platform, object-oriented scripting language that some would say is the official web language. For our purposes, you don’t need to know more, but if you’re familiar with JavaScript, TypeScript will be familiar to you. However, you don’t need to be familiar with JavaScript to use Office Scripts.

As an Excel for the web user, you’ll want to use Office Scripts anytime you often repeat the same task. For instance, you might import foreign data once a day and spend a bit of time scrubbing it, so Excel interprets it correctly. Every day, you repeat exactly the same steps. Using Office Scripts, you can record your steps so Excel for the web would create a reusable script. Not only will you eliminate a bit of work, but the script will also complete those steps much quicker. Furthermore, anyone with access to the Excel file can run the script.

How to create a script in Excel for the web

At this point, you have a conceptual idea of what Office Scripts and TypeScript are, so let’s move on and create a script in Excel for the Web. The good news is that the app does most of the work for you. In a nutshell, you’ll record your actions as a script and then play that script when needed.

To get started, open a blank workbook in Excel for the Web. Let’s suppose that you create the same simple Table for a lot of sheets. If you do this several times a day, the task can easily become a bit tiresome.

When launching Excel for the web, open a blank workbook and then doing the following:

1. Click the Automate tab.
2. Click Record Actions in the Scripting Tools group. Doing so opens the Record Actions pane.
3. Select a few columns and rows—it doesn’t really matter, but I selected C2:F7.
4. Click the Insert tab and then click Table in the Tables group.
5. Click the My Table Has Headers option and click OK.
6. Click Stop in the Record Actions pane (Figure A).

Figure A

7. In the next pane, enter the name CreateTable (Figure B) for the new script.

Figure B

You just created your first script. Congratulations!

Now, close the Record Actions pane and the workbook (simply click the workbook tab to close it). Open another new workbook so you can run the script.

To run the new script, click the Automate tab. Click All Scripts in the Office Scripts group, which will open a list of existing scripts in the Code Editor window, as shown in Figure C.

Figure C

Click the three dots to the right and then choose Run from the resulting submenu shown in Figure D.

Figure D

As shown in Figure E, by running the CreateTable script, you can create the same Table in the same range with just a few clicks.

Figure E

In such a simple example, creating the Table is almost as quick as running the script, but that won’t always be the case. In addition, you can add a script button to the sheet for even quicker access, but that only works with the same workbook.

To view the actual script, click the three dots button and choose Edit. Doing so opens the edit window shown partially in Figure F.

Figure F

Depending on your screen size, you might need to close the ribbon for a while to view this window. The complete script follows:

function main(workbook: ExcelScript.Workbook) {

let selectedSheet = workbook.getActiveWorksheet();

// Add a new table at range C2:F7 on selectedSheet

let newTable = workbook.addTable(selectedSheet.getRange("C2:F7"), true);

}

The script is easy to follow but don’t worry if you don’t understand it because you don’t need to. When you’re ready to move beyond the recorder, you can study TypeScripts. There’s a lot of free documentation available online.

Remember, anyone that can edit the workbook can run attached scripts. Not everyone will have permission to edit. To share the script so that anyone can run it, click the three dots button and choose Share.

This simple how-to is a good place to start, but Office Scripts offers a whole new world of opportunities. How far you travel is up to you and your needs.