Office Scripts Introduction

Sample manual - first two chapters

Manual 1286 - 132 pages –

 TABLE OF CONTENTS (1 of 5)

1 GETTING STARTED Page 3 WRITING OFFICE SCRIPTS Page

1.1 Introduction to Office Scripts 7 3.1 TypeScript Language Basics 19
What are Office Scripts? 7 TypeScript Language Elements 19
What do I need to use Office Scripts? 7
3.2 Tools for Writing Code 20
Can’t I already Automate Excel? 8
Why use Office Scripts and not VBA? 8 IntelliSense 20
What are the Limitations? 8 Showing More Information 20
Showing Help Popups 21
1.2 Preparing to Write Office Scripts 9
The Context Menu 21
The Automate Ribbon Tab 9 The Command Palette 21
The Code Editor 10
3.3 Writing Neat Code 22
1.3 Writing Your First Script 11
Writing Comments 22
Creating a New Script 11 Commenting Out Code 22
The Basic Structure of a Script 11 Writing Long Instructions 23
Adding a Comment 12 White Space and Indenting 23
Writing Basic Instructions 12 Auto Formatting a Script 24
Adding More Instructions 13 Using Semicolons 24
Running the Script 13
3.4 Dealing with Errors 25
1.4 Code Editor Settings 14
Spotting Compile-time Errors 25
Changing Code Editor Settings 14 Viewing Error Information 26
A Note on Visual Studio Code 14 Cycling Through Errors 26
Fixing Errors Automatically 26

2 WORKING WITH SCRIPT FILES Page 3.5 Getting Help 27

2.1 Office Script Files 15 Microsoft Documentation 27

Other Documentation 27
Viewing Existing Script Files 15 Recording Scripts 28
The More Options Menu 15 Searching for Help 28
Creating a New Script File 16
Opening an Existing Script 16
Renaming an Open Script 16 4 RUNNING SCRIPTS Page
Deleting a Script 16
4.1 Introduction 29
2.2 Saving Script Files 17
Running a Script 29
Where Office Scripts are Saved 17 Stopping a Script that is Running 29
Saving an Open Script 17
4.2 When Things Go Wrong 30
The Save As Option 17
Spotting Runtime Errors 30
2.3 Sharing Scripts 18
4.3 Using Buttons 31
Sharing a Script 18
Stopping Sharing a Script 18 Creating a Button 31
Using a Button to Run a Script 31
Editing a Button 32
Removing Buttons 33

© Copyright 2024 Page 2

 TABLE OF CONTENTS (2 of 5)

5 RANGES Page 6 VARIABLES Page

5.1 Referring to Range Objects 34 6.1 What are Variables? 52
Referring to the Active Cell 34 Declaring a Variable 52
Referring to a Cell by Reference 34 Reassigning Variables 53
Referring to Multiple Cells 35 Declaring Constants 53
Using Named Ranges 35 Using the Var Statement 53
Using Row and Column Numbers 36
6.2 Variable Scope 54
Multiple Cells using Indexes 36
Scope with the Let Statement 54
5.2 The RangeAreas Object 37
Scope with the Var Statement 54
Referring to Multiple Separate Ranges 37
6.3 Variables and Types 55
RangeAreas vs. Range Objects 37
Primitive and Object Types 55
5.3 Getting and Setting Cell Values 38
Implicitly Typed Variables 55
Changing the Value of a Cell 38 Explicit Variable Types 56
Changing Values without Selecting Cells 38 Casting Types 56
Reading the Value of a Cell 39 Printing the Type of a Value 57
Cell Data Types 39 The Unknown Type 57

5.4 Cell Formulas 40

