How to automate Excel with reusable Office Scripts

Recently, Office Scripts gained the ability to run scripts using parameters. This means we can provide custom values to change the outcome of the Office Script.

This suddenly makes Office Scripts significantly more useful as we can create more reusable Office Scripts.

We are still hoping Office Scripts will gain functionality that enables creation of our own input forms. But until then (and I don’t know if that will ever happen), this gives us a good start.

To illustrate this capability, let’s look at a common example of unprotecting all sheets in an Excel workbook.

We will work step-by-step to show how to change a recorded code into a reusable script.

NOTE: To work along with this post you will need an Office Script compatible licence: Check your licence here: https://learn.microsoft.com/en-us/office/dev/scripts/testing/platform-limits?tabs=business

Table of Contents

Record a single action
Cleaning the recorded code
Avoiding errors
Apply to all sheets
Creating a reusable Office Script
Sharing the reusable script
Making the script easy to access
Conclusion

Watch the video

How to automate Excel with reusable Office Scripts | Excel Off The Grid

Watch on YouTube

Record a single action

Let’s start by creating the code to unprotect a single sheet. We can obtain this code using the script recorder.

Start with a sheet called “Reusable Office Scripts” protected by a password of “Password.”

Select any other sheet in the workbook. Then click Automate > Record Action to start the script recorder.

Undertake the following actions:

Select the protected sheet
Unprotect the worksheet using the password
Then click the stop icon in the Record Actions pane.

This records the actions and converts them into code.

When we view the code, it will be similar to the following:

function main(workbook: ExcelScript.Workbook) {
	let reusable_Office_Scripts = workbook.getWorksheet("Reusable Office Scripts");
	// Unprotect sheet Reusable Office Scripts
	reusable_Office_Scripts.getProtection().unprotect();
}

You will notice the recorded script did not capture the password.

If we run the script above on a password-protected sheet, it leads to an error.

The error message is:

Line 4: WorksheetProtection unprotect: The argument is invalid or missing or has an incorrect format.

It would be nice if it provided a meaningful message (more on that later).

So, let’s add the password into the code (don’t worry, we will make this a parameter later)

function main(workbook: ExcelScript.Workbook) {
	let reusable_Office_Scripts = workbook.getWorksheet("Reusable Office Scripts");
	// Unprotect sheet Reusable Office Scripts
	reusable_Office_Scripts.getProtection().unprotect("Password");
}

Now, assuming the password is correct, the Office Script removes the sheet protection.

Let’s save this script with the name Unprotect Sheet.

Check out this post for more examples of applying protection with Office Scripts: Office Scripts – Workbook & worksheet protection.

Cleaning the recorded code

The recorded code used the name of the worksheet in the the automatically created variable.

Let’s change this to a shorter variable name by changing reusable_Office_Scripts to ws.

function main(workbook: ExcelScript.Workbook) {
	let ws = workbook.getWorksheet("Reusable Office Scripts");
	// Unprotect sheet Reusable Office Scripts
	ws.getProtection().unprotect("Password");
}

Avoiding errors

As noted above when unprotecting sheets, we can receive errors if the password is incorrect.

We don’t want the default error messages; instead, we want to handle errors and provide meaningful messages.

For this, let’s add a try and catch statement.

function main(workbook: ExcelScript.Workbook) {
	
    let ws = workbook.getWorksheet("Reusable Office Scripts");
	
    //Use try and catch statement to capture errors
    try {

        //Unprotect sheet
        ws.getProtection().unprotect("Password");

    } catch (err) {

        //Record error message
        console.log (ws.getName() + " - Incorrect password")
		
    };
}

In the event of an incorrect password, the message [Sheet Name] – Incorrect password is written to the console.log

Apply to all sheets

Having successfully unprotected a single sheet, now let’s unprotect all sheets.

We use workbook.getWorksheets() to get the array of all sheets in the workbook. This is allocated to the variable of wsArr.

wsArr.length is the count of sheets in the workbook.
wsArr[i] refers to a sheet by its position in the array.

NOTE: Office Scripts is zero-based so the first worksheet is wsArr[0]

function main(workbook: ExcelScript.Workbook) {

    let wsArr = workbook.getWorksheets();

    //Loop through all worksheets in workbook 
    for (let i = 0; i < wsArr.length; i++) {

        //Use try and catch statement to capture errors
        try {

            //Unprotect sheet in the loop
            wsArr[i].getProtection().unprotect("Password")

        } catch (err) {

            //Record error message
            console.log(wsArr[i].getName() + " - Incorrect password")
			
        };

    };

}

The code above loops through all the sheets in the workbook and removes protection for each sheet where the password is “Password”.

Creating a reusable Office Script

Unless we use a single password for everything, this script is not particularly reusable. So, let’s add the password as a string parameter in the function declaration.

function main(workbook: ExcelScript.Workbook, password?: string) {

    let wsArr = workbook.getWorksheets();

    //Loop through all worksheets in workbook and apply to each 
    for (let i = 0; i < wsArr.length; i++) {

        //Use try and catch statement to capture errors
        try {

            //Unprotect sheet in the loop
            wsArr[i].getProtection().unprotect(password)

        } catch (err) {

            //Record error message
            console.log(wsArr.[i].getName() + " - Incorrect password")
			
        };

    };

}

In the function declaration at the start, the ? indicates it is an optional argument. Exclude the ? to make it a required argument.

Now when we run the script, a dialog box appears asking for us to input a password.

This password entered is applied to each sheet.

This is now a reusable script as we can run it on any workbook.

We can go even further. Let’s save the script on SharePoint.

Click on the script name, then on the > button icon.

Save the script on SharePoint in an easy-to-access location for your entire team to access.

The script icon changes. Hovering over the images shows this is a “Team Script”.

Now, anybody in your team can run the script.

Making the script easy to access

Any time we want to remove sheet protection we can run the script. If it’s one of the last 30 scripts we’ve run, it will be in the gallery and our recent scripts list.

However, if it’s not within the last 30 scripts, we need to click View more scripts at the bottom of the recent scripts list. Then, we must navigate to the SharePoint location to run the script. This is not particularly useful, as it can take a lot of clicks to find the script.

Instead, we can associate the script with a workbook.

Select the script, then select either:

Add in workbook – Associates the script with the workbook and creates a button on the worksheet.
Add in workbook (drop down) > Add without a button – associates the script with the workbook, but does not create a button.

If we have a button, we only need to click the button to run the script.

If we don’t have a button, we click This Workbook within the Code Editor pane. This shows all the scripts associated with the workbook. We can easily run the script from here.

Conclusion

In this post, we have seen how to adapt a recorded script into a reusable Office Script. Obviously, the exact code will differ depending on the purpose script.

Where the reusable script requires specific parameters to execute, we include these in the function declaration.

Sharing the script on SharePoint makes it accessible to your entire team. Also, associating the script with the workbook makes it easy to access (with or without a button).

Related Posts:

What next?

Discover how you can automate your work with our Excel courses and tools.