When beginners start building their first Office Scripts, testing the code can be a challenge. Since Office Scripts are designed for Excel automation, a common approach is to display the value of a variable or function return by assigning it to a random range in the spreadsheet, but this method inserts unnecessary data to your file and risks potential data losses as it could result in overwriting cells content.

In this article, we will introduce a simple, clean and safe solution to displaying these values during your Office Script run without the need to write them to the spreadsheet, which is the console.log() function. If you are new to Office Scripts or wants to learn more about it, check this post about how to get started.

For real-world Office Scripts examples, you can also explore the templates available in the Office Scripts Gallery.

What is the console.log?

The console.log() is a built-in function in JavaScript (and TypeScript) used to print messages or outputs to the browser’s console, or in case of Office Scripts, to the Code Editor Console. It works similarly to the print statement in VBA. Also, console.log() is very useful for debugging, which is the process of finding and fixing issues in computer programs. For example, when an Excel formula returns a #REF error, the process of investigating its causes and finding a solution could be considered as a for of debugging.

By using console.log(), you can display the values of variables, outputs of expressions, or error messages directly in the Code Editor Console, without the need to writing it to the spreadsheet. This helps you track how your code is running and identify any issues without affecting your worksheet. You can pass almost any type of data—like strings, numbers, objects, arrays, ranges, or worksheets —into console.log() to inspect what’s happening in your Office Script at different stages. It’s a very useful tool for understanding the flow and behavior of code during development.

Spreadsheet Overview

For the purpose of this article, we will work with a very simple spreadsheet. We will demonstrate how to access and print values from both individual cells and ranges of cells, retrieve cell metadata, and extract the fill color from cell B2:

Debugging your Office Scripts with console.log()

It’s very easy to get started with the console.log(): just type it and pass the value you want to display inside the parenthesis. In the example below, we have a string variable initialized in line of code 7 (address), and in line 9, we pass it as a parameter to console.log(). As a result, the value of this variable is displayed in the Code Editor Console, as highlighted in green:

The console.log() function accepts almost any type of data, including custom types in Office Scripts. For example, we can use this strategy to print the b2 variable, which references the range “B2” in the current worksheet – corresponding to a single cell in this case. Since we are accessing the range object itself, but not the value written to it, the output will display some metadata related to the “B2” cell, such as its address, format, or other properties, rather than the cell’s content:

In addition to variables, it’s also possible to print the properties directly referenced from the Excel file via the workbook object. In the example below, we reference the workbook object, access the current worksheet using getActiveWorksheet(), then target the cell “B3” with getRange(“B3”), and finally return its content using getValue():

We can also print the values from a range containing multiple cells. In this case, we will use the getValues() method (with an “s”) instead of getValue(), and the results will be presented as an array of arrays, where each inner array represents an individual row of the range. In the example below, we access the values from the range “A1:B3”, and the values from first row are represented in green, the second row in blue and the third row in yellow:

It’s even possible to retrieve specific metadata from a cell or range other than its value, like the fill color of cell “B2”. In this case, Office Scripts will return the hexadecimal code representing the color (for our example, this code corresponds to yellow):

The console.log() can also print the outputs from a function, as long as it has a return statement (to learn more about functions in Office Scripts, refer to this article). The syntax will be the same used for printing values from other entities:

Finally, you can also perform text, logical or mathematical operations within the console.log(), printing its results. In the example below, we perform a sum operation between two numbers (highlighted in red), concatenate the string “Suite 2100, ” to the beginning of the address variable (highlighted in green) and test if the address variable matches “123 Main St” using a logical operation (highlighted in blue):

Conclusion

The console.log() function is a versatile tool in Office Scripts that simplifies debugging and make your code development easier. Once you are allowed to display values, metadata, and results directly in the Code Editor Console, a lot of time is saved when testing or fixing your Scripts.