How to Export a Dataverse View to Excel Using Power Automate

Exporting data from a Dataverse table can be achieved in various ways. In this guide, we'll demonstrate how to use the Dataverse API - ExportToExcel feature with Power Automate for a seamless data export experience.

Step 1: Create a Power Automate Flow

• Start by creating a new flow in Power Automate. Choose a trigger based on your scenario. I’m using a manual trigger for testing.

Step 2: Add the HTTP Action

• Add the "Invoke an HTTP Request" action from the HTTP with Microsoft Entra ID (Preauthorized) connector.

Step 3: Create a Connection to Your Environment URL

• In the HTTP action, create a connection to your Dataverse environment by providing the Environment URL.

Step 4: Get Dataverse API Request Details

• To retrieve the necessary API details:

  1. Open your desired view in a Model-Driven App.

  2. Open the browser's Developer Tools (usually F12).

  3. Go to the Network tab and start monitoring traffic.

  4. Click the Export to Excel button in the command bar.

  5. In the Network tab, locate the request and capture the payload details, such as:

     • HTTP method

     • API endpoint

     • Body

Sample Payload JSON

{
    "View": {
        "@odata.type": "Microsoft.Dynamics.CRM.savedquery",
        "savedqueryid": "00000000-0000-0000-00aa-000010001020"
    },
    "FetchXml": "<fetch version=\"1.0\" output-format=\"xml-platform\" mapping=\"logical\" savedqueryid=\"00000000-0000-0000-00aa-000010001020\" returntotalrecordcount=\"true\" no-lock=\"false\"><entity name=\"systemuser\"><attribute name=\"entityimage_url\"/><attribute name=\"fullname\"/><attribute name=\"businessunitid\"/><attribute name=\"title\"/><attribute name=\"address1_telephone1\"/><attribute name=\"systemuserid\"/><attribute name=\"positionid\"/><filter type=\"and\"><condition attribute=\"isdisabled\" operator=\"eq\" value=\"0\"/><condition attribute=\"accessmode\" operator=\"ne\" value=\"3\"/><condition attribute=\"businessunitid\" operator=\"eq-businessid\"/><condition attribute=\"accessmode\" operator=\"ne\" value=\"5\"/></filter><order attribute=\"fullname\" descending=\"false\"/></entity></fetch>",
    "LayoutXml": "<grid name=\"resultset\"><row name=\"result\"><cell name=\"fullname\" width=\"200\" /><cell name=\"businessunitid\" width=\"200\" /><cell name=\"title\" width=\"200\" /><cell name=\"positionid\" width=\"200\" /><cell name=\"address1_telephone1\" width=\"200\" /></row></grid>",
    "QueryApi": "",
    "QueryParameters": {
        "Arguments": {
            "Count": 0,
            "IsReadOnly": true,
            "Keys": [],
            "Values": []
        }
    }
}

Step 5: Configure the HTTP Action

• Populate the HTTP action with the captured details:

  • Method: POST

  • URI: /api/data/v9.0/ExportToExcel

  • Body: Use the payload captured in Step 4.

Step 6: Create a File Content Object

• Use the response from the HTTP action to build a File Content Object:

  • Map the response body to create the content for the Excel file.

  • 

{
    "$content-type": "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
    "$content":@{body('Invoke_an_HTTP_request')?['ExcelFile']
    }
}

Step 8: Save or Distribute the File

• Decide how to handle the exported Excel file:

  • Store it in OneDrive or SharePoint: Use a relevant connector to save the file.

  • Send it via Email: Attach the file to an email and send it directly.

This approach leverages the power of the Dataverse API to provide a flexible and automated solution for exporting data. By following these steps, you can streamline the process and adapt it to various scenarios, such as scheduled exports, user-triggered downloads, or email distribution.

When exporting data from Dataverse to Excel, there is a row limit of 100,000 rows for each export. If your dataset exceeds this limit, consider using filters or pagination to manage large data volumes.