How to Use Office Scripts to Unprotect, Sort, and Reprotect an Excel Worksheet
Question details
The user needs to automate removing worksheet protection, sorting a protected data range by a selected column, and reapplying the same protection using Office Scripts in Excel for the web.
- Product
- Microsoft Excel for the web
- Device & OS
- Web browser (Microsoft 365)
- Scenario
- The user works with a protected worksheet in Excel for the web and wants to sort its data without manually removing and reapplying sheet protection every time.
- Observed behavior
- Sorting is blocked because the worksheet is protected. The goal is to remove protection, sort the selected column, and restore protection automatically.
Make sure you are signed in to Excel for the web with a Microsoft 365 work or school account that includes the Automate tab, and have the worksheet protection password or current protection settings ready before editing the script.
Create an Office Script to Unprotect, Sort, and Re-protect the Worksheet
Use this method when you want a repeatable script in Excel for the web that captures the current worksheet protection, sorts the target range, and restores protection automatically.
Office Scripts are stored in the workbook and run from the Automate tab in Excel for the web. The exact protection methods depend on the Office Scripts API and the Microsoft 365 subscription, so test the script on a copy of the workbook first.
Use a script pattern that stores the password or protection details, unprotects the worksheet, sorts the data range by the selected column, and protects the worksheet again with the same password or settings.
Open the workbook in Excel for the web, select the Automate tab, and click New Script to open the Code Editor.
In the Code Editor, create a script that stores the password, calls worksheet.getProtection().unprotect(), sorts the range with getRange().getSort().apply(), and then calls worksheet.getProtection().protect() to restore protection.
Update the worksheet name, data range, sort column index, and ascending or descending order to match the protected data matrix in your workbook.
Click Run in the Code Editor to sort the protected range and restore protection, then click Save script and give it a name such as Unprotect-Sort-Reprotect.
Use WPS Office as a Free, Lightweight Alternative for Excel-Compatible Automation
WPS Office does not support Office Scripts, but it is a free, lightweight Office alternative that opens Excel .xlsx and .xlsm files, supports worksheet protection and macros, and gives you a familiar ribbon interface for sorting and automation tasks.
- 1. Download WPS Office: Go to the official WPS Office website and download the free installer for your Windows, macOS, Android, or iOS device.
- 2. Open the Excel workbook: Launch WPS Spreadsheets and open the .xlsx or .xlsm workbook that contains the protected worksheet.
- 3. Work with sheet protection or macros: Use the Review tab to unprotect or protect sheets, or use the Developer tab to create a macro for sorting tasks.
Frequently Asked Questions
Does WPS Office support Office Scripts?
No. Office Scripts are a Microsoft Excel for the web feature and require a compatible Microsoft 365 work or school subscription. WPS Office provides its own macro and automation tools through the Developer tab.
Can I sort a protected worksheet in WPS Spreadsheets?
If worksheet protection blocks sorting, select Review > Unprotect Sheet and enter the password, sort the data, then select Review > Protect Sheet to protect it again.
Will my existing Excel macro or VBA code work in WPS Office?
WPS Office supports VBA-style macros in many cases and can open .xlsm files. For sheet protection and sorting tasks, test the macro in WPS Spreadsheets after opening the workbook.
Which Microsoft 365 accounts include the Automate tab for Office Scripts?
Office Scripts is typically available with Microsoft 365 work or school plans. Free Microsoft accounts may not include the Automate tab, so check your plan or use WPS Office as a free alternative.
