Skip to main content

Notifications

Community site session details

Community site session details

Session Id :
Power Automate - General Discussion
Unanswered

Automate the export of a SharePoint List - HELP!

Like (2) ShareShare
ReportReport
Posted on 27 Aug 2024 14:22:50 by 4
Hi,
 
I am really struggling to get PowerAutomate to do what I need here!
 
I have a SharePoint site with 2 lists...what I want to do is:
 
- once a week export those lists to Excel
- save the 2 files with todays date
- save the excels to a second SharePoint/Teams site
 
I am aware that you cannot Automate the simple Export to Excel option (which really annoys me, that would be too easy!)
 
 
so this is my flow in Automate:
I dont think I want the "For Each" part in there....but it gets added automatically as soon as I add a particular list field in the Create CSV table item (its a person name/email field if that matters). This for each seems to prevent the flow from working.
 
Any help would be much appreciated!
Categories:
  • Suggested answer
    WillPage Profile Picture
    1,907 on 28 Aug 2024 at 02:33:33
    Automate the export of a SharePoint List - HELP!
    The reason it's creating that Apply to Each is because your person column allows multiple selections. That makes it an array. You will need to process that array into a string before you can use it in the Create CSV table action.

    Step 1: Add a Select action. In the input of the Select, choose the column name for the person column. Not any of the individual properties, just the parent column
    Now in the Mapping section of the select, click the icon on the right hand side to switch from Key-Value mode to JSON mode. Then use the expression editor to put the expression item()?['Email'] in the box.
     
    Step 2: Add a Join action. This is also in Data operations category. Put the output of the Select in there and choose a character to join with, such as comma.

    Step 3: Instead of the person column in your CSV table action, put the result of that Join there.
  • Suggested answer
    AnthonyAmador Profile Picture
    2,544 Super User 2025 Season 1 on 27 Aug 2024 at 19:43:03
    Automate the export of a SharePoint List - HELP!
    Hi, 
     
    Yes, you have to remove the "for each" Try to declare the value from an expression instead of directly selecting the dynamic content, so it doesn't create the "for each" automatically.
     
    You just enter this as an expression, item()?['Email'], or replace 'Email' with the name of your column, or select it but inside the expression. 
     


     
    Let me know if that helps. 
    Anthony. 
  • Suggested answer
    David_MA Profile Picture
    10,790 Super User 2025 Season 1 on 27 Aug 2024 at 19:10:56
    Automate the export of a SharePoint List - HELP!
    You are on the right track with your flow. However, you need to remove the Apply to each action. In its place, use a Select action. Here you define what fields from the list you want to export. Then pass the output of the Select action to the Create CSV table action. And if you really want to create an Excel spreadsheet, you can review this tutorial on YouTube: https://youtu.be/RB_ySjhm9Sg?si=y3utKKXmnwTXieYA.
     
    Also, do you realize that when you use the export to Excel feature in a SharePoint, it adds a connection reference to the spreadsheet. To refresh the data, you just have to go to the data tab and choose to refresh the data. Note: if you are saving the spreadsheet on another SharePoint site, the people on the other site will need to have at least view permissions on the source site and you will need to use a public view when you export the data to Excel. This way, you would just need to train users how to refresh the data. If you have Power Automate desktop, you could automate the refresh rather than exporting it every time.

    With regard to the e-mail field causing it to create an apply to each action, is only one e-mail address stored in the field or multiple? If only one value should be in the field, update the field settings in SharePoint and choose no for Allow multiple selections:


    In addition, in the Select action, be sure to choose the Email option that shows as Email Email so it returns the e-mail address. The choice from the dynamic content with just the field name is an array of data, which is why it is putting it into the Apply to each.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Understanding Microsoft Agents - Introductory Session

Confused about how agents work across the Microsoft ecosystem? Register today!

Warren Belz – Community Spotlight

We are honored to recognize Warren Belz as our May 2025 Community…

Congratulations to the April Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 146,745 Most Valuable Professional

#2
RandyHayes Profile Picture

RandyHayes 76,287 Super User 2024 Season 1

#3
Pstork1 Profile Picture

Pstork1 66,091 Most Valuable Professional

Leaderboard
Loading started