How to Copy Excel Rows from a Shared Mailbox to SharePoint using Power Automate
Question details
The user needs to automate the process of extracting row data from an Excel workbook received as an email attachment in a shared mailbox, and importing that data into a SharePoint Online list.
- Product
- Power Automate, SharePoint Online
- Device & OS
- not provided
- Scenario
- Setting up an automated workflow to process incoming email attachments containing structured data and populating a central SharePoint list.
- Observed behavior
- Automating the transfer of data from structured Excel tables to individual SharePoint items without manual data entry.
Ensure that the incoming Excel attachment contains data formatted as an official Excel Table, and verify that your Power Automate account has adequate permissions to access the shared mailbox, a temporary file storage location (like OneDrive), and the target SharePoint list.
Create a Power Automate Flow to Process Excel Attachments
Set up a trigger for incoming emails, temporarily save the Excel file, extract the table data, and loop through the rows to create new SharePoint items.
To achieve this automation, the Power Automate flow must first save the email attachment to a reachable cloud location (such as OneDrive for Business or SharePoint) before it can read the Excel table rows. Once the data is accessible, an Apply to each loop maps every row to a new SharePoint list item.
Log in to Power Automate, create an Automated cloud flow, and add the trigger 'When a new email arrives in a shared mailbox (V2)'. Specify the target shared mailbox address and set 'Include Attachments' to Yes.
Add an 'Apply to each' control for the attachments. Inside it, add a 'Create file' action (using OneDrive for Business or SharePoint) to temporarily save the incoming Excel file using the attachment Name and Content.
Add the Excel Online action 'List rows present in a table'. Point it to the location where you just created the file, using the dynamic File ID. Specify the Table name that exists inside the workbook.
Add another 'Apply to each' control using the 'value' output from the 'List rows' action. Inside this loop, add the SharePoint 'Create item' action. Select your target Site Address and List Name, then map the Excel dynamic content columns to the corresponding SharePoint list fields.
Try WPS Office for Your Daily Spreadsheet Tasks
While complex Power Automate workflows rely on the Microsoft 365 cloud ecosystem, WPS Office provides a lightweight, highly compatible, and free alternative for creating, formatting, and managing your Excel workbooks before they are sent through automated flows.
- 1. Open your workbook: Launch WPS Office and open your .xlsx file containing the raw data.
- 2. Format as Table: Highlight your data range, go to the Home tab, and click 'Format as Table' to ensure it can be read by data automation tools.
- 3. Save securely: Save the document in the standard .xlsx format to maintain 100% compatibility with external automation triggers.
Frequently Asked Questions
Why is Power Automate failing to read my Excel attachment?
Power Automate can only read data from an Excel file if it is formatted as an official Excel Table. If the attachment only contains raw data in normal cells, the 'List rows present in a table' action will result in an error or return no data.
Do I need special permissions to read a shared mailbox in Power Automate?
Yes, the Microsoft account used to establish the Outlook/Office 365 connection in Power Automate must have 'Read and Manage' (or Full Access) permissions to the shared mailbox being monitored by the trigger.
Can I process multiple Excel attachments in one email?
Yes. When you use the 'When a new email arrives' trigger, the attachments are returned as an array. Wrapping your 'Create file' and 'List rows' actions in an 'Apply to each' loop allows the flow to iterate through all attachments and process each Excel file individually.
How do I handle dynamic table names in the Excel attachment?
If the table name changes in each attachment, insert the Excel Online 'Get tables' action before 'List rows present in a table'. You can then use an expression like 'first(body('Get_tables')?['value'])?['id']' to dynamically grab the ID of the first table found in the document.
