In this post I will show you how to export data from Power Automate, to an Excel file. This would seem to be a straightforward requirement, but until recently has required the use of apply to each loops which are slow and can potentially use many API actions.
Table of contents
Video Demonstration
In this demonstration I am exporting 2,000 records from a SharePoint list to an Excel document and the flow takes only 5 seconds to execute:
Create an Excel Export Template
The first step in this process is to create an Excel that contains a table for your exported data to be inserted into. Format the cells and add formulas as per your requirements and then save the file. In my example, the file looks like this:
As you can see from the screenshot, the table is called InvoiceExport and it has four fields. The fields containing values have had number formatting applied and there are some formulas to sum the Invoice Amount and count the number of records.
Make a note of what you have called the table, you will need it for your flow.
Convert the Template to Base64
Once you are happy with your Excel template. Head to Base 64 Guru and upload your template file. Base64 Guru will create a base64 representation of your file, like this:
Click on the copy button above the Base64 representation of your file to copy it to your clipboard.
Flow Overview
To give you an idea of how simple this flow is to build, here is a screenshot of the entire thing, it consists of only 5 actions:
Use the Select Action to Reshape your export Data
In my example, the data source is SharePoint, but any data source accessible to Power Automate will work. It could be SQL Server, Dataverse, SharePoint or even another Excel file. The purpose of the select action is to reshape the data down to just the columns that you want to export.
Within the select action create a raw array of values that match the order of the columns of your Excel template. We do not want to name the columns.
The code for my select action looks like this:
[
@{item()?['Title']},
@{item()?['Customer']},
@{item()?['Invoice_x0020_Amount']},
@{item()?['Date']}
]
Select Action Text Mode
Your Select action may look a different to mine, because the default mode is Key/Value mode.
You can switch to Text mode by clicking on this:
Create the Excel File and Populate with your Data
Next step is to create your Excel file and populate it with the data from your Select action:
First, create a Compose action called ExcelDoc and paste in the Base64 representation of your template file.
Next, use a Create File action, name the file as per your requirement and for the file content use the expression:
base64ToBinary(outputs('ExcelDoc'))
Finally, create add a new action by searching for HTTP and you should see this action:
At the time of writing this post, this is a preview action and it is not available in all Office 365 Tenants. If you do not see it in your list of actions, simply copy the code below:
{
"id": "c78ee521-4c62-4f34-84b1-8ef7-98516f0f",
"brandColor": "#EB3C00",
"connectionReferences": {},
"connectorDisplayName": "Office 365 Groups",
"icon": "https://connectoricons-prod.azureedge.net/releases/v1.0.1504/1.0.1504.2515/office365groups/icon.png",
"isTrigger": false,
"operationName": "Send_a_HTTP_request",
"operationDefinition": {
"type": "OpenApiConnection",
"inputs": {
"host": {
"connectionName": "shared_office365groups",
"operationId": "HttpRequest",
"apiId": "/providers/Microsoft.PowerApps/apis/shared_office365groups"
},
"parameters": {
"Uri": "",
"Method": "GET",
"ContentType": "application/json"
},
"authentication": {
"type": "Raw",
"value": "@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$ConnectionKey']"
}
},
"runAfter": {
"Create_file": [
"Succeeded"
]
}
}
}
And then add a new action to your flow, go to My Clipboard and press CTRL-V. The new action will then appear in your clipboard and you can add it to your flow:
Once the new action is in place, configure it as follows:
URI:
https://graph.microsoft.com/v1.0/me/drive/items/@{substring(outputs('Create_file')?['body/Id'], add(indexOf(outputs('Create_file')?['body/Id'], '.'),1))}/workbook/tables/InvoiceExport/rows/add
Notice that I have put the name of the table into the URL:
/workbook/tables/InvoiceExport/rows/add
You will need to update this to reflect the name of your table.
Method: POST
Body:
{
"values": @{outputs('select')['body']}
}
Export to Excel Done!
With this new action it is possible to add many rows to an Excel file very quickly and use only a single API action to do so. I tested this with 2,000 rows and it took 5 seconds to complete.
Once the export is complete you can take whatever action you want to with the file. For example to email it you could do a Get File Content and an email action:
This is a really fast, and easy way to export data from Power Automate to Excel. Please let me know if in the comments if you get stuck or found this useful.
