PnPjs documentation claims that getting items from a list is one of the basic actions that most applications require. It\u2019s hard to disagree with this statement.<\/p>\n\n\n\n
The straightforward way of getting elements would be using the \/items<\/em> endpoint with the filter parameter. Either directly through REST API:<\/p>\n\n\n Or via PnPjs library:<\/p>\n\n\n It works as expected until the list overgrows, and filtering requires too much processing power.<\/p>\n\n\n\n A user can easily filter a large list manually:<\/p>\n\n\n\n In this case, SharePoint creates a request:<\/p>\n\n\n Payload:<\/p>\n\n\n We can use the \/RenderListDataAsStream<\/em> endpoint in our solution as well, but in slightly changed form.<\/p>\n\n\n\n First of all, we need a query that filters our data. We\u2019re going to use Collaborative Application Markup Language aka CAML. It\u2019s based on XML and slightly resembles SQL. You may see its application in other SharePoint processes.<\/p>\n\n\n\n You can learn about CAML<\/a> in another blog post.<\/p>\n\n\n\n Our query would look like this:<\/p>\n\n\n Let\u2019s start from the top:<\/p>\n\n\n \u201cRowLimit\u201d sets the number of items returned by SharePoint. \u201cPaged\u201d property specifies if we want to get more items page by page. In our case, we will get data in chunks of 100.<\/p>\n\n\n \u201cEq\u201d is one of many comparison operators. It simply means \u201cequal to\u201d. To be exact \u2013 we\u2019re looking for items which invoice number (<FieldRef Name=\u201dInvoiceNo\u201d\/><\/em>) is equal to (<Eq><\/em>) \u201c01-2021A\u201d (<Value Type=\u201dText\u201d>01-2021A<\/Value><\/em>).<\/p>\n\n\n\n More operators can be found in the official documentation<\/a>.<\/p>\n\n\n \u201cOrderBy\u201d determines how filtered items should be sorted. We\u2019re making sure that our data is ordered by ID and that it goes from smaller to bigger values.<\/p>\n\n\n\n Let\u2019s put it all together.<\/p>\n\n\n Payload:<\/p>\n\n\n And SharePoint\u2019s response:<\/p>\n\n\n We can find an array of filtered items under the \u201cRow\u201d property.<\/p>\n\n\n\n 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<\/em> endpoint with a special href that can be found under \u201cNextHref\u201d property \u2013 \u201c?Paged=TRUE&p_ID=2152&PageFirstRow=101&View=00000000-0000-0000-0000-000000000000\u201d.<\/p>\n\n\n Payload:<\/p>\n\n\n If the \u201cNextHref\u201d property is missing from the result, all data was already requested and returned.<\/p>\n\n\n\n SharePoint presents an idea of column indexing. It\u2019s a way of increasing the performance of loading list views. Simply put \u2013 SharePoint \u201cis aware\u201d of indexed data and doesn\u2019t have to loop through all items to find it. And that exactly what we need!<\/p>\n\n\n\n This concept can be especially useful if you\u2019re getting data through \/RenderListDataAsStream<\/em> 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 \u201cIndexed\u201d flag on a column via PowerShell script.<\/p>\n\n\n\n SharePoint list can have up to 20 indices. Unfortunately, not all column types can be indexed, including:<\/p>\n\n\n\n Other types:<\/p>\n\n\n\n can be easily indexed.<\/p>\n\n\n\n More about indexing<\/a> can be found in Microsoft resources.<\/p>\n\n\n\n As mentioned before, \u201cRowLimit\u201d property defines how many items will be returned and whether they\u2019re paged. It\u2019s 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.<\/p>\n\n\n\n If my query filters out more items that that, the threshold error will be returned. If I paged my results:<\/p>\n\n\n I can get all the items that meet my criteria, even if there are over 5 000 of them.<\/p>\n\n\n\n The scope is a property of the \u201cView\u201d section of the query.<\/p>\n\n\n 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 \u201cDefault\u201d \u2013 only items<\/strong> in the list root folder <\/strong>will be taken into account. Some other scopes:<\/p>\n\n\n\n You should focus on the \u201cscope\u201d 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.<\/p>\n\n\n\n We built all filters by ourselves in the examples above. That\u2019s 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.<\/p>\n\n\n\n To create a list view, we go to the Views section on the List Settings page. We could define:<\/p>\n\n\n\n and some more settings.<\/p>\n\n\n\n In my case, I need a view that filters orders marked as \u201cSent\u201d. I manually create a view with corresponding settings.<\/p>\n\n\n\n Now, I can use this view\u2019s XML schema in my code.<\/p>\n\n\n\n Firstly, I\u2019ll get the view definition.<\/p>\n\n\n And then retrieve the data using the view\u2019s schema.<\/p>\n\n\n Building custom view definitions can be tedious. To speed up the process, we could use some external libraires. My personal favorite is CamlJs.<\/a><\/p>\n\n\n\n Firstly, we create the builder object:<\/p>\n\n\n Then we build the query:<\/p>\n\n\n And use it as before:<\/p>\n\n\n The definitions of view XML and paging are two of the \/RenderListData<\/em> parameters<\/strong>. The parameters are optional, but you should definitely be aware that they exist. Most of them are self-explanatory:<\/p>\n\n\n\n SharePoint will run our quires in the root folder of the list by default. To change that, we can define the FolderServerRelativeUrl parameter. \u201cServer relative\u201d means that the URL should start from the \/sites\/<\/em> part. For instance: \u201c\/sites\/Test\/Lists\/Shipping Orders\/Europe\u201d. This way API will look for items only in the \u201cEurope\u201d folder.<\/p>\n\n\n\n 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.<\/p>\n\n\n\n\n[GET] \/_api\/web\/lists\/getByTitle(\u2018Shipping Orders\u2019)\/items?$filter=InvoiceNo eq \u201801-2021A\u2019\n<\/pre><\/div>\n\n\n
\nawait sp.web.lists.getByTitle("Shipping Orders").items.filter("InvoiceNo eq \u201801-2021A\u2019")();\n<\/pre><\/div>\n\n\nHow SharePoint does it?<\/strong><\/h2>\n\n\n\n
![\"\"](\"https:\/\/sii.pl\/blog\/wp-content\/uploads\/2022\/02\/1-1.png\")
<\/a>\n[POST]\n\n\/_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\n<\/pre><\/div>\n\n\n
\n{\n "parameters": {\n "__metadata": {\n "type": "SP.RenderListDataParameters"\n },\n "RenderOptions": 1183751,\n "AllowMultipleValueFilterForTaxonomyFields": true,\n "AddRequiredFields": true\n }\n}\n<\/pre><\/div>\n\n\nRenderListDataAsStream<\/strong><\/h2>\n\n\n\n
\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<\/pre><\/div>\n\n\n
\n<RowLimit Paged="TRUE">100<\/RowLimit>\n<\/pre><\/div>\n\n\n
\n<Eq>\n <FieldRef Name="InvoiceNo"\/>\n <Value Type="Text">01-2021A<\/Value>\n<\/Eq>\n<\/pre><\/div>\n\n\n
\n<OrderBy>\n <FieldRef Name="ID" Ascending="TRUE" \/>\n<\/OrderBy>\n<\/pre><\/div>\n\n\n
\nawait sp.web.lists.getByTitle("Shipping Orders").renderListDataAsStream({\n ViewXml: `<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<\/pre><\/div>\n\n\n[POST] \/_api\/web\/lists\/getByTitle('Shipping Orders')\/RenderListDataAsStream\n<\/pre><\/div>\n\n\n\n{\n "parameters": {\n "__metadata": {\n "type": "SP.RenderListDataParameters"\n },\n "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"\n }\n}\n<\/pre><\/div>\n\n\n\n{\n CurrentFolderSpItemUrl: "",\n FilterLink: "?",\n FirstRow: 1,\n FolderPermissions: "0x7ffffffffffbffff",\n ForceNoHierarchy: "1",\n HierarchyHasIndention: "",\n LastRow: 100,\n NextHref: "?Paged=TRUE&p_ID=2152&PageFirstRow=101&View=00000000-0000-0000-0000-000000000000",\n Row: [items],\n RowLimit: 100,\n}\n<\/pre><\/div>\n\n\n\nawait sp.web.lists.getByTitle("Shipping Orders").renderListDataAsStream({\n ViewXml: viewXml,\n Paging: "Paged=TRUE&p_ID=2152&PageFirstRow=101&View=00000000-0000-0000-0000-000000000000",\n });\n<\/pre><\/div>\n\n\n[POST] \/_api\/web\/lists\/getByTitle('Shipping Orders')\/RenderListDataAsStream\n<\/pre><\/div>\n\n\n\n{\n "parameters": {\n "__metadata": {\n "type": "SP.RenderListDataParameters"\n },\n "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>",\n "Paging": "Paged=TRUE&p_ID=2152&ix_Paged=TRUE&ix_ID=2152&PageFirstRow=101&View=00000000-0000-0000-0000-000000000000"\n }\n}\n<\/pre><\/div>\n\n\nImportant elements of RenderListDataAsStream approach<\/strong><\/h2>\n\n\n\n
Indexing<\/strong><\/h3>\n\n\n\n
\n
\n
RowLimit<\/strong><\/h3>\n\n\n\n
<\/a>\n<RowLimit Paged="TRUE">100<\/RowLimit>\n<\/pre><\/div>\n\n\n
Scopes<\/strong><\/h3>\n\n\n\n
\n<View Scope="RecursiveAll">\n<\/pre><\/div>\n\n\n
\n
Using existing list views<\/strong><\/h2>\n\n\n\n
\n
<\/a>\nawait await sp.web.lists.getByTitle("Shipping Orders").views.getByTitle("Sent Orders")();\n<\/pre><\/div>\n\n\n[GET] \/_api\/web\/lists\/getByTitle('Shipping Orders')\/views\/getByTitle('Sent Orders')\n<\/pre><\/div>\n\n\n\nawait sp.web.lists.getByTitle("Shipping Orders").renderListDataAsStream({\n ViewXml: existingView.ListViewXml,\n });\n<\/pre><\/div>\n\n\nBuilding XMLs with external libraries<\/strong><\/h2>\n\n\n\n
\nconst camlBuilder = new CamlBuilder();\n<\/pre><\/div>\n\n\n
\nconst viewXml = camlBuilder.Where()\n .ChoiceField("ShippingStatus").In([\u201cSent\u201d, \u201cReceived\u201d])\n .And()\n .DateField("ShippingDate")\n .LessThanOrEqualTo(CamlBuilder.CamlValues.Now)\n .OrderByDesc("ID")\n .ToString();\n<\/pre><\/div>\n\n\n\nawait sp.web.lists.getByTitle("Shipping Orders").renderListDataAsStream({\n ViewXml: viewXml\n });\n<\/pre><\/div>\n\n\nSpecial parameters that we should be aware of<\/strong><\/h2>\n\n\n\n
\n
FolderServerRelativeUrl<\/strong><\/h2>\n\n\n\n
<\/a><\/figure>\n\n\n\nRenderOptions<\/h2>\n\n\n\n