Power Automate export SharePoint list to excel and send an email

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.