SharePoint Online / Office 365 - Fetch All Users Manager and Writing to .CSV File using CSOM + PowerShell


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:

  1. #Specify tenant admin and URL  
  2. $User = ""   
  4. #Configure Site URL and User  
  5. $SiteURL = ""   
  9. #Password  
  10. $Password =" "  
  12. $securePassword = ConvertTo-SecureString -String $Password -AsPlainText –Force  
  14. $Creds = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($User,$securePassword)   
Step 3: Get the Microsoft.SharePoint.Client.ClientContext instance and set the credentials as:
  1. #client context object and setting the credentials   
  2. $Context = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)   
  4. $Context.Credentials = $Creds   
Step 4: Identify all the users and load them.
  1. #Users collection  
  2. $Users = $Context.Web.SiteUsers  
  3. $Context.Load($Users)  
  4. $Context.ExecuteQuery()   
Step 5: Creating people manager instance:
  1. #create People Manager object to retrieve profile data  
  2. $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:
  1. #CSV file path to write the user profile properties   
  2. $Output = "c:\userproperties.csv"  
  4. Foreach ($User in $Users)  
  5. {  
  6.     #Get properties for user   
  7.     $UserProfile = $PeopleManager.GetPropertiesFor($User.LoginName)  
  8.     $Context.Load($UserProfile)  
  9.     $Context.ExecuteQuery()  
  11.     If ($UserProfile.Email -ne $null)  
  12.     {  
  13.          $upp = $UserProfile.UserProfileProperties  
  15.          #retrieving User display name, Email, Manager and Job Title   
  16. $Properties = $UserProfile.DisplayName, $UserProfile.Email, $UPP.Manager  
  18.         #writing all above properties to csv file   
  19. $Properties -join "," | Out-File -Encoding default -Append -FilePath $Output  
  20.     } #If   
  22.   } #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.


Build smarter apps with Machine Learning, Bots, Cognitive Services - Start free.

Start Learning Now