Power Automate - Building Flows

Export SharePoint list to Excel .xlsx format and save to SharePoint

Like (0) Share

Report

Posted on 20 Nov 2024 16:45:01 by axray

244

***PLEASE can someone help me?!***

Hi all,

I have been trying to build this flow for weeks now, and I'm losing my mind. I have followed every single YouTube video and article I can find, but nothing is working.

All I am trying to do is set up a daily backup of a SharePoint list to an Excel .xlsx file and save to SharePoint. I have successfully set this up to save as a .csv file, but I cannot seem to get it to work for an .xlsx file. It is maddening!

Can someone please help me? Specific steps/instructions would be greatly appreciated. I have managed to get one to work up until adding the rows into the table - it gives me an overlap table error, but I don't know why? I have attached a screenshot of the working .csv flow.

Thank you!

Categories:

Business Process Flows

WorkingCSVFlowSet...

All responses (23)

Answers (0)

axray 244 on 13 Jan 2025 at 18:03:44

Like (0)

Report

Export SharePoint list to Excel .xlsx format and save to SharePoint

Another version of the flow I tried - getting the same error, though. These flows both do everything perfectly, except adding the data into the rows in the file created... the headers are added perfectly, the table name is correct... I just can't get the rows added in. I've checked the data it's grabbing and I see it is pulling the information from the SharePoint list correctly into the fields, too. It just isn't writing any of it to the .xlsx file.
axray 244 on 13 Jan 2025 at 14:40:41

Like (0)

Report

Export SharePoint list to Excel .xlsx format and save to SharePoint

OK, I'm back... so, I was finally able to get the values populated on the flow before... but then I had to move the Excel file to a different SharePoint and it somehow broke everything, even though I pointed everything to the new/correct location. I even went as far as recreating the flow from scratch to point to the new location, but no dice. I don't understand what is going wrong. I have changed this thing so many times at this point, I have no clue what is going wrong. I have provided detailed screenshots below - hopefully those will help.

My most recent version is coming back with the below error. I have tried changing the apply to each to so many different things, but nothing is fixing it.

Action 'Add_a_row_into_a_table' failed: A value must be provided for item. clientRequestId: xxx

This is another I tried for the apply to each and add a row into a table values:
axray 244 on 18 Dec 2024 at 23:51:52

Like (0)

Report

Export SharePoint list to Excel .xlsx format and save to SharePoint

OK, so I decided to start deleting and replacing all the dynamic content values, since I've changed this flow so many times at this point - I'm wondering if something is just glitching within those values. I've been doing a few at a time, and so far, it seems like it might be working. Thank you to those who tried to help!
axray 244 on 18 Dec 2024 at 17:52:35

Like (0)

Report

Export SharePoint list to Excel .xlsx format and save to SharePoint

It is just blowing my mind that it is this hard to accomplish. The .csv flow was a piece of cake. Mind boggling. My boss and team really want separate .xlsx dated files, so I have to keep on trucking to see if I can get it done... thanks, though.
Suggested answer

David_MA 11,217 Super User 2025 Season 1 on 18 Dec 2024 at 17:20:40

Like (0)

Report
**PLEASE HELP!** Export SharePoint list to Excel .xlsx format and save to SharePoint

If you are having this much trouble, I would take a different approach. This isn't something I've had a need to do, but it should be possible by doing this:

Go to your SharePoint list and export it to Excel through the Export to Excel feature. This creates a connection within the spreadsheet to the data source (the SharePoint list).

Save the generated Excel file someplace that can be accessed by Power Automate.

Create an Office script to refresh the data sources in the spreadsheet and export the file to a .osts file.

You can then create a scheduled flow to refresh the data connections each day by running the Office Script.

Again, I haven't done this, but I did find a blog post outlining what I think should work for you: How to refresh all Excel workbook connections with Office Scripts and Power Automate - Stringfest Analytics
axray 244 on 18 Dec 2024 at 17:09:47

Like (0)

Report

**PLEASE HELP!** Export SharePoint list to Excel .xlsx format and save to SharePoint

This is so insanely frustrating. I did a test and changed the "Employee_x0020_Name" to "Employee Name" (as it is actually written in the Excel Table header) - and it wrote all items to that column. I was so excited - so I went through that entire list and changed them all to match the actual Excel table header names (removed all of the _x0020_ and such) - and now it is giving me the error message again:

"message": "A value must be provided for item."

},
axray 244 on 16 Dec 2024 at 16:24:34

Like (0)

Report

