How to Share Limited Excel Data from SharePoint Without Exposing the Master File
Question details
An organization needs to share a refreshed subset of data from a protected master Excel workbook in SharePoint without granting users access to the master data itself.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Securely sharing specific filtered records from a master spreadsheet with a wide audience in SharePoint.
- Observed behavior
- Local query tools like Power Query require source file access and manual refreshing, making them unsuitable for server-side automatic updates where the master file must remain strictly protected.
Ensure you have editing permissions for the master Excel workbook in SharePoint and access to Microsoft Power Automate with standard connectors enabled.
Use Power Automate to Sync Data to a Secondary Workbook
Create a server-side Power Automate flow that retrieves approved records from the master document and updates a secondary document that users can access.
Power Query is a local query tool that requires the user to have read access to the source file and generally requires manual refreshing. To achieve a secure, automated server-side data transfer, Power Automate is the best solution.
Create two separate Excel workbooks in SharePoint: a Master file (restricted access) and a Secondary file (shared access). Format the data ranges in both files as Excel Tables by selecting the data and pressing Ctrl + T.
Log in to Microsoft Power Automate. Click 'Create' on the left sidebar and select 'Scheduled cloud flow' to set up an automation that runs at specific intervals (e.g., daily).
Add a new action and search for 'Excel Online (Business)'. Select the 'List rows present in a table' action. Point the Location and Document Library to your SharePoint site, and select the Master file and its corresponding Table.
Add a 'Filter array' action to isolate only the subset of data you want to share. Set the 'From' field to the dynamic content 'value' (List of items) from the previous step, and configure the condition to match your specific criteria (e.g., Status is equal to 'Approved').
Add an 'Apply to each' control using the output from the Filter array. Inside the loop, add the 'Add a row into a table' action from Excel Online. Select your Secondary file and map the filtered dynamic content to the corresponding columns.
Use Office Scripts with Power Automate
For complex data extraction, write an Office Script in Excel Online to copy the data, and trigger it using Power Automate.
Try WPS Office for Lightweight and Secure Spreadsheet Management
While server-side SharePoint automations are highly specific to the Microsoft ecosystem, WPS Office provides an excellent, free alternative for everyday spreadsheet creation, sharing, and data protection. It is lightweight, user-friendly, and fully compatible with all standard document formats.
- 1. Download and Install: Visit the official WPS website to download and install WPS Office for free.
- 2. Open Your Spreadsheets: Launch WPS Spreadsheets and open your existing .xlsx files without worrying about format conversion issues.
- 3. Protect Your Data: Navigate to the Review tab to utilize Protect Sheet and Protect Workbook features to keep your master data safe.
Frequently Asked Questions
Why can't I use Power Query to share data without giving users master file access?
Power Query is a local query tool. When a user opens an Excel file containing a Power Query connection to another SharePoint file, the query runs using their credentials. If they do not have read access to the master file, the refresh will fail. Additionally, Power Query in SharePoint Online generally requires manual refreshing.
Can I hide sheets or rows instead of creating a secondary workbook?
Hiding rows, columns, or entire sheets is not a secure method for sharing data. Unless the workbook structure is strictly protected with a password, users can easily unhide the data. Even with protection, users with access to the file might extract hidden data using other software tools. Separating the data physically via an automated workflow is much more secure.
Does Power Automate require a premium license to move Excel data between SharePoint files?
No. The standard 'Excel Online (Business)' and 'SharePoint' connectors in Power Automate are typically included in basic Microsoft 365 business and enterprise licenses, meaning you usually do not need a premium license for simple table-to-table data transfers.
