Home Newsletter

Deze pagina in het NederlandsHome > Article index >

Excel Office Script Basics

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. Later on, I will list some example scripts which you cannot record but might come in handy.

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. And, very very useful: An Office script can be part of a Flow, which you can create from that button 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.

Further reading

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

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.

Please enter your name (required):

Your e-mail address (optional, will only be used to inform you when your comment is published or to respond to your question directly):

Your request or comment (max 2000 characters):

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

I give permission to process this data and display my name and my comment on this website according to our Privacy Policy.