SharePoint Online: Get Lookup Column Value using PowerShell

Requirement: Get Lookup field value in SharePoint Online using PowerShell.

Scenario: We have a parent list called “Parent Projects” and a child list called “Project Milestones”. The “Parent Project Name” field in the child list is being looked up from the parent list’s “Project Name” field. Here is my PowerShell script to get lookup field values.

sharepoint online get set lookup field values using powershell

PowerShell to Get Lookup Field Value in SharePoint Online:

To retrieve the value of the lookup column in a SharePoint Online list, use this PowerShell.

#Load SharePoint CSOM Assemblies
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"

#Set parameter values
$SiteURL="https://crescent.sharepoint.com/Sites/PMO"
$ListName="Project Milestones"
$FieldName="ParentProjectName" #Internal Name
$ListItemID="25"

#Get Credentials to connect
$Cred= Get-Credential
$Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)
 
#Setup the context
$Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
$Ctx.Credentials = $Credentials

#Get the List Item
$List = $Ctx.Web.lists.GetByTitle($ListName)
$ListItem = $List.GetItemById($ListItemID)
$Ctx.Load($ListItem)
$Ctx.ExecuteQuery()

#Get Lookup Field Value
$Lookup = [Microsoft.SharePoint.Client.FieldLookupValue]$ListItem[$FieldName]
Write-host $Lookup.LookupValue

SharePoint Online: Retrieve Multi-Value Lookup Field Values using PowerShell:

When “Allow Multiple Values” is enabled, it becomes a multi-valued lookup column. So the script to read the lookup column goes here.

#Load SharePoint CSOM Assemblies
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"

#Set parameter values
$SiteURL="https://crescent.sharepoint.com/sites/PMO"
$ListName="Projects"
$FieldName="ParentProjects" #Internal Name
$ListItemID="25"

#Get Credentials to connect
$Cred= Get-Credential
$Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)
 
#Setup the context
$Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
$Ctx.Credentials = $Credentials

#Get the List Item
$List = $Ctx.Web.lists.GetByTitle($ListName)
$ListItem = $List.GetItemById($ListItemID)
$Ctx.Load($ListItem)
$Ctx.ExecuteQuery()

#Get Multi-Lookup Field Value
$LookupValueCollection = [Microsoft.SharePoint.Client.FieldLookupValue[]]$ListItem[$FieldName]

Foreach ($LookupValue in $LookupValueCollection)
{
    write-host $LookupValue.LookupValue 
}

PnP PowerShell to Retrieve Lookup Field Value

Here is the PnP PowerShell way to get the lookup field value.

#Parameters
$SiteURL = "https://crescent.sharepoint.com/sites/Retail"
$ListName = "Projects"
$LookupFieldName = "Parent_x0020_Project" #Internal Name

#Connect to SharePoint Online
Connect-PnPOnline -URL $SiteURL -Interactive

#Get the first list item
$ListItem = Get-PnPListItem -List $ListName -PageSize 500 | Select -First 1

#Get the Lookup Field value
$ListItem[$LookupFieldName].LookupValue

Similarly, to get all values from the multivalued lookup field, use the following:

$ListItem[$LookupFieldName].LookupValue -join "; "

Here is another post on updating lookup column value in SharePoint Online using PowerShell Update Lookup Field using PowerShell in SharePoint Online

Salaudeen Rajack

Salaudeen Rajack - SharePoint Expert with Two decades of SharePoint Experience. Love to Share my knowledge and experience with the SharePoint community, through real-time articles!

Leave a Reply

Your email address will not be published. Required fields are marked *