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

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
    }
}

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;

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;

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;

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);

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!

Comments

[Rubén Ferreira]

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?

[Vincent Biret]

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).

[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?