Forum Discussion
SharePoint list query alternative or optimization
hjaf FYI that I finally made a video to describe this approach, and am adding it here for others that may find this post. It also gets the count of items and makes the right number of API calls.
Get SharePoint List Data with Power BI ... Fast - YouTube
Also, a reminder to mark one of these as the solution.
Regards,
Pat
Replace after the ? with the following
?$skipToken=Paged=TRUE%26p_ID=30&$top=5000", [Headers=[Accept="application/json"]]))
I would make a list with = {0, 5000, 10000, 15000, 20000} or something more dynamic for when the list gets bigger. Convert that to a table and add a custom column that concatenates the list value in place of the 30 in red text above. Then expand the table to get all your data.
Sharepoint Lists can be slow. This approach has saved much refresh time.
If this works for you, please mark it as solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
Ok, this is promising!
I successfully got the first 5000 item in a breeze, however, the way you suggest iterating / paging the query is somewhat unclear to me.
In my case, the lowest ID starts at 5495, item # 5000 has ID around 80k. The "density" of the id range varies because of creations and deletions over time, and that sharepoint doesn't immediately re-use ID's. I also noticed that sharepoint provides an odata.nextLink value for the next 5k items, maybe I can somehow create an iteration that continues until this property is not appearing. Right now this appears to happen at about 120 000 (even though the item list contains just around 20k items).
I can probably use a list as you suggested and have the list stop at 200 000 in 5000 increments, but not exactly sure how to make a query that iterates around the this list. can you provide an example?
PS:
I had to add "/items?" in the uri so the current uri for web.contents() in my case is as follows, could you use these uris in your answer so that when I tag the post as a solution, other people would have a better understanding 🙂
= Json.Document(Web.Contents("https://<TennantShortName>.sharepoint.com/sites/<SiteShortName>/_api/Web/Lists/GetByTitle('<List Title>')/items?%24skiptoken=Paged%3dTRUE%26p_ID%3d<StartAtId>&%24top=5000" , [Headers=[Accept="application/json"]]))
or using list guid the url looks like this :
https://<TennantShortName>.sharepoint.com/sites/<SiteShortName>/_api/web/lists(guid'<item list guid>')/items?%24skiptoken=Paged%3dTRUE%26p_ID%3d<StartAtId>&%24top=5000