Use Case: Export User Profiles to a SharePoint list – phonebook style

SharePoint User Profiles Service Application has a lot of nice and very useful features but it misses one small but important thing. Customers often want to display all their contacts (from User Profile Store) in a single list, phonebook style.

Here is a simple solution how to achieve this with a PowerShell script. The script queries the User Profile store and then copies user profile information to a simple SharePoint list (based on Contacts list template). The final result might look like this:

Please note:

  • If your company has more than 1000 employees this solution might not scale properly.
  • The script below was designed for English based SharePoint site templates, additional modifications might be required for other languages.
  • The script must be executed under user that has both read privileges to User Profile Service Application and contribute privileges for your site.

Configuration steps:

  • On your intranet/team site create a list based on the Contacts list template
  • Create an additional column in this list: UserName – Single line of text

Here is the script:

01cls
02if((Get-PSSnapin | Where {$_.Name -eq "Microsoft.SharePoint.PowerShell"}) -eq $null) {
03 Add-PSSnapin Microsoft.SharePoint.PowerShell;
04}
05function Enumerate-SPUserProfiles($webUrl, $listName)
06{
07 $x= [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")
08 $x= [System.Reflection.Assembly]::LoadWithPartialName("microsoft.sharepoint.portal")
09 $x= [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.Office.Server")
10 $x= [System.Reflection.Assembly]::LoadWithPartialName("System.Web")
11 $sites = Get-SPSite
12 $context = [Microsoft.Office.Server.ServerContext]::GetContext($sites[0])
13 $profileManager = new-object Microsoft.Office.Server.UserProfiles.UserProfileManager($context)
14 $web = Get-SPWeb $webUrl
15 $list = $web.Lists[$listName]
16 # gets all the user profiles, those that have domain\username as DisplayName are excluded
17 $profileManager.GetEnumerator() | ?{$_.DisplayName -notlike '*\*' -and $_.ProfileType -eq "User" } | foreach-object { Update-PhoneBook $_ $web $list $webUrl } |
18}
19function Update-PhoneBook($user, $web, $list, $webUrl)
20{
21 if($user.Item("FirstName") -ne "" -and $user.Item("FirstName") -ne $null)
22 {
23 $spQuery = new-object Microsoft.SharePoint.SPQuery
24 $userName = $user.Item("UserName")
25 $camlQuery = "<Where><Eq><FieldRef Name='UserName' /><Value Type='Text'>$userName</Value></Eq></Where>";
26 $spQuery.Query = $camlQuery
27 $spQuery.RowLimit = 1
28 $spListItemCollection = $list.GetItems($spQuery)
29 $domain = (gwmi WIN32_ComputerSystem).Domain
30 if($spListItemCollection.Count -ge 1)
31 {
32 Write-Host "Updating $username..."
33 Update-Contact $spListItemCollection[0] $user $domain $webUrl
34 }
35 else
36 {
37 Write-Host "Adding $username..."
38 $contact = $list.items.add();
39 Update-Contact $contact $user $domain $webUrl
40 }
41 }
42}
43function Update-Contact($contact, $user, $domain, $webUrl)
44{
45 $lastName = $user.Item("LastName");
46 $contact["Title"] = "$lastName";
47 $firstName = $user.Item("FirstName");
48 $contact["FirstName"] = "$firstName";
49 $workEmail = $user.Item("WorkEmail");
50 $contact["Email"] = "$workEmail";
51 $workPhone = $user.Item("WorkPhone");
52 $contact["WorkPhone"] = "$workPhone";
53 $CellPhone = $user.Item("CellPhone");
54 $contact["CellPhone"] = "$cellPhone";
55 $office = $user.Item("Office");
56 $contact["Office"] = "$office";
57 $department = $user.Item("Department");
58 $contact["Department"] = "$department";
59 $userName = $user.Item("UserName");
60 $contact["UserName"] = "$userName";
61 $contact.update()
62}

You can run it by typing:

1Enumerate-SPUserProfiles "http://your_site_url" "List_Name"
Tags