DEV Community

Manuel Sidler
Manuel Sidler

Posted on

Create a SharePoint list item with a people lookup field using Microsoft Graph SDK

Starting point

Assume we have a simple SharePoint list with the following columns to manage expenses:

Column Name Column Type
Title Single line of text
Amount Currency
Employee Person or Group

Now we'd like to create expense list items from our C# application. Thankfully, Microsoft Graph provides an endpoint to do that:

POST https://graph.microsoft.com/v1.0/sites/{site-id}/lists/{list-id}/items
Enter fullscreen mode Exit fullscreen mode

So we don't have to fiddle around with some SharePoint APIs and leverage the Microsoft Graph SDK.

The Problem

Let's take a look at the JSON representation of an expense list item:

{
  "id": "2",
  "fields": {
    "Title": "Flight to Zurich",
    "Amount": 125.6,
    "EmployeeLookupId": 13
    }
}
Enter fullscreen mode Exit fullscreen mode

The employee field isn't a user principal name nor an Azure AD object id. That's how we usually work with users inside Graph. Instead, SharePoint uses a hidden lookup list to store a user reference, which differs from site to site.

So how do we get the actual lookup id for a user?

The Solution

There's a way with Microsoft Graph. A bit quirky, to be honest, but at least we don't have to use a SharePoint API and stay inside our Graph bubble.

First, we have to fetch the id of the hidden user lookup list:

var hiddenUserListId = (await _graph
    .Sites["<siteid>"]
    .Lists
    .Request()
    .Filter("displayName eq 'User Information List'")
    .GetAsync())[0].Id;
Enter fullscreen mode Exit fullscreen mode

Next we have to get the user principal name if we don't have it yet:

var userName = (await _graph
    .Users[userId]
    .Request()
    .Select("userPrincipalName")
    .GetAsync()).UserPrincipalName;
Enter fullscreen mode Exit fullscreen mode

With the user principal name in hand, we now can query the lookup id:

var userLookupId = (await _graph
    .Sites["<siteid>"]
    .Lists[hiddenUserListId]
    .Items
    .Request()
    .Header("Prefer", "HonorNonIndexedQueriesWarningMayFailRandomly")
    .Filter($"fields/UserName eq '{userName}'")
    .GetAsync())[0].Id;
Enter fullscreen mode Exit fullscreen mode

The prefer header with HonorNonIndexedQueriesWarningMayFailRandomly is required to query non-indexed columns.

And finally, we can create a new expense list item:

var expenseItem = new ListItem
{
    Fields = new FieldValueSet
    {
        AdditionalData = new Dictionary<string, object>
        {
            {"Title", "Train to Zurich"},
            {"Amount", 23.2},
            {"EmployeeLookupId", userLookupId}
        }
    }
};

await _graph
    .Sites["<siteid>"]
    .Lists["Expenses"]
    .Items
    .Request()
    .AddAsync(expenseItem);
Enter fullscreen mode Exit fullscreen mode

If there's another option to save a user reference in a people column or a more clean way to get the lookup id, let me know in the comments!

Top comments (3)

Collapse
 
rferreiraperez profile image
Rubén Ferreira • Edited

I can't retrieve "User Information List" from the site, neither using the root site nor the current site.

I can see the list using the Browser but I can't retrieve it through the code. I'm using this URLs:

https://{organization}.sharepoint.com/_catalogs/users/detail.aspx
https://{organization}.sharepoint.com/_catalogs/users/simple.aspx

Any thoughts?

Collapse
 
baywet profile image
Vincent Biret • Edited

you can query https://graph.microsoft.com/v1.0/sites/{site-id}/lists?$select=system,name,id iterate through the results, and find the list with the name "users" (unfortunately filter doesn't seem to be supported on that endpoint for this field).

Collapse
 
tw_30 profile image
tw

Hi,
what if the user is not in the "User Information List" from the site? How can I add the user? Is this possible without adding every user to the sites group?