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!
Advertisements
Try Out Code2care Dev Tools:

Advertisements



Android Java Linux Microsoft Google Python macOS Notepad++ Microsoft Teams CSS PHP SharePoint Html Linux C Programs Bootstrap jQuery Sublime Android Studio Facebook Eclipse WhatsApp News MySQL Json HowTos JavaScript FTP S3


Advertisements


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, Tutorials, Trouble-Shooting steps, Video Tutorials, Code Snippets, How Tos, Blogs, Articles, etc.
🎉 We are celebrating the 10th years of Code2care! Thank you for all your support! We hope we made a difference.
We strong support Gender Equality & Diversity.

We stand in solidarity with Ukraine - Make a donation to UNHCR - https://donate.unhcr.org/in/en-in/ukraine-emergency