Creating Formulas using Cell References 40 7 CONDITIONAL STATEMENTS Page
Creating Formulas using R1C1 Notation 40 7.1 The If Statement 58
5.5 Formatting Cells 41 Basic If Statements 58
Fill Formatting Properties 41 The Else Clause 59
Font Formatting Properties 41 Else If Clauses 59
Formatting Cell Borders 42 7.2 Writing Logical Tests 60
Changing Number Formats 42
Using Cell Styles 42 Comparison Operators 60
Combining Conditions 60
5.6 Moving Between Worksheets 43 The Not Operator 60
Activating a Worksheet 43 7.3 Conditions and Objects 61
Modifying Cells without Switching Sheets 43
Testing if an Object Exists 61
5.7 Relative Cell References 44 Conditional Actions 61
Finding the Last Cell in a List 44 7.4 Conditions and Cell Values 62
Selecting to the End of a Filled Range 44
Referring to a Region of Cells 45 Testing the Type of a Value 62
Offsetting Rows and Columns 45 Testing for Empty Cells 62
Resizing a Range 46 7.5 Other Conditional Statements 63
Entire Rows and Columns 46
Getting the Used Range 47 The Conditional Operator 63
Finding Special Cells 47 The Switch Statement 63

5.8 Inserting, Deleting and Clearing Cells 48

Inserting Cells 48
Deleting Cells 48
Clearing Cells 48

5.9 Moving and Copying Cells 49

Moving Cells 49
Copying Cells 50
Options for Copying Cells 50

5.10 Finding Ranges 51

Finding a Single Cell 51
Finding Multiple Cells 51

© Copyright 2024 Page 3

 TABLE OF CONTENTS (3 of 5)

8 WORKING WITH DATA Page 10 CONDITIONAL LOOPS Page

8.1 Working with Numbers 64 10.1 Conditional Loops 82
Basic Operators for Numbers 64 While Loops 82
Order of Operations 64 Do While Loops 83
Number Assignment Operators 65 Nesting Loops 83
Formatting Numbers 65
10.2 Exiting a Loop 84
Converting Strings to Numbers 66
The Number Object 66 The Break Statement 84
The Math Object 67 The Continue Statement 84
Rounding Numbers 67 Using Line Labels 85
Generating Random Numbers 67
10.3 Finding Text with a Loop 86
8.2 Working with Strings 68
Concatenating Text 68 11 FOR LOOPS Page
String Templates 68
Escaping Characters 69 11.1 Introduction to For Loops 87
New Lines 69 For Loop Syntax 87
Editing Strings 70 A Basic Example 87
Comparing Strings 70
Searching Strings 71 11.2 For Loops and Ranges 88
Regular Expressions 71 Getting a Cell by Row and Column 88
Number
8.3 Working with Dates 72 Offsetting from a Range 88
Creating a Date Object 72 Looping Over Rows and Columns 89
Getting and Setting Date Parts 72
11.3 More For Loop Features 90
Formatting Dates 73
Converting Excel Dates to TypeScript 73 Continue and Break Statements 90
Dates Optional Loop Statements 90
Testing for Date Values 74 Multiple Loop Statements 90
A Quirk of TypeScript Dates 74

9 FUNCTIONS AND PARAMETERS Page

9.1 Functions 75
Why Create Extra Functions? 75
Declaring a Function 76
Calling a Function 76

9.2 Parameters 77
Declaring Parameters 77
Default Parameter Values 77
Optional Parameters 78

9.3 Returning Values 79

The Return Statement 79
Specifying the Return Type 79

9.4 Function Expressions 80

Creating a Function Expression 80
Anonymous Function Expressions 80

9.5 Arrow Function Expressions 81

Creating an Arrow Function Expression 81
Including Multiple Statements 81

© Copyright 2024 Page 4

 TABLE OF CONTENTS (4 of 5)

12 ARRAYS Page 13 COLLECTIONS Page

