⚠️ 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.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!
- Move Copy Migrate SharePoint OneDrive files folders to different site collection location
- How to generate client id and secret to register SharePoint App with OAuth
- How to share SharePoint site or document with all users in organization
- Get-ADUser PowerShell - Get AD user details using email address
- error CAML Query containing special characters
- Access URL for SharePoint Tenant Admin Center (Online Office 365)
- Fix SharePoint Error - The Managed Metadata Service or Connection is currently not available. The Application Pool or Managed Metadata Web Service may not have been started
- SharePoint - The URL is invalid. It may refer to a nonexistent file or folder, or refer to a valid file or folder that is not in the current Web.
- How to delete SharePoint List Item programmatically using C#.Net
- How to Share Microsoft SharePoint Site with Users or Groups
- JSON column formatting to preview SharePoint Online file on mouse hover
- SharePoint List redirect user after submitting form NewForm.aspx
- See actual SharePoint error exception modify web.config
- SharePoint Server 2016 IT Preview Deprecated Removed features
- How to create SharePoint Document Library
- How to Get or Set SharePoint Document ID _dlc_DocId using PowerShell
- How to disable SharePoint subsite creation option for owners
- PowerShell - How to use Try Catch Finally blocks for error exception handling (Windows/SharePoint)
- SharePoint error - Your organization doesn't allow sharing with users from this domain. Please contact your IT department for help. (OSE403)
- [Solved] SharePoint Search Internal server error exception
- How to wrap column text in SharePoint Online Modern List Grid View using JSON formatting
- How to extend retiring SharePoint 2010 Workflows and continue with Office 365
- Changed AD user display name showing old name in SharePoint
- How to hide or remove quick launch left navigation from SharePoint Online Modern site page
- How to enable anonymous public access for SharePoint Online site collection, file, folder without login ?
- Read file from resources folder in Java project code - Java
- How to pass value to another Power Apps screen - PowerApps
- Prettify JSON in Notepad++ - NotepadPlusPlus
- How to add sleep in Powershell Script - Powershell
- airbnb website and mobile App is down worldwide - clear browser cache or reinstall app to fix error - News
- 86 Gmail keyboard shortcuts that you may find Advantageous - Google
- How to check if Java main thread is alive - Java
- Adding Sub Headings to Bootstrap Header tags - Html