0

I need to query items in a given folder and all of its children in a document library with as few APIs as possible. The ideal solution seemed to be getItems with Scope='RecursiveAll' and a FolderServerRelativePath. However the API below returns an SPQueryThrottledException for a specific document library on Sharepoint Online, and I can't figure out the reason.

POST <web-url>/_api/Web/Lists(<list-id>)/getItems?$select=*,FileRef

POST BODY:
{
    "query": {
        "odata.type": "SP.CamlQuery",
        "ViewXml": "<View Scope='RecursiveAll'><RowLimit>1</RowLimit></View>",
        "DatesInUtc": true,
        "FolderServerRelativePath": {
            "DecodedUrl": "<subfolder's-server-relative-path>"
        }
    }
}

Some observations:

  • The subfolder has 4 files and no folders in it.
  • Getting items under any folder in the document library fails with the same error
  • The document library has around 6.5k items (folders+files) in it
  • Scope='Recursive' also fails, but 'Default' and 'FilesOnly' work fine
  • The same query worked for another document library with 100k files

What's the problem with the query? How can I fix it? If it's impossible to fix, are there any other APIs that can recursively query items in a folder?

  • Hi, have you tried changing the view scope to 'Recursive'? link Right now, it instructs SharePoint to return all list items and all list folders from the list which is having 6.5 k items, i.e. exceeding the threshold limit of 5000. – UBK May 3 at 16:20
  • @UBK 1. Yes, and it also fails. 2. Shouldn't the request only return files & folders in the FolderServerRelativePath? 3. Since I set the RowLimit to 1, I expected the query to shortcut and return a file in the folder, but it didn't work – user3058273 May 6 at 2:10
0

Replace the "FolderServerRelativePath" with "FolderServerRelativeUrl". The following code for your reference.

<script src="https://code.jquery.com/jquery-1.12.4.min.js" type="text/javascript"></script>
<script type="text/javascript">
$(document).ready(function(){
    var viewXml = "<View Scope='RecursiveAll'><RowLimit>1</RowLimit></View>";
    getListItems(_spPageContextInfo.webAbsoluteUrl,'DL0506',viewXml,'/sites/lz/DL0506/folder1').then(function(data){
         var items = data.d.results;
         for(var i = 0; i < items.length;i++) {
             console.log(items[i].FileRef);
         }    
    }).fail(function(error){
        console.log(JSON.stringify(error));
    });
});
function getListItems(webUrl,listTitle,viewXml,folderUrl){    
    var url = webUrl + "/_api/web/lists/getbytitle('" + listTitle + "')/getitems?$select=*,FileRef"; 
    var queryPayload = {  
               'query' : {
                      '__metadata': { 'type': 'SP.CamlQuery' }, 
                      'ViewXml' : viewXml,
                      "FolderServerRelativeUrl": folderUrl 
               }
    };
    return $.ajax({
           url: url,
           method: "POST",
           data: JSON.stringify(queryPayload),
           headers: {
              "X-RequestDigest": $("#__REQUESTDIGEST").val(),
              "Accept": "application/json; odata=verbose",
              "content-type": "application/json; odata=verbose"
           }
     });
}
</script>

If you want to get all the files along with associated list items from a specific folder, we can using the REST API below to achieve it.

/_api/web/getfolderbyserverrelativeurl('<serverrelativefolderurl>')/files?$expand=ListItemAllFields
  • 1. Replacing "FolderServerRelativePath" with "FolderServerRelativeUrl" didn't work. 2. I want to get all list items under a folder and its subfolders, however I don't need their file metadata. – user3058273 May 6 at 3:17
  • Did you use /_api/web/getfolderbyserverrelativeurl('<serverrelativefolderurl>')/files? – LZ_MSFT May 6 at 8:05
  • Yes, it works, but I'd rather use GetItems with the view-scope set to 'Default' since it gets the items (files & folders) in one API – user3058273 May 6 at 9:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.