SharePoint Online: PowerShell to Get Site Columns
Requirement: Get Site Columns in SharePoint Online using PowerShell.
SharePoint Online: How to Get Site Columns?
Site columns or metadata fields you create at any list or library stay within that list or library. Say you create a choice column “Department” in a list with a number of choices. If you need the same column with the same choices in another list or library on the same site, you’ll end up re-creating it. Also, If you have to change a column, you have to do it in all the places where it is being used. Whereas, Site columns in SharePoint are created centrally at the site level and can be used in any list or library on the site. The primary purpose of SharePoint site columns is reusability and manageability. Once you create a Site Column, you can reuse it anywhere on the site, and if you want to make changes to the column, you can do it once, and it gets updated in all the places where the particular site column is in use.
To get all site columns in SharePoint Online, Go to Site Settings >> Click on “Site Columns” under the Web Designer Galleries section.
Site columns are hierarchical. So, when you create a site column, it flows on all sites underneath it.
How to get a Site Column in SharePoint Online using PowerShell?
To get a site column in SharePoint Online, use this CSOM PowerShell script:
#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 Variables
$SiteURL= "https://crescent.sharepoint.com/sites/Marketing"
$InternalName = "MyEditor"
#Setup Credentials to connect
$Cred = Get-Credential
Try {
#Setup the context
$Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
$Ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.UserName,$Cred.Password)
#Get the Web
$Web = $Ctx.Web
$Ctx.Load($Web)
#Get the Site Column
$SiteColumn = $Web.Fields.GetByInternalNameOrTitle($InternalName)
$Ctx.Load($SiteColumn)
$Ctx.ExecuteQuery()
#Get the Site Column's Name,Type, ID and Group
$SiteColumn | Select Title, TypeDisplayName, ID, Group
}
Catch {
write-host -f Red "Error: " $_.Exception.Message
}
SharePoint Online: PowerShell to Get All Site Columns
Let’s get all site columns from a SharePoint Online site.
#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 Variables
$SiteURL= "https://crescent.sharepoint.com/sites/Marketing"
#Setup Credentials to connect
$Cred = Get-Credential
Try {
#Setup the context
$Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
$Ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.UserName,$Cred.Password)
#Get the Web
$Web = $Ctx.Web
$Ctx.Load($Web)
#Get All Site Columns of the Web
$SiteColumns = $Web.Fields
$Ctx.Load($SiteColumns)
$Ctx.ExecuteQuery()
#Get the Site Column Name and Group
$SiteColumns | Select Title, Group
}
Catch {
write-host -f Red "Error: " $_.Exception.Message
}
This script gets all site column names and its group.
PowerShell to Export Site Columns to CSV File
Let’s extract all site column data in a SharePoint Online site and export it to CSV using PowerShell script.
#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 Variables
$SiteURL= "https://crescent.sharepoint.com/sites/Marketing"
$CSVPath ="C:\Temp\SiteColumns.csv"
#Get Credentials to connect
$Cred = Get-Credential
Try {
#Setup the context
$Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
$Ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.UserName,$Cred.Password)
#Get the Web
$Web = $Ctx.Web
$Ctx.Load($Web)
#Get All Site Columns of the Web
$SiteColumns = $Web.Fields
$Ctx.Load($SiteColumns)
$Ctx.ExecuteQuery()
$DataCollection = @()
#Loop through each site column
ForEach($Field in $SiteColumns)
{
#Collect data
$Data = New-Object PSObject -Property @{
FieldName = $Field.Title
InternalName = $Field.InternalName
Group = $Field.Group
SchemaXML = $Field.SchemaXml
ID = $Field.Id
Type = $Field.TypeDisplayName
}
$DataCollection += $Data
}
#Export Site columns data to CSV file
$DataCollection | Export-Csv -Path $CSVPath -NoTypeInformation -Force
Write-host -f Green "Site Columns Data Export to CSV!"
}
Catch {
write-host -f Red "Error: " $_.Exception.Message
}
And the result:
PnP PowerShell to Get Site Columns in SharePoint Online
Using PnP PowerShell, we can retrieve all available site columns of the site.
#Parameters
$SiteURL = "https://crescent.sharepoint.com/sites/marketing"
#Connect to PnP Online
Connect-PnPOnline -Url $SiteURL -Interactive
#Get All Site Columns
Get-PnPField
This script gets the title, internal name, and ID of all site columns of the current site.
How do I get all columns, types, and internal names in my site by PnP PowerShell?
Get-PnPField | Select Title, TypeDisplayName, InternalName
If you want to backup-restore (or export-import) site columns in SharePoint Online, use another script: SharePoint Online: Export-Import Site Columns using PowerShell
how do you do this export from SP online with MFA enabled account?
Refer here: How to Connect to SharePoint Online using PowerShell with MFA Enabled Account?