Office Scripts: A Smarter Way to Automate Excel Tasks

I recently learned more about Office Scripts, and… I kind of love them? I know everyone that has ever opened Excel is at least vaguely familiar with the concept of macros, but comparatively few people have ever written or used one. They’re very useful for automating repetitive tasks. I’ve written a few in my time, but VBA is definitely not sitting on my Mount Rushmore of languages, and I generally just find writing macros to be a pain. Hopefully, I’ve written my last Excel macro.

Office Scripts, on the other hand, are written in TypeScript–a superset of JavaScript, which I use every day. Additionally, Office Scripts run in the cloud, and can be shared across teams in a corporate environment or integrate with Power Automate. I’m pretty pro at Excel formulas, but if you’re finding yourself writing the same kinds of formula(s) ALL THE TIME, it may be time to consider using an Office Script instead.

A Business Case Study

Recently, a client approached me with a request to help streamline an extremely tedious manual task that they were doing every 2 weeks. They export a specific view from the CRM, scan through the data for duplicates, competitors, and records that are ineligible (either because they are outside the US or don’t meet the terms and conditions for fulfillment) before ultimately copying and reformatting the data into a specific template to send to a shipping partner.

They asked if I could do anything to make this process less painful. Honestly, if it were me, I probably would have automated the whole thing in near-real time with a human approval step. But for now, they just wanted help with automating some of the Excel work.

I ended up creating a ~250 line script (about 100 of that is just configuration options, and probably another 50 of whitespace, comments, error handling, and debugging statements) that does the following:

1. Re-names columns based on a configurable map so that the output matches the shipping partner’s template
2. Discards any duplicate rows, based on a configurable column to check for duplicates
3. Discards any competitors, based on a configurable set of keywords to check for in a configurable column to check
4. Normalizes State/Province values based on a configurable map, outputting only two-letter abbreviations, and discarding any non-US rows
5. Leaves the original data in place, and outputs the cleaned data onto a new sheet with a configurable name of “Cleaned Data”
6. For any discarded rows, outputs the full original row onto a new sheet with a configurable name of “Discarded Data” while also adding a “Reason” column for why the data was discarded

Sure, I could probably do all of that in about 5-10 minutes with Excel formulas if I had my State/Province normalizing formula saved somewhere, but this runs in seconds.

When to Use Office Scripts

Office Scripts are super useful when you’re doing very similar tasks over and over. Examples include formatting data for imports, normalizing values, or splitting names. If you often repeat formulas or data tasks, creating a script can save time.

Office Scripts can also be useful for data manipulation that is relatively easy to do in another programming language (Java, Python, etc.) but is somewhat painful to accomplish in Excel. One example I use somewhat frequently is finding Campaign Members related to Accounts related to Opportunities that match certain conditions and “flattening” the data for insert into a Campaign Influence table. This definitely can be done entirely with Excel formulas, but it’s more comfortable for me to write a little bit of code to do the same thing. And it’s FAST.

Here are some sample Office Scripts that you can use for inspiration:

• Normalizing State Values
• Splitting Full Name

I’ve also used an Office Script to parse UTM values from Marketo Form Submission Activity Exports. The export includes JSON, which is hard to parse in Excel but easy with TypeScript.

>>Related: Why Marketing Automation Platform Implementations Fail<<

What I Don’t Love/Kind Of Hate About Office Scripts

While Office Scripts can be incredibly useful, they’re not without their own flaws. My biggest gripe is that you can’t “undo” an Office Script. Once you run it, it’s done. Save a workbook copy and output cleaned data to a new sheet. That way, if something goes wrong, you still have the original data to work with.

Additionally, while you can define helper functions to serve as mini work units, your Office Scripts cannot reference other scripts. This means that all of your helper functions need to be defined within your script, and then you can reference them or comment them out in your main function. This can make your script slightly unwieldy if you have lots of different helper functions.

Tips for Office Scripts

Notes: In Office Scripts, the main function is the entry point for script execution in Excel. The script calls this function when it runs, and you must always define it in an Office Script.

Understanding the main Function

• Mandatory: Every Office Script requires a main function.
• Workbook Parameter: It takes a workbook: ExcelScript.Workbook parameter, which represents the active workbook instance.
• Execution Entry Point: The script starts running from the main function and executes its logic.

Example of a Simple main Function

function main(workbook: ExcelScript.Workbook) {
    let sheet = workbook.getActiveWorksheet();
    sheet.getRange("A1").setValue("Hello, Office Scripts!");
}

This script writes “Hello, Office Scripts!” into cell A1 of the active worksheet.

Using Helper Functions

Helper functions improve code readability and reusability. You can define additional functions outside main to perform specific tasks.

Example: Using a Helper Function

function main(workbook: ExcelScript.Workbook) {
    let sheet = workbook.getActiveWorksheet(); // Call helper function to write data
    writeGreeting(sheet, "A1", "Hello, Excel!");
}

// Helper function
function writeGreeting(sheet: ExcelScript.Worksheet, cell: string, message: string) {
    sheet.getRange(cell).setValue(message);
}

Key Benefits of Helper Functions

• ✅ Code Reusability: Helps avoid repetition by centralizing logic.
• ✅ Improved Readability: Keeps main focused while delegating tasks to smaller functions.
• ✅ Organized Structure: Modular design makes debugging and extending functionality easier.

You can define helper functions to format data, retrieve values, manipulate sheets, or perform other specialized tasks.