'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Save Live Dynamics Data to an Excel File in SharePoint
Question details
The user wants to integrate and automatically update live Dynamics data into an Excel workbook hosted on SharePoint.
- Product
- Microsoft Dynamics, Excel, SharePoint
- Device & OS
- not provided
- Scenario
- Syncing live customer or operational data from Microsoft Dynamics directly into a shared Excel file for reporting, tracking, and collaboration.
- Observed behavior
- The goal is to establish an automated pipeline where any created or updated record in Dynamics is instantly reflected as a new or updated row in the SharePoint Excel table.
Ensure you have an active Microsoft Power Automate license, proper permissions to access the Microsoft Dynamics environment, and edit access to the target Excel workbook stored in SharePoint. The Excel file must contain a formatted table with headers to receive the data.
Use Power Automate to Sync Dynamics and Excel Online
Creating an automated cloud flow in Power Automate is the most efficient way to capture Dynamics record changes and write them directly to a SharePoint-hosted Excel file.
Power Automate (formerly Microsoft Flow) serves as the bridge between Dynamics and SharePoint. By leveraging the Dataverse trigger and the Excel Online connector, you can ensure your spreadsheets stay perfectly in sync with your live database without manual data entry.
Open your target Excel file in SharePoint. Highlight your header row and at least one empty data row underneath, then press Ctrl+T (or go to Insert > Table) to format the range as a table. Name the table in the Table Design tab.
Log into Power Automate, click on 'Create', and select 'Automated cloud flow'. Give your flow a name and search for the Microsoft Dataverse trigger 'When a row is added, modified or deleted'.
Select the Trigger type (e.g., Added or Modified), choose your specific Dynamics table (like Accounts or Contacts), and set the Scope to 'Organization'.
Click 'New step', search for 'Excel Online (Business)', and select either 'Add a row into a table' or 'Update a row' depending on your workflow needs.
Select your SharePoint Site, Document Library, Excel file, and the Table you created in step 1. Power Automate will display the columns from your Excel table. Click into each field and use the dynamic content menu to map the corresponding data fields from Dynamics. Save and test your flow.
Need a Lightweight Spreadsheet Solution? Try WPS Office
While automated background flows require the Microsoft ecosystem, WPS Office provides an exceptional, free alternative for viewing, editing, and analyzing your exported data. It is fully compatible with Microsoft Excel formats and offers a highly familiar interface without the heavy subscription fees.
- 1. Download and Install: Get the free WPS Office suite from the official website and install it on your PC or Mac.
- 2. Export or Download your Data: Download your synchronized Excel file from SharePoint to your local machine.
- 3. Open with WPS Spreadsheets: Launch WPS Office and open the .xlsx file to instantly view, filter, and analyze your Dynamics data with zero compatibility issues.
Frequently Asked Questions
Can I use Power Apps instead of Power Automate for this integration?
Power Apps is great for building custom user interfaces to interact with Dynamics data, but Power Automate is the dedicated tool for running background synchronization tasks, like saving backend data to an Excel file automatically.
Why isn't Power Automate finding my Excel file in SharePoint?
Ensure the file is saved in a standard Document Library within SharePoint and that you have the correct site address selected in the flow. Most importantly, the data range inside the Excel file must be formatted as a Table for the Excel Online connector to recognize it.
Does this method update existing Excel rows when Dynamics data changes?
Yes, but you must specifically use the 'Update a row' action in Power Automate. You will also need a unique identifier column (like a Record ID or Customer ID) mapped between your Dynamics table and your Excel table so the system knows exactly which row to overwrite.
Can I use a personal OneDrive account instead of SharePoint?
Yes, you can use the 'Excel Online (OneDrive)' connector in Power Automate if you prefer to save the spreadsheet to your personal OneDrive storage rather than a shared SharePoint team library.