**PLEASE HELP!** Export SharePoint list to Excel .xlsx format and save to SharePoint

@Nived_Nambiar OK, I think you are on to something...

I changed all the left side field name values to match the dynamic content values (I had some without the _x0020_ and such) -- this time, it actually did pull the values and did populate a FEW of the fields in the rows... but now I don't know why it only populated 5 field values out of 37???

Here is one output:

{
"host": {
"connectionReferenceName": "shared_excelonlinebusiness",
"operationId": "AddRowV2"
},
"parameters": {
"source": "sites/azureadminprodxxx.sharepoint.com,201d966f-035b-4d4a-83c8-b14934605adb,4801cbc1-a211-4570-8a62-2feb9a6fe865",
"drive": "b!b5YdIFsDSk2DyLFJNGBa28HLAUgRonBFimIv65pv6GWsukmTfAqsQJWziCFnok1x",
"file": "%252fxx%2bDaily%2bReporting%2bData%2bArchives%252fDailyReporting_12162024.xlsx",
"table": "{BA1F7CAB-10FF-41AD-BA1D-C2F1X888A57E}",
"item": {
"Employee_x0020_Name": "Last, First",
"Team_x0020_Name": "NAME",
"Report_x0020_Date": "2024-12-09",
"Item_x0020_ID": "Non-Assignment Work",
"Item_x0020_Name": "Non-Assignment Work",
"Tasks": "OOO",
"Total_x0020_Hours": "8",
"Deliverables0": null,
"OData_x0025_x0020_Completed": null,
"OData_x0023_x0020_Requirements_x0020": null,
"OData_x0023_x0020_Requirements_x00200": null,
"OData_x0023_x0020_Requirements_x00201": null,
"OData_x0023_x0020_Test_x0020_Cases_x": null,
"OData_x0023_x0020_Test_x0020_Cases_x0": null,
"OData_x0023_x0020_Test_x0020_Cases_x1": null,
"OData_x0023_x0020_Requirements_x00202": null,
"OData_x0023_x0020_Requirements_x00203": null,
"OData_x0023_x0020_Cases_x0020_Create": 0,
"OData_x0023_x0020_Cases_x0020_Passed": 0,
"OData_x0023_x0020_Cases_x0020_Failed": 0,
"Total_x0020_Cases_x0020_Executed": "0",
"OData_x0023_x0020_PIV_x0020_Claims_x": null,
"OData_x0023_x0020_PIV_x0020_Claims_x0": null,
"OData_x0023_x0020_Automation_x0020_C": null,
"Comments": null,
"Defect_x0020_Log_x0020_Completed": false,
"Defect_x0020_Log_x0020_ID": null,
"Item_x0020_ID_x003a_Developer_x0": null,
"Item_x0020_ID_x003a_Development_": null,
"Modified": "2024-12-06T19:04:12Z",
"Editor": "NAME",
"Created": "2024-12-06T19:04:12Z",
"Author": "NAME",
"ID": 37000,
"Employee_x0020_Name_x003a_Employ": "Last, First",
"Employee_x0020_Name_x003a_ID": "156"
}
}
}

Out of all these, the only fields that populated in the Excel doc are: Tasks, Comments, Modified, Created, and ID. What am I doing wrong?
axray 244 on 16 Dec 2024 at 15:32:44

Like (0)

Report

**PLEASE HELP!** Export SharePoint list to Excel .xlsx format and save to SharePoint

@Nived_Nambiar how would I know if/where that is happening? Are you saying one of the dynamic content values is incorrect? Or the output is null? If the output is null (which it will be on some rows, for some of the values), will that make it bottom out?
Suggested answer

Nived_Nambiar 17,659 Super User 2025 Season 1 on 15 Dec 2024 at 14:02:50

Like (0)

Report

**PLEASE HELP!** Export SharePoint list to Excel .xlsx format and save to SharePoint

Hi @axray

Sorry - i could not respond back as there were some issues with my account.

So coming to your question- I think you have passed null values to some properties within json - may be would that be the reason why flow is getting failed.

Thanks & Regards,
Nived N

Stay connected:
LinkedIn | YouTube | Blogs

Was this answer helpful?
If yes, please mark it as the solution by selecting the checkbox in the discussion thread.
Your feedback motivates me to keep contributing. Thank you!
axray 244 on 13 Dec 2024 at 18:08:17

Like (0)

Report

**PLEASE HELP!** Export SharePoint list to Excel .xlsx format and save to SharePoint

Is this not possible to do? I can't find anything online that has worked and only one person here is responding.... maybe this is just impossible?