Get folder contents of large lists using REST

If you have a large lost with more items than the list view threshold you may want to use folders to scope the contents.

It’s possible to get just the contents of a folder using REST before applying a filter and cheat the threshold. The trick is to do a POST and set the CAML query for the view in the body. Additionally set the FolderServerRelativeUrl parameter to the server relative URL of the folder.

Using fetch:

        fetch(
            _spPageContextInfo.webAbsoluteUrl + "/_api/Web/Lists/getByTitle('Big Big List')/getItems",
            {
                method: 'POST',
                headers: {
                    'Accept': 'application/json; odata=verbose',
                    'content-type': 'application/json; odata=verbose',
                    "X-RequestDigest": jQuery("#__REQUESTDIGEST").val()
                },
                credentials: 'same-origin',
                body: JSON.stringify({
                    query: {
                        "__metadata": { type: "SP.CamlQuery" },
                        ViewXml: '<View><Query><Where><Eq><FieldRef Name="BigField" LookupId="TRUE"/><Value Type="Lookup">' + id + '</Value></Eq></Where></Query></View>',
                        FolderServerRelativeUrl: "/MySite/BigBigList/SubFolder/2015"
                    }
                })
            })
            .then(response => {
                return response.json();
            })
            .then(data => {
                if (data.d &amp;&amp; data.d.results) {
                    data.d.results.map(item => {
                        console.log(item.Title);
                    });
                }
            })
            .catch(err => {
                console.log(JSON.stringify(err));
            });

SharePoint REST and Large Lists

As you may have discovered there is a limit on the size of lists in SharePoint.

Well that is not strictly true, the amount of items can be pretty large (30 million or so).  However, you may have received a message:

This view cannot be displayed because it exceeds the list view threshold (5000 items) enforced by the administrator.

The number of items a single view can return is set to 5000 items by default.

You may be returning what you think is way less than that but still get this pesky error.  So what do you do?

Well you could increase the threshold in Central Admin.  But you may not have access to this, or you may realise that this is limited for a reason, and changing such things ‘willy nilly’ is setting the table and pulling up a chair for Mr Cockup!

Better to tackle the issue at source.  So what can you do?

  1. Index your columns
    Yes – this is the first thing you should do if you foresee large numbers of items.  Identify which columns will slice and dice your data most efficiently and get them indexed before your list starts filling up.
  2. Design your Queries / Views
    Make sure that your views (these are ultimately CAML queries) use these indexed columns efficiently.  If you are not using an indexed column in your query then it’s pulling everything back from the container before querying the data.
  3. Utilise Containers
    The view limit is per container – a container can be a view or a folder.  This can be a case for using folders if appropriate.  Folders may be the anathema of what SharePoint is about(!) but here they may avoid some headaches.  Just remember when using Recursive and RecursiveAll in your queries that you’ll be losing their protection.

My query uses an indexed field but I still get the error!

The order matters.  If you are querying on more than one field and not using a compound index then the first value in your query will be used to query the database, following retrieval the remaining filters will be applied.  If this first query brings back more than 5000 items back from the database it will hit the threshold.  So if you’re using an AND put the filter with the most significance as the first AND parameter.

I want to get (much) more than the threshold back for my custom report web part…

You may require to pull back thousands of items for a one off report or such like and knowingly want to exceed for this purpose – e.g. a report.

Not to worry – REST to the REST-cue.

If you’re using REST calls to populate your data client-side you can encapsulate a calls within a single function for this type of requirement.  Here’s an example using jQuery deferred objects and promises.

