Working with large SharePoint lists

While working with large SharePoint list, you have probably encountered the “The attempted operation is prohibited because it exceeds the list view threshold” error. Let’s take a close look at it and discuss how to go around this issue.

Basic approach

PnPjs documentation claims that getting items from a list is one of the basic actions that most applications require. It’s hard to disagree with this statement.

The straightforward way of getting elements would be using the /items endpoint with the filter parameter. Either directly through REST API:

[GET] /_api/web/lists/getByTitle(‘Shipping Orders’)/items?$filter=InvoiceNo eq ‘01-2021A’

Or via PnPjs library:

await sp.web.lists.getByTitle("Shipping Orders").items.filter("InvoiceNo eq ‘01-2021A’").get();

It works as expected until the list overgrows, and filtering requires too much processing power.

How SharePoint does it?

A user can easily filter a large list manually:

In this case, SharePoint creates a request:

[POST]

/_api/web/GetListUsingPath(DecodedUrl=@a1)/RenderListDataAsStream?@a1='/sites/Test/Lists/Shipping Orders'&View=360f56cd-7b81-4912-abc1-7eea91134c24&TryNewExperienceSingle=TRUE&FilterField1=InvoiceNo&FilterValue1=01-2021A&FilterType1=Text

Payload:

{
  "parameters": {
    "__metadata": {
      "type": "SP.RenderListDataParameters"
    },
    "RenderOptions": 1183751,
    "AllowMultipleValueFilterForTaxonomyFields": true,
    "AddRequiredFields": true
  }
}

We can use the /RenderListDataAsStream endpoint in our solution as well, but in slightly changed form.

RenderListDataAsStream

First of all, we need a query that filters our data. We’re going to use Collaborative Application Markup Language aka CAML. It’s based on XML and slightly resembles SQL. You may see its application in other SharePoint processes.

You can learn about CAML in another blog post.

Our query would look like this:

<View>
  <RowLimit Paged="TRUE">100</RowLimit>
  <Query>
    <Where>
      <Eq>
        <FieldRef Name="InvoiceNo"/>
        <Value Type="Text">01-2021A</Value>
      </Eq>
    </Where>
    <OrderBy>
      <FieldRef Name="ID" Ascending="TRUE" />
    </OrderBy>
  </Query>
</View>

Let’s start from the top:

<RowLimit Paged="TRUE">100</RowLimit>

“RowLimit” sets the number of items returned by SharePoint. “Paged” property specifies if we want to get more items page by page. In our case, we will get data in chunks of 100.

<Eq>
  <FieldRef Name="InvoiceNo"/>
  <Value Type="Text">01-2021A</Value>
</Eq>

“Eq” is one of many comparison operators. It simply means “equal to”. To be exact – we’re looking for items which invoice number (<FieldRef Name=”InvoiceNo”/>) is equal to (<Eq>) “01-2021A” (<Value Type=”Text”>01-2021A</Value>).

More operators can be found in the official documentation.

<OrderBy>
  <FieldRef Name="ID" Ascending="TRUE" />
</OrderBy>

“OrderBy” determines how filtered items should be sorted. We’re making sure that our data is ordered by ID and that it goes from smaller to bigger values.

Let’s put it all together.

await sp.web.lists.getByTitle("Shipping Orders").renderListDataAsStream({
   ViewXml: `<View>
   <RowLimit Paged="TRUE">100</RowLimit>
      <Query>
        <Where>
          <Eq>
            <FieldRef Name="InvoiceNo"/>
            <Value Type="Text">01-2021A</Value>
          </Eq>
        </Where>
        <OrderBy>
          <FieldRef Name="ID" Ascending="TRUE" />
        </OrderBy>
      </Query>
    </View>`
  });

[POST] /_api/web/lists/getByTitle('Shipping Orders')/RenderListDataAsStream

Payload:

{
  "parameters": {
    "__metadata": {
      "type": "SP.RenderListDataParameters"
    },
    "ViewXml": "\n    <View> \n    <RowLimit Paged=\"TRUE\">100</RowLimit>\n      <Query>\n        <Where>\n          <Eq>\n            <FieldRef Name=\"InvoiceNo\"/>\n            <Value Type=\"Text\">01-2021A</Value>\n          </Eq>\n        </Where>\n        <OrderBy>\n          <FieldRef Name=\"ID\" Ascending=\"TRUE\" />\n        </OrderBy>\n      </Query>\n    </View>\n\n"
  }
}

And SharePoint’s response:

{
   CurrentFolderSpItemUrl: "",
   FilterLink: "?",
   FirstRow: 1,
   FolderPermissions: "0x7ffffffffffbffff",
   ForceNoHierarchy: "1",
   HierarchyHasIndention: "",
   LastRow: 100,
   NextHref: "?Paged=TRUE&p_ID=2152&PageFirstRow=101&View=00000000-0000-0000-0000-000000000000",
   Row: [items],
   RowLimit: 100,
}

We can find an array of filtered items under the “Row” property.

Since we requested our data in chunks, we got first set of items that meet our criteria. The next batch of elements is available under /RenderDataAsStream endpoint with a special href that can be found under “NextHref” property – “?Paged=TRUE&p_ID=2152&PageFirstRow=101&View=00000000-0000-0000-0000-000000000000”.

await sp.web.lists.getByTitle("Shipping Orders").renderListDataAsStream({
    ViewXml: viewXml,
   Paging: "Paged=TRUE&p_ID=2152&PageFirstRow=101&View=00000000-0000-0000-0000-000000000000",
  });

