How to write your first Office Script for Excel

In a previous post we delved into the distinctions between VBA (Visual Basic for Applications) and Office Scripts for Excel:

To summarize, although many Excel automation tasks can be accomplished using either platform, Office Scripts provide a modern, cloud-based solution for automating Excel Online. This enhances ease of sharing and accessibility across various devices, a notable advantage over VBA.

Now, let’s examine a straightforward example. You’ll notice that much of what we’re about to discuss could be achieved using VBA. However, the mechanisms for storage and sharing are somewhat different. Additionally, we have yet to explore the more advanced capabilities of Office Scripts, such as integrating with APIs.

Getting started with sample scripts

For this post, I’m assuming you have Excel for Office 365 and can see the Automate tab in the ribbon of your Excel application. If that’s not the case, consider checking out this post from Microsoft, which details some issues related to organizational access to Office Scripts.

Once you’re in the Automate tab, head to “All Scripts,” then “Sample” to access an expanded menu and review the built-in scripts provided.

One nice first aspect of Office Scripts is its provision of basic scripts for experimentation. These include functionalities such as unhiding all rows and columns, freezing panes, counting empty cells, and more.

To explore these features, select the script titled “Freeze Selection.” Then, click on the three dots adjacent to this sample and choose “Make a Copy.” This action underscores the cloud-first nature of the application, as it directly navigates you to your OneDrive within an Office Scripts folder, where you can save a copy of the script. I will name my new script “Freeze and Format Selection.”

While it’s not necessary to save a sample script for its use, I wanted to do so because we plan to modify it. But first, let’s explore the script as it currently stands and experiment with it. Please select a range of data and then click “Run” to observe the macro in action.

The provided raw script primarily functions to freeze selected panes, though it currently lacks formatting capabilities. We will delve into potential enhancements for this script.

This script is presented as an illustrative example. The subsequent instructions, originally provided by ChatGPT, can be pasted into your script for now. Familiarity with TypeScript, the language utilized by Office Script (a superset of JavaScript), is not required. If you are adept with ChatGPT, achieving the desired outcomes with Office Scripts is typically feasible.

That said, gaining an understanding of the fundamental principles and syntax of TypeScript for Office Scripts is advisable. This guide from Microsoft offers a helpful starting point.

Okay, now proceed to run the script. You will notice that the cells are colored as well! We successfully managed to enhance the sample script with a bit of assistance from ChatGPT.

Creating your own script

The previous example was based on a sample script. However, it’s likely that you’ll need to create your own script, as the samples may not meet your specific requirements. How will this process unfold?

Get started by pasting this dataset into your workbook:

Let’s proceed to create our own Office Script to generate a small report using this dataset. This process will closely resemble the macro recorder functionality in VBA. Head to Automate on the ribbon, then select Record Actions:

To automate the production of this report, I will:

1. Convert the source data into a table.
2. Apply conditional formatting to the Sales column: highlight values greater than 500 in green.
3. Add a total row to the Sales column.
4. Format the Sales column as currency.

You can create your own script by following these steps or use the recording provided below:

When you are done click “Stop” under the Record Actions menu. You should have a script that closely resembles the one below:

You will then have the option to save your script, which could be named something like Format Sales Report. While the script recorder functions in a manner similar to the VBA macro recorder, I find it to be more forgiving. It does not necessarily record every single cell or range selection that might occur accidentally during the process. Additionally, it provides a clear, running log of the steps being applied, lending a more modern feel to the experience.

Improving the script

However, similar to the VBA macro recorder, the script recorder also has significant deficiencies when attempting to build a scalable solution. For instance, what if you later realize you omitted a step? Unless you’re proficient in the language, it can be challenging to know how to modify the script. Moreover, repeatedly executing all the point-and-click steps correctly until you achieve the desired outcome can be exhausting. For example, it appears I forgot to name the resulting table sales.

Or, consider a scenario where the dataset starts in a different row than cell A1 or if it contains more than five rows of data. In such cases, the generated script can become unreliable. It’s somewhat naive to assume that the circumstances under which you might invoke this script again will remain identical to your current setup.

Therefore, our goal now is to revise this script to make it more adaptable. Again, I suspect that as an Excel user, you might not be familiar with the TypeScript being used in this script. Fortunately, having a basic understanding of programming through experience with VBA, Python, etc., can assist you in leveraging ChatGPT to obtain a significantly improved script.

Please replace your current script with the following code and ensure you save the changes.

Now, you should possess a more dynamic solution for generating this formatted sales report. Try appending a few rows to the dataset and relocating it within the workbook. It should function seamlessly without issues.

Sharing and saving your script

Once you’ve finished modifying your script, please proceed back to the Script Details to add a description of the script, modify its sharing settings, and so on. By default, the script is saved to your OneDrive, but you have the option to relocate it, including to a SharePoint site, among other locations.

Office Scripts in Excel are stored in the workbook’s script library by default. This setup ensures that users with access to the workbook can execute or alter the scripts, contingent upon their permissions. To alter the sharing or saving preferences, you should adjust the workbook’s sharing settings on OneDrive or SharePoint. This action directly influences who can access the scripts contained within the workbook.

You’ve written an Office Script… now what?

Congratulations on creating your first Office Script for Excel! This accomplishment marks a significant milestone in automating tasks and boosting your productivity with Excel online.

Currently, there are limited comprehensive resources available for Office Scripts and Excel. However, I strongly recommend exploring the materials available on Microsoft Learn. Should you have any preferred additional resources, feel free to share them in the comments.

A noteworthy aspect for beginners in Office Scripts is understanding their integration with Power Automate. Power Automate is a service that enables the creation of automated workflows between applications and services, facilitating task automation without the need for coding. By integrating Excel Office Scripts with Power Automate, you can set up flows that activate these scripts in Excel Online. This integration enables automated tasks such as data processing or report generation in Excel. I look forward to sharing more insights on Power Automate, Office Scripts, and related topics in future posts.

In the meantime, do not hesitate to reach out with any questions or feedback regarding Office Scripts. I’m eager to hear about your experiences and where you see the greatest potential for their application in your work. Let’s discuss in the comments.