How to Password-Protect an Excel Sheet in Office Scripts
Question details
The user needs to automate a Power Query refresh on a protected Excel worksheet by temporarily removing sheet protection and then reapplying it with a password through an Excel Office Script.
- Product
- Microsoft Excel Office Scripts
- Device & OS
- not provided
- Scenario
- Refreshing Power Query data in a protected worksheet while keeping sheet protection enabled after the refresh.
- Observed behavior
- The worksheet protection blocks Power Query refresh, so the Office Script must unprotect the sheet before refreshing connections and protect it again with the same password.
Open the workbook in Excel on the web and confirm that the Automate tab is available. Store the worksheet protection password securely because the sample script uses it in plain text.
Use ExcelScript to unprotect the sheet, refresh data connections, and protect it again
Use this solution when a protected Excel worksheet prevents Power Query from refreshing and you want the script to restore password protection automatically after the refresh.
This Office Script calls getProtection().unprotect() with the existing password, runs workbook.refreshAllDataConnections(), and then applies protection again with the same password and your chosen protection options.
Go to the Automate tab in Excel on the web and click New Script to open the Code Editor.
Paste the code that calls selectedSheet.getProtection().unprotect("Password"), workbook.refreshAllDataConnections(), and selectedSheet.getProtection().protect(options, "Password").
Change both "Password" values to the actual worksheet protection password that is already applied to the sheet.
Click Run in the Code Editor. The script unprotects the active worksheet, refreshes all Power Query data connections, and then protects the sheet again with the password.
Use WPS Spreadsheet as a Free Alternative for Protected Excel Workbooks
Office Scripts is a Microsoft 365 feature and is not available in WPS Office. WPS Spreadsheet is a free, lightweight alternative that opens Excel workbooks, supports sheet password protection and data refresh, and uses a familiar tabbed interface for easy migration.
- 1. Download and install WPS Office: Go to the official WPS website and click Download WPS Office Free, then install WPS Spreadsheet on your computer.
- 2. Open the Excel workbook in WPS Spreadsheet: Launch WPS Spreadsheet, click Open, and select the Excel .xlsx workbook that contains the protected sheet.
- 3. Protect the sheet after refreshing data: Go to the Review tab, click Protect Sheet, and set a password after refreshing the data connections under the Data tab.
Frequently Asked Questions
Why does Power Query fail to refresh on a protected sheet?
Worksheet protection can block operations that refresh or modify protected ranges. Unprotect the sheet before the refresh, then apply protection again after the refresh, as shown in the Office Script.
Can I store the Office Script password more securely instead of writing it in the code?
Office Scripts do not have a built-in secret store for this case. Keep the workbook and script access restricted, or use a limited-permission account. In WPS Spreadsheet, you can protect the sheet directly from the Review tab without writing script code.
Does WPS Office support Excel Office Scripts?
No, Office Scripts is a Microsoft 365 feature. WPS Office opens Excel files and offers sheet protection and data refresh through its own ribbon tools, but it does not run ExcelScript automation.
What should I do if the script runs but the sheet remains unprotected?
Check that the protect method uses the same password string as the unprotect call and that your protection options allow the intended actions. Run the script manually and verify that the refresh step completes before the protect step runs.
