Power Automate - General Discussion

Unanswered

Automate the export of a SharePoint List - HELP!

Like (2) Share

Report

Posted on 27 Aug 2024 14:22:50 by AF-27081412-0

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:

Process Advisor

All responses (3)

Answers (0)

Suggested answer

WillPage 1,907 on 28 Aug 2024 at 02:33:33

Like (2)

Report

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 2,544 Super User 2025 Season 1 on 27 Aug 2024 at 19:43:03

Like (1)

Report

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 11,036 Super User 2025 Season 1 on 27 Aug 2024 at 19:10:56

Like (2)

Report

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.

Community site session details

Automate the export of a SharePoint List - HELP!

Helpful resources

News and Announcements

Quick Links

Announcing the Engage with the Community forum!

🌸 Community Spring Festival 2025 Challenge Winners! 🌸

Warren Belz – Community Spotlight

Leaderboard > Power Automate - General Discussion

Featured topics

Product updates

Community site session details

Automate the export of a SharePoint List - HELP!

Helpful resources

News and Announcements

Quick Links

Announcing the Engage with the Community forum!

🌸 Community Spring Festival 2025 Challenge Winners! 🌸

Warren Belz – Community Spotlight

Subscribe to this forum!

Select categories

Leaderboard > Power Automate - General Discussion

Featured topics

Product updates