12.1 Introduction to Arrays 91 13.1 Introduction to Collections 107
Declaring and Populating an Array 91 What are Collections? 107
Viewing Array Contents 91
13.2 Collections and Objects 108
Referring to Array Elements 92
Declaring an Empty Array 92 Referencing Collections 108
Collections as Arrays 108
12.2 Modifying Array Contents 93
Referencing Objects in Collections 109
Sorting Arrays 93 Referencing Objects as Items in Arrays 109
Adding Items 93
13.3 Adding and Deleting Objects 110
Removing Items 94
Altering Array Values 94 Adding Items to Collections 110
Deleting Items from a Collection 110
12.3 Searching Arrays 95
13.4 Looping Through Collections 111
Basic Array Search Methods 95
Searching Arrays with Arrow Functions 95 Deleting Objects in a Loop 111

12.4 Extracting Values from Arrays 96 13.5 Worksheet Examples 112

Extracting Items to New Arrays 96 Protecting and Unprotecting Worksheets 112
Combining Arrays into a New Array 96 Hiding all but One Worksheet 112
Converting an Array to a String 96 Consolidating Worksheets 113
Dividing a List into New Worksheets 114
12.5 Looping Over Arrays 97
13.6 Chart Examples 115
While Loops 97
For Loops 97 Changing Chart Types and Formats 115
For Of Loops 98 Creating a Chart on Every Worksheet 115
The forEach Method 98 Looping Over Chart Series 116
Looping Through Data Points 116
12.6 Multi-Dimensional Arrays 99
13.7 Table Examples 117
Creating a Multi-Dimensional Array 99
Referring to Elements 99 Creating a Table on Each Worksheet 117
Looping Over Multi-Dimensional Arrays 100 Looping Through Tables 117
Table Columns 118
12.7 Arrays and Ranges 101
Adding Table Rows 118
Getting Cell Contents as an Array 101
Writing an Array to a Range 102
Looping Through Range Values 103 14 ERROR HANDLING Page
RangeAreas and Arrays 104 14.1 What is Error Handling? 119
12.8 Arrays and Function Parameters 105 The Try Catch Statement 119
The Argument Array 105 The Finally Block 120
Passing an Array to a Function 105 The Error Object 120
The Rest Parameter 106 14.2 Throwing Exceptions 121
Returning an Array 106
The Throw Statement 121

14.3 Errors in Functions 122

Catching Errors in the Calling Function 122
Throwing Errors in a Called Function 122
Catching Errors in a Called Function 123

© Copyright 2024 Page 5

 TABLE OF CONTENTS (5 of 5)

15 CRIB SHEET Page

15.1 Office Scripts Reference 124
Selecting and Activating Things 124
Selecting a Range Relatively 125
Using Variables 126
Conditional Statements 127
Looping 128
Creating Functions 129

© Copyright 2024 Page 6

 Chapter 1 - Getting Started

CHAPTER 1 - GETTING STARTED

1.1 Introduction to Office Scripts

This chapter introduces you to Office Scripts, beginning with the answers to a few simple questions.

What are Office Scripts?

Office Scripts are small programs used to automate tasks in Microsoft Excel. You write code in the
TypeScript language and you can either record the steps or write your program from scratch.

A basic Office Script to add

a value to the end of a list
and apply some formatting.

What do I need to use Office Scripts?

According to Microsoft, these are the three requirements for using Office Scripts:

1) Excel for Windows, for Mac, or on the web.

2) OneDrive for Business.

3) Any commercial or educational Microsoft 365 license with access to the Microsoft 365 Office
desktop apps.

© Copyright 2024 Page 7

 Chapter 1 - Getting Started

Can’t I already Automate Excel?

If you’re thinking that Office Scripts sound a lot like VBA macros, you’re right! In fact, there are three
main ways to automate Excel, summarised in the table below:

Method What you can do

Office Scripts Automate the desktop or online version of Excel.
VBA Macros Automate any desktop Office application.
Office Add-Ins Create an add-in to extend the features of any desktop or online Office application.

Why use Office Scripts and not VBA?

Office Scripts allow you to do some things you can’t do with VBA macros, as described in the table
below:

