SharePoint Online : Export User Profile Properties using CSOM
Introduction
With SharePoint 2013, Microsoft has provided the ability to fetch User Profile information client side using CSOM API’s. User Profiles and user profile properties provide information about the SharePoint Users.
The scope of this article is to talk about how we can make use of CSOM API to fetch the user profile information on Office 365 and export profile properties to a csv file.
How does authentication happen
The authentication process with CSOM in SharePoint Online has been greatly simplified in SharePoint 2013 with the introduction of the SharePointOnlineCredentials class. With the use of this class, we simply need to pass the email id for login into our SharePoint Online site and the password in the SecureString format. This class has been introduced as a part of Microsoft.SharePoint.Client.dll
Case Statement
While working with Office 365 you must have thought about finding all the user profile information of the users belonging to a site collection and exporting those properties to a csv file.
Approach
In SharePoint 2010 using the client-side object model we are unable to retrieve the user profile properties whereas in SharePoint 2013 Microsoft has added a new assembly for the user profile service. Using "Microsoft.SharePoint.Client.UserProfiles.dll" located in hive 15 of the ISAPI folder we are able to retrieve the user profile properties using CSOM. As discussed above using SharePointOnline class we can authenticate to SharePoint Online and then using the API's provided by the dll mentioned above we can access user profile properties for any user.
Prerequisites
Below are the prerequisites dll that you need to refer in the solution:
- Microsoft.SharePoint.Client.dll
- Microsoft.SharePoint.Client.Runtime.dll
- Microsoft.SharePoint.Client.UserProfiles.dll
Implementation
Let us jump into the code and there we will get more clarity. We will be making use of a console application project for the sake of simplicity.
Let us first create a project of type Console Application and give it a name ExportUserProfile:
Next step is to add all the prerequisite dll’s mentioned above.
Once we have done this we are good to go. Now since we are connecting to Office 365 here so we first need to create the client context using the tenant admin site and after that we need to pass the credentials of the admin to the SharePointOnlineCredentials class that will be used for authenticating to SharePoint Online.
using (ClientContext tenantContext = new ClientContext("https://yousite-admin.sharepoint.com/"))
{
SecureString passWord = new SecureString();
foreach (char c in "yourpassword".ToCharArray())
passWord.AppendChar(c);
tenantContext.Credentials = new SharePointOnlineCredentials("admin@yoursite.onmicrosoft.com", passWord);
}
To understand the SharePointOnlineCredentials class better refer to this link where we already talked about how we can do basic operations using CSOM.
Once we have the client context ready, the next very important this is to create an instance of the PeopleManager class. This class forms the heart and soul of the User Profile. This class provides methods for operations related to people.
PeopleManager peopleManager = new PeopleManager(tenantContext);
Now the scenario is to fetch profile information about all the users that belong to a particular site collection. So we first need to fetch all the users that belong to the given site collection.
UserCollection users = tenantContext.Web.SiteUsers;
tenantContext.Load(users);
tenantContext.ExecuteQuery();
After that we need to iterate through each user and fetch its profile properties. There are lot of user profile properties available. So, either we can fetch and export all these profile properties to excel or we can have a limited set of properties exported. In the code snippet we are covering few desired and highly used properties only.
StringBuilder items = new StringBuilder();
string[] userProfileProperties = { "AccountName", "FirstName", "LastName", "PreferredName", "Manager", "AboutMe", "PersonalSpace", "PictureURL", "UserName", "WorkEmail", "SPS-Birthday" };
foreach (string propertyKey in userProfileProperties)
{
items.Append(propertyKey);
items.Append(",");
}
items.AppendLine();
In the above code snippet we are creating a string builder object and appending all the properties to the string builder object. This is used to create the first row in the csv which will act as the header.
In case you want to fetch the values of all the User Profile Properties for only a specific user then you can implement it like below.
using (ClientContext tenantContext = new ClientContext("https://yoursite-admin.sharepoint.com/"))
{
SecureString passWord = new SecureString();
foreach (char c in "yourpassword".ToCharArray())
passWord.AppendChar(c);
tenantContext.Credentials = new SharePointOnlineCredentials("geetanjali@yoursite.onmicrosoft.com", passWord);
PeopleManager peopleManager = new PeopleManager(tenantContext);
PersonProperties personProperties = peopleManager.GetPropertiesFor("i:0#.f|membership|geetanjali@yoursite.onmicrosoft.com");
tenantContext.Load(personProperties);
tenantContext.ExecuteQuery();
string csvFilePath = @"C:\Users\Geetanjali\Desktop\users.csv";
StringBuilder items = new StringBuilder();
foreach (var property in personProperties.UserProfileProperties)
{
items.Append(property.Key);
items.Append(",");
}
items.AppendLine();
foreach (var property in personProperties.UserProfileProperties)
{
items.Append(property.Value);
items.Append(",");
}
System.IO.File.WriteAllText(csvFilePath, items.ToString());
}
If you look at the above code snippet then you will see that here we will not be iterating through UserCollection but will simply be fetching all the user profile properties only for the user whose account name we provide to the method GetPropertiesFor. This method simply returns user properties for specific user.
The method syntax is
public PersonProperties GetPropertiesFor(
string accountName
)
The PersonProperties.UserProfileProperties returns all the user profile properties for the user. So in the above code snippet, we first fetch all the user profile properties for the specific user and then using the Key property we fetch all the property names for the user and using the Value we find the value for each user.
Below is the list of all the User Profile properties for any user.
- UserProfile_GUID
- SID
- ADGuid
- AccountName
- FirstName
- SPS-PhoneticFirstName
- LastName
- SPS-PhoneticLastName
- PreferredName
- SPS-PhoneticDisplayName
- WorkPhone
- Department
- Title
- SPS-JobTitle
- SPS-Department
- Manager
- AboutMe
- PersonalSpace
- PictureURL
- UserName
- QuickLinks
- WebSite
- SPS-DataSource
- SPS-MemberOf
- SPS-Dotted-line
- SPS-Peers
- SPS-Responsibility
- SPS-SipAddress
- SPS-MySiteUpgrade
- SPS-DontSuggestList
- SPS-ProxyAddresses
- SPS-HireDate
- SPS-DisplayOrder
- SPS-ClaimID
- SPS-ClaimProviderID
- SPS-ClaimProviderType
- SPS-LastColleagueAdded
- SPS-OWAUrl
- SPS-SavedAccountName
- SPS-SavedSID
- SPS-ResourceSID
- SPS-ResourceAccountName
- SPS-ObjectExists
- SPS-MasterAccountName
- SPS-UserPrincipalName
- SPS-PersonalSiteCapabilities
- SPS-O15FirstRunExperience
- SPS-PersonalSiteFirstCreationTime
- SPS-PersonalSiteLastCreationTime
- SPS-PersonalSiteNumberOfRetries
- SPS-PersonalSiteFirstCreationError
- SPS-DistinguishedName
- SPS-SourceObjectDN
- SPS-LastKeywordAdded
- SPS-FeedIdentifier
- SPS-PersonalSiteInstantiationState
- WorkEmail
- CellPhone
- Fax
- HomePhone
- Office
- SPS-Location
- Assistant
- SPS-PastProjects
- SPS-Skills
- SPS-School
- SPS-Birthday
- SPS-StatusNotes
- SPS-Interests
- SPS-HashTags
- SPS-PictureTimestamp
- SPS-EmailOptin
- SPS-PicturePlaceholderState
- SPS-PrivacyPeople
- SPS-PrivacyActivity
- SPS-PictureExchangeSyncState
- SPS-MUILanguages
- SPS-ContentLanguages
- SPS-TimeZone
- SPS-RegionalSettings-FollowWeb
- SPS-Locale
- SPS-CalendarType
- SPS-AltCalendarType
- SPS-AdjustHijriDays
- SPS-ShowWeeks
- SPS-WorkDays
- SPS-WorkDayStartHour
- SPS-WorkDayEndHour
- SPS-Time24
- SPS-FirstDayOfWeek
- SPS-FirstWeekOfYear
- SPS-RegionalSettings-Initialized
- OfficeGraphEnabled
Now coming back to our original scenario of fetching multiple user profiles for multiple users, the next step is to iterate through each user and fetch the property values for all the above mentioned properties.
foreach (User user in users)
{
try
{
if (user.PrincipalType != Microsoft.SharePoint.Client.Utilities.PrincipalType.User) continue;
UserProfilePropertiesForUser userProfilePropertiesForUser = new UserProfilePropertiesForUser(tenantContext, user.LoginName, userProfileProperties);
IEnumerable<string> profileProperties = peopleManager.GetUserProfilePropertiesFor(userProfilePropertiesForUser);
tenantContext.Load(userProfilePropertiesForUser);
tenantContext.ExecuteQuery();
foreach (string property in profileProperties)
{
items.Append(property);
items.Append(",");
}
items.AppendLine();
}
catch (Exception ex)
{
}
}
If you look at the above piece of code, we are first checking if the value obtained is of PrincipalType user or not. If the value is a user then we need to fetch its properties.
We first create an instance of UserProfilePropertiesForUser. The constructor accepts the client context, the account name and the string[] containing the properties that we want to fetch for the given user.
public UserProfilePropertiesForUser(
ClientRuntimeContext context,
string accountName,
string[] propertyNames
)
After that we create an IEnumerable object containing the user profile properties. Now simply iterate through this object to fetch user profile property values for every individual profile.
The complete code is as follows
using Microsoft.SharePoint.Client.UserProfiles;
using Microsoft.SharePoint.Client;
using System;
using System.Collections.Generic;
using System.Security;
using System.Text;
namespace ExportUserProfile
{
class Program
{
static void Main(string[] args)
{
using (ClientContext tenantContext = new ClientContext("https://yousite-admin.sharepoint.com/"))
{
SecureString passWord = new SecureString();
foreach (char c in "yourpassword".ToCharArray())
passWord.AppendChar(c);
tenantContext.Credentials = new SharePointOnlineCredentials("admin@yoursite.onmicrosoft.com", passWord);
PeopleManager peopleManager = new PeopleManager(tenantContext);
UserCollection users = tenantContext.Web.SiteUsers;
tenantContext.Load(users);
tenantContext.ExecuteQuery();
string csvFilePath = @"C:\Users\Geetanjali\Desktop\users.csv";
StringBuilder items = new StringBuilder();
string[] userProfileProperties = { "AccountName", "FirstName", "LastName", "PreferredName", "Manager", "AboutMe", "PersonalSpace", "PictureURL", "UserName", "WorkEmail", "SPS-Birthday" };
foreach (string propertyKey in userProfileProperties)
{
items.Append(propertyKey);
items.Append(",");
}
items.AppendLine();
foreach (User user in users)
{
try
{
if (user.PrincipalType != Microsoft.SharePoint.Client.Utilities.PrincipalType.User) continue;
UserProfilePropertiesForUser userProfilePropertiesForUser = new UserProfilePropertiesForUser(tenantContext, user.LoginName, userProfileProperties);
IEnumerable<string> profileProperties = peopleManager.GetUserProfilePropertiesFor(userProfilePropertiesForUser);
tenantContext.Load(userProfilePropertiesForUser);
tenantContext.ExecuteQuery();
foreach (string property in profileProperties)
{
items.Append(property);
items.Append(",");
}
items.AppendLine();
}
catch (Exception ex)
{
}
}
System.IO.File.WriteAllText(csvFilePath, items.ToString());
}
}
}
}
Once you run this code an csv file gets created. If you open that csv file you will observe the following data.
Conclusion
Hope this article gives enough insights to understand how the new Microsoft.SharePoint.Client.UserProfiles.dll can be used along with SharePointOnlineCredentials class to fetch user profile properties in SharePointOnline.
References
Have a look at the following links for further reference.
- SharePointOnlineCredentials
- SecureString
- PeopleManager
- Retrieve User Profile Properties
- UserProfilePropertiesForUser
- Microsoft.SharePoint.Client.UserProfiles
- Work with User Profiles in SharePoint 2013
- GetPropertiesFor method
- PersonProperties
- List of PersonProperties
- UserProfileProperties
You can download the entire solution from the TechNet Gallery at http://gallery.technet.microsoft.com/SharePoint-Online-Export-f9b38f2c