Use Graph API Get Items on a SharePoint List with >5000 items

Question

I'm trying to get items on a SharePoint list >5000 items with Graph API, I get this error "The request is unprocessable because it uses too many resources":

{'error': {'code': 'notSupported',
  'message': 'The request is unprocessable because it uses too many resources',
  'innerError': {'code': 'tooManyResources',
   'date': '2023-04-25T05:40:06',
   'request-id': 'xxx',
   'client-request-id': 'xxx'}}}

Filter added in query params and result should be aournd 10 items only, request url:
"https://graph.microsoft.com/v1.0/sites/sharepoint_id/lists/list_id/items?expand=fields(select=ID, LinkTitle, field_33, result, field_4, time, remark, field_24)&$filter=fields/result eq 'Y'"
Tried to add {'Prefer': 'HonorNonIndexedQueriesWarningMayFailRandomly'} in header still get same error.
I have to keep this list >5000 items, is there any solution to this? Thanks...

Answer 1

Hi @Gablic

I'm glad to hear you solve the problem ,if you have any issue about SharePoint, you are welcome to raise a ticket in this forum.

By the way, since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others." and according to the scenario introduced here: Answering your own questions on Microsoft Q&A, I would make a brief summary of this thread:

[Use Graph API Get Items on a SharePoint List with >5000 items]

Issue Symptom:

Unable to get SharePoint List items over 5000, return error The request is unprocessable because it uses too many resources

Solution:

First remove 'Prefer': 'HonorNonIndexedQueriesWarningMayFailRandomly' in header, then go to list settings -> Indexed columns -> Create a new index -> add all the fields you need in $filter. If your list is large, it may take some time to finish indexing.

---

You could click the "Accept Answer" button for this summary to close this thread, and this can make it easier for other community member's to see the useful information when reading this thread. Thanks for your understanding!

Answer 2

Found solution for my case, record it here as may help others...
The key was I didn't fully understand what does this param do in request header:
{'Prefer': 'HonorNonIndexedQueriesWarningMayFailRandomly'}
Remove it will throw a different error:
"Field 'result' cannot be referenced in filter or orderby as it is not indexed. Provide the 'Prefer: HonorNonIndexedQueriesWarningMayFailRandomly' header to allow this, but be warned that such queries may fail on large lists."
How it works:
When you include $filter in query param (in my case "....$filter=fields/result eq 'Y'") to get list items, this field(s) must set as index column in list setting otherwise you will get the error above. You can add 'Prefer': 'HonorNonIndexedQueriesWarningMayFailRandomly' in header to bypass it, but this only works when you have a list <5000 items (or whatever the threshhold is), >threshhold will result in the "too many resources" error i got at first.
To solve it, first remove 'Prefer': 'HonorNonIndexedQueriesWarningMayFailRandomly' in header, then go to list settings -> Indexed columns -> Create a new index -> add all the fields you need in $filter. If your list is large, it may take some time to finish indexing.

Answer 3

Hi @Gablic If you are trying to fetch a large list, you will need to use pagination, or you may receive list view threshold error. If you're using MS Graph Service Client (C#), please take a reference of the below tutorial:

• https://learn.microsoft.com/en-us/graph/sdks/paging?tabs=csharp demo1:

List<ListItem> Allitems = new List<ListItem>();  
  
            var items = await graphClient.Sites["abc.sharepoint.com,5ae427d1-c859-4c58-97c4-e20ad27b850b,352d1542-3d0a-4f99-a529-723e41552082"]  
                .Lists["40418c29-06c3-442e-9d01-972d4e9f0129"]  
                .Items  
                .Request()  
                .Top(100)    
                .GetAsync();  
              
            var i = 1;  
            var pageIterator = PageIterator<ListItem>  
                .CreatePageIterator(graphClient, items, (e) =>  
                {  
                    Console.WriteLine($"{i++} {e.WebUrl}");   
                    Allitems.Add(e);  
                    return true;  
                });  
  
            await pageIterator.IterateAsync();  

demo2:

var totalitems = new List<Microsoft.Graph.ListItem>();  
  
  
            var items = graphClient  
                .Sites["abc.sharepoint.com,5ae427d1-c859-4c58-97c4-e20ad27b850b,352d1542-3d0a-4f99-a529-723e41552082"]  
                .Lists["40418c29-06c3-442e-9d01-972d4e9f0129"].Items  
                .Request()  
                .GetAsync().Result;  
  
            totalitems.AddRange(items.CurrentPage);  
            while (items.NextPageRequest != null)  
            {  
                items = items.NextPageRequest.GetAsync().Result;  
                totalitems.AddRange(items.CurrentPage);  
            }  
  
            Console.WriteLine(totalitems);  

Pagination using CSOM:

• https://piyushksingh.com/2016/12/04/query-listitems-in-batches-sharepoint-online/ More references:
• https://natechamberlain.com/2018/05/21/how-to-resolve-sharepoint-list-view-threshold-error-the-view-cannot-be-displayed-because-it-exceeds-the-list-view-threshold/
• https://support.microsoft.com/en-us/office/manage-large-lists-and-libraries-b8588dae-9387-48c2-9248-c24122f07c59?ui=en-US&rs=en-US&ad=US

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.