Office Scripts: Protect a Worksheet Automatically

Yes. In an Office Script, protect a worksheet with worksheet.getProtection().protect(); unlock any cells users should still be able to edit before applying protection. For example, a script can leave an input range such as B2:B20 editable while protecting the rest of a report sheet.

What worksheet protection does

Worksheet protection is a way to prevent routine edits to a sheet’s locked cells and to limit actions such as formatting or inserting rows. It is useful for templates, reports, and forms where people should enter values in designated places without accidentally changing formulas or layout. An Office Script can set the cell locking and protection options consistently, which is handy when preparing a workbook repeatedly.

Protection does not automatically make a workbook read-only. A protected sheet can still contain unlocked cells, and collaborators may still be able to view its contents, copy data, or change other parts of the workbook depending on workbook permissions. Sheet protection is primarily an editing safeguard, not a confidentiality feature or a substitute for controlling access to the file.

How Office Scripts applies protection

Excel stores a locked or unlocked setting with cells, but that setting only restricts editing after worksheet protection is turned on. In a new worksheet, cells are generally marked as locked by default. If the sheet is unprotected, that default does not prevent editing. The usual sequence is therefore to ensure the cells that should remain editable are unlocked, then protect the worksheet.

Office Scripts exposes this through the worksheet protection object. The worksheet’s getProtection() method returns that object, and its protect() method applies protection. A range’s protection setting is available through its format object. These APIs let a script specify both which cells are editable and which permitted actions users can take on the protected sheet.

Example: protect a report and leave an input range open

This example expects a worksheet named Report and treats B2:B20 as the user-entry cells. It locks the worksheet’s used range, unlocks the input range, and protects the sheet. Run it on an unprotected worksheet; if the sheet is already protected, unprotect it first. The example does not set a password.

function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getWorksheet("Report");
  const protection = sheet.getProtection();

  if (protection.getProtected()) {
    throw new Error("Unprotect the worksheet before running this script.");
  }

  const usedRange = sheet.getUsedRange();
  if (!usedRange) {
    return;
  }

  // Lock cells containing the report, then open the input cells.
  usedRange.getFormat().getProtection().setLocked(true);
  sheet.getRange("B2:B20").getFormat().getProtection().setLocked(false);

  // Allow selection of unlocked cells while protecting the sheet.
  protection.protect({ selectionMode: "unlocked" });
}

The script uses getUsedRange() so it does not need to set the locked property across every cell in Excel’s full grid. If the entry cells are outside the used range because they are blank, the script still unlocks B2:B20 explicitly. Check that the worksheet name and input addresses match the workbook, and test the result with a normal user workflow.

Choosing which actions to allow

The protection options are useful when users need more than simple data entry. For example, a protected sheet might allow sorting or filtering while still blocking edits to formulas. The options belong to the protection call, so decide which actions are genuinely needed before enabling them. A permissive setting can undermine the purpose of protecting the sheet.

selectionMode controls what users can select: "normal" allows normal selection, while "unlocked" restricts selection to unlocked cells. "none" prevents selection.
Options such as allowFormatCells, allowInsertRows, allowDeleteColumns, and allowSort govern particular actions on the protected worksheet.
allowAutoFilter can be relevant when a sheet already has a filter that users need to operate. Set only the options that fit the workbook’s actual use.

For instance, if a report has unlocked input cells and a filter users need to operate, the protection call could include allowAutoFilter: true alongside selectionMode: "unlocked". Try the chosen options in the Excel version and workbook configuration your audience uses: permissions and how a feature behaves can depend on the worksheet’s setup. Do not assume that allowing one operation automatically allows related operations.

Adding a password and removing protection

A password is optional. To apply one, provide it as the second argument to protect(), for example protection.protect({ selectionMode: "unlocked" }, password), where password is a string variable in the script. To remove protection, use sheet.getProtection().unprotect() for a sheet without a password, or pass the matching password to unprotect(password).

Do not treat a password written directly into an Office Script as a secret: people who can view or edit the script may be able to see it. Avoid sharing a script containing a sensitive password, and consider how your organization stores and distributes scripts. Worksheet protection is not encryption, and it does not stop someone with sufficient access from making a separate copy or using other means to work with the file. Keep important files in a location with appropriate sharing permissions.

