How to get more than 5,000 rows from a SharePoint list into a PowerApp with Microsoft Power Automate

[@naioption]

I love your solution, A lot of thanks !!!

[@PaulieM]

Thank you! Did you try the new version I made? It is much better. Faster and easier to implement.

[@naioption]

@@PaulieM Yep, I like a new version very much. I just stared to follow your step, Thanks for unlock the limit and ideas :)

[@shaunvanhuyssteen7966]

You absolute legend! Thank you so much for this break down.

[@PaulieM]

Ha ha, thanks Shaun, glad it helped.

[@falconvalley2237]

Brilliant explanation... spot on.

[@upputurianjamma6041]

Tqu for giving such solution Sir you have any viedo or any blog which discuss in detail way of using rest api flow

[@ambarish360]

Thanks a lot @paulie , this is what I was asked in my interview. 👌 If any videos to pull that array output to Powerapps and show, pls lemme know

[@DMK573]

Hi Paul, This is an excellent video and the explanation of the same task I was interested in learning. I was able to download and run your version 1 (Sequential) flow and it works fine. But when I have created my own flow without using Power App (just a trigger manually), and also I excluded response action in it as well. So my flow works only on the first set (5000) and then stops it. in other words the Do Until breaks. So I decided to add "response" action similar to yours. But I don't have the premium license and I was not able to do so. If I am not using Power App portion, do I still need the response action? if not which other action could be used in my flow to continue with rest of the items (about 3000) ? Any advice and recommendations would be really appreciated. Thanks so much in advance.

[@PaulieM]

Just use a compose action

[@DMK573]

@@PaulieM Thanks so much for your prompt reply!. I tried with a compose action outside the Do until loop per your suggestion. And the input value was arrayItems. But it did not work. Then I tried the compose action (within the scope of Do until) at the end, and it did not work either. I am not sure if I did use the correct input to Compose action. Any advice would be appreciated. Thanks!!

[@जयभारती-प2च]

Really appreciate this video, Kindly guide me in my situation I have a Project list and Task list Each Project have several tasks , I have to display the Project and Task value in the Combo box on the PowerApps form , How I can achieve my goal , Your support is really good

[@JohnHegs007]

Great way to solve the problem. Is there any limit on the number of parallel jobs is can run ?

[@PaulieM]

Hi John, I am not sure, but I think it is 50 parallel branches at one time: docs.microsoft.com/en-us/power-automate/limits-and-config But the documentation, is not really clear. So, conceivably you could get 250,000 rows in one iteration.

[@AndrewSurblys]

You are GOD !!! Thank you so much.

[@diegoendo6003]

Excellent video. One question, since the "response action" is PREMIUM how can I replace this actions and get my data to powerapps

[@PaulieM]

When you are using it to respond to PowerApps, the premium does not apply.

[@dinoatcharterdotnet]

If I create multiple document libraries in a sharepoint site, will I have sync all of them to my desktop? and if so, will they be in all different folders? is it easy to edit files (Like InDesign files) cross-document libraries?

[@sreekanth3115]

Its very helpful, Could you Please share me the Flow

[@jamesmorrison4713]

Really appreciate this video. Question: If my SharePoint list is greater than 5000 rows, but my http QUERRY request returns less than a 100 rows, do I know need to loop http request? I.e. is the 5000 limit based of the number of rows returned or is it based on the number of rows queried?

[@PaulieM]

James Morrison it will keep looping in batches of 5,000 until there are no more rows left. The 5,000 limit is based on the maximum number of rows returnable on a single query from SharePoint. If your query returns less than 5,000 it will only iterate the loop one time.

[@jamesmorrison4713]

@@PaulieM Thanks

[@ulises0502]

Hello, I already made the flow and everything is correct, how can I bring the results obtained to my PowerApps application. I understand that I have to add the PowerAutomate action but I have not been able to get the destro results from the App. Thanks in advance.

[@PaulieM]