Feature Description
Excel online You can use Office Scripts to automate workbooks in the web version of Excel.
Power Automate You can run Office Scripts from a Power Automate flow.
External APIs Office Scripts support calls to external APIs which can provide data to your files.
Better security An Office Script only has access to the workbook in which it is running, unlike VBA
which has access to your entire computer.

What are the Limitations?

Office Scripts have several limitations compared to VBA and Office Add-Ins, as described in the
table below:

Feature Description
Excel only Currently, Office Scripts can only be used in Excel. Office Add-Ins can also work with
Word, PowerPoint, Outlook, OneNote and Project. VBA can use OLE and COM
libraries to control a variety of other applications, including the Microsoft Office apps.
No events VBA and Office Add-Ins can respond to events to make code run automatically. Office
Scripts must be run explicitly by the user.
Single workbook An Office Script only has access to the workbook in which it is running. Even something
as simple as copying a value from one workbook to another requires the use of a Power
Automate flow.
User interface Office Scripts don’t have access to any of the user interface elements of Excel. This
means that you can’t display dialog boxes or similar UI elements. If you need to create
a user-interface it would be better to use VBA or to create a full Office Add-In.

© Copyright 2024 Page 8

 Chapter 1 - Getting Started

1.2 Preparing to Write Office Scripts

This section shows you the basic things you need to do before you can write your first script.

The Automate Ribbon Tab

The Automate tab of the ribbon contains the tools you need to write Office Scripts.

Select Automate in
the ribbon to see
tools for working
with Office Scripts.

If you’re using the desktop version of Excel, the Automate tab may not appear automatically. You
can see how to display it in the diagram below:

a) Right-click any ribbon tab (here

we’ve chosen the Home tab) and
choose Customize the Ribbon...

b) On the right side of the dialog

box, check the Automate box.

c) Click OK to add the Automate

tab to the ribbon.

© Copyright 2024 Page 9

 Chapter 1 - Getting Started

The Code Editor

You edit Office Scripts in the Code Editor window in Excel. You can open the Code Editor in several
ways; a simple option is to choose Automate | All Scripts from the Excel ribbon.

Click All Scripts to

see the Code Editor.

The Code Editor

appears on the right of
the Excel window.

You can click here to

create a new script.

This area will show a

list of existing scripts
and gives you a quick
way to open them.

You can change the

width of the Code
Editor (you will
definitely want to
increase this!) by
clicking and dragging
the vertical divider.

In the desktop version of Excel you can move and resize the Code Editor window by clicking and
dragging its title bar.

Click and drag the title bar of the Code

Editor to move it.

If you leave the Code Editor floating over

the Excel window you can click and drag
the window borders to resize it.

You can drag the Code Editor window to

the left or right of the Excel window to
dock it in position.

© Copyright 2024 Page 10

 Chapter 1 - Getting Started

1.3 Writing Your First Script

This section shows you how to write a simple program to add text to a cell and apply some formatting
to get used to the basics of writing code.

The result of running the

script is this beautifully
formatted cell!

Creating a New Script

Start by creating a new workbook in either the desktop or online version of Excel and then choose
to create a new Office Script.

You can create a new script by choosing Automate | New

Script from the ribbon. Alternatively, click New script at the
top of the Code Editor window.

Your new script appears in the

Code Editor window. Excel will
immediately save the script to
the default Office Scripts folder
in OneDrive.

The Basic Structure of a Script

You can see the basic structure of your new script in the diagram below:

The script contains a function called main The function has a parameter called workbook which gives your
which acts as a container for your code. code access to the Excel file that you currently have open.

The instructions of the

function are contained in
a set of curly brackets, or
braces.

The function contains a single line of code consisting of a comment. You can use comments to annotate your code.

© Copyright 2024 Page 11

 Chapter 1 - Getting Started

Adding a Comment

You can add your own comments to your code to provide helpful reminders of what it is meant to do.

