Wednesday, 23 March 2016

SharePoint Online / Office 365 - Fetch all users manager and writing to .CSV file using CSOM + PowerShell

Hi All,

In this article I’ll explain in details step by step how to get all users manager in CSV file using CSOM and PowerShell.

Background: In one of our Office 365 project we have Approval workflow. We have written 2013 platform approval workflow. In workflow we are getting current users manager and then starting a task process (approval request) with Manager.

Need to write this script: Workflow was working fine. But for some users it didn't work. We looked into this issue and noticed that some user has no manager set in there user profile. There manager field is empty.

We need to update the manager field of those users. We need to have list of all users whose manager field is empty. So the need of script to find all the users whose manager field is empty and writing them to .CSV file. So that we can share the .CSV file with customer, they will fill the manager field and then we will update the manager property in user profile.

Step By Step PowerShell script to get manager and writing to .CSV file:

Step 1: Load the required libraries

 # add references to SharePoint client assemblies and authenticate to Office 365 site - required for CSOM
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.UserProfiles.dll"
 
Step 2: Set the required variables like root SiteCollection URL , tenant admin UserName and Password of your site to connect as
 
#Specify tenant admin and URL
$User = "" 
 
#Configure Site URL and User
$SiteURL = ""
 
#Password
$Password =" "
 
$securePassword = ConvertTo-SecureString -String $Password -AsPlainText –Force
 
$Creds = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($User,$securePassword)
 
Step 3: Get the Microsoft.SharePoint.Client.ClientContext instance and set the credentials as
 
 
 
#client context object and setting the credentials 
$Context = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
 
$Context.Credentials = $Creds
 
Step 4: Identify all the users and load them
 
#Users collection
$Users = $Context.Web.SiteUsers
$Context.Load($Users)
$Context.ExecuteQuery()
 
Step 5: Creating people manager instance
 
#create People Manager object to retrieve profile data
$PeopleManager = New-Object Microsoft.SharePoint.Client.UserProfiles.PeopleManager($Context)
 
Step 6: Lopping through each user in Users collection, retrieving user profile, fetching all user profile properties to get the Manager
 
#CSV file path to write the user profile properties 
$Output = "c:\userproperties.csv"
 
Foreach ($User in $Users)
{
    #Get properties for user 
    $UserProfile = $PeopleManager.GetPropertiesFor($User.LoginName)
    $Context.Load($UserProfile)
    $Context.ExecuteQuery()
 
    If ($UserProfile.Email -ne $null)
     {
         $upp = $UserProfile.UserProfileProperties
 
         #retrieving User display name, Email, Manager and Job Title 
$Properties = $UserProfile.DisplayName$UserProfile.Email$UPP.Manager
#writing all above properties to csv file 
$Properties -join "," | Out-File -Encoding default -Append -FilePath $Output
} #If 
         
} #for each
 
Also using the above script we can fetch any other user properties also. We can make this script generic also by passing property name or we can loop through each and every property and write to CSV file. 
 
Thanks!
 
Enjoy Reading :)
 
As usual any comment / suggestions / feedback / questions always welcome :)

2 comments:

Amjad said...

Nice Article Prasham

Prasham Sabadra said...

Thanks Amjad!!!