You need to load the results into a collection and then make that collection the datasource for a gallery component, kinda like this: ClearCollect(sampleCollection,'flowToGetSharePointItems'.Run())

[@ulises0502]

@@PaulieM and how can I generate the schema JSON?

[@PaulieM]

Ulises C get in touch with me via my blog and I will see if I can help you out.

[@ulises0502]

@@PaulieM thanks, I will contact you

[@diegocabralfigueiredo1771]

​@@PaulieM Boa noite. Muito obrigado pela ajuda. Conseguir fazer a automate e ele está executando normalmente. O problema que estou enfrentando é com relação a memória. Após o fluxo ser executado com êxito dentro do powerapps, consuma a consumir muita memória do navegador até dar o erro: "Código de erro: Out of Memory". Não sei como resolvar. Poderia me ajudar? Obrigado!!

[@amt27videos]

Can this powerautomate technique be used without powerapp user access directly to the sharepoint list?

[@PaulieM]

I guess so, it’s running under the scope of the user running the flow. But I’ve never tried it. I’m actually making a new version of this flow which is a bit simpler and a bit faster

[@PaulieM]

The new version is live now. Check it out, faster and much easier to implement.

[@amt27videos]

Thanks, I've tried the new version, really good and fast, thanks. I will using it for some large data sets... I need to try and prevent direct access of users to the SharePoint list if at all possible, which I understand is an issue for many Powerapps creators.

[@zaydarendse2812]

amazing thanks for sharing

[@pauladams3907]

Very useful - thank you so much for sharing! I'm working with adapting Version 6 for use with a PowerApp for a school. I'm almost there, but I'm stuck on the final step! After the union of the four arrays, the JSON code is displaying values from your SharePoint list (email, Furloughed etc). When I try and update the JSON, it gives me the error:  The schema definition for action with status code '200' is not valid. The schema definitions for actions with same status code must match. Can you give me a clue how to update the JSON to correspond to my SharePoint list? Best wishes.

[@PaulieM]

Sure, in the final "Combine Arrays and Respond" step, you need to click on "Show Advanced Options" and click "Generate from Sample". Paste in the output from one of the previous steps, such as "Loop through first quarter" and it will update the schema to match your data.

[@pauladams3907]

@@PaulieM I am SO grateful for the help! However, I'm still really stuck on the last step. I've put a recording together that is really short but demonstrates where I'm going wrong. Best wishes, Paul www.loom.com/share/1f9473002cba4efe8e5cab5ace3b9b97

[@pauladams3907]

Update: I think I've worked out the error. It seems that LookUp fields in my Sharepoint list were causing the problem. I've since avoided such fields. Thanks once again.

[@PaulieM]

Paul Adams well done! How many records are you dealing with? (Just curious to see how this method is being used).

[@pauladams3907]

Paulie M We’ve only just gone over 5000 records, and as such have been looking at Common Data Service but CDS seems to be extremely expensive for schools. As such, it’s brilliant that we’ve got a solution - this will save the school a lot of money.

[@pliwireframe573]

How do I connect the flow with PowerApps?

[@upputurianjamma6041]

Sir please provide the link step by step of api method to get the record I am new to flows I try to implement the api flow by I am getting errorrs Please sir provide the link of Ali method in detail

[@PaulieM]

Did you read the blog post? The details of the flow are there and you can download them

[@upputurianjamma6041]

Sir thank you for reply I didn't see any download option in that blog and I am only struct rest api approach in that Do until step inside compose in union expression can you please give me the expression in detail

[@upputuriyamini3121]

@@upputurianjamma6041 Sir please provide Union code It's very Important to run the flow

[@fibonacci9445]

Hi, need help please, I was able to replicate this and its running fine on my end, however its not working for other users of the app. I made sure they have permissions on the sharepoint list so im not sure what the problem is.

[@PaulieM]

Arjay Bernardo look at the flow run history for your first clue