Rest connector getting data from a sharepoint list with paging (Pagination)

ChristopheG — Thu, 23 Dec 2021 14:38:47 GMT

I want to post information on how to load items from a sharepoint list with paging. I have searched for some time how to get the paging working in a sharepoint list and I eventually find it out. So that is why I wanted to share this with the community on how you can do that.

Let me start with describing the issue:

The issue with sharepoint is that as a default it will load only 100 items even when you have more items in the list. You have actualy 2 solutions one being a temporary workaround if you have less than 5000 items and the other a permanent solution with pagination that will work even over 5000 items.

SOLUTION 1 (Less than 5000 items, workaround):

1. You can add in the request url $top=5000 which will grab 5000 items. However when you have more than 5000 items this will NOT work anymore

example url https://yoursite.sharepoint.com/_api/lists/getbytitle('SharePointList List')/items?$top=5000 with query header Name: Accept Value: application/json;odata=verbose

SOLUTION 2 (Permanent solution works even over 5000 items)

You can enable pagination where it will keep on doing requests until it reaches the end of the list for example if you would use top100 it will get the next url until it reaches the end of the list.

Setup:

1. Add an extra parameter in the request url to enable paging in SharePoint url: https://yoursite.sharepoint.com/_api/lists/getbytitle('SharePointList List')/items?$skiptoken=Paged=TRUE&$top=100

2. Enable pagination in the qlik rest connector and select Next Url with as parameter d/__next
Do NOT enable Look in header.

3. With query header Name: Accept Value: application/json;odata=verbose

4. Save the connection and give it a try

5. Note you can also use top=5000 it will then do a request per 5000 items.

Re: Rest connector getting data from a sharepoint list with paging (Pagination)

andoryuu — Thu, 11 Jul 2019 20:40:05 GMT

Thank you, this was super helpful!

Re: Rest connector getting data from a sharepoint list with paging (Pagination)

caiogil1 — Mon, 28 Sep 2020 22:48:35 GMT

Thank you, helped me a lot!

topic Re: Rest connector getting data from a sharepoint list with paging (Pagination) in Connectivity & Data Prep

Rest connector getting data from a sharepoint list with paging (Pagination)

Re: Rest connector getting data from a sharepoint list with paging (Pagination)

Re: Rest connector getting data from a sharepoint list with paging (Pagination)