A script cannot recover a forgotten worksheet password. If you do not know the password, ask the workbook owner or administrator about the approved recovery process rather than assuming an Office Script can bypass it. Also distinguish a worksheet password from a password used to encrypt or restrict access to the workbook file: these are different protections.

Running and maintaining the script

In Excel for Microsoft 365, Office Scripts are commonly accessed from the Automate tab, where you can create or open a script in the code editor and run it for the current workbook. Availability depends on the Excel platform, account, subscription, and organization settings; an administrator may disable Office Scripts. If the Automate tab or script editor is missing, check with your Microsoft 365 administrator or consult the support information for your Excel environment.

Keep scripts understandable and tied to a specific workbook layout. A script that refers to a fixed name such as Report will fail if someone renames that sheet. A changed input range can also leave the wrong cells editable. Before reusing a protection script on a modified template, verify the sheet name, used range, input addresses, and required permissions. If a script is part of an automated workflow, also consider whether it may run more than once and how it should behave when protection is already on.

Common problems and how to diagnose them

The script fails while changing locked settings. The worksheet may already be protected. Unprotect it first using the correct password, then run the configuration steps. The example deliberately stops rather than silently changing an already-protected sheet.
Users cannot edit the intended cells. Confirm that the exact cells were unlocked before protection was applied. Check for merged cells, a mistaken range address, or a different sheet name. Also confirm that users are opening the workbook they expect.
Users can still change something unexpected. Review which cells are unlocked and which permissions were enabled. Protection applies to worksheet actions, so a range accidentally left unlocked may remain editable.
Filtering or sorting does not work as expected. Check that the relevant action is allowed and that the sheet’s filter or data layout is compatible with the operation. Test the protected sheet using the same steps users will take.
The Automate feature is unavailable. Office Scripts availability can depend on platform and organization policy. This is different from a problem in the protection code itself.

Office Scripts versus VBA and manual protection

Manual worksheet protection is often simplest for a one-time change: in Excel for Windows, use Review > Protect Sheet, choose the actions to permit, and confirm. Before protecting manually, select intended input cells, open Format Cells, and clear the Locked setting on the Protection tab. The ribbon path and dialogs can differ across Excel platforms, so check the controls in the version you are using.

Office Scripts are useful when the same preparation needs to be repeated, or when the process is part of an Excel automation workflow. They use TypeScript and the Office Scripts API; they are not VBA macros. VBA is a separate automation option with different capabilities and security considerations. Choose based on where the workbook runs, what your organization allows, and whether the task needs to be repeatable. For a single protected template, the Excel interface may be easier to maintain than code.

Frequently Asked Questions

Can an Office Script protect every worksheet in a workbook?
Yes. A script can loop through worksheets and call each worksheet’s protection API. Decide whether every sheet should receive identical settings, and handle already-protected sheets and passwords deliberately rather than assuming their current state.

Can users edit unlocked cells on a protected sheet?
Yes. Unlock the intended cells before protection is applied. The protection option selectionMode: "unlocked" can also limit selection to those editable cells.

Can I protect a worksheet without a password?
Yes. Call protect() with the protection options and omit the password argument. This can prevent casual changes, but it should not be treated as strong security or as a way to hide data.

Can an Office Script unprotect a sheet if I forgot its password?
No. The script needs the correct password to remove password-based worksheet protection. Ask the workbook owner or administrator for help with the legitimate recovery process.

Does protecting a worksheet stop people from opening the workbook?
No. Worksheet protection limits certain edits to a sheet; it does not control who can open the file. Use the workbook’s sharing and access controls for that purpose.

Before you rely on the protection

Check the finished worksheet as a user would: try entering data in the intended input cells, attempt an edit in a formula cell, and test any allowed sorting or filtering. Confirm that the correct sheet is protected and that no unintended cells are unlocked. For a template used by a team, document the editable ranges and keep the script with the workbook’s normal maintenance process. This makes the protection useful as an error-prevention measure without mistaking it for file-level security.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.