Getting User Information with the SharePoint 2013 REST API
Sometimes the tiniest little throw-away comment on an article out there somewhere can prove useful to someone. Sometimes, it’s even a comment I’ve made.
That’s Andrew “AC” Clark (@bitterac) who tweeted. I may get a club soda out of it, but at Andrew’s suggestion, I figured I’d put up a post as well.
There’s far less documentation about SharePoint 2013’s REST capabilities than most of us would like. As has happened so many times in the past, the blogosphere fills in the gaps.
The post where Andrew Clark found my comment was a great one by Andrew Connell (@andrewconnell) about Applying Filters to Lookup Fields with the SharePoint 2013 REST API.
Andrew Connell (this post is a bit of an Andrew festival – an AC festival, at that) gave extremely useful (and hard to find) info about how to filter based on the values of lookup columns in SharePoint lists using REST. He covered regular lookup columns as well as Managed Metadata columns.
What it comes down to is using the projection to the source of the lookup or Managed Metadata column and then filtering base on the original, underlying value. We do this in REST for SharePoint using the $expand operator.
My comment was about needing to figure out the projection for an Author (Created By) column. The columns Created By and Modified By are populated by SharePoint automagically and the data used for that population is stored in the User Information List. This is a semi-hidden list which exists in the root of each Site Collection. I say semi-hidden because it’s actually the list you’re looking at when you go into the People and Groups view in Site Settings.
If you need to retrieve the name of the Author in a REST call, you can make it work by adding the projection for the Author column.
/_api/web/lists/getbytitle(listname)/items?$select=Title,Author/ID,Author/Title&$expand=Author/ID,Author/Title
Going a little further, you can request any of the data you’re used to seeing on the userdisp.aspx page (_layouts/15/userdisp.aspx?Force=true).
/_api/web/lists/getbytitle(listname)/items?$select=Title,Author/ID,Author/FirstName,Author/LastName,Author/Title,Author/Department,Author/SipAddress&$expand=Author/ID
It doesn’t really make a lot of sense when you look at it (at least not to me), but by adding the $expand clause, you can retrieve the corresponding info the the User Information List. It feel like you’d need to specify where that list is or something, but under the covers that happens for you.
It seems that by providing each of the values you want in both the $select and the $expand operators, it works. I couldn’t find any documentation on this anywhere. AC2 (the Connell one) has suggested to me several times that it makes sense to simply look at the OData standard. As he said in a reply to me on the same post:
For me, there’s simply nothing better than the raw SDKs & specs on the www.odata.org site. Aside from that, I’ll query www.StackOverflow.com. Then, as a SharePoint guy, you then need to look at WCF Data Services and see what it does not support in the OData v3 spec & the same is true for SharePoint 2013.
I think this stuff is about as clear as mud, but the main reason is that I haven’t spent a lot of time with it yet. REST calls are just a different flavor of how we ask for data in other languages. It’s simply a matter of getting the accent right.
Hello Marc,
I have a large list (15000 items) with a multilookup field to another list which is also large (51000 items). I am trying to use $expand on the multilookup column but it doesnot work because of the threshold, although the column in the second list that this Multilookup column links to is indexed.
Thanks Mark. This post helped me a lot. Can you help me with one problem.
I am trying to find rest api to give reply to a discussion in sharepoint.
What ever i do, reply gets added to as message object to main discussion list.
Say discussion list name is MyDiscussion and it has a heading MyHeading.
I am trying to add reply to MyHeadding.
Please help me.
Basil:
Discussion topics inherit from the Folder Content Type, you need to specify the topic folder as the location for the new item.
M.
Can you please elaborate it with sample request and sample url. I am confused as to how to specify topic folder. I know you are a busy man. But I think this should be a simple task for an experienced guy like you. Please help me out.
Hi Marc,
How can we insert “Like” for a discussion and for its replies through share point rest api ?
I am new to this technology.I am using a advanced rest console for testing purpose.
I was able to query upto the below mentioned point
/_api/lists/getByTitle(‘MyDiscussion’)/items(1)$Select=ID,Title,Body,LikesCount,LikedBy/Id&$expand=LikedBy,
Beyond this point I don’t know how to construct the request parameter and the post url.
Is it possible for you to help me on this?
Joseph and Basil:
It sounds like QBurst could use some good SharePoint consulting help! You’re sort of going off on a tangent from the original intent of this post, which was simply using a projection to get user info.
I would suggest you use one of the public forums like http://sharepoint.stackexchange.com/ for general SharePoint questions.
M.
Thanks Marc for the “Stackexchange” site.
You’re welcome. It’s not that I don’t want to help, but I shouldn’t be your bottleneck.
M.
This is exactly what I’ve been looking for! Thanks very much!
For some reason, I haven’t been able to get this to work. (I am trying to get the Created by name only)
I used this code based on your article.
http://nameofsite/dept/promotions/creative/_api/Web/Lists(guid'E29E7D6E-15B3-4ACA-8B71-2C4E04F57139‘)/Items?$select=Title,Author/Id,Author/Name,Author/FirstName,Author/LastName&$expand=Author
I am not sure how it would associate with AuthorID – which appears to be the column containing the number associated with the user who submitted the form.
It returns this error:
“We couldn’t refresh the connection. Please go to existing connections and verify they connect to the file or server.”
I should mention that I am trying to pull this into excel using powerpivot, but powerpivot pulls the data fine when I just use (except it returns numbers instead of names for my report) the below:
http://namemysite/dept/promotions/creative/_api/Web/Lists(guid'E29E7D6E-15B3-4ACA-8B71-2C4E04F57139‘)/Items
Also wondering if it would work if the field isn’t one of standard SharePoint list fields because I am also getting a number for my assigned designer (chosen with peoplepicker from ad connection)
The field name I am using is Assigned Designer and when I pull the data it shows in a column called Assigned DesignerID
Any help would be appreciated.
Rich:
You should try the link in your browser first. Odds are one of the columns you’re requesting isn’t correct, but it all looks OK to me. Since you’re passing it from PowerPivot, you may have an auth problem.
M.
Marc,
Very nice post. But I am facing issue because I cant take person type column in lookup and I need its information on another looked up column. Can you please have a look this issue I will be very grateful to you.
email : [email protected]
http://stackoverflow.com/questions/33752084/sharepoint-2013-how-to-get-person-field-value-from-another-lookup-of-that-list
Hi Marc, your awesome! I always learn a lot and wanted to thank you for sharing with us!
Question time ~ I’m trying to figure out how I would call a list and in that list, I have a lookup column that points to a people picker field. I need to get the value of the email for that person.
In other words, how would I get the info of a list that looks up another list which contains a people picker column?
I can get the item ID’s of the list that contains the people picker column I’m interested. But I need to go further.
How can we get the User profile URL for SharePoint Online sites? like this. – \Layouts\15\userdisp.aspx?ID=<>
good one. Thanks a lot