function getBigList(listId, data, url, dfd) {

    // deal with empty parameters    
    dfd = dfd || $.Deferred();
    data = data || [];
    url = url || _spPageContextInfo.webAbsoluteUrl 
            + '/_api/lists/getbyid(\'' + listId + '\')/items?$OrderBy=ID&$top=5000';

    // make sure digest up to date (for long operations)
    UpdateFormDigest(_spPageContextInfo.webServerRelativeUrl, _spFormDigestRefreshInterval);

    jQuery.ajax({
        url: url,
        type: 'GET',
        aync: true,
        headers: {
            'Accept': 'application/json;odata=verbose',
            'X-RequestDigest': $('#__REQUESTDIGEST').val()
        },
        success: onSuccess,
        error: onError
    });

    function onSuccess(response) {
        if (response.d && response.d.results) {
            $.each(response.d.results, function (index, resItem) {
                data.push(resItem)
            });

            // check for more pages
            if (response.d.__next) {
                // there are more so recursively call with next page url (response.d.__next)
                // and pass the data and deferred object
                getBigList(listId, data, response.d.__next, dfd);
            }
            else {
                // now we have all
                dfd.resolve(data);
            }
        }
        else resolve([]);
    }

    function onError(error) {
        dfd.reject('getBigList: ' + getErrorMessage(error));
    }

    return dfd.promise();
}

The key here is that the parameter $top is used to specify the upper limit to what the call can return, additionally the results must be sorted by ID – the default, unqueried data.  If the $top number is exceeded then the first ‘page’ is returned along with a URL to the subsequent page.  This can be called recursively until all the data is received.

Call this in the following way:

getBigList(LIST_ID)
    .done(function (data) {
        $.each(data, function (index, dataItem) {
            //dataItem.stuff......
        });
    })
    .fail(function (msg) {
        console.log(msg);
    });

So by being mindful of what SharePoint is doing server-side and structuring our lists and queries thoughtfully we can get around some of the limitations in an efficient way.

Getting the ID of Content Editor & Script Web Parts the RIGHT way

Since SharePoint 2007 many people have been using Content Editor Web Parts to provide customisations in their SharePoint solutions.  This has always been a very powerful and simple way to achieve results, either by embedding fragments of CSS, JavaScript or HTML directly (not always ideal when the markup gets garbled on save!), or by referencing a hosted file stored elsewhere – in the Style Library for example.

Drawback

This is fine for quick and dirty adjustments, or UI enhancements with jQuery and the like.  But sometimes you might have written some JavaScript specific to a web part, but then find you need to have more than one instance of the same web part on the page.  You have your markup and JavaScript referenced from a single file, and add two instances of it to the page – and voilà! It’s not doing what it should be doing. All the IDs and classes you referenced are now duplicated!

Fiddlesticks!

Draw Forward

Actually, when any web part is added to the page it is given a unique ID by the framework.  This ID is the ID attribute of the web part’s container element. What if you could get a hold of this specific element? This is what you need to ensure you select the correct web part from your code and update the right parts of the page.

Luckily every script block in the page added via a CEWP or Script web part can be picked up while the page is loading. At this point in time we can get a reference to the web part container it resides in and, once the page has completed loading, return it. Then we will be ready to execute our web part initialisation armed with a reference to the right bit of the page to either inject content or make jQuery selections upon.

The following function can be added to the master page to perform this task when called upon.

function _initWebPartOnLoad(cbOnLoad) {
    closest(document.getElementsByTagName('script')[document.getElementsByTagName('script').length - 1], 'ms-WPBody', function (el) {
        if (el) {
            var fnName = 'startWebPart_' + el[0].id;

            window[fnName] = function () {
                cbOnLoad(el);
            }
            _spBodyOnLoadFunctionNames.push(fnName);
        }
    });

    function closest(el, classname, cb) {
        var newEl = el.parentNode.getElementsByClassName(classname);

        if (newEl.length)
            cb(newEl);
        else {
            el.parentNode && closest(el.parentNode, classname, cb) || cb();
        }
    }
}

So then, for our web part implementation, we call the above function from a script tag in our CEWP, or from a js file we have statically linked (in a CEWP or Script web part).

    function onLoadMyWebPart(webpart) {
        var $webpart = $(webpart);
        $webpart.append('<h1>My Web Part ID is ' + $webpart.attr('id') + '</h1>');
    }

    _initWebPartOnLoad(onLoadMyWebPart);

Notice a callback function is supplied. This callback is added to the array of functions that SharePoint will execute after the rest of the page has loaded.  When it is called the web part will be initialised, and it takes one argument – the web part element from the DOM!

So now you are ready to go – and you have reference to the correct instance of the web part.

Lovely.