Update OneDrive Excel file from Dynamics 365 using Power Automate (MS Flow)

Create automated flows to dynamically update an Excel file stored in OneDrive with data from the Dynamics 365 table.

This page contains step-by-step instructions for exporting the Dynamics 365 table and setting up a Power Automate flow (MS Flow).

Export Dynamics 365 table to Excel

Open the Dynamics 365 table and the view which data you want to export.

On the command bar, click ExportStatic Worksheet:

Export to Excel

The exported Excel table has hidden columns. The headers of these columns contain (Do Not Modify).

Find the column with the name of the exported Dynamics 365 table in the header. This column stores unique row IDs that we will use to identify the row in the Excel table that needs to be updated.

Column that stores unique row IDs

Flow

Below are instructions for creating automated flows to add, update, and delete Excel table rows when data in the Dynamics 365 table changes.

To create an automated flow, sign in to Power Automate. On the navigation bar, select My flows. Then select + New flowAutomated cloud flow:

New flow

Give your flow a name and enter Dataverse into the Search all triggers field. Select When a row is added, modified or deleted trigger:

When a row is added, modified or deleted trigger

Add a row

Create a flow that triggers when a new row in the Dynamics 365 table is created and adds a new row to the Excel table.

These are the flow steps:

Flow to add a row to the Excel table

  1. When a row is added, modified or deleted trigger.

    The Change Type parameter defines the trigger condition. Select the Added option from a dropdown. Then select the Dynamics 365 table name from a dropdown.

    The Scope parameter defines which rows must be monitored to trigger the flow. You can find more information about the Scope parameter in the Microsoft documentation here.

    When a row is added trigger

  2. Add a row into a table action.

    Enter the Excel file location details and select the table. After selecting the table, you will see a list of all available table columns.

    For table columns that need to be updated, select the corresponding Dynamics 365 table columns.

    Important

    Update the column that stores unique row IDs. Select the corresponding column from a Dynamic Content tab.

    Add a row into a table

Update a row

Create a flow that triggers when a row in the Dynamics 365 table is modified and updates a row in the Excel table.

These are the flow steps:

Flow to update a row in the Excel table

  1. When a row is added, modified or deleted trigger.

    The Change Type parameter defines the trigger condition. Select the Modified option from a dropdown. Then select the Dynamics 365 table name from a dropdown.

    The Scope parameter defines which rows must be monitored to trigger the flow. You can find more information about the Scope parameter in the Microsoft documentation here.

    When a row is modified trigger

  2. Update a row action.

    Enter the Excel file location details and select the table. After selecting the table, you will see additional required parameters and a list of all available table columns.

    For the Key Column and Key Value parameters, select the column that stores unique row IDs.

    For table columns that need to be updated, select the corresponding Dynamics 365 table columns.

    Update a row

Delete a row

Create a flow that triggers when a row in the Dynamics 365 table is deleted and removes a row from the Excel table.

These are the flow steps:

Flow to remove a row from the Excel table

  1. When a row is added, modified or deleted trigger.

    The Change Type parameter defines the trigger condition. Select the Deleted option from a dropdown. Then select the Dynamics 365 table name from a dropdown.

    The Scope parameter defines which rows must be monitored to trigger the flow. You can find more information about the Scope parameter in the Microsoft documentation here.

    When a row is modified trigger

  2. Delete a row action.

    Enter the Excel file location details and select the table. After selecting the table, you will see additional required parameters.

    For the Key Column and Key Value parameters, select the column that stores unique row IDs.

    Update a row