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.
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