[POST] /_api/web/lists/getByTitle('Shipping Orders')/RenderListDataAsStream

Payload:

{
  "parameters": {
    "__metadata": {
      "type": "SP.RenderListDataParameters"
    },
    "ViewXml": "<View> \n    <RowLimit Paged=\"TRUE\">100</RowLimit>\n      <Query>\n        <Where>\n          <Eq>\n            <FieldRef Name='InvoiceNo' />\n            <Value Type='Text'>1-2021</Value>\n          </Eq>\n        </Where>\n      </Query>\n    </View>",
    "Paging": "Paged=TRUE&p_ID=2152&ix_Paged=TRUE&ix_ID=2152&PageFirstRow=101&View=00000000-0000-0000-0000-000000000000"
  }
}

If the “NextHref” property is missing from the result, all data was already requested and returned.

Important elements of RenderListDataAsStream approach

Indexing

SharePoint presents an idea of column indexing. It’s a way of increasing the performance of loading list views. Simply put – SharePoint “is aware” of indexed data and doesn’t have to loop through all items to find it. And that exactly what we need!

This concept can be especially useful if you’re getting data through /RenderListDataAsStream endpoint and still get the threshold error. Try adding indices to columns that you use in your queries. You can do this manually in the List Settings page in the Columns section. Alternatively, you can update the “Indexed” flag on a column via PowerShell script.

SharePoint list can have up to 20 indices. Unfortunately, not all column types can be indexed, including:

• multiple lines of text,
• choice (multi-valued),
• calculated,
• hyperlink or picture,
• custom columns,
• person or group (multi-valued),
• external data.

Other types:

• single line of text,
• choice (single value),
• number,
• currency,
• date and time,
• person or group (single value),
• managed metadata,
• yes/no,
• lookup

can be easily indexed.

More about indexing can be found in Microsoft resources.

RowLimit

As mentioned before, “RowLimit” property defines how many items will be returned and whether they’re paged. It’s important too look into this option, especially when you try to retrieve more items than the view threshold. For instance, my list has a default limitation of 5000 elements for a list view.

If my query filters out more items that that, the threshold error will be returned. If I paged my results:

<RowLimit Paged="TRUE">100</RowLimit>

I can get all the items that meet my criteria, even if there are over 5 000 of them.

Scopes

The scope is a property of the “View” section of the query.

<View Scope="RecursiveAll">

It defines what kind of items will be included in the result and how deep the query will look in the list structure. If omitted, the option will be set for “Default” – only items in the list root folder will be taken into account. Some other scopes:

• All – looks for items and folders in the root folder,
• Recursive – looks for items in the root folder and inside the subfolders,
• RecursiveAll – looks for items and folders in the root folder and in the subfolders.

You should focus on the “scope” setting if you have folders in your lists structure. If the property is not defined correctly, some data may not be included in the results.

Using existing list views

We built all filters by ourselves in the examples above. That’s a great practise when our query is dynamically changing. However, when the filter values are constant, we could take another approach. We can use schema of list view that already exists.

To create a list view, we go to the Views section on the List Settings page. We could define:

• columns presented on the view,
• sorting of the items,
• filtering of the items,

and some more settings.

In my case, I need a view that filters orders marked as “Sent”. I manually create a view with corresponding settings.

Now, I can use this view’s XML schema in my code.

Firstly, I’ll get the view definition.

await await sp.web.lists.getByTitle("Shipping Orders").views.getByTitle("Sent Orders").get();

[GET] /_api/web/lists/getByTitle('Shipping 0Orders')/views/getByTitle('Sent Orders')

And then retrieve the data using the view’s schema.

await sp.web.lists.getByTitle("Shipping Orders").renderListDataAsStream({
    ViewXml: existingView.ListViewXml,
  });

Building XMLs with external libraries

Building custom view definitions can be tedious. To speed up the process, we could use some external libraires. My personal favorite is CamlJs.

Firstly, we create the builder object:

const camlBuilder = new CamlBuilder();

Then we build the query:

const viewXml = camlBuilder.Where()
    .ChoiceField("ShippingStatus").In([“Sent”, “Received”])
    .And()
    .DateField("ShippingDate")
    .LessThanOrEqualTo(CamlBuilder.CamlValues.Now)
    .OrderByDesc("ID")
    .ToString();

And use it as before:

await sp.web.lists.getByTitle("Shipping Orders").renderListDataAsStream({
    ViewXml: viewXml
  });

Special parameters that we should be aware of

The definitions of view XML and paging are two of the /RenderListData parameters. The parameters are optional, but you should definitely be aware that they exist. Most of them are self-explanatory:

• AddRequiredFields,
• AllowMultipleValueFilterForTaxonomyFields,
• DatesInUtc,
• ExpandGroups,
• FirstGroupOnly,
• FolderServerRelativeUrl,
• ImageFieldsToTryRewriteToCdnUrls,
• OverrideViewXml,
• RenderOptions,
• ReplaceGroup.

FolderServerRelativeUrl

SharePoint will run our quires in the root folder of the list by default. To change that, we can define the FolderServerRelativeUrl parameter. “Server relative” means that the URL should start from the /sites/ part. For instance: “/sites/Test/Lists/Shipping Orders/Europe”. This way API will look for items only in the “Europe” folder.

RenderOptions

Beside the list items, SharePoint can return some other information, for instance: list schema, view metadata, media URLs or some data about files and folders. To define what we need, we should set up the RenderOptions parameter.

Specific description of the options can be found in the official documentation.