Overcoming Sharepoint Rest Api Limitations For Joining Lists

The Problem with Joining SharePoint Lists via REST

The default SharePoint REST APIs provide basic CRUD (create, read, update, delete) operations for accessing individual lists. However, they lack built-in support for joining or correlating data across multiple lists in a single API call.

Attempting to join SharePoint lists via client-side code using the out-of-the-box REST endpoints results in errors due to unsupported query combinations or excessive list view lookups that exceed list view threshold limits.

For example, queries using the $filter parameter to correlate data across lists may fail with the error “The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.” Other unsupported filter expressions return generic 400 errors rather than specific guidance on the limitation.

Alternative Approaches to Join SharePoint Lists

Faced with REST API restrictions around joining SharePoint lists, common workarounds rely on server-side code using CAML queries or SharePoint PnP PowerShell scripts. However, these options have downsides.

Custom CAML queries allow complex data access but require non-portable server code in the form of custom Web Parts, Application Pages, or Workflows. PowerShell scripts can query multiple lists but have to be re-run manually when data changes.

Introducing the Extended OData Endpoint

Recent SharePoint API enhancements now allow creating custom OData endpoints that overcome many previous SharePoint list joining limitations. These declarative endpoints provide a sustainable method for accessing joined SharePoint list data from client-side code.

Compared to default SharePoint REST APIs, the key advantages of extended OData endpoints include:

• Joins between SharePoint lists and libraries are directly supported
• Aggregated filtering across joined data sources
• Endpoint queries count as a single API request, avoiding threshold limits
• Generated endpoints are reusable across solutions

Walkthrough: Joining Lists with the Extended OData Endpoint

This section provides a step-by-step walkthrough for creating a custom OData endpoint to join two SharePoint lists and return correlated results.

Step 1 – Enable Custom Endpoints

The SharePoint tenant admin first needs to enable custom endpoint capabilities for the target site collection using the following PowerShell command:

Set-SPOSite -Identity https://contoso.sharepoint.com/sites/team -DenyAddAndCustomizePages 0 

Step 2 – Access Site Collection App Catalog

In the SharePoint administration backend, navigate to the app catalog for the site collection by going to /_layouts/15/appregnew.aspx. This is where the endpoint definition will be deployed.

Step 3 – Create Endpoint Definition

Using the “Generate custom endpoint definitions” option, define a join between a list of customers and a list of sales orders including filters and projected fields:

{
  "$schema": "schema.json",

  "source": {
    "name": "CustomerOrders", 
    "description": "Join Customers and Orders",
    "defaultValue": "Customers/Orders",
    "baseType": "list",  
    "enablePaging": true,
    "pageSize": 500
  },

  "projections": [
    {
      "baseType": "SP.List",  
      "id": "Customers",
      "name": "Customers List",
      "fields": [
        {"name": "Title", "alias": "CustomerName"}
      ]
    },
    {
      "baseType": "SP.List",   
      "id": "Orders",
      "name": "Orders List",
      "fields": [
        {"name": "OrderTotal", "alias": "OrderTotal" }, 
        {"name": "ShipCountry", "alias": "ShipCountry" }
      ] 
    }
  ],

  "joinGroups": [ 
    {  
      "sources": [ 
        "Customers", 
        "Orders" 
      ],
      "joinType": "inner",
      "joinClauses": [ 
        {
          "left": { 
            "list": "Customers",
            "id": "Id"  
          },
          "right": {
            "list": "Orders",
            "id": "CustomerId"  
          }
        }
      ] 
    }
  ],

  "filters": [
    {
      "list": "Orders", 
      "filterOp": "gt",
      "filterField": "OrderTotal",  
      "filterValue": 1000
    }
  ] 
}

Step 4 – Deploy and Test Endpoint

Save, deploy, and test the custom endpoint definition using client-side JavaScript code or an API tool like Postman. The endpoint URL will follow the format:

https://contoso.sharepoint.com/sites/team/_api/CustomEndpoints/ENDPOINTGUID

Using Custom Endpoints in Client-Side Code

Client-side code can consume custom OData endpoints to join SharePoint list data. Here is sample JavaScript for calling a custom endpoint using the fetch API and processing response data:

const endpointUrl = 
  "https://contoso.sharepoint.com/sites/team/_api/CustomEndpoints/81623da5be174ece967aae3fd76a69cf";

async function getJoinedListData() {

  const response = await fetch(endpointUrl);

  const data = await response.json();

  // Output projected fields
  data.value.forEach(item => {
    console.log(item.CustomerName, item.OrderTotal); 
  });

}

getJoinedListData();

When using custom endpoints in production, also consider:

• Parameterizing endpoint values like filters and projections
• Paging over large result sets
• Caching frequently used responses
• Handling errors gracefully

Custom Endpoints Beyond Joining Lists

While the examples focus on joining SharePoint lists, the capabilities of custom OData endpoints go further:

• Merge data from SQL databases, external APIs, and other sources with SharePoint list data
• Perform server-side processing on responses
• Expose custom APIs instead of full OData surface

At a high level, the framework for developing a custom endpoint includes:

1. Creating endpoint definition
2. Adding data source connectors
3. Defining projections and filters
4. Deploying endpoint to catalogue
5. Consuming in client-side code

With a full understanding of the connector framework and endpoint definition schema, the possibilities are extensive in terms of composable business logic. Refer to Microsoft documentation and community posts for guidance on more advanced topics.