• Home
  • /
  • 2023
  • /
  • How to get UserNames in SPO-list with Graph

How to get UserNames in SPO-list with Graph

For instance, you have a SharePoint Online List with a bunch of users in it (column field ‘People’), for example ‘Users who followed training’. And now you want to check if each M365-Group member has followed training. In this post I’ll explain how I did it.

I’ve created a function ‘Get-ListUsersWhoFollewedTraining’. The first step is to get a Graph API connection.

function Get-ListUsersWhoFollewedTraining {
        param (
            [string]$clientId
        )

        Write-Host "`tConnect to Microsoft Graph"

        # Connect to Micorosoft Graph
        try { 
            [System.Collections.IDictionary]$graphApiConnection = New-GraphApiConnection -applicationId $clientId
        } catch {
            Write-Host -ErrorFlag "`t...something went wrong with [New-GraphApiConnection]"
            Write-Host -ErrorFlag "`tERROR : $($_.Exception.message)"
            $status = 1
            return $status
        }
}

The function ‘New-GraphApiConnection’ is described in this post

Than you can connect to SharePoint Online with a site- and listname. In this example I hardcoded the site- and listname; you can also apply them as parameters.

function Get-ListUsersWhoFollewedTraining {
        param (
            [string]$clientId
        )

        Write-Host "`tGet list of Users who follewed training"

        # Connect to Micorosoft Graph
        try { 
            [System.Collections.IDictionary]$graphApiConnection = New-GraphApiConnection -applicationId $clientId
        } catch {
            Write-Host -ErrorFlag "`t...something went wrong with [New-GraphApiConnection]"
            Write-Host -ErrorFlag "`tERROR : $($_.Exception.message)"
            $status = 1
            return $status
        }
        
        # Connect to SPO
        $siteName = '<sitename>'
        $uri = "https://graph.microsoft.com/v1.0/sites?search=$siteName"
        $siteObj  = (Invoke-RestMethod -Headers $graphApiConnection -Uri $uri -Method GET).Value | Select-Object id

        # Connect to List
        $listName = '<listname>'
        $uri = "https://graph.microsoft.com/v1.0/sites/$($siteObj.id)/lists/$listName"  
        $listObj = Invoke-RestMethod -Headers $graphApiConnection -Uri $uri -Method GET | Select-Object id

        # Get List Users
        $uri = "https://graph.microsoft.com/v1.0/sites/$($siteObj.id)/lists/$($listObj.id)/items?expand=fields"
        $listUsers = ((Invoke-RestMethod -Headers $graphApiConnection -Uri $uri -Method GET).value).fields | Select-Object MedewerkerLookupId
}

Now you have a list of Users who followed the training. But…
In this list you’ll only see an ID of the user; the info about the user (like DisplayName, UPN, emailaddress) is in the ‘HiddenUserList’ behind this page. To go to the HiddenUserList, you can use ‘/_layouts/15/people.aspx?membershipGroupId=0’ after the URL. In this example the ID of the HiddenUserList is hardcoded given to the function. Put al info in a list so later we can loop through it.

