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.
This is an excellent nugget! Thanks, Marc.
Not that I’m needing it right this moment, but it just got filed away for future use because I know I’ll need it soon.
You’re welcome, Jim Bob!
M.
so for the folks following at home. This saved me especially considering that I’ve seen other blog posts that talk about making an additional call to the get the User Name. Think about that for a minute, depending on how you wrote your JavaScript, you are making at least two async calls for each item. So really you’d have to play around with promises to ensure that your calls are being handled properly. Or you can just fiddle with $expand and remove some clutter. Assuming that you do not consider JavaScript in general to be clutter. Thanks Marc!
Fantastic tip! (As usual.) Can you think of any reason I would receive a page could not be displayed when I try it?
https://.sharepoint.com/_api/web/lists/getbytitle(UserInformationList)/items?$select=Title,Author/ID,Author/FirstName,Author/LastName,Author/Title,Author/Department,Author/SipAddress&$expand=Author/ID
Carl:
In my example, I’m calling getbytitle with list that’s NOT the User Information List. In the REST call, the $expand parameter is what does the “join” to get the information from the User Information List. Also, your list name needs to be in quotes: (‘User Information List’). Basically, when you issue an invalid REST call, you get a Page Not Found.
M.
Perfect thanks Marc. You are correct about the documentation is can be hard to find what you want. Like Jim I do not need it this very moment but it will be filed for when I do.
Thanks man, I literally just used this and posted about it at the weekend for a AngularJS mission I’m on! http://www.jeremythake.com/2014/01/sharepoint-rest-api-to-host-web-with-angularjs-services/
Mark,
Nice post on how to expand fields to retrieve other data… This is very similar to how we can use QueryOptions in SOAP requests (ex. GetListItemChangesSinceToken), but with the value added feature of actually controlling which additional data columns you want to get back… I had bookmarked some MS pages about REST and the only one I found that mentions the $expand option is this one: http://msdn.microsoft.com/en-us/library/office/fp142385.aspx
I have not done much with the REST API, so I’ll just tuck this post away in my “drawer” – I know I’ll come back to it in the future.
Paul.
Paul:
As you know, it’s all OData at this point. But more documentation on the SharePoint side would certainly make people a lot more comfortable journeying into what for many is uncharted territory.
M.
Good post. The oData.org documentation is useful, but I’ve found a few instances where SP just doesn’t implement it as specified in the oData.org standard. MS did not implement all of the available query functions, so it can get really frustrating when you think something should work, then just doesn’t.
I’ve worked with this enough to have made a few discoveries on my own. You don’t have to add all the fields to the expand parameter:
_api/lists/getbytitle(‘Tasks’)/items?$select=Title,Author/Id,Author/Name,Author/FirstName,Author/LastName&$expand=Author
Filter works with any of the fields available on the related list:
&$filter=Author/FirstName eq ‘Shelly’
JSON is easy to get directly with &$format=’json’
I didn’t really see anyone mention here or on the linked blog posts that this will work for *any* lookup column on a list. You aren’t limited to the user information list. I love to use it to get related info and avoid making additional calls.
Sweet!
Just tried removing all the fields from expand and just keeping the actual column.
Seems very logical once you try it.
Made my url look a whole lot better :)
Thanks Shelly!
How would you recommend to use this to get User/Picture . I was getting an error
Jim:
After a little flailing around, I couldn’t get it to work, either. Did you figure it out? One would expect that Author/Picture ought to work.
It does work to go against the User Information List directly:
/_api/web/lists/getbytitle('User%20Information%20List')/items?$select=Title,PictureM.
Marc,
Picture is returned as a nested object when you go against the User Information List directly. It is a value with a __metadata object (“type”: “SP.FieldUrlValue”). It seems like you should be able to use $expand on it. My best guess is that you can’t because it doesn’t have any kind of ID or key value to join on.
We can expand on Author/ID because it isn’t just a value but a complete standalone entity that has an ID. It’s frustratingly inconsistent. It would be nice if we could return the entire entity with all the values as part of the expanded data if it isn’t possible to individually select any field we fancy to get from it.
Shelly-
You can’t use $expand in this case… $expand in OData is used for expanding related items that are exposed as NavigationProperties as explained in the spec (see 4.6 on this page: http://www.odata.org/documentation/odata-version-2-0/uri-conventions/). The picture is not a NavigationProperty. It’s actually a SharePoint type “SP.FieldUrlValue”. That is not a common type… it’s a SharePoint-y thing. This field type is implemented as an array in a single string within the object model of SharePoint. The SharePoint REST API is actually being nice in that it is returning the two inner parts of this object which are just strings.
You can see what NavigationProperties (aka: relationships) are available on an entity if you remove the query operators (ie: $select) from your query… in the XML response you will see a bunch of references that describe other paths… notice nothing about a picture…
So $expand isn’t applicable here. But all is not lost…
Use the NavigationProperty “FieldValuesAsText” on a specific entity to ask SharePoint to “convert the response to a flat and not nested structure of strings.” So like this: /_api/web/lists/getbytitle(‘User%20Information%20List’)/items(9)/FieldValuesAsText (just replace the ‘9’ with the ID of your user. No, you can’t do this for everyone… it’s a one-by-one. that gives you a comma-seperated value to the picture. Use some simple string split() call to break it up on the comma and grab the URL to the picture.
-AC
AC:
If I read you correctly, it’s unfortunate that we’d need to do an additional call per item to get the Picture info.
The original thrust of the post above was to show how to get additional info about the authors of list (or library) items. There are times when we might want to know their Department, etc. Thus the $expand approach to get that info from the User Information List.
So based on what I think you’re saying, the fact that the Picture field is stored in a complex type (SP.FieldUrlValue) means we’d need to do a call per item we retrieve from the list to get the author’s Picture. (We could be more efficient by not making multiple calls per author in the results, but it still would require more calls, right?)
I’d love it if you told me I’m wrong about this…
M.
Sooooooo… :)
You aren’t wrong per say, but I think this is one of those “we are trying to apply new tech to an old architecture designed in pre-2003” things. See, SharePoint has some “custom types” for things like URLs (which is what the picture is) as well as lookups. For lookups they did map the $expand OData operator to open it up as related data, but URLs, attachments & pictures don’t have that same metaphor of related data. If the pictures were stored in a special doc library & referenced using a lookup, then that would be different.
In more recent arch changes / additions to the platform (ala: managed metadata) did leverage non-custom field types… managed metadata fields are actually lookups to a hidden list in the root of the site collection.
What does this all mean? Yes, you do have to get picture data with either a separate call or using the method I showed. Sorry man :(
AC,
I agree that you can’t use $expand in this case. I think you misunderstood my point about that. The original problem we were attempting to solve is to retrieve all the values we wanted in one data call by using $expand. I can get almost everything I want to know about the author of a list item by expanding on Author/ID and adding fields from the User Information List to my $select statement in the format of Author/FieldName, but because the Picture field is a complex type, it won’t return that way.
My main point in my last response is that $expand or another similar method should let you drill down into complex types and return their values as well. It’d be great if dot notation worked in the $select statement for lists joined by $expand. For example, $select=Author/Title, Author/Picture.Url, Author/Picture.Description. Or maybe a combo of dot notation and the index: $select=Author/Title, Author/Picture.Url[0]
–Shelly
I hear you Shelly… it would be nice. Best approach is to add it to the UserVoice on O365 and them pimp it here for folks to vote it up… I would! :)
You can retrieve UserName, and than build the picture URL from that.
/_layouts/15/userphoto.aspx?size=S&accountname=[UserName]
Thanks Marc, I have spent 2 days to get Author details. This blog saw last. just one line saved me. Question for you can we get _metadata as well same like this?
Eranga