As Excel users, we often find ourselves protecting and unprotecting worksheets and workbooks. While Excel’s protection is not perfect, it does prevent others or even us from accidentally making changes. It can be as simple as one keystroke that turns our worksheet perfection into an error-filled disaster.
Unfortunately, protecting and unprotecting is an enormous time suck. Each of the sheets has to be protected one at a time, plus we have to type in the password correctly… twice. If we are only protecting a single worksheet, that’s OK. But if we are protecting more than that, we need to turn to a faster solution.
What’s even worse for the Excel online user is that the protection settings are not yet available in the ribbon (this is correct at the time of writing, but hopefully will change soon). Which means we have to flip back and forth between desktop Excel and online Excel. The good news is that since Office Scripts are available in Excel online, we can apply protection to lots of sheets with just a few clicks.
If you have written VBA Macros before, then much of what you know will apply directly to Office Scripts, but with a slightly different syntax. The good news is that this post can help you transition to Office Scripts even faster.
It is not essential to read the entire post from start to finish, but doing so will help you understand more about Office Scripts.
Using this post
This post is intended to be a reference guide to help you build your own Office Scripts. By using and adapting the code, you should be able to achieve most of what you need for protecting and unprotecting without needing to look elsewhere.
Most of the examples have been structured in a standard way. This consistency should help with adapting the code for your own needs. The worksheet examples are either applied to a worksheet called “Sheet1”, or to all the items within the worksheets collection. There are many ways to refer to worksheets, so check out my Office Scripts guide to worksheets for further information for adapting this part of the code.
Some of the examples do not provide visual output. In these circumstances, the console log is used to output the result. You would be unlikely to use this approach in the real world, but it is useful to demonstrate the concepts.
getProtection method
Before we get into the examples, we must first look at where the worksheet, range, and workbook protection settings exist within Excel’s object model.
You will notice that every example which protects or unprotects the worksheet, range, or workbook uses the getProtection as an intermediary method. For worksheets, getProtection is always used between the worksheet object and the protect or unprotect method (see the bold text below).
let ws = workbook.getWorksheet("Sheet1"); ws.getProtection().protect();
let wsArr = workbook.getWorksheets(); for (let i = 0; i < wsArr.length ; i++) { wsArr[i].getProtection().protect(); }
Purpose: Returns the sheet protection object for a worksheet.
Syntax: ExcelScript.Worksheet.getProtection();
Parameters: None
Returns: ExcelScript.WorksheetProtection
The same is also true of the workbook, where getProtection is used between the workbook object and the protect or unprotect action (see the bold text below).
workbook.getProtection().protect();
Purpose: Returns the protection object for a workbook.
Syntax: ExcelScript.Workbook.getProtection();
Parameters: None
Returns: ExcelScript.WorkbookProtection
Finally, the same can be said when locking cells and hiding formulas. getProtection is used between the cell format and the action (see the bold text below).
ws.getRange("A1").getFormat().getProtection().setLocked(false);
Purpose: Returns the format protection object for a range.
Syntax: ExcelScript.RangeFormat.getProtection();
Parameters: None
Returns: ExcelScript.FormatProtection
While we don’t really use getProtection in its own right, it is a key part of the object model that we must include.
Worksheet protection
In this section, we are covering a few key actions:
- Checking if a worksheet is protected
- Checking which worksheet protection options have been applied
- Protecting a worksheet – with and without passwords, and applying to multiple worksheets at once
- Unprotecting a worksheet – with and without passwords, and applying to multiple worksheets at once
Check if a worksheet is protected
To confirm if a worksheet is already protected, we use the getProtected method. It returns true if it is protected or false if it is not.
The example code below uses an if statement to check whether getProtected returns true. If it is, the text “Worksheet is protected” appears in the console log; otherwise, the message “Worksheet not protected” will be displayed.
//Assign the Sheet1 worksheet to the ws variable let ws = workbook.getWorksheet("Sheet1"); //Check if worksheet is protected if (ws.getProtection().getProtected() == true) { //Action if protected console.log("Worksheet is protected"); } else { //Action if not protected console.log("Worksheet not protected"); };
This is an important check because if a worksheet is already protected, trying to protect it again will result in an error. Running this check helps to avoid this error.
Purpose: Specifies if the worksheet is protected.
Syntax: ExcelScript.WorksheetProtection.getProtected();
Parameters: None
Returns: boolean (true/false)
Which protection options have been applied?
Having established whether a worksheet is protected or not. We may also need to understand what options have been applied. For the worksheet object, there are 14 protection options. These options coincide with those available when protecting manually in Excel desktop.
The code below displays the status of each protection option within the console log.
//Assign the Sheet1 worksheet to the ws variable let ws = workbook.getWorksheet("Sheet1"); //Display the status of each protection option in the console log console.log(ws.getProtection().getOptions().allowAutoFilter); console.log(ws.getProtection().getOptions().allowDeleteColumns); console.log(ws.getProtection().getOptions().allowDeleteRows); console.log(ws.getProtection().getOptions().allowEditObjects); console.log(ws.getProtection().getOptions().allowEditScenarios); console.log(ws.getProtection().getOptions().allowFormatCells); console.log(ws.getProtection().getOptions().allowFormatColumns); console.log(ws.getProtection().getOptions().allowFormatRows); console.log(ws.getProtection().getOptions().allowInsertColumns); console.log(ws.getProtection().getOptions().allowInsertHyperlinks); console.log(ws.getProtection().getOptions().allowInsertRows); console.log(ws.getProtection().getOptions().allowPivotTables); console.log(ws.getProtection().getOptions().allowSort); console.log(ws.getProtection().getOptions().selectionMode);
We won’t go through these individually as they apply the same settings as the standard Excel desktop interface. But it is worth noting that all the options have a true or false value, except for selectionMode, which has none, normal or unlocked values.
Purpose: Returns the protection options for the worksheet.
Syntax: ExcelScript.WorksheetProtection.getOptions();
Parameters: None
Returns: ExcelScript.WorksheetProtectionOptions
Protect a worksheet
To protect a worksheet, we used the protect method. This gives us the ability to apply any of the options noted in the section above, along with defining a password.
One of the key points to note about the protect method is that it cannot be applied to an already protected worksheet. Doing so will trigger an error: WorksheetProtection protect: This operation is not permitted for the current object.
Therefore, before protecting a sheet, we should either:
- check it is unprotected
- apply the unprotect method before protecting.
Purpose: Protects a worksheet. Fails if the worksheet has already been protected.
Syntax: ExcelScript.WorksheetProtection.protect(options?, password?);
Parameters:
- options: ExcelScript.WorksheetProtectionOptions – Optional sheet protection settings.
- password: string – optional parameter to set a password
Returns: void
Protect a worksheet without a password
The following example demonstrates how to use the protect method without any parameters.
//Assign the Sheet1 worksheet to the ws variable let ws = workbook.getWorksheet("Sheet1"); //Protect worksheet without password ws.getProtection().protect();
Protect a worksheet with a password
The following code builds on the example above by adding a password. The password parameter is the second parameter within the method; therefore, we have used two curly brackets ( { } ) in place of the first parameter. The use of the two curly brackets avoids the need to apply the protection options.
//Assign the Sheet1 worksheet to the ws variable let ws = workbook.getWorksheet("Sheet1"); //Protect worksheet with a password ws.getProtection().protect({},"myPassword");
Protect all worksheets – Method #1 forEach loop
Applying protection one sheet at a time can be time-consuming. The following code uses a forEach loop to apply protection to every item in the worksheet collection.
//Assign the worksheet collection to the wsArr variable let wsArr = workbook.getWorksheets(); //Protect all worksheets in workbook - Method #1 wsArr.forEach(ws => { ws.getProtection().protect(); });
Protect all worksheets – Method #2 for loop
An alternative method to the forEach loop is the for loop, as shown by the example below.
//Assign the worksheet collection to the wsArr variable let wsArr = workbook.getWorksheets(); //Protect all worksheets in workbook - Method #2 for (let i = 0; i < wsArr.length ; i++) { wsArr[i].getProtection().protect(); };
As the for loop consists of three parts, which are separated by semi-colons, we have a lot of control over the loop
- i = 0 : The first parameter is the initializer. i is used as a number variable.
- i < wsArr.length : The loop is executed while this condition continues to be true.
- i++ : The final argument increments the i variable at specific intervals. i++ adds 1 to i for each iteration.
Because we can control each of the three parts, the for loop gives us a lot of flexibility as we do not need to loop through every item in the collection.
Applying protection options
We saw the protection options above when we looked at the getOptions method. Those options are applied using the protect method. This is demonstrated in the code below.
//Assign the Sheet1 worksheet to the ws variable let ws = workbook.getWorksheet("Sheet1"); //Set individual options when protecting ws.getProtection().protect({ allowAutoFilter: false, allowDeleteRows: false, allowDeleteColumns: false, allowEditObjects: false, allowEditScenarios: false, allowFormatCells: false, allowFormatColumns: false, allowFormatRows: false, allowInsertColumns: false, allowInsertHyperlinks: false, allowInsertRows: false, allowPivotTables: false, allowSort: false, selectionMode: ExcelScript.ProtectionSelectionMode.none });
The last option in the code above, ExcelScript.ProtectionSelectionMode, can have three possible values (none, normal and unlocked). The code for all 3 options are shown below.
selectionMode: ExcelScript.ProtectionSelectionMode.none selectionMode: ExcelScript.ProtectionSelectionMode.normal selectionMode: ExcelScript.ProtectionSelectionMode.unlocked
- none: No cells can be selected
- normal: All cells can be selected
- unlocked: Only unlocked cells can be selected
Unprotect a worksheet
To unprotect a worksheet, we use the unprotect method. If a password was used to protect the sheet, a password is also required to unprotect it.
If the wrong password is used, it will result in an error which states: WorksheetProtection unprotect: The argument is invalid or missing or has an incorrect format.
Purpose: Unprotects a worksheet.
Syntax: ExcelScript.WorksheetProtection.unprotect(password?);
Parameters: password: string – optional parameter for the sheet protection password
Returns: void
Unprotect a worksheet without a password
The following example demonstrates how to use the unprotect method without a password.
//Assign the Sheet1 worksheet to the ws variable let ws = workbook.getWorksheet("Sheet1"); //Unprotect worksheet without password ws.getProtection().unprotect();
Unprotect a worksheet with a password
The code below builds on the example above by adding a password to unprotect the worksheet.
//Assign the Sheet1 worksheet to the ws variable let ws = workbook.getWorksheet("Sheet1"); //Unprotect worksheet with password ws.getProtection().unprotect("myPassword");
Catching errors when incorrect password entered
As noted above, providing an incorrect password results in an error within the console log. It is much better to catch the error to manage the user journey.
In the example below, an incorrect password has been used. The error is caught, and then a message is displayed in the console log.
//Assign the Sheet1 worksheet to the ws variable let ws = workbook.getWorksheet("Sheet1"); //Try unprotecting the worksheet with a password try { ws.getProtection().unprotect("wrongPassword") //Catch the error for an incorrect password } catch (err) { //Display a message in the console log console.log ("Incorrect password"); };
Unprotect all worksheets – Method #1 forEach loop
The following code uses a forEach loop to unprotect every item in the worksheet collection.
//Assign the worksheet collection to the wsArr variable let wsArr = workbook.getWorksheets(); //Unprotect all worksheets in workbook - Method #1 wsArr.forEach(ws => { ws.getProtection().unprotect(); });
Unprotect all worksheets – Method #2 for loop
Another option to unprotect all worksheets is to use the for loop, as demonstrated below.
//Assign the worksheet collection to the wsArr variable let wsArr = workbook.getWorksheets(); //Unprotect all worksheets in workbook - Method #2 for (let i = 0; i < wsArr.length ; i++) { wsArr[i].getProtection().unprotect(); };
Cell locked settings
When a sheet is protected with the ProtectionSelectionMode set to normal or unlocked, some cells can still be edited. To obtain the current setting for a specific range, we use the getLocked method, and to change the setting, we use the setLocked method.
Return the lock setting
The getLocked method provides a true or false result to indicate whether a range has the lock setting applied. The setting is part of a range’s format.
The code below checks the lock setting of cell A1 and returns true or false into the console log accordingly.
//Assign the Sheet1 worksheet to the ws variable let ws = workbook.getWorksheet("Sheet1"); //Check a ranges lock setting console.log(ws.getRange("A1").getFormat().getProtection().getLocked());
When the worksheet is unprotected, the lock setting has no impact; it only comes into force when protection is applied.
Purpose: Confirms if Excel locks the cells in the object. A null value indicates that the entire range doesn’t have a uniform lock setting.
Syntax: ExcelScript.FormatProtection.getLocked();
Parameters: None
Returns: boolean (true/false)
Change the lock setting
To change the lock setting, we use the setLocked method.
The example below uses setLocked to change cell A1 to be locked.
//Assign the Sheet1 worksheet to the ws variable let ws = workbook.getWorksheet("Sheet1"); //Set the lock setting of a range ws.getRange("A1").getFormat().getProtection().setLocked(true);
If we want to change the lock settings for an entire worksheet, we can achieve that by not providing any range address into the getRange method. For example, the code below sets the entire worksheet to not be locked.
//Assign the Sheet1 worksheet to the ws variable let ws = workbook.getWorksheet("Sheet1"); //Set the lock setting for entire range of worksheet ws.getRange().getFormat().getProtection().setLocked(false);
Purpose: Specifies if Excel locks the cells in the object.
Syntax: ExcelScript.FormatProtection.setLocked(locked);
Parameters: locked: boolean (true/false)
Returns: void
Hiding formulas
Personally, I don’t consider hiding a range’s calculation to be part of its protection. However, Excel does; therefore, I have provided this section for completeness.
If a range is formatted with hidden, the calculation of that range cannot be seen when protection is applied to the worksheet. To obtain the current setting, we use the getFormulaHidden method, and to change the setting, we use the setFormulaHidden method.
Return the formula hidden setting
The getFormulaHidden method provides a true or false result to indicate if a range has the hidden setting applied. The setting is part of a range’s format.
The code below checks the hidden setting of cell A1 and returns true or false into the console log accordingly.
//Assign the Sheet1 worksheet to the ws variable let ws = workbook.getWorksheet("Sheet1"); //Check formula hidden setting for a range console.log(ws.getRange("A1").getFormat().getProtection().getFormulaHidden());
When the worksheet is unprotected, the formula hidden setting has no impact; it only comes into force when worksheet protection is applied.
Purpose: Confirms if Excel hides the formula for the cells in the range. A null value indicates that the entire range doesn’t have a uniform formula hidden setting.
Syntax: ExcelScript.FormatProtection.getFormulaHidden();
Parameters: None
Returns: boolean (true/false)
Change the formula hidden setting
To change the formula hidden setting, we use the setFormulaHidden method.
The example below uses setFormulaHidden to change cell A1 to have formulas hidden.
//Assign the Sheet1 worksheet to the ws variable let ws = workbook.getWorksheet("Sheet1"); //Set the formula hidden setting of a range ws.getRange("A1").getFormat().getProtection().setFormulaHidden(true);
If we want to change the hidden settings for an entire worksheet, we can achieve that by not providing any range address into the getRange method. For example, the code below sets the entire worksheet to have the hidden setting.
//Assign the Sheet1 worksheet to the ws variable let ws = workbook.getWorksheet("Sheet1"); //Set the formula hidden setting for entire worksheet ws.getRange().getFormat().getProtection().setFormulaHidden(true);
Purpose: Specifies if Excel hides the formula for the cells in the range.
Syntax: ExcelScript.FormatProtection.setFormulaHidden(formulaHidden);
Parameters: formulaHidden: boolean (true/false)
Returns: void
Workbook protection
In this section, we are covering a few key actions:
- Check if a workbook is protected
- Protecting a workbook – with and without passwords
- Unprotecting a workbook – with and without passwords
In Excel online, the top-level object is the workbook. The workbook variable is declared as part of the main function. Therefore, in the examples, I have not re-declared a workbook variable. But if the code is used outside of the main function then the workbook variable may need to be re-declared.
Check if a workbook is protected
We use the getProtected method to confirm if a workbook already has protection applied. A true result means it is protected; a false result means it is not. An error occurs from trying to protect an already protected workbook.
The example code below uses an if statement to check whether getProtected returns true. If it is true, the text “Workbook is protected” appears in the console log; otherwise, the message “Workbook not protected” will be displayed.
//Check if workbook is protected if (workbook.getProtection().getProtected() == true) { //Action if protected console.log("Workbook is protected"); } else { //Action if not protected console.log("Workbook not protected"); };
Purpose: Specifies if the workbook is protected.
Syntax: ExcelScript.WorkbookProtection.getProtected();
Parameters: None
Returns: boolean (true/false)
Protect a workbook
Due to the lack of additional options, protecting workbooks is similar, but easier than it is worksheets.
Just like for worksheets, protection cannot be applied to a workbook that is already protected. Doing so will trigger an error: WorkbookProtection protect: This operation is not permitted for the current object.
Therefore, before applying protection to a workbook, we should either:
- check the workbook is unprotected
- apply the unprotect method before protecting
Purpose: Protects a workbook. Fails if the workbook has already been protected.
Syntax: ExcelScript.WorkbookProtection.protect(password?);
Parameters: password: string – optional parameter to set a password
Returns: void
Protect a workbook without a password
The following example shows how to protect a workbook without a password.
//Protect workbook without password
workbook.getProtection().protect();
Protect a workbook with a password
The code below builds on the above by adding a password to the protect method.
//Protect workbook with password workbook.getProtection().protect("myPassword");
Unprotect a workbook
To unprotect a workbook, we use the unprotect method. If a password was used to protect, then a password is also required to unprotect.
If the wrong password is used, it returns an error into the console log: WorkbookProtection unprotect: The argument is invalid or missing or has an incorrect format.
Purpose: Unprotects a workbook.
Syntax: ExcelScript.WorkbookProtection.unprotect(password?);
Parameters: password: string – optional parameter for the workbook protection password
Returns: void
Unprotect a workbook without a password
The following example uses the unprotect method without the password parameter.
//Unprotect workbook without a password
workbook.getProtection().unprotect();
Unprotect a workbook with a password
The code below demonstrates how to unprotect a workbook with a password.
//Unprotect workbook with password workbook.getProtection().unprotect("myPassword");
Catching errors when incorrect password entered
As noted above, providing an incorrect password results in an error. Therefore, it is preferable to catch the error and control what happens next.
In the example below, an incorrect password is supplied. The error is caught, and the text “Incorrect password” is displayed in the console.log.
//Catch error from using incorrect workbook password try { workbook.getProtection().unprotect("wrongPassword") } catch (err) { //Action if incorrect password console.log ("Incorrect password"); };
Discover how you can automate your work with our Excel courses and tools.
Excel Academy
The complete program for saving time by automating Excel.
Excel Automation Secrets
Discover the 7-step framework for automating Excel.
Office Scripts: Automate Excel Everywhere
Start using Office Scripts and Power Automate to automate Excel in new ways.
Tried the Workbook protect method with password. Didn’t work.
Isn’t workbook.getProtection().protect(“password”); enough?
To protect a workbook, yes that is the correct code. This is covered in the “Protect a workbook with a password” section.
The protect workbook is also not working for me is only protecting the structure but need to apply a password and when use open file it request the password to access file, it only protect the excel structure but file can still be open with out a password request. Anyone has the solution or the code to be use
Office Scripts doesn’t give access to protect access and encrypt the file in the API, so you can’t do it with Office Scripts.
Is there maybe some workaround to encypt the file? I’m loving the Office Scripts in Power Automate cloud, but I don´t want to migrate a rather simple solution to Power Automate Desktop just to encrypt a file with 3 lines of vba script.
Office Scripts can’t change the workbook object, only what’s in the workbook. So there are no options for encryption.