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.
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
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. Out audiences are from the United States, Canada, United Kingdom, Australia, New Zealand, etc. For my expertise knowledge and SharePoint tutorials, Microsoft has been awarded a SharePoint MVP(8 times), check out My MVP Profile. 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
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)]
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 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.