Handling up to 100k SharePoint records

[mrQ]

Dear community,

I am aware that this might be a challenging question, and I would really appreciate your support.

The direct question towards you guys can be found at the bottom - in case the intro text is too long 😁

My flow will be kicked off with an HTTP request containing up to 500 objects in an array.

Within the flow, I need to check each received object against a SharePoint list containing up to 100k items. Meaning, I need to check whether the property "id" already exists in the SharePoint list.

With pagination in SharePoint I can only get 200 rows at once. With 100k items, this alone would be already 500 individual API calls to SharePoint to get all of them. Once I have all items, I can use the filter action to drill down and check whether the id already exists or not. Based on the outcome, I would either need to create a new item or update the existing one - resulting in another call PER object received.

With 100k items in SharePoint and 500 objects received, that would result in 1000  individual API calls towards SharePoint (500 to get all items + 500 for each object). If I use batching, I can break that down to 525 calls (as I can batch 20 items per call).

To make things even worse, I may receive multiple batches of 500 objects. In the worst case, up to 10x on a given day - once per month.

10 x 500 objects = 5000

10 x 20 batches = 250

total = 5250 individual API calls

My questions to you

• How can I handle 5250 individual API calls towards SharePoint without getting throttled?
• Is there a better/other way to reduce those calls? 

Some additional information:

• I am using Logic Apps, not Power Automate
• If somehow possible, I don't want to involve SQL servers as that would require premium licenses for users. The mentioned SharePoint list is being used in a Power App
• All SharePoint calls are made with an app-only account through the HTTP connector

[lbendlin]

You must be the favorite customer of the 429 team...

If somehow possible, I don't want to involve SQL servers

MySQL? SQLite? MariaDB? PostGreSQL ?  Access (shudder) ?

Your question screams "database" but your limitations are somewhat understandable.  Not sure if you have tried it yet but you could think about using ODATA queries against your sharepoint list to avoid pulling the data.

[mrQ]

Hi @lbendlin 

I appreciate your response.

Yeah, I am aware of being their favorite customer 😁.

The databases you mentioned all involve premium licenses, which is why I'd like to avoid it if somehow possible. With odata I would still make a call per query - resulting in even more individual calls. Or did I miss something?

I was even thinking of pulling the data ONCE and writing it in a SharePoint multitext field (not even sure about the length...). By the time the second flow gets kicked off, it would just pull the text from the multitext field. Meaning, instead of 500+ calls per flow, I could reduce that to 1+. Seems like an ugly solution, if even feasable.

What do you think? Any better ideas? I'm really open towards crazy solutions - as long as it helps my case 🤣

[lbendlin]

I don't know if there is a size limitation for ODATA query parameters but let's assume you can craft a single ODATA query that checks your list against all 500 objects, all at once. Then you can use the result to decide if you want to update or insert.  That means best case scenario would be 501 calls.

[takolota]

Several questions…

-What does your organization do? Who are they?

-Why only 200 SP items per call? I thought the max was 100k for standard license users, & maybe as low as 5000 for lesser licenses.

-Is that limit different in a Power Automate flow?

-Would using SQL help alleviate this?

Like let’s say there is a way to pass data with a Power App to & from SQL without any premium connectors, would that help alleviate this problem?

[mrQ]

Hi @lbendlin 

Can you elaborate on how you would do that? 

I would need to check whether ID already exists in the SharePoint list and if so, update the item. Otherwise, create.

Are you thinking of just querying the ID of each object in one single call?

[mrQ]

Hi @takolota 

You can get 100k items in a SharePoint list, but not all at once. You have to do that with something called "paging", meaning, it gets the first (max) 200 items in a list before it goes on to the next page and gets the next 200, until 100k items reached. However, each of those 200 chunks is one additional API call.

And yes, if without a premium license we could get data from SQL, that'd be great. Though, not with Dataverse 4 Teams (in case you were implying to use Azure API Management and export the connector...).

[lbendlin]

Yes, pretty much. Similar to 

Select Id from list where id in (incoming message) 

The based on the presence or absence of the id in the result set you perform an update or an insert.  

Note: some query languages already support an UPSERT command.

[mrQ]

Hi @lbendlin 

Thanks - I give it a try and let you know.

[takolota]

If anyone needs a faster & more efficient set-up for something like this, there is now this template for batch creating & updating items in a SharePoint list: https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Batch-Update-Create-and-Upsert-SharePoin...