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.

get site columns in sharepoint online

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:

sharepoint online powershell get site columns

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

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!

2 thoughts on “SharePoint Online: PowerShell to Get Site Columns

Leave a Reply

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