Employee Directory in SharePoint Online

The Issue

We have been working on a long, slow project to bring some new services online for our staff. One is our "Knowledge Hub", the other is our "Connect" site - a SharePoint Hub site, linking various functional group's SharePoint sites together.

This post is about "Connect".

One feature that is seemingly missing in SharePoint online is a straight up employee list. There are a few alternatives - people finders, org chart visualizers, etc.. but we're a small enough operation, that a list of people is sufficient for our needs. Additionally, another page showing all the staff's photos has been valuable to help figure who's who.

The code for this operation is reasonably straightforward:

  • Connect to the SharePoint site
  • Get a list of entries in the list that contains the people
  • Get a list of entries from Active Directory of people who are considered employees
  • Compare those two lists
  • Remove the entries that shouldn't be there
  • Create (and upload a photo) of the entries that should be there.

Working Script

I still have a couple small to-do's, like actually scheduling and automating it.. but!

After setting up the AzureAD application for PnP, connecting is easy, the rest of the code is pretty straightforward.

 2$_ROOT_URL =
 3$_TENANT =
 6Connect-PnPOnline -ClientId $_CLIENT_ID -Url $_ROOT_URL -Tenant $_TENANT -CertificatePath $_CERTIFICATE
 8$List = "Employee Directory"
10function main() {
11    # Attributes that are compared. If any of these change, a new record will be created.
12    $_COMPARE_TARGETS = @('Office','Department','OfficePhone','MobilePhone')
14    # Get all staff from Active Directory, and pack them in a handy array.
15    $Staff = get-aduser -filter { Enabled -eq $true -and Company -eq "Employee" } -Properties Initials,Office,Department,OfficePhone,MobilePhone
16    $ADUsers = [System.Collections.ArrayList]@()
17    foreach ($EachStaff in $Staff) {
18        $ADUsers.Add([PSCustomObject]@{
19            Firstname = $EachStaff.GivenName;
20            LastName = $EachStaff.Surname;
21            Initials = $EachStaff.Initials;
22            Office = $EachStaff.Office;
23            Department = $EachStaff.Department;
24            OfficePhone = $EachStaff.OfficePhone;
25            MobilePhone = $EachStaff.MobilePhone;
26            Title = "$($EachStaff.GivenName) $($EachStaff.Surname)"
27        }) | Out-Null
28    }
30    # Get all staff from the list in SharePoint online, and pack them in a handy array.
31    # There are subtle difference in field names here.
32    $items = Get-PnPListItem -List $list -PageSize 500
33    $SPOUsers = [System.Collections.ArrayList]@()
34    foreach ($EachSPOUser in $items) {
35        $SPOUsers.Add([PSCustomObject]@{
36            Firstname = $EachSPOUser.FieldValues.Firstname;
37            LastName = $EachSPOUser.FieldValues.LastName;
38            Initials = $EachSPOUser.FieldValues.Initials;
39            Office = $EachSPOUser.FieldValues.Office;
40            Department = $EachSPOUser.FieldValues.Department
41            OfficePhone = $EachSPOUser.FieldValues.OfficePhone;
42            MobilePhone = $EachSPOUser.FieldValues.MobilePhone;
43            Title = "$($EachSPOUser.FieldValues.Firstname) $($EachSPOUser.FieldValues.LastName)"
44            _SPOID = $EachSPOUser.Id
45        }) | Out-Null
46    }
48    # Compare the two lists.
49    $CompareResults = Compare-Object -ReferenceObject $ADUsers -DifferenceObject $SPOUsers -PassThru -Property $_COMPARE_TARGETS
50    $Counter = 1
51    foreach ($Comparison in $CompareResults) {
52        Write-Progress -Activity "Updating SPO" -Status "Working on $($Counter) of $($CompareResults.Count)" -PercentComplete ($Counter/$CompareResults.Count *100)
53        if ($Comparison.SideIndicator -eq "<=") {
54            # This record needs to be added
55            $AddRecord = Add-PnPListItem -List $list -Values @{
56                Firstname = $Comparison.FirstName;
57                LastName = $Comparison.LastName;
58                Initials = $Comparison.Initials;
59                Office = $Comparison.Office;
60                Department = $Comparison.Department;
61                OfficePhone = $Comparison.OfficePhone;
62                MobilePhone=$Comparison.MobilePhone;
63                Title="$($Comparison.Firstname) $($Comparison.LastName)"
64            }
66            $InsertID = $AddRecord.Id
67            $EmployeePhoto = Join-Path "C:\Employee Photos\" "$($Comparison.Initials).JPG"
68            if (Test-Path $EmployeePhoto) {
69                $FileUploaded = Add-PnPFile -Path $EmployeePhoto -Folder "/sites/C-FERConnect/SiteAssets/Employee Directory Photos"
70                Set-PnPListItem -List $list -Identity $InsertID -Values @{Image = "{`"fieldName`":`"Image`",`"nativeFile`":{},`"type`":`"thumbnail`",`"fileName`": `"$($Comparison.Initials).JPG`", `"serverRelativeUrl`": `"$($FileUploaded.ServerRelativeUrl)`", `"fieldId`":`"xxx`", `"id`":`"$($FileUploaded.UniqueId)`",`"serverUrl`":`"https://tenant.sharepoint.com`"}"}
71            }
72        } else {
73            # This record $needs to be removed from SPO
74            if ($null -ne $Comparison._SPOID) {
75                Remove-PnPListItem -List $list -Identity $Comparison._SPOID -Force
76            }
77        }
78        $Counter++
79    }
80    Write-Progress -Activity "Updating SPO" -Completed

See all that garbage around line 70? Yeah, that was lots of fun to figure out.


My root issue was: How do I use PnP to add the employee's picture?

There was a similar question posted on Stack Overflow that got me sorta there, but not quite. In a test site, if I set the information manually (like, through the web UI), things look pretty good:

Looks good in a list

Changing to a Gallery view, it looks equally as good:

Looks good in a gallery

ok, so removing the content, and doing this in code requires creating the content first, then uploading the image, then updating the field in the original upload.

1# First create the content.
2> Add-PnPListItem -List "Employee Directory" -Values @{FirstName="Doggy";LastName="Dog."}
4Id    Title                                              GUID
5--    -----                                              ----
62                                                        3576fb44-d288-...

Now, upload the image. I'm being lazy here and just throwing the file into the Documents library. Ideally, I want to attach these images to the list entry.. but that's work for another day. In the meantime.. the upload.

1# Now upload the file.
2> Add-PnPFile -Path .\employee.jpg -Folder "Shared Documents"
5Name         Type Items/Size Last Modified
6----         ---- ---------- -------------
7employee.jpg File    2305574 10/19/2022 2:07:54 AM

But oops, I need the relative path to this file. So let's do that again, capture the value, the evaluate it:

 1> $fileUpload = Add-PnPFile -Path .\employee.jpg -Folder "Shared Documents"
 3$fileUpload | fl
 6Length                                       : 2305574
 7ServerRelativeUrl                            : /teams/tlatest12-renamedURL/Shared Documents/employee.jpg
 8TimeCreated                                  : 10/19/2022 2:07:54 AM
 9TimeLastModified                             : 10/19/2022 2:09:25 AM
10UniqueId                                     : 8d29cf38-5aac-4880-a083-

So, I care about ServerRelativeUrl. Cool. On to step 3, updating the initial list entry with a reference to the newly created file. Note from above, the id 2 came was returned when I created the list entry.

1> Set-PnPListItem -List "Employee Directory" -Identity 2 -Values @{Image= "{'fileName':'employee.jpg','serverRelativeUrl':'$($fileUpload.ServerRelativePath)'}"}
3Id    Title                                              GUID
4--    -----                                              ----
52                                                        3576fb44-d288-408a-86aa-

Evaluating the results, we don't quite get the output we want. The image in the list is blank, the edit view kinda shows the filename, and the Gallery view is more or less broken:

Powershell Upload SPO list

Powershell Upload SPO gallery

So what's going on?

Going back to the manual upload, we can explore the image entry in the list.

1> (Get-PnPListItem -List "Employee Directory" -Id 3).FieldValues.Image |fl

The automatic upload looks a bit different, and surprisingly close to what was supplied in step 3 when Set-PnPListItem.. was called:

1> (Get-PnPListItem -List "Employee Directory" -Id 2).FieldValues.Image

Oh look, we're missing a bunch of data. So where's it all coming from? Well, most of it isn't too hard to get. Prettying up the returned JSON, and commenting it..

 2    "type": "thumbnail",          // This is the actual 'type' that PnP sees this field as.
 3    "fileName": "employee.jpg",   // This should be reasonably obvious
 4    "nativeFile": {},             // No idea
 5    "fieldName": "Image",         // Field name. Also reasonably obvious
 6    "serverUrl": "https://tenant.sharepoint.com",   // This is our root SharePoint online site
 7    "fieldId": "904ce852-2a6d-4f41-a95d-",          // More on this in a sec.
 8    // Interesting. More on this, too.
 9    "serverRelativeUrl": "/teams/tlatest12-renamedURL/SiteAssets/Lists/aa346236-d9ec-4491-b7f9-/employee1.jpg",
10    "id": "831be3d6-2c0a-44c3-8f5a-"                // Yep, more on this too.

Of the fields that are returned from a working image, we've got some properties that we need dig into a bit deeper.


This is pretty obvious from the name. It's the unique identifier for the field. We can get this by doing what we just did (Get-PnPListItem ..) and keeping the field value, but we can also get it from SharePoint:

1> Get-PnPField -List "Employee Directory" "Image"
4Title InternalName Id
5----- ------------ --
6Image Image        904ce852-2a6d-4f41-a95d-

And while I am truncating the GUID so that you, the reader, can't copy it (dunno why you would, or even what value it'd provide), it's evident that the two ID's match.

