Limitations Of Expanding Sharepoint People Columns In Rest Calls

The Problem: Request URL Length Limits

When querying SharePoint lists and libraries via the REST API, it is common to use the $expand parameter to retrieve additional columns in the same call. However, when expanding people columns that contain large amounts of user profile data, the request URL can quickly become too long, resulting in errors.

SharePoint REST APIs have length limits on the GET request URL due to web server and browser restrictions. For IIS, the default maximum URL length is around 2000 characters, while browsers tend to limit around 8000. When expanding multiple people columns and profile fields, these limits can be reached even with a moderate number of items.

Attempting to expand people columns beyond the URL length limits results in HTTP 400 errors stating that the request URL exceeded the allowable length. Solutions are needed to work around this issue when trying to obtain additional user profile data in SharePoint REST queries.

How People Columns Are Represented in REST

In SharePoint lists, people columns are represented as lookup columns pointing to a user profile. For example, a “Assigned To” person column would contain the integer ID of the user it points to.

When using $expand in REST queries, additional metadata can be pulled in from the user profiles. For example:

/items?$expand=AssignedToId/Title,AssignedToId/Email

This expands the AssignedToId column and retrieves the “Title” and “Email” attributes from the user profile it points to.

However, because user profiles contain a large amount of metadata like job title, department, office location, and more, expanding all user profile columns can quickly result in long URLs exceeding browser and web server limits.

Examples of Long Expand Strings

To illustrate how lengthy expand strings for people columns can become, here are some examples that approach or exceed URL length limits:

Expand two people columns with 5 attributes each:

/items?$expand=User1Id/Title,User1Id/Email,User1Id/Department,User1Id/Office,User1Id/PastProjects,User2Id/Title,User2Id/Email,User2Id/Department,User2Id/Office,User2Id/PastProjects

Expand three people columns with 8 attributes each:

/items?$expand=AssignedToId/Title,AssignedToId/Email,AssignedToId/Department, AssignedToId/Office,AssignedToId/Manager,AssignedToId/CellPhone, AssignedToId/DirectReports,AssignedToId/PastProjects, CreatedById/Title, CreatedById/Email,CreatedById/Department,CreatedById/Office, CreatedById/Manager,CreatedById/CellPhone,CreatedById/DirectReports, CreatedById/PastProjects, ModifiedById/Title,ModifiedById/Email, ModifiedById/Department,ModifiedById/Office,ModifiedById/Manager, ModifiedById/CellPhone,ModifiedById/DirectReports,ModifiedById/PastProjects

As you can see, even a handful of expanded people columns combined with a modest number of attributes can result in exceedingly long URLs that surpass browser and server limits. When dealing with multiple items and people columns, length limits can be reached even faster.

Working Around Length Limits

When faced with expanding SharePoint people columns in REST and hitting request URL length limits, there are several approaches to retrieve the necessary user profile data without errors:

Using Multiple REST Calls Instead of Expands

Rather than a single call with long expands, split into separate REST calls for each person column. Query the list items without expands first to get IDs, then make a second call per user to get their profiles. Combine results client-side.

Pros:
– Avoids long URLs by splitting calls.
– Can format profile data nicely for client after.

Cons:
– Requires multiple round trips instead of one.
– More coding effort to orchestrate calls.

Filtering Columns Before Expanding

Reduce expand length by explicitly selecting columns. For example:

/items?$expand=AssignedToId/Title,AssignedToId/Email,AssignedToId/Office

Pros:
– Shortens expand length when only some profile data is needed.

Cons:
– Still constrained by maximum URL size.
– Changes required to add more columns later.

Calling the Profile API Directly

Rather than expanding people columns from list endpoints, call the user profile API directly using IDs retrieved from list.

Pros:
– Full control over profile data returned.
– Avoid limits from list expand strings.

Cons:
– Additional API complexity to learn.
– More coding effort to orchestrate calls.

Custom Solutions for Expanding People Data

To fully consolidate profile data from SharePoint lists, custom APIs can be created to handle expands server-side. Options like Azure functions and web apps expose custom endpoints that expand columns behind-the-scenes while avoiding URL limits. Useful for complex queries across multiple items with user data.

Pros:
– Hides complexity of multiple API calls from client.
– Custom expands fully consolidated to client.

Cons:
– Development effort for custom APIs.
– Infrastructure to host solution.

Key Takeaways and Alternatives

When working with SharePoint people columns via REST APIs, expanding user profile attributes often results in request URLs exceeding length limits. To avoid this issue, consider the following solutions:

– Split calls across multiple REST endpoints instead of long expand strings.
– Filter columns to only essential profile attributes.
– Call user Profile API directly after retrieving IDs.
– Create custom services to handle complex expands server-side.

In some situations, storing user details directly in custom columns rather than separate people fields may resolve the issue altogether while avoiding multiple calls. But this denormalization leads to data duplication.

For most robust operations on SharePoint user profiles at scale, migrating to Microsoft Graph eliminates expand limits. This provides a unified endpoint and query language across Office 365 assets including users. But requires expertise with Graph rather than SharePoint REST.

Understanding these alternatives helps craft performant solutions as apps work with expanding volumes of SharePoint profile data.