Office Scripts is a feature in Excel that enables users to automate tasks by writing scripts in TypeScript, enhancing productivity through automation. It works as a VBA in Excel for Web.

As Office Scripts use TypeScript (a typed superset of JavaScript), we need to understand some basic programming concepts to succeed when creating automation scripts for Excel, which can even be initialized from Power Automate (to learn more about this integration, check this article). One of the core topics in TypeScript, as in any programming language, is the use of variables. This article explores the essentials of variables in TypeScript, including their declaration, types, scope, and best practices.

What is a variable?

In TypeScript, a variable is a named storage that can hold a value of a specific type, allowing users to store, retrieve, and manipulate data within their code. Jus like in Power Automate, TypeScript variables are very useful when creating solutions and are a main topic for getting started with Office Scripts, as they can hold temporary values that can be changed during the execution of your script.

Similarly to Power Automate, you also need to specify the type of data that a variable can hold by using type annotations. However, while in Power Automate there is a list of six possible data types (check this article to learn more about variables in Power Automate), TypeScript allows you to work with many other types of data, both built-in and custom. Some of these types are specific to Office Scripts, holding data about Excel tables, worksheets, ranges and more.

Declaring a variable in Office Scripts

In Office Scripts, you can declare (create) a variable using any of the three reserved keywords: let, const, and var:

• let: This keyword should be used when you need a block-scoped variable whose value can change.
• const: This keyword should be used when you need a block-scoped variable whose value should not change after its assignment.
• var: This keyword should be used when you need a function-scoped variable whose value can change. The usage of var is discouraged, as it can lead to issues due to its lack of block scope.

We will see what a “scope” means later in this article.

To create a variable, you need to use the reserved keyword (let, const or var) followed by the variable name and its data type annotation, as shown in line 4. Notice that the variable name and the data type annotation are separated by a colon:

When you are creating a const variable, like in line 6, you must also assign a value to it, as this type of variable holds an immutable value.

As you can see in line 8, it is also possible (although it’s not mandatory) to assign values to variables declared with let and var. Still in line 8, in some cases you can omit the data annotation, as TypeScript can implicitly understand the type of data that you are assigning to the variable.

Variable scope in Office Scripts

In TypeScript, variable scope determines where variables can be accessed within the code. Variables declared with let and const are block-scoped, meaning they can be accessed only within the block (enclosed by {}) where they are defined.

This ensures that issues like variable collisions and unintended overwrites are avoided, as the variables are confined to their respective blocks. For example, a variable declared inside an if statement cannot be accessed outside of that statement:

In the example above, the variable counter was initialized inside an if statement (line 11). Because of its block-scope, this variable is accessible only within the if statement (marked in red), so when we try to access it from the outside, like in line 17, we receive an error.

On the other hand, variables declared with var are function-scoped, making them accessible throughout the entire function in which they are declared (as seen in line 2, the whole Office Scripts code is encapsulated into a function called main). Like in the example below, even if we initialize a variable inside the if statement block (line 12), its value is accessible outside of the block without any errors (line 19):

Similarly, variables initialized inside other TypeScript blocks, such as loops and switch statements, exist exclusively within these structures and cannot be accessed from outside (unless they are declared with var).

Variable type annotation in Office Scripts

As mentioned earlier, a variable must include a data annotation, which specifies the type of data that a variable can hold. For instance, when declaring a variable, you can specify its type using a colon followed by the type name, such as let age: number = 30. This helps in catching type-related errors during the Script writing process, as TypeScript will enforce these types and issue errors if the variables are used inconsistently.

In the following example, we have a variable initialized with the proper data annotation (line 4, underlined in green) and another one without an explicit annotation (line 6, underlined in red). Sometimes, TypeScript can automatically identify types based on the assigned values, dismissing the need for explicit annotations. When we try to assign a text value to rowNumber, which was created with a numeric type (line 14, underlined in orange), and error is issued stating that a string value cannot be assigned to a variable of type number (highlighted in white):

Data annotations are essential not only for the built-in TypeScript data types, like string, number, boolean and others, but also for custom types. In the context of Office Scripts, specific data types represent Excel worksheets, tables, charts, and more. For example, to assign a worksheet to a variable, you must annotate the ExcelScripts.Worksheet data type.

In the following image, we access the worksheet called “Sheet1” from the current workbook (underlined in yellow), and assign it to a variable named “ws”. During this process, we explicitly annotate the ExcelScripts.Worksheet data type to the variable (underlined in red):

Changing variable values

After initializing a variable, it is possible to change its value as many times as needed, except for const variables. To assign a new value to a variable, simply use the variable name followed by an equal sign and insert the new value afterward.

In the example below, the variable “rowNumber” is initialized with a value of 1 (line 4, underlined in yellow), and some lines later, its value is changed to 3 (line 8, underlined in red) and then to 5 (line 13, underlined in green):

You can verify the variable’s value change by running the scripts. For each value assignment, a console.log() function displays the current value of the variable “rowCount”. As you can see from the image below, each log entry has a different value for the variable “rowCount”:

Conclusion

As a fundamental TypeScript topic, variables are essential to develop efficient and reliable solutions with Office Scripts. This knowledge creates new opportunities for the users willing to leverage from Excel and Power Automate integration, enabling them to manipulate spreadsheets in an advanced level and perform tasks like populate spreadsheets, send data from ranges to Power Automate, manage worksheets, and much more.