How to Remove Password Protection from an XLSM Sheet in SharePoint and Copy the File to ADLS?

Question

Hi everyone,

I have an .xlsm file stored in SharePoint, and one of the worksheets is password-protected. As part of my data pipeline, I need to:

Remove the worksheet password protection.

Keep the workbook in .xlsm format.

Copy the updated file to Azure Data Lake Storage (ADLS).

What is the best approach to achieve this? Can this be done using Azure Data Factory, Logic Apps, Power Automate, or Azure Functions? If anyone has implemented a similar solution, I'd appreciate your recommendations or sample approaches.

Thanks in advance!

Answer 1

Hello @Yernaidu Siraparapu ,

Yes, this can be automated, but the recommended approach depends on whether you know the worksheet protection password and are authorized to remove it.

If the password is known, the best approach is usually:

1. Download the .xlsm file from SharePoint
2. Use an Azure Function to open the workbook and remove worksheet protection using the known password
3. Save the workbook back in .xlsm format
4. Upload/copy the updated file to ADLS Gen2

Azure Data Factory, Logic Apps, and Power Automate are good for orchestration and file movement, but they are not ideal for modifying Excel worksheet protection inside an .xlsm file. ADF’s SharePoint Online List connector is primarily for SharePoint list data, not direct Excel file manipulation, and Microsoft Graph is commonly used to download SharePoint/OneDrive file content programmatically.

For the SharePoint file access part, you can use Microsoft Graph API to download the file content from SharePoint/OneDrive. Microsoft Graph supports downloading a driveItem using endpoints such as: GET /sites/{site-id}/drive/items/{item-id}/content

https://learn.microsoft.com/en-us/graph/api/driveitem-get-content?view=graph-rest-1.0

For copying the final file to ADLS Gen2, you can use the Azure Storage SDK from the Azure Function or use ADF after the file has already been processed. ADF is suitable for pipeline orchestration and copy activities, but the actual Excel modification should be handled in code, such as an Azure Function.

• If you do not know the password, Microsoft services will not provide a supported way to bypass or crack the worksheet protection. You should obtain the password from the file owner or ask the owner to provide an unprotected version.
• Avoid using Excel desktop automation/COM automation in Azure Functions, as it is not suitable for server-side automation.
• Use a library that supports .xlsm and can preserve macros while modifying only the worksheet protection setting.
• Make sure the Azure Function identity or app registration has appropriate permissions to read the SharePoint file and write to ADLS.

If this answer was helpful, please consider accepting it as the answer. If you have any questions or need further clarification, please comment on this thread and I will be happy to assist.

Answer 2

Hi @Yernaidu Siraparapu ,

Short answer: don't try to do the "unprotect" step in ADF, Logic Apps, or Azure

Functions. The clean, supported pattern is Power Automate + Office Scripts for the

unprotect, then a Copy activity for the move.

Do this (only works if you're authorized and know the worksheet password):

1. Office Script (Excel Online Business) – unprotect the sheet:

function main(workbook: ExcelScript.Workbook) {

const sheet = workbook.getWorksheet("YourSheetName");

sheet.getProtection().unprotect("YourPassword");

}

2. Power Automate flow:

• Trigger on the SharePoint .xlsm file
• Run script (Excel Online Business) -> your script above

(the "Run script" action is the only one that supports .xlsm via the file

browser; macros are preserved but can't be executed by Power Automate)

• Get file content and Create file into ADLS Gen2

3. Keep the password in Key Vault / secure input, and test on a copy first.

Important checks:

• If Excel asks for a password BEFORE the file opens, that's file-level encryption

(not worksheet protection) and Office Scripts won't apply.

• If it's Review > Protect Workbook, that's workbook-structure protection, not sheet.
• Microsoft cannot recover a forgotten password - get it from the file owner.
• ADF Copy can move the .xlsm as Binary, but it cannot open Excel or change

protection, so keep ADF only for the file movement.

Hope this helps!

Answer 3

Hello Yernaidu Siraparapu,

Welcome to the Microsoft Q&A and thank you for posting your questions here.

I understand that you would like to know how you can remove Password Protection from an XLSM Sheet in SharePoint and Copy the File to ADLS.

For more practical ways of handling this. What you need to do is to use Power Automate with an Office Script, provided that you are the authorized workbook owner and have the worksheet password. Power Automate supports .xlsm files through the Excel Online (Business) connector’s Run script action. While Office Script permanently unprotects the named worksheet using the supplied password, while the workbook remains in .xlsm format. The Power Automate can then retrieve the updated file content and write it to ADLS. Embedded VBA macros are preserved but cannot be executed by Power Automate. - https://learn.microsoft.com/en-us/javascript/api/office-scripts/excelscript/excelscript.worksheetprotection?view=office-scripts, https://learn.microsoft.com/en-us/office/dev/scripts/develop/macros-power-automate, and https://learn.microsoft.com/en-us/office/dev/scripts/develop/power-automate-integration gives more insights.

You can follow this steps:

• Confirm that the file opens normally and only the worksheet is protected.
• Obtain the authorized, case-sensitive worksheet password.
• Create an Office Script that validates the worksheet and password, then unprotects the worksheet.
• Run the script against the SharePoint .xlsm file from Power Automate.
• Retrieve the updated file content after the script completes.
• Create the destination file in ADLS using the same .xlsm extension.
• Validate that the target sheet is unprotected and that the VBA project remains present.

If the workbook requests a password before it opens, this is file-level encryption and the Office Script solution does not apply. If the Review > Protect Workbook option is active, the problem is workbook-structure protection rather than worksheet protection. Microsoft cannot recover a forgotten password. - https://support.microsoft.com/en-us/excel/get-started/protect-an-excel-file, https://support.microsoft.com/en-us/excel/protect-a-workbook, https://support.microsoft.com/en-us/excel/protection-and-security-in-excel, and https://learn.microsoft.com/en-us/office/vba/api/excel.worksheet.unprotect give more information.

Azure Support is not required to implement this solution. Open a support case only if the Excel Online connector or Office Scripts fails on a supported workbook after licensing, permissions, worksheet name, password, and connector limitations have been verified. Use the associated links for more reading and steps to resolve it.

I hope this is helpful. Please! Do not hesitate to let me know if you have any other questions, steps or clarifications.

---

Please do not close the thread by upvoting and accepting the answer if any part of it is helpful.

Answer 4

Use Power Automate with Office Scripts for the modification, then Data Factory for transfer. Data Factory Copy activity can copy an .xlsm as Binary, but it cannot open Excel or change worksheet protection.

Create an Office Script that gets the target worksheet's protection object and calls unprotect(password). In Power Automate, trigger on the SharePoint file, run the script with Excel Online (Business), pass the password securely, and wait for save. Microsoft documents .xlsm support for Run script; macros are retained but cannot be executed, and workbooks with ActiveX or Form controls require testing.

Then start a Data Factory pipeline that downloads the SharePoint file through Microsoft Graph and writes it to ADLS Gen2 using Binary source and sink datasets. Store the password in Key Vault or secured flow input, mark activity inputs and outputs secure, and prevent concurrent edits. Test the workflow on a copy before replacing the source file.