I opted for the first method, and I'm assuming that this field ID will not change. That may bite me in the bottom later, but that's a later me problem.


This one is a bit interesting. It seems that when you upload an image to an Image field type (well, Thumbnail I suppose..), it's storing the image in the SiteAssets/Lists/<guid>/ directory. I can get the ID of the "Employee Directory" list:

1> get-pnplist "Employee Directory"
4Title              Id                            Url
5-----              --                            ---
6Employee Directory aa346236-d9ec-4491-b7f9-      /teams/tlatest12-renamedURL/Lists/Employee Directory

And once again, the ID's match (at least the first 4 groups).

If I go check out that path in SharePoint, I'm not surprised to see the file(s) I've uploaded:

SharePoint Online SiteAsset listing


Finally, Id. This is GUID attached to the uploaded file. Formatting on this one's a bit weird (because there's multiple files in the SiteAsset List), but you get the gist:

1(Get-PnPListItem -List "Site Assets" -FolderServerRelativeUrl "/teams/tlatest12-renamedURL/SiteAssets/Lists/aa346236-d9ec-4491-b7f9-0ed3f9d38a4c") | % {$_.FieldValues.ID, $_.FieldValues.FileRef, $_.FieldValues.GUID}

We see that file 12 matches.

So, how to put it all together.

