If your organization doesn't restrict its usage, you can go with Office Scripts for this task. You can either unlock an entire workbook, or some individual worksheets (as long as you have the passwords, of course).
I'm sharing some blogs that I posted about Office Scripts, so you can have more context about it, but as a short description, it works like a VBA for Excel on the Web and it can be initialized by Power Automate:
First thing to do is to create your Script, which will look like this. Go to Automate tab and click New script:
Then you can enter your script in the side pane:
- You can use the code in line 5 to unprotect a workbook, just including the password between the parenthesis (in yellow).
- You can use the code in line 7 to unprotect a worksheet, just including the sheet name (in green) and the password (in yellow) between the parenthesis. If you want to unprotect more than one worksheet, just copy/paste the same line, changing the sheet name and the password.
function main(workbook: ExcelScript.Workbook) {
workbook.getProtection().unprotect('123') //Unprotect workbook
workbook.getWorksheet('Sheet1').getProtection().unprotect('123') //Unprotect worksheet
//PROTECTING AGAIN
workbook.getProtection().protect('123')
workbook.getWorksheet('Sheet1').getProtection().protect(null, '123')
}
After saving the Script, go to Power Automate and add a Run Script action, informing the file where you want to run it and the script to be used. In your case, since you're working with an email attachment, you will first need to save it to SharePoint or OneDrive:
You can even pass dynamic values from PA to Office Scripts, following the instructions from the "Sending values from Power Automate to Excel with Office Scripts" blog, mentioned above.
Let me know if it works for you or if you need any additional help!
✅
If this solved your issue, please mark it as Accepted Answer.
👍
If it helped, feel free to give it a like!
🌐 Explore more Power Platform content on my
Website or on my ▶️
YouTube
💼 Find me on
LinkedIn
