Friday, November 12, 2021

Introduction to office scripts in excel

Automate your repetitive tasks with Office Scripts in Excel for the web. Record your actions, then replay them whenever you want. You can also edit your scripts as your workflow changes. Office Scripts are stored in the cloud, letting you update any of your workbooks as needed. 

Note: Before you can use Office Scripts, an administrator must enable them.

When you record your actions with the Action Recorder, a script is created. These actions can include entering text or numbers, clicking cells or commands on the ribbon or on menus, formatting cells, rows, or columns, formatting data as Excel tables, and so on. What you see when you're done is a clean task pane that displays a descriptive list of all the steps you just took. You don't need any coding experience to record and run Office Scripts. If you choose to edit your actions, you can do so from the Code Editor, where you can edit the script's TypeScript code. Learn all about writing scripts with the Code Editor in Record, edit, and create Office Scripts in Excel on the web

  • An active Microsoft 365 subscription account with a commercial or educational Microsoft 365 license that has access to the Microsoft 365 Office desktop apps. This includes:

    • Microsoft 365 Apps for business 

    • Microsoft 365 Business Standard

    • Microsoft 365 Apps for enterprise

    • Office 365 ProPlus for Devices

    • Office 365 Enterprise E3

    • Office 365 Enterprise E5

    • Office 365 A3

    • Office 365 A5

  • OneDrive for Business.

  • Server authentication - When first running the Script Recorder or Code Editor, you will need to authenticate with the server, so your scripts can be saved to the cloud. Sign-in to your Microsoft account as usual, then when you see the Permissions requested prompt, choose Accept to continue.

    Note: Once you've toggled on the correct setting in the admin center, end users with the appropriate license will be able to access the feature. This feature is not yet available in Microsoft 365 for US Government and customers with data residency in one of the new local datacenter geos.

Getting started

  1. First, select the Automate tab on the ribbon. This will expose your two primary options in the Scripting Tools group: Record Actions and New Script.

    Scripting Tools
  2. To create a new Office Script, press the Record Actions button, then start taking the steps you want to automate. For this example, we're taking a simple data range, converting it to an Excel table, adding a total column and total row, and finishing by formatting our values as currency.

    Before and after images of a 5x3 grid of data that will be used to create an Office Script to convert it to an Excel table with a total row and column, then format the data as currency.
  3. When you start recording a new Office Script, you'll see a Record Actions task pane open on the right. Here you'll see a short description of the actions you're taking listed in order. When you're done with all your steps, you can press the Stop button.

    Once you've recorded an Office Script, you'll see a description of what each step does.
  4. Once you've pressed the Stop button, the Code Editor pane will display with your new script selected.

    Code Editor
  5. By default, Excel will name your scripts "Script 1", "Script 2", and so on. However, you'll want to give your scripts meaningful names; otherwise, you'll have to dig through each of them to find the one you want. To give your script a new name, click on the three dots and select Rename from the pop up menu. 

    Office Script context menu

Replaying an Office Script

  1. If the gallery of Office Scripts isn't already displayed, you can show it from Automate > Scripting Tools.

    Script Ribbon
  2. Click the script you want to run. It will display in the Code Editor. Click the Run button to start the script. You'll see a brief notification that the script is running, which will disappear when the script is complete.

    Run a script

    Note: This topic doesn't discuss editing or writing your own TypeScript code, but you can review our Office Scripts technical documentation.

  3. Other options - If you click the ellipsis (...) on the right-hand side of the Code Editor pane, you'll see the contextual menu. Here you have the option to:

    More Options

    • Rename the script

    • Make a Copy of the script

    • Share the script

    • Automate the script by using Create Flow

    • Delete the script

Potential Errors

  • It's important to know that you when you record an Office Script, the Script Recorder captures almost every supported action that you take. So if you make a mistake in your sequence, for example, clicking a button that you did not intend to click, the Script Recorder will record it. The resolution is to re-record the entire sequence, or modify the TypeScript code itself. This is why whenever you record something, it's best to record a process with which you're highly familiar. The more smoothly you record a sequence, the more efficiently it will run when you play it back.

  • Certain actions may be fine the first time you record your script, but fail when you try to play them back. For instance, in the earlier example, where we formatted some sample data as a table, our code would fail if we tried to run it on the updated table, because Excel doesn't allow tables to overlap each other. At this point, the Code Editor will display an error message.

    Code Editor error message stating that the script ran with errors. Press the Logs button to learn more.

    Clicking the View Logs button will display a brief error explanation at the bottom of the Code Editor pane.

    Script error
  • Unsupported features - We're constantly working to add support for more features, but at this time not everything is supported. When this happens you'll see a note in the Record Actions pane. You can replay the code, and it will simply ignore any steps it couldn't record.

    Racord Actions dialog indicating when certain steps could not be recorded.

Need more help?

You can always ask an expert in the Excel Tech Community or get support in the Answers community.

See Also

Office Scripts technical documentation

Record, edit, and create Office Scripts in Excel on the web

Troubleshooting Office Scripts

Sample scripts for Office Scripts in Excel on the web

Create a button to run an Office Script

No comments:

Post a Comment