In this Power Automate tutorial, we will see how to export the SharePoint list to excel dynamically and send an email.
Now we will create a flow, which will export the SharePoint list to excel and send an email using Microsoft Flow or Power Automate.
Power Automate export SharePoint list to excel and send email
Here we will see how to export the SharePoint list to excel and send email using Microsoft Flow or Power Automate.
I have a SharePoint list called salesOrder list, so we will export the SharePoint list to excel, and we will dynamically create table, column and add rows in excel, at last we will send an email the excel.
Read Power Automate delete all items in SharePoint list
Steps Power Automate export sharepoint list to excel and email
Here we will the steps Power Automate export SharePoint list to excel and send an email.
Step 1: Create an Excel template
In this step, we will create a blank excel template, which we will use later in the flow. So open Onedrive for business, and then click on the New icon -> Excel Workbook.
Now Excel file will open, go to the File option-> Rename, then close the tab. Now you can see the Excel template file created in One drive for business.
Now we will move forward to create a flow in the Power Automate.
Step 2: Create a flow in Power Automate
To create a Flow, Login to Power Automate, then click on Create and select Instant Cloud Flow.
Now provide the flow name, and select Manually triggered Flow. Then click on Create.
Step 2: Retrieve items from the SharePoint list
In this step, we will retrieve items from the SharePoint list, and we will filter the list whose status is delivered.
So click on the Next step and select Get items action, then provide the site address, list name, and in Filter query add the below query:
Status eq 'Delivered'
Step 3: Map the array of items
In this step we will map the array of items, so click on the Next step and select Select action. Then in From provide the value of get items from dynamic content, and map the key-value pair like below.
Read How to get email address from name in Power Automate
Step 4: Create the First row of the Excel table
To create an automatic dynamic excel column, we will get the array of columns from the above step. So click on the Next step and select Create CSV table action. Then in From inside square bracket insert the below expression:
first(body('Select'))
Step 5: Create the Array of column names
In this step we will get the first row of data which has all that column information, so click on the Next step and select Compose action then provide the below expression:
split(first(split(body('Create_CSV_table'),decodeUriComponent('%0D%0A'))),',')
Step 6: Get file content of excel template
In this step, we will get the file content from the excel template, which we will later use to create an excel file. So click on the Next step and select Get File content-OneDrive for Business. Then provide the excel template file.
Step 7: Create Excel file in OneDrive for Business
Now we will create an excel file, so click on the Next step and select Create File action. Then provide the folder path, and in File name add the below expression with .xlsx extension and in File content and the body from the get file content action.
guid()
Step 8: To create a dynamic column in excel table
Now we will create a dynamic column in the excel table, so click on the Next step and select Compose action. Then in input write the below expression:
split('A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z',',')
Step 9: Create a table in Excel
Now we will create an excel table dynamically, so click on the Next step and select Create table-Excel Online action. Now provide Location and Document library. Then in File provide the id of create a file from the dynamic content, in table range write $A1:$ below expression with 1
outputs('A1_Notation_Mapping'[sub(length(outputs('Array_of_col')),1)])
Then provide table name of excel sheet, and in column name provide the below expression:
first(split(body('Create_CSV_table'),decodeUriComponent('%0D%0A')))
Read Power Automate copy files with Examples
Step 10: Add rows to the excel table
To add multiple rows in the excel table from the output of Select action, we will use Apply to each action, inside we will create an action that will add rows to the excel table. So click on the Next step and select Apply to each action then provide the output of the select action.
Next click on Add an action and select Add a row into a table -Excel Online action. Then provide the Location, Document library, in File provide the Id of create file from the dynamic content, in table provide the name from the dynamic content and in Row provide the current item from the dynamic content.
Now we will move forward to send an email with the excel attachment.
Step 11: Delay action
Before we send an email, we will add a delay of 1 minute, so click on the Next step and select Delay action then provide the count as 1 and Unit as Minute.
Step 12: Get Excel File content
To send the excel file in email, we need the content of excel file, so click on the Next step and select Get file content-OneDrive For Business, Then provide the file, select the id of create file from the dynamic content.
Step 13: Send an email with email attachment
Now we will send an email with the created excel file as an attachment, so click on the Next step and select Send an email(V2) action, then provide the To, subject, and body field. Next click on Show Advanced options then provides the Attachment name 1 and Attachment content from the dynamic content.
Read Microsoft Flow or Power Automate employee onboarding
Step 14: Run the Flow Manually
Now click on Save and run the Flow manually, and you can see the file get created in the OneDrive Business.
Once you open the excel file you can see the data get exported to the excel file from SharePoint list.
At last, we can check the outlook email, the email comes with an excel attachment.
This is how we can export the SharePoint list to excel file dynamically and send email.
Related Power Automate tutorials:
- Convert SharePoint list item to PDF using Flow or Power Automate
- Power Automate Create a CSV Table
- How to update SharePoint Multiselect column in Power Automate
- Power Automate Create File
- How to create a word document from a template in Power Automate
- Power Apps Navigate Function + 16 Examples
In this Power Automate tutorial, we learned how to export SharePoint list to an excel file and send an email, and also we learned how to create a table, columns, and rows in excel dynamically.
After working for more than 15 years in Microsoft technologies like SharePoint, Office 365, and Power Platform (Power Apps, Power Automate, and Power BI), I thought will share my SharePoint expertise knowledge with the world. Our audiences are from the United States, Canada, the United Kingdom, Australia, New Zealand, etc. For my expertise knowledge and SharePoint tutorials, Microsoft has been awarded a Microsoft SharePoint MVP (9 times). I have also worked in companies like HP, TCS, KPIT, etc.
Says this is not a valid expression
outputs(‘A1_Notation_Mapping'[sub(length(outputs(‘Array_of_col’)),1)]
Same for me.
The parentheses and brackets are messed up. Try this:
outputs(‘A1_Notation_Mapping’)[sub(length(outputs(‘Array_of_col’)),1)]
Receiving an error that is is not a valid expresssion:
outputs(‘A1_Notation_Mapping'[sub(length(outputs(‘Array_of_col’)),1)])
This is no longer an expression:
outputs(‘A1_Notation_Mapping'[sub(length(outputs(‘Array_of_col’)),1)])
Was anyone able to get this to work still?
The parentheses and brackets are messed up. Try this:
outputs(‘A1_Notation_Mapping’)[sub(length(outputs(‘Array_of_col’)),1)]
I have not.
Correct to include a valid reference to ‘Array_of_col’ for the input parameter(s) of action ‘Create_table’.
Correct to include a valid reference to ‘A1_Notation_Mapping’ for the input parameter(s) of action ‘Create_table’.
I get both of those errors in flow checker.
Hello,
this is not working for me either!
I keep getting ‘The expression is invalid.’ when inserting outputs(‘A1_Notation_Mapping'[sub(length(outputs(‘Array_of_col’)),1)]).
@Bijay Kumar any tips please??
Same for me
One question, how can I bring more than 100 items in Get Items, since it only brings 100 and if I have more than 100 records it does not generate them in the excel table. Thanks
You can increase the threshold by clicking on setting of the get items connector.
I keep getting ‘The expression is invalid.’ when inserting outputs(‘A1_Notation_Mapping'[sub(length(outputs(‘Array_of_col’)),1)]).
@Bijay Kumar any tips please??
Unfortunately both versions of the
outputs(‘A1_Notation_Mapping’)[sub(length(outputs(‘Array_of_col’)),1)] and outputs(‘A1_Notation_Mapping'[sub(length(outputs(‘Array_of_col’)),1)]) are giving me the error, has anyone been able to find a working solution?
You have to change the name of the flows as show in the images below for example when you do “Compose” PowerAutomate will just name it Compose, Compose1, Compose2 so on. You have to click the 3 polka dots, click rename and rename it to “A1 Notation Mapping” or “Array of col” respectively. Also correct formula to use is this outputs(‘A1_Notation_Mapping’)[sub(length(outputs(‘Array_of_col’)),1)]
The single quotes aren’t being recognized because it’s a different font. Fix the quotes or copy paste the one in this comment, up to you.
I am still receiving the Invalid expression error.
Chris it worked for me but when I pasted the formula I had to correct the quotations, so basically wipe out the quotations around A1 Notation Mapping and retype them ‘. Also, I didnt have underscores.
This is what I used, but remember, delete the quotations and retype them because when you copy paste it reformats them incorrectly: outputs(‘A1 Notation Mapping’)[sub(length(outputs(‘Array of col’)),1)]
this worked for me. I think its best you write the code in, instead of copy and paste.
Changing the single quoted worked for me! Thanks!
Sorry correction, keep the underscores, I just got a warning and I had to add them and now it works! so it should be A1_Notation_Mapping and Array_of_col
Hello,
I have a problem in this Step “10: Add rows to the excel table”.
I can’t provide the name from the dynamic content (I don’t have expression field) and i don’t have Row field.
Can you help me?
Thanks
I ran into the same issue. Changing that filed to “Dynamic Content” is what worked for me.
You have to press on : “Enter custom value” first and then you can add it.
I am having a issue with this expression when using the create a table.
outputs(‘A1_Notation_Mapping’)[sub(length(outputs(‘Array_of_col’)),1)]
It is returning this error.
The argument is invalid or missing or has an incorrect format.
clientRequestId: 556c0553-59e4-4184-805c-79ea66f5b408
serviceRequestId: 6d499c8d-354e-41a1-b7b2-f0d99560e3c9
Also the value that is being shown in the table range is ($A1:$N)
So I think that the outputs formula has something wrong in it. Any help would be much appreciated.
I am also getting the same error. Any assistance with this would be greatly appreciated!
Did somebody find a solution? I am also stuck here … the rownumber seems to be not working out well and flow is therefore stuck!
*TIPS ON HOW TO FIX*
I’m including feedback from other people, so I do not take full credit.
1. Make sure you have renamed the step after “Create CSV table” and before “Get file content” to “Array of col”.
2. Make sure you have renamed the step after “Create file” and before “Create table” to “A1 Notation Mapping”.
3. In the table range, make sure you type “$A1:$” first.
4. When you paste the formula, re-enter the ‘ symbols.
outputs(‘A1_Notation_Mapping’)[sub(length(outputs(‘Array_of_col’)),1)]
Flow run failed in the “Add a row ito a table” step , the error message is : “unsupported Media Type , the request entity’s media typ ‘text/plain’is not supported for this resource” and how to fixed ? thanks
Had the same issue. When the dynamic choices come up, make sure to select the “Output” option under the “Select” category, meaning that it is taking the output from the “Select” step at the beginning as opposed to the output of the CSV. To be honest, I’m not certain that’s overall correct, but it made the flow run successfully.
Please give the valid fx
outputs(‘A1_Notation_Mapping’)[sub(length(outputs(‘Array_of_col’)),1)]
I’m having a different problem to most, I have changed the problem expression as others have suggested (but also removed the spaces for ease) but when I run the flow I get this error:
“Unable to process template language expressions in action ‘Create_table’ inputs at line ‘0’ and column ‘0’: ‘The template language expression ‘outputs(‘A1NotationMapping’)[sub(length(outputs(‘Arrayofcol’)),1)]’ cannot be evaluated because property ‘7’ cannot be selected. Property selection is not supported on values of type ‘String’.”
Anyone any ideas?
you need to edit your commands, e.g. from (‘A1_Notation_Mapping) to (‘A1NotationMapping’).
or simply change the element names.
Hi Bijay,
You may be able to really speed up the export by using this Excel batch update & create method. It may even help make this functional as a real-time export of large files when a user presses a button somewhere.
https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Excel-Batch-Create-Update-and-Upsert/td-p/1624706
Question: Why is my excel missing data for a few columns?
Hi All, this is a great tool and working for me great after reading the comments in the email. Unfortunately, when I receive the email the file states it could be corrupted. Any ideas or thoughts to fix this?
This is great! Thank you!
Different problem from most- If I run the aforementioned flow steps without the “Apply to Each” function, the document and table is created. However, I run into a problem in the “Add a row into a table” function. After selecting File= Id, I am presented with the following error in the Table section:
Could not retrieve values. Value segment type ‘literal’ expression ” invalid. Only literal segment types may be used.
Copy Error
Does anyone know what the issue may be? As mentioned, all previous steps work until trying to create this function.
I ran into the same issue. Changing that filed to “Dynamic Content” is what worked for me.
I’m stuck on the 2nd step. I changed the Query filter to match my column header(Active Employye) and data(Yes) but I keep receiving an error:
The expression “Active Employee eq ‘Yes'” is not valid. Creating query failed.
clientRequestId: 30c6a3ab-63ef-4e40-a67e-e430bd9730bc
serviceRequestId: 30c6a3ab-63ef-4e40-a67e-e430bd9730bc
Any Suggestions?
Hi,
i have 2 issues
first beeing Status EQ ‘DELIVERED’ creating an error saying status does not exist. removing the filter seems to have it working tru.
second, beeing that darn “apply to each” issue, for some reason its stops saying “A value must be provided for item.
clientRequestId: 85cb1274-1e0d-4496-ab50-4269d9a6381b”
{
“message”: “A value must be provided for item.rnclientRequestId: 85cb1274-1e0d-4496-ab50-4269d9a6381b”,
“status”: 400,
“error”: {
“message”: “A value must be provided for item.”
},
“source”: “excelonline-eus.azconn-eus-002.p.azurewebsites.net”
}
i have triple checked my output to be the correct one as well as id, name, current item.
even tried tweeking Select nothing come along the issue havent manage to get 1 complete run yet so far.
thanks for all the help.
Did you get it to work ? I am getting the same error.
“A value must be provided for item.
clientRequestId: bf609545-bd99-4cdf-9bda-449fadb726a9”
Hi All, the ‘Add row into table’ has reported a Unsupported Media Type under Apply to Each.
Error: Action ‘Add_a_row_into_a_table’ failed
Error Details: The request entity’s media type ‘text/plain’ is not supported for this resource.
Any ideas.
Thanks
Error Details
Start time
Oct 12, 06:59 PM (9 seconds ago9 sec ago)
Duration
00:00:05
Error
Action ‘Create_table’ failed
Error Details
The argument is invalid or missing or has an incorrect format.
clientRequestId: 2d6010ca-7e82-499c-8902-855c17718a63
serviceRequestId: 1bbdd434-fa8a-4c2b-bd66-c09336137eb0;041f962a-6af8-4532-9383-448948b7272a
I have an issue in create a table please guide me
$A1:$ outputs(‘A1_Notation_Mapping’)[sub(length(outputs(‘Array_of_col’)),1)]
first(split(body(‘Create_CSV_table’),decodeUriComponent(‘%0D%0A’)))
but not working
Hi Bijay,
good sharing…thanks a lot.
yet while i follow the steps, come out with error:
Error
Action ‘Create_table’ failed
Error Details
The resource could not be found.
clientRequestId: aa025608-6180-491d-8963-fef8473a048b
serviceRequestId: 09892b2c-d63b-4332-96b6-299179bd3bfe
do you have any idea on this? thanks
I think solution which is present here ….. it is one big s.h.i.t
outputs(‘A1_Notation_Mapping’)[sub(length(outputs(‘Array_of_col’)),1)]
Flow has no errors but is failing at create table stage. Managed to correct the mistakes in the expression with help from this thread but no more.
getting a value must be added in add a row into table action
getting error in add row into a table action stating that a value must be added
anyone getting this error:
The request is forbidden by Graph API. Error code is ‘AccessDenied’. Error message is ‘Could not obtain a WAC access token.’.
clientRequestId: 7d8d0012-0318-4461-84e4-4145f142111f
serviceRequestId: cfb00024-2d7d-4323-8796-82385e3814d9
Hi,
Can we also do this for a document library? I am trying to mimic the export to excel function using power automate for a document library. I am not sure which connector to use as Get items only works for Lists. Any suggestions would be appreciated. thanks
Create CSV table error:
BadRequest. The ‘from’ property value in the ‘table’ action inputs is of type ‘String’. The value must be of type ‘Array