Let's start with uploading the file. Same as before.

1> $fileUpload = Add-PnPFile -Path .\employee.jpg -Folder "Shared Documents"

Now, create the new list item, but we're adding the image bit. This is a gross bit of JSON that don't get the pleasure of passing in directly...

1> Add-PnPListItem -List "Employee Directory" -Values @{FirstName="Doggy";LastName="Dog.";Image = "{`"fieldName`":`"Image`",`"nativeFile`":{},`"type`":`"thumbnail`",`"fileName`": `"employeePhoto.JPG`", `"serverRelativeUrl`": `"$($fileUpload.ServerRelativeUrl)`", `"fieldId`":`"904ce852-2a6d-4f41-a95d-`", `"id`":`"$($fileUpload.UniqueId)`",`"serverUrl`":`"https://teanant.sharepoint.com`"}"}
3Id    Title                                              GUID
4--    -----                                              ----
54                                                        7a7a8fa0-2025-46fd-bf91-

Checking out the upload, it looks successful:

PnP Upload SPO list, 2nd try

In the gallery:

PnP Upload SPO gallery, 2nd try

Todo ..

The original reference suggested that you upload the files to a folder in the library, but list items support attachments.. and I can refer to an attachment by URL, but there's no ID:

 1> Add-PnPListItemAttachment -List "Employee Directory" -Identity 4 -Path .\employee.jpg
 4FileName           : employee.jpg
 5FileNameAsPath     : employee.jpg
 6ServerRelativeUrl  : /teams/tlatest12-renamedURL/Lists/Employee Directory/Attachments/4/employee.jpg
 7ServerRelativePath : /teams/tlatest12-renamedURL/Lists/Employee Directory/Attachments/4/employee.jpg
 8Key                : employee.jpg
 9All                :
10Metadata           : {[restId, employee.jpg], [type, SP.Attachment]}
11PnPContext         : PnP.Core.Services.PnPContext
12Parent             : {employee.jpg}
13Requested          : True
14MappingHandler     :
15PostMappingHandler :
16HasChanges         : False
18> Add-PnPListItem -List "Employee Directory" -Values @{FirstName="NewDoggy";LastName="Dog.";Image = "{`"fieldName`":`"Image`",`"nativeFile`":{},`"type`":`"thumbnail`",`"fileName`": `"employeePhoto.JPG`", `"serverRelativeUrl`": `"/teams/tlatest12-renamedURL/Lists/Employee Directory/Attachments/4/employee.jpg`", `"fieldId`":`"904ce852-2a6d-4f41-a95d-`", `"serverUrl`":`"https://tenant.sharepoint.com`"}"}
20Id    Title                                              GUID
21--    -----                                              ----
225                                                        332f8209-e189-4c08-8357-9c127557b55d

And neat-o enough, it seems to work:

List item attachments list

List item attachments gallery

This isn't quite perfect - The image I'm using is actually attached to list item 4, but the general approach seems to suggest:

  • Create a list item
  • Upload a file to that list item as an attachment
  • Set the Image field to the uploaded file URL.