How to create an excel spreadsheet dynamically from a sharepoint list using power automate?

[SMW1]

Hi there,

I have a sharepoint list with over 700 rows of data for multiple suppliers (supplier data is in one of the columns). How to create an excel spreadsheet for each supplier from the sharepoint list and store the excel files in a documen library using power automate ? It will be a manual trigger. 

When I started off using manual trigger, then 'get items', I can't apply the dynamic content of 'supplier' under ODATA filter query. I'd appreciate any advice on how to do it end-to-end. Thank you.

[David_MA]

Watch this YouTube tutorial to learn how to create the spreadsheets: https://youtu.be/RB_ySjhm9Sg?feature=shared. Then:

1. Use a get items action to get all of the items in your list.
   1. You will need to enable paging on the get items action since you have over 100 items.
2. Add an Apply to each for the items returned.
3. In the Apply to each add another Get items action and use a query filter to get items where the supplier eq supplier from the current item.
   1. You may need to enable paging again if any one supplier has more than 100 items.
4. Then follow the steps from the YouTube tutorial to create the spreadsheets, which you will also put into the Apply to each action.

[SMW1]

HI David,

Thank you so much for your help. It seems to work half way but failed in the second part. I had to compose an output because there are 60 different columns in the table. I wondered if I've done something wrong in the flow.

 

Here's the error message:

{"statusCode":400,"headers":{"Cache-Control":"no-store, no-cache","Pragma":"no-cache","Strict-Transport-Security":"max-age=31536000; includeSubDomains","x-ms-request-id":"15315f54-2e06-4d40-90ab-cb9d620174c1","X-Content-Type-Options":"nosniff","X-Frame-Options":"DENY","Timing-Allow-Origin":"*","x-ms-apihub-cached-response":"true","x-ms-apihub-obo":"false","Date":"Tue, 27 Feb 2024 22:17:33 GMT","Content-Length":"232","Content-Type":"application/json","Expires":"-1"},"body":{"status":400,"message":"Invalid request\r\nclientRequestId: 15315f54-2e06-4d40-90ab-cb9d620174c1","error":{"message":"Invalid request"},"source":"excelonline-ukw.azconn-ukw-001.p.azurewebsites.net"}}

I'd be grateful if you can spot what's gone wrong and how to troubleshoot. Thank you.

best wishes,

SMW

[David_MA]

I would double-check the column names are separated correctly with a comma, and that the number you have would actually create columns through A1:BH1. Make sure the column names are not excessively long, that they don't contain special characters, and that you don't have a space after the comma that separates the columns.

[SMW1]

Thanks David. I've managed to shorten the column names. To overcome the multiple loops of 'Apply to each', I've used 'Select' to map out the column names against the dynamic contents of my sharepoint list.

Now I'm stuck with another new problem, where flow failed at the stage of create file. All I'm getting is blank excel files populated with the correct headers but the actual data failed to pull across from my sharepoint list.

Here's the error message -  "status": 400,
"message": "Save Conflict\n\nYour changes conflict with those made concurrently by another user. If you want your changes to be applied, click Back in your Web browser, refresh the page, and resubmit your changes.\

I've refreshed the sharepoint list, and the flow itself, but it still failed. I'd be really grateful if you can suggest anything to troubleshoot this problem. Thank you.

best wishes,

See Mun