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.

power automate export list to excel
power automate export list to 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.

How power automate export list to excel
How power automate export list to excel

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.

How power automate export list to excel and email
How power automate export list to excel and email

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.

can power automate export sharepoint list to excel
can power automate export sharepoint list to excel

Now provide the flow name, and select Manually triggered Flow. Then click on Create.

can power automate export list to excel and email
can power automate export list to excel and email

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'
can Microsoft Flow export list to excel and email
can Microsoft Flow export list to excel and email

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.

can MS Flow export list to excel and email
can MS Flow export list to excel and email

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'))
How MS Flow export list to excel and email
How MS Flow export list to excel and email

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'))),',')
How MS Flow export list to excel and send email
How MS Flow export list to excel and send email

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.

How Microsoft Flow export list to excel and send email
How Microsoft Flow export list to excel and send email

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()
How Power Automate export list to excel and send email
How Power Automate export list to excel and send email

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',',')
Microsoft Flow export list to excel and send email
Microsoft Flow export list to excel and send email

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')))
MS Flow export list to excel and send email
MS Flow export list to excel and send email

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.

export sharepoint list to excel automatically using power automate
export sharepoint list to excel automatically using power automate

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.

export sharepoint list to excel automatically using Microsoft Flow
export sharepoint list to excel automatically using Microsoft Flow

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.

export sharepoint list to excel automatically using MS Flow
export sharepoint list to excel automatically using MS Flow

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.

How to export sharepoint list to excel automatically using MS Flow
How to export sharepoint list to excel automatically using MS Flow

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.

How to export sharepoint list to excel automatically using Microsoft Flow
How to export sharepoint list to excel automatically using Microsoft Flow

Once you open the excel file you can see the data get exported to the excel file from SharePoint list.

How to export sharepoint list to excel automatically using Power Automate
How to export sharepoint list to excel automatically using Power Automate

At last, we can check the outlook email, the email comes with an excel attachment.

How to export sharepoint list to excel using Power Automate
How to export sharepoint list to excel using Power Automate

This is how we can export the SharePoint list to excel file dynamically and send email.

Related Power Automate tutorials:

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.

  • Says this is not a valid expression
    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?

  • 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??

  • 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.

        • 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.

      • 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 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?

  • 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’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

  • >