Skip to main content

Notifications

Community site session details

Community site session details

Session Id : Z+eHb91tekq5R8M7Z0P/Po
Power Automate - Building Flows
Unanswered

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

Like (0) ShareShare
ReportReport
Posted on 27 Feb 2024 10:40:19 by 38

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.

  • SMW1 Profile Picture
    38 on 01 Mar 2024 at 13:41:01
    Re: How to create an excel spreadsheet dynamically from a sharepoint list using power automate?

    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.

     

    SMW1_0-1709563999933.png

    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

  • David_MA Profile Picture
    10,790 Super User 2025 Season 1 on 28 Feb 2024 at 16:57:09
    Re: How to create an excel spreadsheet dynamically from a sharepoint list using power automate?

    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 Profile Picture
    38 on 28 Feb 2024 at 08:18:14
    Re: How to create an excel spreadsheet dynamically from a sharepoint list using power automate?

    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.

     

    SMW1_0-1709107563496.png 

    SMW1_2-1709108052911.pngSMW1_3-1709108150816.png

     

    SMW1_1-1709107933660.png

    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 Profile Picture
    10,790 Super User 2025 Season 1 on 27 Feb 2024 at 13:13:12
    Re: How to create an excel spreadsheet dynamically from a sharepoint list using power automate?

    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.

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