Excel Office Script Basics

Content

Introduction

Since July 2020, Microsoft 365 users can record their actions in Excel on-line into Office Script macros. This article shows you how to get started. I've also listed some example scripts which you cannot record but might come in handy.

Video

Here's a link to the video on Youtube.

Enabling the Automate tab

First of all, whether or not you can use Office scripts is controlled by your Microsoft 365 administrator. If you are a one-man shop, you can turn this on yourself. If not, you can at least show your admin what setting you want turned on and where that switch is.

  1. Go to https://office.com and click the admin button:
    The Admin button on Office.com
  2. In the admin center, click the search box and enter "Office Script" (without the quotes) and choose the "Office Scripts" entry as shown below:
    Microsoft 365 Admin Center Search box with Office Scripts
  3. A side-pane opens containing the options to pick. Notice how the admin can grant access to Office scripts to everyone but also limit access to just specific groups. I have it set like this:
    Microsoft 365 Admin Center Office Scripts settings

Odd enough, when I was retracing the steps above (just minutes after creating those screen-shots!), I could not get it to show the same search result. So, if the Search does not return the button I showed earlier, here are the individual steps:

  1. Open the Admin center and click on "Show All":
    Microsoft 365 Admin Center Show All
  2. Next, click on Settings, then click Org settings and then in the right-hand pane scroll down until you find the Office Scripts entry. Click on that:
    Microsoft 365 Admin Center Office Scripts In Org Settings
  3. You'll get the same pane as shown in step 3 above.

Using the Automate tab

Once the admin changes have trickled through to your user account (this may take more time than you expect, perhaps as much as two days!), you should see an Automate tab appear as soon as you open an Excel file in your browser:

Automate tab in Excel on-line

Lets have a look at the options we have here.

Record Actions button

What, Really? An on-line macro recorder? Yes, an on-line macro recorder!

What does it record, you might ask? The Record actions button records your Workbook editing steps into a special version of Office JavaScript (Office-JS). Fortunately, Microsoft has taken the trouble of "hiding" the most complicated part around programming JavaScript from the user.

Because JavaScript runs in the browser and has to communicate with a server (Excel on-line), most commands are processed asynchronously. Office JS code does not work the same as VBA code does, where each line triggers an action immediately. This means that "executing" a JavaScript line is more like sending a request to the server running Excel. It is up to the server to perform that request in its own time. You cannot depend on the request having been executed before your next line of code is executed. This principle is explained well in this documentation.

But as I said: That complexity is hidden from us and Office Script works more or less the same for us mere mortals as VBA code does.

As soon as you click the record Actions button, the recorder is running. There is no Macro name or location to be entered like when recording VBA:

The Record Actions Pane in Excel on-line

Notice there's a "Learn more" link at the bottom. It takes you to the official Microsoft tutorial about recording scripts.

I took three actions: I inserted a new worksheet, typed my name into cell A1 and entered today's date into A2. The pane now looks like this:

The Record Actions Pane in Excel on-line

The recorder has added the steps and a new "Copy as code" link has appeared. If you click that, the currently recorded code is placed on the clipboard. In addition there is a "Restart" button and a Stop button. Restart just deletes all recorded actions from the macro. Stop -well- stops the recorder.

After you've clicked the Stop recording button, the script is saved using a default name, which you can change later:

The Code Editor Pane in Excel on-line

Of course Microsoft hid some important features behind the infamous ellipses button:

The Code Editor Pane in Excel on-line

So this is where you find the option to rename your script to something more meaningful like AddSheetAndEnterMyName. More importantly, this is also where the option to share your script with your co-workers is, either by using the Share feature, or by using the Add button item (see below). And, very very useful: An Office script can be part of a Flow, which you can create from that menu too.

Now what does that recorded code look like and how do I access it? Simple, click that "Edit" button up there (next to "Run")! This is what my recorded actions translate into in Office Script:

Recorded Office Script Code in Excel on-line

Work in progress

You'll soon notice this Office script business is a work-in-progress, there are actions which the recorder cannot turn into script yet. If that happens, the recorder pane will show "This action is not yet recordable":

Action which could not be Recorded in Excel on-line

These unrecordable actions show up in the script as commented lines:

  // Unknown event received with eventId:578
  // Unknown event received with eventId:578

Rest assured Microsoft is working hard to add new actions to the recorder. I'm impressed how many actions have been added since I first started trying the script recorder.

New Script button

If you're brave enough, this is the way to start writing Office Scripts from scratch; you just click the New Script button and off you go!

Recent Scripts gallery

Microsoft keeps a list of recently used or recorded scripts in the Recent Scripts gallery for easy access:

Recent Scripts gallery in Excel on-line

Needless to say you can access all your scripts using the All scripts button at the bottom of that fly-out. More importantly, for the beginners there's a couple of samples available too.

See? Simple! Nothing to it. I suggest you to start experimenting. Try various different things with that script recorder turned on.

Here are some example scripts I was unable to record. But wrote anyway.

Starting a Script

There are currently three ways to start a script:

  1. By clicking Run in the Script Code Editor pane
  2. By adding a Run script trigger in a Power Automate Flow
  3. By adding a button (as decribed below)

After clicking the "Add Button" menu item in the screen-shot below, Excel will insert a button with the name of the script as its caption:

The Code Editor Pane in Excel on-line

The button is inserted at the cell which happened to be active when you clicked the Add button menu item, so it is a good idea to first select the cell where you want the button to appear.

A newly inserted Script button in Excel on-line

After clicking the button, the Script Editor pane opens to show progress:

Script Editor pane opens after you click a Script button in Excel on-line

Once the script is done, the pane shows its result:
Script Editor pane shows script completed after you click a Script button in Excel on-line

A script button now also works on desktop Excel, provided you are subscribed to the Office Insider program.

Further reading

Here's an an article with some example scripts

Here are some links to the Microsoft documentation on Office Scripts:

Office Scripts documentation

Office Scripts API reference

Introduction to Office Scripts in Excel


Comments

All comments about this page:


Comment by: Chris van Zyl (17-12-2022 14:40:00) deeplink to this comment

Greetings JKP and thank you for a useful site.
I am a "one man shop", retired, with a Microsoft 365 Family license. I have used VBA for years but my attempt to enable the automate tab failed at step 1: when I logged in to office.com I got a screen similar to the first screenshot above but without the Admin button.
I assume this may be because my license is not a business license, correct?


Comment by: Jan Karel Pieterse (21-12-2022 18:22:00) deeplink to this comment

Hi Chris,

This page lists the requirements to have access to Office Scripts:

https://support.microsoft.com/en-us/office/record-your-actions-as-office-scripts-453ab58d-708f-40a9-ab87-99a743bfa69a

If your subscription type is listed there, you should be able to get that Automate tab.


Comment by: Julian (22-7-2023 19:45:00) deeplink to this comment

With a Microsoft 365 Business Basic subscription, can I use office script on Excel online and no need to subscribe onedrive business any more?


Comment by: Jan Karel Pieterse (22-7-2023 23:06:00) deeplink to this comment

Hi Julian,

See: https://answers.microsoft.com/en-us/msoffice/forum/all/hi-i-wish-to-use-office-scripts/66be1f4a-9bcb-4ca7-91be-08881a34a137


Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.




To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].