Qlik Community

Ask a Question

Connectivity & Data Prep

Discussion board where members can learn more about Qlik Sense Data Connectivity.

Announcements
Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
ChristopheG
Contributor II
Contributor II

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

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.

Labels (6)
2 Replies
andoryuu
Creator III
Creator III

Thank you, this was super helpful!

caiogil1
Contributor III
Contributor III

Thank you, helped me a lot!

I hope to learn a lot here and help if I can