Microsoft gets busy collating realistic examples for Office Scripts. Tinkering with Office Scripts yet? Check these out!

Office Scripts examples

Microsoft has made some open source examples of Office Scripts code available on Microsoft Docs.

This looks like a great resource to get people started with a variety of examples, accessible from a single repository.

You can find these examples at Office Scripts samples – Office Scripts | Microsoft Docs.

What is Office Scripts?

If you are already familiar with using Excel for Web and you know about Office Scripts, you can skip this section.

If you’re not familiar with the concept, that’s okay – many Excel users have not yet encountered Office Scripts.

In a nutshell, Office Scripts is the scripting language used to automate Excel for Web. Much like you would use macros and VBA to automate Excel for Windows Desktop, you would use Office Scripts to automate Excel for Web.

Office Scripts is intended to be more suited to web-delivered SaaS apps (such as Excel for Web), and is based on TypeScript/JavaScript/ECMAScript which is more aligned to web-related interaction and connectivity.

The desktop version of Excel is not going anywhere fast and neither is VBA, so there is no reason to panic about that; think of Office Scripts as the automation method for Excel for Web, a younger cousin of Excel.

Excel for Web is still in its infancy in terms of functionality compared to normal Excel, so many users are leaving it alone until it is closer to their existing tools in terms of capability.

Meanwhile, a lot of effort is going into both Excel for Web and Office Scripts to add more functionality and features, which is encouraging to see and will prove very useful down the track. It seems that much of the focus is consistent with modern ways of working and collaborating, which is quite exiting and fits well with the current cloud-centric focus and direction.

We are looking forward to both Excel for Web and Office Scripts coming of age in due course!

Meanwhile we continue to stick to Excel for Windows Desktop and VBA, which is where all our tips and updates continue to focus. This is unlikely to change anytime soon.

At the time of writing, Office Scripts is in public preview.

What Office Scripts examples are included?

The examples now include:

• Basics
  • Scripting basics
  • Add comments in Excel
  • Copy multiple Excel tables into a single table
• Beyond the basics
  • Count blank rows in a specific sheet or in all sheets
  • Cross-reference and format an Excel file
  • Email chart and table images
  • External fetch calls
  • Filter Excel table and get visible range
  • Manage calculation mode in Excel
  • Move rows across tables
  • Output Excel data as JSON
  • Remove hyperlinks from each cell in an Excel worksheet
  • Run a script on all Excel files in a folder
  • Send a Teams meeting from Excel data
• Scenarios
  • Analyze web downloads
  • Fetch and graph water-level data from NOAA
  • Grade calculator
  • Task reminders
• Community contributions
  • Seasons greetings animation

Observations

These examples represent a good cross-section of use case examples, written by different authors.

They are a bit of a mix (different factoring styles, some have more error-trapping than others, etc) but provide a good demonstration of how to approach each task.

There’s even an asynchronous example scenario that uses promises (noting that Office Scripts requirements for asynchronous code have changed since an earlier version; familiarity vs simplicity is not always easy)!

Some of the basic use concepts around FETCH requests and parsing JSON will be recognizable to developers who are familiar with web workflows and JavaScript. On the other hand, some automators whose experience is more purely with VBA and spreadsheet models may find that more of the content is relatively unfamiliar.

It is encouraging to see more examples referenced and distributed. Hopefully this will only grow over time along with community contributions through the official channels.