How to understand console.log() in Office Scripts for Excel

Office Scripts provide an exciting new way to automate solutions for Excel workbooks using a variant of the TypeScript programming language. As with any programming language, it’s crucial to know how to debug your code effectively. If you’re looking to enhance your Excel workflows and improve efficiency, mastering debugging techniques in Office Scripts is essential.

One such technique is using console.log(), a simple yet powerful tool that allows you to track what’s happening inside your scripts in real-time. In this blog post, we’ll explore how to use console.log() for debugging, with a particular focus on its application within loops.

Basic usage of console.log()

Here’s a straightforward example of how console.log() can be utilized in an Office Script. Go ahead and insert this code into a new Script and run it to see what happens:

In this script, console.log() is used to log the start of the script and the name of the active worksheet. This helps confirm that the script is running and interacting with the correct sheet. You will notice that the Output log at the bottom of your Office Scripts code editor will print the results of console.log():

Using console.log() to debug

While console.log() can indeed be used, as shown in the previous example, to print outputs and provide a basic understanding of what’s happening in the script, it can also be a powerful tool for debugging. This function is particularly useful within loops, where it can be invaluable for monitoring iterations and inspecting changes in data or the flow of logic.

To get started, let’s say you want to log numbers from 1 to 5. Here’s how you might do it:

This script will log each number in the loop, helping you verify that the loop iterates the correct number of times and that the correct numbers are being produced.

Now let’s move onto a similar example using actual data from your Excel workbook. In this case, we want the script to sum the values in cells A1 to A5. I have created the script below, and I run it with console.log() to display the results. However, the results are clearly incorrect: It appears that the numbers are being concatenated rather than added.

Let’s add a console.log() inside the loop to figure out what’s happening. I will insert the statement from within the loop to print out exactly what is being added and what the intermediate results are.

From this, we can see that the loop is indeed just appending all the items together and not actually adding them. It appears there’s some kind of indexing issue here.

After some digging (here’s looking at you, gen AI!), I figured out the necessary change in the code (we’ll discuss the specifics shortly). I kept the console.log() statement to ensure the intermediate results make sense. This time, it seems to be performing a cumulative sum and giving me the correct result—amazing!

What caused the error?

At this point in your Office Scripts journey, it might be challenging to understand what exactly caused the differences in results. If that’s the case, don’t worry—it’s a perfect opportunity to deepen your understanding of how Office Scripts handles data, particularly the use of arrays.

If you’re ready to dive deeper, here’s a good example. But if you’re not up for it right now, that’s OK too, as long as you recognize the importance of using console.log() for debugging and displaying results!

The key difference between the two scripts is how they access cell values from the values array, which impacts how the numbers are added together.

In the first script:

let value = values[i]; // Correctly access the value
sum += value; // Add the value to the sum

The line let value = values[i]; is not correctly accessing the cell value. The getValues() function returns a two-dimensional array, even if the range is a single column or row. Therefore, values[i] is still an array, not a single number. This means when you attempt to add value to sum using sum += value;, JavaScript treats value as an array object, leading to unexpected behavior such as concatenation.

In the second script:

let value = values[i][0]; // Correctly access the value
sum += value; // Add the value to the sum

Here, let value = values[i][0]; correctly accesses the first element of the array values[i], which is the actual numeric value of the cell. This allows the script to correctly add up the numbers using sum += value;.

In summary, the first script fails to properly handle the two-dimensional nature of the array returned by getValues(), while the second script correctly accesses the individual cell values and successfully computes the sum.

Removing unnecessary console.log() statements

While console.log() can be extremely helpful for debugging, it’s a good idea to remove any instances used for testing to streamline the output and make it clearer for others to use. This is particularly important when console.log() statements are used in loops, as printing at each iteration can significantly bog down performance.

Conclusion

Debugging with console.log() in Office Scripts can illuminate the inner workings of your scripts, especially within loops where tracking data flow and iteration is crucial. By mastering these techniques, you can ensure your scripts are efficient and error-free, ultimately saving time and reducing frustration in your Excel tasks. Do you have any specific questions about debugging Office Scripts, or about Office Scripts in general? Please let me know in the comments.