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

Gablic 21 Reputation points
2023-04-25T05:49:50.5766667+00:00

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...

Microsoft Graph
Microsoft Graph
A Microsoft programmability model that exposes REST APIs and client libraries to access data on Microsoft 365 services.
11,151 questions
SharePoint Development
SharePoint Development
SharePoint: A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.Development: The process of researching, productizing, and refining new or existing technologies.
2,770 questions
0 comments No comments
{count} votes

Accepted answer
  1. RaytheonXie_MSFT 32,876 Reputation points Microsoft Vendor
    2023-04-28T01:53:46.6533333+00:00

    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!

    1 person found this answer helpful.
    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Gablic 21 Reputation points
    2023-04-26T07:50:40.81+00:00

    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.

    0 comments No comments

  2. RaytheonXie_MSFT 32,876 Reputation points Microsoft Vendor
    2023-04-25T07:54:18.71+00:00

    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:

    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:

    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.