SharePoint Online REST API not returning all list items and limit to only 100 rows


⚠️ PROBLEM

SharePoint Online REST API to get List items returns only 100 results.

A standard REST call to get all Projects, but it returns only 100 projects.

https://c2c.sharepoint.com/sites/SP/_api/web/lists/getbytitle('Code2Care Projects')/items


⚡️ REASON

The REST API has a limit set to return only 100 results by default to protect against developers inadvertently making a request that returns large result sets. The limitation is due to server side paging.

⛏️ HOW TO RETRIEVE MORE THAN 100 ITEMS USING REST ?

There are 2 ways,

✔️ Use $top parameter to override the limitation and specify how many items to return.

Below REST call example will return 500 results.

https://c2c.sharepoint.com/sites/SP/_api/web/lists/getbytitle('Code2Care%20Projects')/items?$top=500

NOTE - The SharePoint List threshold of 5000 applies to this and you cannot retrieve more than 5000 items. Specifying a value over 5000 will give error, Microsoft.SharePoint.SPQueryThrottledException - The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.

SharePoint REST List view threshold error.PNG
SharePoint REST List view threshold error

TIP - Use $skiptoken=Paged=TRUE with SharePoint Server 2013.

https://c2c.sharepoint.com/sites/SP/_api/web/lists/getbytitle('Code2Care%20Projects')/items?$top=500&$skiptoken=Paged=TRUE

⛔️ $limit does not work with SharePoint 2013.



✔️ Get 100 results at a time and keep appending. Check if next page exists by using __next.
if (response.d.__next)
{
	urlNext = response.d.__next;
	//Do something
}
If __next returns a value --> use the url which will return next set of items.
If __next is null --> it's the end of the result set and no further values.


⭐ Code with REST query to read more than 5000 items from SharePoint list or document library

var listUrl = _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle('Code2Care Projects')
/items?$select=ProjectID&$top=1000";
var response = response || [];  //create variable to store fetched list items

function GetProjectListItems(){
return $.ajax({
	url: listUrl,
	method: "GET",
	headers: {
		"Accept": "application/json; odata=verbose"
	},
	success: function(data){
		response = response.concat(data.d.results); //append results to response variable
		//Use __next to check if next set of items exist in the result or its null
		if (data.d.__next) { //logic to check if further results exist
			url = data.d.__next; //returns url to get the next set of items
			GetProjectListItems(); //call recursively to fetch results in bunch of 1000 items
		}
		$.each(response, function(index, listItem) {
			//store ProjectID and other column values and perform further actions like displaying
			arrayProjectID[index] = listItem.ProjectID;
		});
	},
	error: function(error){
		// code to handle error and perform any action
	}
	});
}

✌️ CONCLUSION

Use __next to check if next set of items exist in the result set and keep appending until you get __next as null. This will ensure you do not encounter the SharePoint List View Threshold limit of 5000 and get all items.

Using $top is a quick and easy solution, but will work only if the list has upto 5000 items and you are sure list/library will never reach the threshold limit.


Comments:

  • Why cant' we just increase the threshold for SharePoint 2013? The way we do it for list view threshold?
    11 Dec 2020 22:09:13 GMT
  • Does Graph API also has this limitation or I can query all list items using Graph query? I want to avoid the loop as I'm having about 100k+ files and folders in my SP 2013 document library.
    03 Dec 2020 13:00:30 GMT
  • Still getting only 100 results, tried using $top in the rest query but not working. I am using SharePoint Online. Any suggestions?
    29 Nov 2020 07:11:28 GMT
  • I have SP list with 9000 records, which will increase only by a few items every month, can I use $top=9000 in the REST call and get all together?
    23 Nov 2020 11:23:36 GMT
  • Further comments disabled!



Recent Posts:




Code2care is an initiative to publish and share varied knowledge in programming and technical areas gathered during day-to-day learnings and development activities.

Students and Software Developers can leverage this portal to find solutions to their various queries without re-inventing the wheel by referring to our easy to understand posts. Technical posts might include Learnings, Video Tutorials, Code Snippets, How Tos, Blogs, Articles, etc.