Click at the end of the line 2 then press Enter twice to Type two forward slashes to begin the comment and
provide a new line on which to write your comment. follow these with anything you feel is appropriate.

Writing Basic Instructions

Our first instruction will write the name Wise Owl into the currently selected cell in the workbook. To
do this:

1) On a new line in the script, begin typing the word workbook (take care when doing this –
TypeScript is a case-sensitive language!).

As you begin typing you’ll see a list

of options matching what you’ve
typed so far. To select an item
from this Intellisense list you can
press Tab or Enter or click on the
word with the mouse.

2) After the word workbook, enter a full stop and begin typing getActiveCell.

Use the Intellisense

list to help you finish
entering the word.

3) After getActiveCell, open and close some round brackets.

You can type in the open round

bracket and the closing bracket will
be added for you automatically.

4) Enter another full stop followed by setValue then an open round bracket and double-quotes.

The other double-quote and closing

round bracket will be added for you
automatically.

© Copyright 2024 Page 12

 Chapter 1 - Getting Started

5) Between the double quotes, enter the name you want to write into the cell.

You can change the text

in the double quotes to
anything you like.

6) Complete the instruction by adding a semicolon to the end of the line.

Each TypeScript instruction

ends with a semicolon.

Adding More Instructions

You can continue adding as many instructions as you need to complete your program. The diagram
below shows the complete script with two extra instructions and relevant comments:

We’ve deleted the first

comment to tidy up the
code.

The pattern of the new

instructions is similar to
the first one we added.

Running the Script

You can see how to run your finished script in the diagram below:

Click Run at the top of the Code Editor to run the script that you
currently have open.

A message appears to tell you

the code is running or, if you’re
unlucky, that the code has
failed! You can click Stop if
you need to abandon a script
that appears to be stuck. The beautiful end result!

Wise Experienced VBA programmers should be careful not to press F5 to run your code.
Owl’s
Hint
In the online version of Excel this will refresh the page and close the Code Editor!

© Copyright 2024 Page 13

 Chapter 1 - Getting Started

1.4 Code Editor Settings

Changing Code Editor Settings

To change Code Editor settings you first need to open a script file.

Click ... at the top right of the Code Editor and choose Editor Settings
from the menu.

To return to your script, click

this arrow.

You can change the Theme

to alter the appearance of
the editor. The example
below uses the Dark theme.

You can enable a Minimap

of your script which appears
at the top right of the code
window.

The Folding option allows you to collapse and expand sections of your
code contained within different types of brackets.

With the Folding option enabled you can This option allows you to edit
click arrows like this one to collapse and a script in the online version
expand sections of your script. of Visual Studio Code.

A Note on Visual Studio Code

If you’re working in the online version of Excel, you can enable the Visual Studio Code connection
option. You can then open a script in the Visual Studio Code web application, as shown below:

Use the menu at the top right of

the code window and choose
Open in VS Code.

You can edit the script You can close the Visual Studio tab or click
in the new tab. Close to return to the Code Editor in Excel.

© Copyright 2024 Page 14

 Chapter 2 - Working with Script Files

CHAPTER 2 - WORKING WITH SCRIPT FILES

2.1 Office Script Files

This section explains the basics of working with Office Script files.

Viewing Existing Script Files

You can see your recently used and sample scripts in the Automate tab in the Excel ribbon.

Your Office Scripts appear in a

gallery. You can click a script
to see its details in the Code
Editor window.

To see all your scripts, choose Automate | All Scripts from the Excel ribbon.

The list of script files appears

in the Code Editor window.

You can click the script name

to see its details, as shown on
the right.

You can click the file location

to open it in OneDrive.

Click View more scripts to open a dialog box which lets you browse to
other folders in OneDrive and SharePoint.

The More Options Menu

You can use a script’s More options menu to perform several useful tasks on it.

Hover over a script in the list then

click the three vertical dots to
open the More options menu.

