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!
Top comments (3)
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?
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).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?