How to Uppercase an Entire Sheet in Office Scripts Excel Using TypeScript

Описание: Learn how to efficiently convert all text in an Excel worksheet to uppercase using Office Scripts and TypeScript, seamlessly transitioning from VBA.
---
This video is based on the question https://stackoverflow.com/q/71003467/ asked by the user 'Siaris18' ( https://stackoverflow.com/u/9541451/ ) and on the answer https://stackoverflow.com/a/71003854/ provided by the user 'Skin' ( https://stackoverflow.com/u/5772095/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: How do I uppercase an entire sheet in Office Scripts Excel (Typescript)?

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Uppercase an Entire Sheet in Office Scripts Excel Using TypeScript

Are you transitioning your scripts from VBA to Office Scripts in Excel 365? One common question that arises is how to convert all text in a worksheet to uppercase. Unlike VBA, where this could easily be done with a simple loop, Office Scripts require a different approach. In this post, we'll guide you through the process of uppercasing an entire sheet using TypeScript in Office Scripts.

Understanding the Problem

In VBA, you might be used to using a loop over a selected range to convert each cell's content to uppercase. Here’s a quick reminder of how you achieved this with VBA:

[[See Video to Reveal this Text or Code Snippet]]

While this works perfectly in VBA, Office Scripts has its own structure and syntax that you'll need to adapt to. Let's explore how to implement a similar functionality in Office Scripts.

The Solution: Step-by-Step

1. Basic Structure of an Office Script

An Office Script is built around a function called main, which is the entry point for the script. The first thing you'll need to define is to get the current worksheet context. This will allow you to work with the data in your Excel sheet.

2. Accessing the Used Range

To convert all text in the worksheet to uppercase, the next step is to retrieve the used range of the worksheet. Here’s how to do it:

[[See Video to Reveal this Text or Code Snippet]]

3. Converting Values in Place

Now that we have all the values in the used range, we can loop through each cell in the 2D array and convert their values to uppercase. Here’s the typical loop you would implement:

[[See Video to Reveal this Text or Code Snippet]]

In this loop:

values.length gives you the total number of rows

values[row].length gives the number of columns in that row

We convert the value to a string (if it isn’t already) and apply the toUpperCase() method

4. Updating the Used Range with Uppercase Values

After transforming the values, you'll need to set the modified values back into the used range:

[[See Video to Reveal this Text or Code Snippet]]

5. Full Office Script Example

Combining all the pieces together, here’s the complete script that converts all text in a specified worksheet to uppercase:

[[See Video to Reveal this Text or Code Snippet]]

Note on Function Parameters

It's important to note that your function currently expects a worksheet name in order to run. If you prefer to use it on the active worksheet, simply change the function signature to:

[[See Video to Reveal this Text or Code Snippet]]

This adjustment allows for easier execution when you don’t want to specify the worksheet name explicitly.

Conclusion

Transitioning from VBA to Office Scripts can seem daunting at first, but with the right understanding of the API and TypeScript syntax, it becomes a manageable task. By following the steps outlined in this guide, you can easily convert the entire content of your Excel sheet to uppercase. Now you can confidently automate your Excel tasks in Office 365!

Happy scripting!