DEV Community

Manuel Sidler
Manuel Sidler

Posted on

7 2

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!

👋 While you are here

Reinvent your career. Join DEV.

It takes one minute and is worth it for your career.

Get started

Top comments (3)

Collapse
 
rferreiraperez profile image

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

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?

👋 Kindness is contagious

Discover a treasure trove of wisdom within this insightful piece, highly respected in the nurturing DEV Community enviroment. Developers, whether novice or expert, are encouraged to participate and add to our shared knowledge basin.

A simple "thank you" can illuminate someone's day. Express your appreciation in the comments section!

On DEV, sharing ideas smoothens our journey and strengthens our community ties. Learn something useful? Offering a quick thanks to the author is deeply appreciated.

Okay