How to Retrieve Excel Data in a SharePoint Form Using Power Automate
Question details
The user needs to look up and retrieve specific values from an Excel workbook and use them within a SharePoint form.
- Product
- Excel, SharePoint, Power Automate
- Device & OS
- not provided
- Scenario
- Automating data retrieval where a user submits a SharePoint form (e.g., entering a mobile number), and the system needs to fetch associated records from an Excel document library.
- Observed behavior
- SharePoint forms do not natively support direct lookups to Excel workbooks, requiring an automated flow or data migration to achieve the lookup functionality.
Ensure your source Excel file is saved in a SharePoint Document Library or OneDrive for Business, and that the data you want to retrieve is formatted as an official Excel Table (Insert > Table).
Use Power Automate to Link SharePoint and Excel Online
Create a Power Automate flow that triggers when a SharePoint form is submitted, searches the Excel table, and retrieves the matching row.
Because SharePoint cannot directly query an Excel file natively, Power Automate acts as the bridge. By using the 'Excel Online (Business)' connector, the flow can read the table data and return the necessary fields back to the SharePoint list or send them via email.
Open your Excel workbook, select your data range, navigate to 'Insert' > 'Table', and give your table a recognizable name in the Table Design tab.
Log into Power Automate, click 'Create', and select 'Automated cloud flow'. Choose the trigger 'When an item is created' under SharePoint.
Enter your SharePoint Site Address and select the List Name corresponding to your SharePoint form.
Click 'New step', search for 'Excel Online (Business)', and select the 'List rows present in a table' action. Point it to the Document Library and Table where your Excel file is saved.
In the Excel action, use the 'Filter Query' field to match a SharePoint form input (e.g., MobileNumber eq '@{triggerOutputs()?['body/MobileNumber']}'). You can then add an 'Update item' SharePoint action to populate the rest of the form fields with the retrieved Excel data.
Migrate Excel Data to a SharePoint List (Simpler Alternative)
Convert your Excel workbook data into a SharePoint List to utilize native SharePoint lookup columns without requiring Power Automate.
Manage Your Spreadsheet Data Seamlessly with WPS Office
While Power Automate and SharePoint manage your complex cloud workflows, WPS Office provides a free, lightweight, and highly compatible alternative to Microsoft Office for preparing, formatting, and editing your Excel tables before uploading them to your document library.
- 1. Download WPS Office: Install the free WPS Office suite on your desktop or mobile device.
- 2. Open Your Spreadsheet: Launch WPS Spreadsheet and open your .xlsx data file.
- 3. Format for Cloud Automation: Highlight your data, select 'Insert' > 'Table' to properly format it, and save the file to be uploaded to your SharePoint library.
Frequently Asked Questions
Why is Power Automate failing to read my Excel file?
Power Automate can only read data inside an officially designated Excel Table. Simply formatting cells with borders and colors is not enough. You must highlight the data and use the 'Insert > Table' command in Excel or WPS Spreadsheet before uploading.
Can I use OneDrive instead of SharePoint to store the source Excel file?
Yes. Power Automate includes connectors for both Excel Online (Business) for SharePoint and Excel Online (OneDrive). You can store your source spreadsheet in OneDrive and still link it to a SharePoint form trigger.
Is it better to use a SharePoint list or an Excel file for lookups?
If the data is static or updated collaboratively by a team via web forms, a SharePoint list is better because it supports native lookups. If the data requires complex formulas, macros, or frequent bulk edits, keeping it in an Excel file and using Power Automate is preferred.
How do I avoid a 'Timeout' error when reading large Excel files in Power Automate?
Timeout errors occur when the flow tries to process too many rows at once. To fix this, always use the 'Filter Query' field in the 'List rows present in a table' action so Power Automate only retrieves the exact row matching your SharePoint form entry.
