Monday, 4 July 2016

Working With List Columns On SharePoint Using PnP PowerShell

In this post, you will learn how we can create, retrieve and delete the columns on SharePoint lists, using PnP PowerShell. The Client Side Object Model is used internally for these operations. The update operation is not available for the site/list columns (fields).


You need to have PowerShell 3.0, available on a Windows machine. You need to install or import PnP PowerShell packages. You can download the installers or view more documentation on the official site. The installers are available here. Online version installer is preferred and is named On premise or Office 365 operations. You can also install all three installers for testing (SharePoint 2013, 2016, online).

The PnP PowerShell is supported from SharePoint 2013 on premise and Office 365 versions. The following operations are tested on SharePoint 2013 and Office 365 environments.

Connect To Site:

Connect to the site, using the snippet, given below. The PnP PowerShell code, given below, helps in getting the current context of the site, using the Client Side Object Model (CSOM).
  1. $siteurl = ""    
  2. Connect-SPOnline -Url $siteurl    
  3. $ctx = Get-SPOContext  
Once connected, you can carry out any of the operations mentioned below, based on the requirement.

Retrieve List Columns:

The list columns (fields) can be retrieved using PnP commands.
  • Get-SPOField command is used to get all the columns available on the SharePoint list.
  • The required parameters to get list column are list and identity. The list name is passed through list parameter. The field name is passed through identity parameter.
  • The properties like title, internal name, default value, description, etc. can be accessed.
The code snippet, given below, shows getting properties of a list column from the SharePoint list using column name.
  1. $column = Get-SPOField -List "PnPList" -Identity "PnPListColumn"  
  2. Write-Host "Column Title  :" $column.Title  
  3. Write-Host "Description   :" $column.Description  
  4. Write-Host "Group Name    :" $column.Group  
  5. Write-Host "Internal Name :" $column.InternalName  
  6. Write-Host "Static Name   :" $column.StaticName  
  7. Write-Host "Scope         :" $column.Scope  
  8. Write-Host "Type          :" $column.TypeDisplayName  
  9. Write-Host "Schema XML    :" $column.SchemaXml  
  10. Write-Host "Is Required?  :" $column.Required  
  11. Write-Host "Is read only? :" $column.ReadOnlyField  
  12. Write-Host "Unique?       :" $column.EnforceUniqueValues  
  13. Write-Host "-------------------------------------------"  
To get all the fields from a SharePoint list, identity parameter is not required. The code snippet, given below, shows getting all columns from the SharePoint list.
  1. $columns = Get-SPOField -List "PnPList"  
  2. foreach($column in $columns){  
  3.     Write-Host "Column Title  :" $column.Title  
  4.     Write-Host "Description   :" $column.Description  
  5.     Write-Host "Group Name    :" $column.Group  
  6.     Write-Host "Internal Name :" $column.InternalName  
  7.     Write-Host "Static Name   :" $column.StaticName  
  8.     Write-Host "Scope         :" $column.Scope  
  9.     Write-Host "Type          :" $column.TypeDisplayName  
  10.     Write-Host "Schema XML    :" $column.SchemaXml  
  11.     Write-Host "Is Required?  :" $column.Required  
  12.     Write-Host "Is read only? :" $column.ReadOnlyField  
  13.     Write-Host "Unique?       :" $column.EnforceUniqueValues  
  14.     Write-Host "-------------------------------------------"  
  15. }  

Create List Column:

The columns can be created on the lists available on SharePoint site or sub site by setting the context, using the site URL. PnP CSOM PowerShell can be used to complete the operation. 
  • Add-SPOField command, which is used to create the columns on SharePoint lists.
  • The required parameters for creating new column on the list are display name, internal name, group name and field type. 
  • The new values can be passed as the parameters. 
  • AddToDefaultView option is used to make to list column available on default views. The column can be made as mandatory, using the required parameter.
In my example, I have created the new column called "PnPListColumn", which is a text type. The below code snippet shows adding new column. 
  1. Add-SPOField -DisplayName "PnPListColumn" -InternalName "PnPListColumn" -Group "PnPGroup" -Type Text -List "PnPList" -AddToDefaultView -Required  
 As of now, only few properties can be updated while creating the fields.

The image given below shows list default view with list column.

Delete List Column:

The columns can be deleted from a SharePoint list, using PnP PowerShell.
  • Remove-SPOField is used to delete the columns (fields).
  • The field name and list name are required to delete the column. The name or Id can be passed with the command, using the identity parameter. The list name is passed using list parameter.
  • The force attribute is used to delete the field without any confirmation prompts.
The below code snippet shows removing list column.
  1. Remove-SPOField -List "PnPList" -Identity "PnPListColumn" -Force  

Note: The above operations are tested on SharePoint 2013 and Office 365 environments.