function Get-ListUsersWhoFollewedTraining {
        param (
            [string]$clientId,
            [string]$ListHiddenUserInfoId
        )

        Write-Host "`tGet list of Users who follewed training"

        # Connect to Micorosoft Graph
        try { 
            [System.Collections.IDictionary]$graphApiConnection = New-GraphApiConnection -applicationId $clientId
        } catch {
            Write-Host -ErrorFlag "`t...something went wrong with [New-GraphApiConnection]"
            Write-Host -ErrorFlag "`tERROR : $($_.Exception.message)"
            $status = 1
            return $status
        }
        
        # Connect to SPO
        $siteName = '<sitename>'
        $uri = "https://graph.microsoft.com/v1.0/sites?search=$siteName"
        $siteObj  = (Invoke-RestMethod -Headers $graphApiConnection -Uri $uri -Method GET).Value | Select-Object id

        # Connect to List
        $listName = '<listname>'
        $uri = "https://graph.microsoft.com/v1.0/sites/$($siteObj.id)/lists/$listName"  
        $listObj = Invoke-RestMethod -Headers $graphApiConnection -Uri $uri -Method GET | Select-Object id

        # Get List Users
        $uri = "https://graph.microsoft.com/v1.0/sites/$($siteObj.id)/lists/$($listObj.id)/items?expand=fields"
        $listUsers = ((Invoke-RestMethod -Headers $graphApiConnection -Uri $uri -Method GET).value).fields | Select-Object MedewerkerLookupId

        # Get List Hidden User items
        # Get through "/_layouts/15/people.aspx?membershipGroupId=0" after URL
        $uri = "https://graph.microsoft.com/v1.0/sites/$($siteObj.id)/lists/$ListHiddenUserInfoId/items?expand=fields"
        $listHiddenUserInfo = ((Invoke-RestMethod -Headers $graphApiConnection -Uri $uri -Method GET).value).fields | Select-Object UserSelection,SipAddress,Title

        $AllHiddenUsers = @() # Save here selected info of the HiddenUserList
        # Save each value in temporary object $hiddenUserObject
        ForEach ($HiddenUserItem in $ListHiddenUserInfo) {
        $Obj_HiddenUserInfo = [PSCustomObject]@{
                LookupId = $HiddenUserItem.UserSelection 
                Upn = $HiddenUserItem.SipAddress
                DisplayName = $HiddenUserItem.Title
        }
        $AllHiddenUsers += $Obj_HiddenUserInfo # Add temporary object to the list
}

The last thing to do is to loop through al users from the list en check if they exist in the list of AllHiddenUsers

function Get-ListUsersWhoFollewedTraining {
        param (
            [string]$clientId,
            [string]$ListHiddenUserInfoId
        )

        Write-Host "`tGet list of Users who follewed training"

        # Connect to Micorosoft Graph
        try { 
            [System.Collections.IDictionary]$graphApiConnection = New-GraphApiConnection -applicationId $clientId
        } catch {
            Write-Host -ErrorFlag "`t...something went wrong with [New-GraphApiConnection]"
            Write-Host -ErrorFlag "`tERROR : $($_.Exception.message)"
            $status = 1
            return $status
        }
        
        # Connect to SPO
        $siteName = '<sitename>'
        $uri = "https://graph.microsoft.com/v1.0/sites?search=$siteName"
        $siteObj  = (Invoke-RestMethod -Headers $graphApiConnection -Uri $uri -Method GET).Value | Select-Object id

        # Connect to List
        $listName = '<listname>'
        $uri = "https://graph.microsoft.com/v1.0/sites/$($siteObj.id)/lists/$listName"  
        $listObj = Invoke-RestMethod -Headers $graphApiConnection -Uri $uri -Method GET | Select-Object id

        # Get List Users
        $uri = "https://graph.microsoft.com/v1.0/sites/$($siteObj.id)/lists/$($listObj.id)/items?expand=fields"
        $listUsers = ((Invoke-RestMethod -Headers $graphApiConnection -Uri $uri -Method GET).value).fields | Select-Object MedewerkerLookupId

        # Get List Hidden User items
        # Get through "/_layouts/15/people.aspx?membershipGroupId=0" after URL
        $uri = "https://graph.microsoft.com/v1.0/sites/$($siteObj.id)/lists/$ListHiddenUserInfoId/items?expand=fields"
        $listHiddenUserInfo = ((Invoke-RestMethod -Headers $graphApiConnection -Uri $uri -Method GET).value).fields | Select-Object UserSelection,SipAddress,Title

        $AllHiddenUsers = @() # Save here selected info of the HiddenUserList
        # Save each value in temporary object $hiddenUserObject
        ForEach ($HiddenUserItem in $ListHiddenUserInfo) {
        $Obj_HiddenUserInfo = [PSCustomObject]@{
                LookupId = $HiddenUserItem.UserSelection 
                Upn = $HiddenUserItem.SipAddress
                DisplayName = $HiddenUserItem.Title
        }
        $AllHiddenUsers += $Obj_HiddenUserInfo # Ad temporary object to the list

        # Loop through the userlist and save UPN's
        $AllTrainedUsers = @()
        ForEach ($UserItem in $listUsers) {
            $User = $AllHiddenUsers | Where-Object {$_.lookupId -eq $UserItem.MedewerkerLookupId}
            $AllTrainedUsers += $User.Upn
        }

        return $AllTrainedUsers
}

Now you have a object with all trained users in it. To check if the members of a M365 Group has followed the training, just get all members of the M365 Group and check if they exist in the $AllTrainedUsers object.

Good luck with your code of getting UserName of a SharePoint Online List with the Graph.API in PowerShell

I’ve you have a better way of doing this, please let me know be sending an email to alextop@m365withalextop.com

0
0

Leave a Reply