You can open the More options

menu when the file is open by
clicking the three horizontal dots.

The menu allows you to perform

several useful tasks on the file.

© Copyright 2024 Page 15

 Chapter 2 - Working with Script Files

Creating a New Script File

The simplest way to create a new script is to choose Automate | New Script from the Excel ribbon.

The new script opens ready

for editing. Excel saves the
script for you in the default
save location.

Opening an Existing Script

You can open an existing script to edit its code as shown in the diagram below:

Hover the mouse over the script and then

click the pencil icon which appears.

Alternatively, you can choose Edit from

the script’s More options menu.

Renaming an Open Script

You can rename a script file, even if you’re currently editing it.

Click the script name or choose Rename from the More options menu.

Type the new name for the script file and then press Enter to rename it.

Deleting a Script

You can delete a script from the More options menu, even if you are currently editing it.

Choose Delete from the

More options menu.

Click Delete to confirm

your choice.

© Copyright 2024 Page 16

 Chapter 2 - Working with Script Files

2.2 Saving Script Files

Where Office Scripts are Saved

By default, Office Script files are saved in the Documents/Office Scripts folder in OneDrive.

You can click the file location of a script to

open the folder in OneDrive.

Office Script files have the osts filename

extension.

Saving an Open Script

You can save the script that you’re currently working on in the default folder as shown below:

Click here to save the script you have open.

You’ll see a confirmation message like this.

The Save As Option

You can choose to save your script in a different location as shown below:

a) Pick Save script as from the More options menu.

b) Navigate to the folder you want to save the script

into. You can create a new folder by clicking the
button at the top right of the dialog box.

c) Click Save to save

the file in its new
location.

© Copyright 2024 Page 17

 Chapter 2 - Working with Script Files

2.3 Sharing Scripts

Scripts saved to your OneDrive are accessible only to you. If you’d like others to use your scripts,
you’ll need to share them in some way.

Sharing a Script

The first step in sharing a script is to share it in a workbook. To do this, first open the workbook you
want to share the script in.

a) Choose Share from the script’s b) Click Share to confirm

More options menu. that you want to share
the script. The script’s
icon changes to show
that it is being shared:

You’ll also need to give other people access to the workbook, as shown in the diagram below:

a) Click the Share menu in the

top right of the Excel window
and choose Share.

c) Share the file

by sending a
link in an email,
or by copying
b) In the dialog box, configure the link and
with whom the workbook is to distributing it
be shared. another way.

Stopping Sharing a Script

The diagram below shows how you can reverse the process of sharing an Office Script:

a) From the script’s

More options menu,
choose Stop
sharing.

b) On the dialog box which appears, choose whether to stop

sharing in all workbooks or just the currently open one, and
then click Stop sharing.

© Copyright 2024 Page 18

 WHAT WE DO

ONLINE MANCHESTER AT YOUR BESPOKE

TRAINING OR LONDON OFFICE CONSULTANCY

Microsoft
Excel ✓ ✓ ✓ ✓
VBA
✓ ✓ ✓ ✓
OFFICE

macros
365

Office
Scripts ✓ ✓
Microsoft
Access ✓

Power BI
and DAX ✓ ✓ ✓ ✓
PLATFORM
POWER

Power
Apps ✓ ✓
Power
Automate ✓ ✓ ✓ ✓

Reporting
Services ✓ ✓ ✓ ✓
Report
✓ ✓ ✓
SERVER

Builder
SQL

Integration
Services ✓ ✓ ✓ ✓
Analysis
Services ✓ ✓

SQL ✓ ✓ ✓ ✓
LANGUAGES
CODING

Visual C# ✓ ✓ ✓ ✓

Python ✓ ✓ ✓ ✓

www.wiseowl.co.uk | (0161) 883 3606 | sales@wiseowl.co.uk

www.wiseowl.co.uk | (0161) 883 3606 | sales@wiseowl.co.uk