Automating Unprotecting an XLSM Workbook, Unhiding a Hidden Pivot Sheet, and Copying the Updated File to ADLS Gen2

Question

Hi everyone,

I'm looking for guidance on implementing a fully automated Azure-based solution for processing an Excel Macro-Enabled Workbook (.xlsm) stored in SharePoint Online.

Business Scenario

We receive an XLSM workbook in SharePoint Online that is used as a source file for downstream reporting and analytics processes.

The workbook contains multiple worksheets and is protected using Review → Protect Workbook (Structure) with a password.

When the workbook is opened, only three worksheets are visible:

• Instructions
• Template
• Pivot Table

There is an additional worksheet named "Pivot", but it is hidden because the workbook structure is protected.

To access the hidden worksheet manually, we currently perform the following steps:

1. Open the XLSM workbook.
2. Go to Review → Unprotect Workbook.
3. Enter the workbook password.
4. Unhide the Pivot worksheet.

After these steps, the workbook contains four visible worksheets:

• Instructions
• Template
• Pivot
• Pivot Table

Requirement

We want to automate this entire process without any manual intervention.

Source

• SharePoint Online
• XLSM (Macro-Enabled Workbook)
• Workbook structure protected with a known password

Desired Automated Process

1. Run on a schedule (daily/hourly).
2. Read the XLSM workbook from SharePoint Online.
3. Unprotect the workbook structure using the known password.
4. Unhide the hidden worksheet named Pivot.
5. Preserve:
   • VBA macros
     • Workbook formatting
       • Formulas
         • Pivot tables
         1. Save the workbook in XLSM format.
         2. Upload the updated workbook to Azure Data Lake Storage Gen2 (ADLS Gen2).
6. Store the final file in ADLS with all four worksheets visible.Hi everyone, I'm looking for guidance on implementing a fully automated Azure-based solution for processing an Excel Macro-Enabled Workbook (.xlsm) stored in SharePoint Online.

   Business Scenario

   We receive an XLSM workbook in SharePoint Online that is used as a source file for downstream reporting and analytics processes. The workbook contains multiple worksheets and is protected using Review → Protect Workbook (Structure) with a password. When the workbook is opened, only three worksheets are visible:
   • Instructions
   • Template
   • Pivot Table
   There is an additional worksheet named "Pivot", but it is hidden because the workbook structure is protected. To access the hidden worksheet manually, we currently perform the following steps:
   1. Open the XLSM workbook.
   2. Go to Review → Unprotect Workbook.
   3. Enter the workbook password.
   4. Unhide the Pivot worksheet.
   After these steps, the workbook contains four visible worksheets:
   • Instructions
   • Template
   • Pivot
   • Pivot Table

   Requirement

   We want to automate this entire process without any manual intervention.

   Source

   • SharePoint Online
   • XLSM (Macro-Enabled Workbook)
   • Workbook structure protected with a known password

   Desired Automated Process

   1. Run on a schedule (daily/hourly).
   2. Read the XLSM workbook from SharePoint Online.
   3. Unprotect the workbook structure using the known password.
   4. Unhide the hidden worksheet named Pivot.
   5. Preserve:
   • VBA macros
   • Workbook formatting
   • Formulas
   • Pivot tables
   1. Save the workbook in XLSM format.
   2. Upload the updated workbook to Azure Data Lake Storage Gen2 (ADLS Gen2).
   3. Store the final file in ADLS with all four worksheets visible.

Answer 1

I think that, in 2026/7, Azure and Graph only document reading an xlsm and uploading it to ADLS, and they do not document any step to unprotect workbook‑structure or show a hidden sheet.

Answer 2

Hi @Yernaidu Siraparapu ,

Welcome to the Microsoft Q&A Platform! Thank you for asking your question here.

Yes, this requirement can be automated end-to-end without any manual intervention.

A recommended approach is to use an Azure Function with a Timer Trigger to run on the required schedule (daily/hourly). The function can download the XLSM workbook from SharePoint Online using Microsoft Graph, modify the workbook by removing workbook structure protection and making the hidden "Pivot" worksheet visible, save the file back in XLSM format while preserving the workbook content, and then upload the processed file to Azure Data Lake Storage Gen2 (ADLS Gen2). Azure Functions support scheduled execution, Microsoft Graph supports downloading file content from SharePoint Online, and ADLS Gen2 SDKs support programmatic file uploads.

Recommended Workflow

Azure Function Timer Trigger

↓

Download XLSM from SharePoint Online using Microsoft Graph

↓

Remove workbook structure protection

↓

Unhide the "Pivot" worksheet

↓

Save workbook as XLSM

↓

Upload the processed file to ADLS Gen2

This approach satisfies the requirement of processing the SharePoint-hosted XLSM file, exposing all four worksheets (Instructions, Template, Pivot, Pivot Table), and storing the final XLSM file in ADLS Gen2 without manual intervention. [learn.microsoft.com],

References

Azure Functions Timer Trigger (Scheduled Automation) https://learn.microsoft.com/azure/azure-functions/functions-bindings-timer [learn.microsoft.com]

Download files from SharePoint Online using Microsoft Graph https://learn.microsoft.com/graph/api/driveitem-get-content [learn.microsoft.com]

• Upload files to Azure Data Lake Storage Gen2 https://learn.microsoft.com/azure/storage/blobs/data-lake-storage-directory-file-acl-python [learn.microsoft.com] Please accept as Yes if the answer is helpful so that it can help others in the community.