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.
jayant khushalani says
i am getting an error while running the workflow – “Could not obtain a WAC access token.” How can i fix this?
Juan says
i am getting an error while saving the workflow, pass: “Send a HTTP request”
Some of the connections are not yet authorized. If you just created a workflow from a template, add the authorized connections to your workflow before saving.
{“id”:”5a4a51fe-a6e3-4f86-be1c-8607-82f78640″,”brandColor”:”#EB3C00″,”connectionReferences”:{“shared_sql_1”:{“connection”:{“id”:”/providers/Microsoft.PowerApps/apis/shared_sql/connections/ae54955c66464f1cbe727046af24dd09″}},”shared_office365″:{“connection”:{“id”:”/providers/Microsoft.PowerApps/apis/shared_office365/connections/shared-office365-86f90a62-3616-4628-b864-e6d092ae5383″}},”shared_onedriveforbusiness”:{“connection”:{“id”:”/providers/Microsoft.PowerApps/apis/shared_onedriveforbusiness/connections/shared-onedriveforbu-77bfbdeb-b8a6-4f2b-8477-4c22-e89733fd”}}},”connectorDisplayName”:”Grupos de Office 365″,”icon”:”https://connectoricons-prod.azureedge.net/releases/v1.0.1525/1.0.1525.2578/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”:”https://graph.microsoft.com/v1.0/me/drive/items/@{substring(outputs(‘Crear_archivo’)?[‘body/Id’], add(indexOf(outputs(‘Crear_archivo’)?[‘body/Id’], ‘.’), 1))}/workbook/tables/Rep_Deuda/rows/add”,”Method”:”POST”,”Body”:”{\n \”values\”: @{outputs(‘Exceldoc’)}\n}”,”ContentType”:”application/json”},”authentication”:”@parameters(‘$authentication’)”},”runAfter”:{“Crear_archivo”:[“Succeeded”]}}}
Juan Pierre Jesus Gonzales says
Hi Paulie, it’s amazing what you did, i just have a question pls, when I send the file after the http request, the attached file is empty, but when I go to my file in one drive the data is there. How can I resolve this? Do you think that could work to create and send many files more than 1K without any problem? I tried with office scripts but it has limitations. Thanks in advance.
Andy Lawrence says
Hi Paul,
Following up on my Youtube comment, I just wanted to leave the information here about the problem and solution I found when working through this great method. Thanks for getting me started with this by the way.
The problem I had was that your URI didn’t work for me – that was;
POST 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
Even after doing the following;
– removing the string manipulation
– realising that me/drive is for Onedrive – for sharepoint I would need drives/{drive ID}
– discovering and hard-coding the IDs
Which meant that I ended up with this;
POST https://graph.microsoft.com/v1.0/drives/{drive ID}/items/{item ID}/workbook/tables/CSVOutput/rows/add
It still didn’t work in the Power Automate ‘send http’ call …
… but it DID work in the brilliant and time-saving Graph Explorer tool (https://developer.microsoft.com/en-us/graph/graph-explorer) !
The error I was getting in Power Automate said;
“URI path is not a valid Graph endpoint, path is neither absolute nor relative or resource is not one of the supported (me, users, groups, sites, search, planner, teams, chats):”
In the end I put it down to the 3rd reason listed here;
“resource [i.e. /drives/] is not one of the supported (me, users, groups, sites, search, planner, teams, chats)”
The way I worked around this was to use the /sites/ resource instead which is listed as supported in Power Automate, so
POST https://graph.microsoft.com/v1.0/sites/{site ID}/drive/items/{file ID}/workbook/tables/CSVOutput/rows/add
The above URI works in both environments and allows you to write multiple rows directly to an Excel file in a sharepoint library.
I hope this is helpful for future readers of your article,
Andy
PS. Some readers that haven’t read up on the basic calls of the Graph REST API might be asking, “how do I get those IDs I need for this to work?”. I would refer them here for the API resource;
https://docs.microsoft.com/en-us/graph/api/overview?view=graph-rest-1.0
And also give them a little help as follows;
Find your site ID;
GET https://graph.microsoft.com/v1.0/sites/contoso.sharepoint.com:/sites/{site name}
Drive ID;
GET https://graph.microsoft.com/v1.0/sites/{site ID}/drive
File (item) ID;
https://graph.microsoft.com/v1.0/drives/{drive ID}/root:/{folder}/{subfolder}/{Filename}.xlsx
🙂
Paulie says
Cheers Andy, really helpful information.
yang says
I got an error in final step, “The response is not in a JSON format.”
is there any idea for this error?
Herbert Auster says
Hello Paulie,
I would be very grateful if you could show me an example of how to do this syntactically if I want to create the Excel file on SharePoint instead of OneDrive. Unfortunately I did not understand the contribution of Andy.
Thanks in advance
Regards
Herb
Herbert Auster says
Hello Paulie,
I have now tried to recreate this exactly as you suggest here, i.e. with the Excel file in the OneDrive.
But I always get the error message: The requested resource does not exist.
It would be nice if you could get back to me in the new year before I jump out of the window in despair.
Regards
Herb
PS: The action “Send a HTTP request” was already available for me.
Jile says
Is there a way to do this with lists over 5000? I saw your other post on how to create the do until loop but I can’t get it to work and send the output to a file. It always gets stuck in a loop.
Sourav Mahapatra says
Hi,
i am getting below error while creating the flow. Can you please help me out.
Correct to include a valid reference to ‘select’ for input parameter(s) of action “Send_a_HTTP_request’.
Sourav Mahapatra says
I got an error in final step, “The response is not in a JSON format.”
is there any idea for this error?
Dave says
@Sourav Mahapatra,
I would check the body of your http request, and make sure the { } brackets are there. The body should look like:
{
“values” : ARRAY
}
My problem is that my Graph API keeps timing out, even with prefer: respond-async set in the header… my array is 621 columns wide and 115 rows high. LOL
Gideon says
Hi Paul
What a great article! It was really helpful and it is working beautifully for me.
I just have one strange thing happening – I used a similar excel template than you for creating the base64 representation. The table in the template has a header row and then one blank row (…because excel does not allow you to create a table with only a header row).
When I append rows to the table using the HTTP request, the resulting excel file always have row 2 blank and the appended rows starting at row 3.
How can I ensure the appending starts at row 2 and not leave this blank row?
Thank you
Gideon
Miro Atanasova says
Hi Paul, wonderfull post.
I am having issues similar to Andrew, and followed his suggestions but still struggling with the flow and cannot get it past failing with “URI path is not a valid Graph endpoint, path is neither absolute nor relative or resource/object is not supported for this connect”.
Are you able to advise?
Paulie says
HI Miro,
I am not sure what your problem is, but if you use the get in touch form I will have a look with you.
Paul
Sourav Mahapatra says
I have tried that, But Getting error of “unsupportedWorkbook”, “message”: “This workbook couldn’t be opened because the file format may not be matching with the file extension.”. Any Idea what went wrong?
David Haygood says
Hey Paulie,
Thank you so much for the automations you continue to develop and explain…extremely helpful. I have implemented several! As I was reviewing this one, I thought about the reverse of this process…acquiring an excel file (without tables) and grabbing the content and converting it to json. Have you ever done something like that? Seems like it would be easy enough to decodebase64 and then convert to json. What do you think?
Paulie says
The problem is you’d have to unzip it first (which is possible). Inside of each worksheet the content is xml – so it should be possible to convert to json quite easily.
But power Automage already has actions to read excel files easily (office scripts).
And you can read and write to cells using MS Graph if you need even more control.
Chris Florian says
I am trying to use this process with a document I create in a SharePoint library.
I have everything working up to the Send HTTP Request.
The error I’m getting is “Item not found”.
Here are some of the data values:
ID = “%252fShared%2bDocuments%252fWF%2bBatch%2bFiles%252f2023-04-28%2b(560).xlsx”
The substring output = “xlsx”
The body of the Create File looks like this:
{“ItemId”:3,
“Id”:”%252fShared%2bDocuments%252fWF%2bBatch%2bFiles%252f2023-04-28%2b(560).xlsx”,
“Name”:”2023-04-28 (560).xlsx”,
“DisplayName”:”2023-04-28 (560).xlsx”,
“Path”:”/Shared Documents/WF Batch Files/2023-04-28 (560).xlsx”,
“LastModified”:”2023-04-28T16:52:12Z”,
“Size”:19505,
“MediaType”:”application/vnd.openxmlformats-officedocument.spreadsheetml.sheet”,
“IsFolder”:false,
“ETag”:”\”{2A2B9FC5-578A-4DF6-BEFA-ADE8AFEA9D2B},6\””,”FileLocator”:”dataset=aHR0cHM6Ly91c2Jhbmsuc2hhcmVwb2ludC5jb20vdGVhbXMvSUNTL0FwcHMvV0ZNZ3Q=,id=JTI1MmZTaGFyZWQlMmJEb2N1bWVudHMlMjUyZldGJTJiQmF0Y2glMmJGaWxlcyUyNTJmMjAyMy0wNC0yOCUyYig1NjApLnhsc3g=”}