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
listthat 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.
1$_CLIENT_ID =
2$_ROOT_URL =
3$_TENANT =
4$_CERTIFICATE =
5
6Connect-PnPOnline -ClientId $_CLIENT_ID -Url $_ROOT_URL -Tenant $_TENANT -CertificatePath $_CERTIFICATE
7
8$List = "Employee Directory"
9
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')
13
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 }
29
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 }
47
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 }
65
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.
Images.
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:
Changing to a Gallery view, it looks equally as good:
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."}
3
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"
3
4
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"
2
3$fileUpload | fl
4
5...
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-
11...
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)'}"}
2
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:
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
2{"type":"thumbnail","fileName":"employee.jpg","nativeFile":{},"fieldName":"Image","serverUrl":"https://tenant.sharepoint.com","fieldId":"904ce852-2a6d-4f41-a95d-","serverRelativeUrl":"/teams/tlatest12-renamedURL/SiteAssets/Lists/aa346236-d9ec-4491-b7f9-/employee1.jpg","id":"831be3d6-2c0a-44c3-8f5a-"}
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
2{'fileName':'employee.jpg','ServerRelativeUrl':''}
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..
1{
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.
11}
Of the fields that are returned from a working image, we've got some properties that we need dig into a bit deeper.
fieldId
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"
2
3
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.
serverRelativeUrl
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"
2
3
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:
Id
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}
2
311
4/teams/tlatest12-renamedURL/SiteAssets/Lists/aa346236-d9ec-4491-b7f9-/employee.jpg
5cae4bc08-ec1a-4f1b-b5d6-
6
712
8/teams/tlatest12-renamedURL/SiteAssets/Lists/aa346236-d9ec-4491-b7f9-/employee1.jpg
9831be3d6-2c0a-44c3-8f5a-
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`"}"}
2
3Id Title GUID
4-- ----- ----
54 7a7a8fa0-2025-46fd-bf91-
Checking out the upload, it looks successful:
In the gallery:
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
2
3
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
17
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`"}"}
19
20Id Title GUID
21-- ----- ----
225 332f8209-e189-4c08-8357-9c127557b55d
And neat-o enough, it seems to work:
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.