Microsoft 365 : SharePoint Online – Create Site Columns in a SharePoint online site through PowerShell

.

Plant beautiful thoughts in your mind, to create beauty in your life.

.

Hello Everyone,

Hope you all are doing well.

We have good list of articles on SharePoint Online using PowerShell, please have a look.

https://knowledge-junction.com/category/technology-articles/powershell-cmdlets/sharepoint-online-powershell-cmdlets/

In this article we are going to discuss how to create site columns in a SharePoint online site from CSV file through PowerShell.

So without getting late, let’s get started.

Background

In our organization, one of our project requirement is to create PowerApps form from a SharePoint online list. The SharePoint list have too many columns to create manually and also time taking process. So we use PowerShell script to create all the columns at a time from a .csv file.

Details

  • We need to prepare the site columns list in a CSV format and saved in a preferred location.
  • We can use Microsoft Excel , to prepare the site columns list, then save as CSV.
  • CSV file with headings and details to create site columns.

CSV file
fig: CSV file

  • Open PowerShell ISE.
  • Now ran the PowerShell with the following cmdlet to add create site columns.

Note: The line starting with “#” are just description heading or comment.

Prerequisites

  • Install PowerShell ISE / PowerShell / Visual Studio Code if its not installed.
  • Install Module SharePoint Online Management Shell if its not installed.
  • Prepare CSV file with required details to create site columns.

#Install SharePoint Online Management Shell if its not installed
Install-Module -Name Microsoft.Online.SharePoint.PowerShell

Detailed Steps – PowerShell script

  • Import SharePoint online Client and Client Runtime libraries and update path for the respective libraries.

#Import SharePoint Client and Client Runtime libraries - Update your path for the respective libraries

Import-Module 'C:\Program Files\SharePoint Online Management Shell\Microsoft.Online.SharePoint.PowerShell\Microsoft.SharePoint.Client.dll'

Import-Module 'C:\Program Files\SharePoint Online Management Shell\Microsoft.Online.SharePoint.PowerShell\Microsoft.SharePoint.Client.Runtime.dll'

  • Give the URL of site where site columns to be create.

#Site collection URL where we need to create the site columns  
$siteurl = "https://osinfotech.sharepoint.com/sites/Forms" # site collection URL

  • Give the credentials of Office 365.

#Give the User name and Passwords  
$userName ="khasims@osinfotech.onmicrosoft.com" # user name  
$password ="XXXXXXXXXX" # Password

  • Create the site URL object.

#Create the site url object
[Microsoft.SharePoint.Client.ClientContext]$cliCnt = New-Object Microsoft.SharePoint.Client.ClientContext($siteurl)

  • Convert the password into secure string.

#convert password into secured string  
$securedpw = ConvertTo-SecureString $password -AsPlainText -Force

  • Create the credentials object.

#Create the credentials object
$clientContext.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($userName, $securedpw)
  • Get the respective site and web.

#Get the respective site and web  
$site = $clientContext.Site  

$web = $site.RootWeb

  • Get all fields from the web.

#Get all fields from the web  
$fields = $web.Fields

  • Load the web and fields.

#Load the web and fields  
$cliCnt.Load($web)  
$cliCnt.Load($fields)  

  • Execute the Query.
#Execute the query  
$cliCnt.ExecuteQuery()

  • Import the CSV file.
#Import the site columns CSV file  
$fieldslist = Import-Csv -path 'E:\PowerShellScripts\sitecolumns.csv' # give here csv file path

  • Apply for each field in the CSV file.

#Apply for each to the fields in the CSV file 
ForEach($field in $fields)

  • Here we need to find any fields are already exists on the respective site.
  • So we apply try loop to create the fields on the respective site.

#Create the respective fields
try{

  • In try loop prepare the field XML with the fields to add.

#Prepare the fieldxml 
$fieldXML = 
"<Field Name='" + $field.Name + "' Type='" + $field.Type + "' DisplayName='" + $field.DisplayName + "' Group='" + $field.Group + "' Description='" + $field.Description + "'>
</Field>"

  • Create the site columns from field XML.

#Create Site Column from fieldXML  
$fields.AddFieldAsXml($fieldXML, $true, [Microsoft.SharePoint.Client.AddFieldOptions]::AddFieldToDefaultView)

  • Load the fields and execute the same.

#Load the fields and execute the query
$cliCnt.Load($fields)  
$cliCnt.ExecuteQuery()

  • Here we end the try loop.
  • Now we need to identify the errors.
  • So we apply the catch loop.

#Catch the errors
catch{

  • Display the errors

#Print the error details
Write-Host "An error occurred:"
Write-Host $_

  • Add the errors in a CSV file.
#Add the error details in a CSV file and continue create other site columns.
Add-Content -Path "E:\PowerShellScripts\errorslog.csv" -Value $_
continue

  • Here we end the catch loop and apply for each.

Complete PowerShell Script

<#
===================================================================================================================================================
Name:           Create site columns in a SharePoint online site through PowerShell
Description:    This script helps to create site columns in SharePoint online site in an Organization through PowerShell
Version:        1.0
===================================================================================================================================================
#>

#Install SharePoint Online Management Shell if its not installed
Install-Module -Name Microsoft.Online.SharePoint.PowerShell

#Import SharePoint Client and Client Runtime libraries - Update your path for the respective libraries
Import-Module 'C:\Program Files\SharePoint Online Management Shell\Microsoft.Online.SharePoint.PowerShell\Microsoft.SharePoint.Client.dll'
Import-Module 'C:\Program Files\SharePoint Online Management Shell\Microsoft.Online.SharePoint.PowerShell\Microsoft.SharePoint.Client.Runtime.dll'

#Site collection URL where we need to create the site columns  
$siteurl = "https://osinfotech.sharepoint.com/sites/Forms" # site collection URL  
 
#Give the User name and Passwords  
$userName ="khasims@osinfotech.onmicrosoft.com" # user name  
$password ="XXXXXXXXXX" # password

#Create the site url object
[Microsoft.SharePoint.Client.ClientContext]$cliCnt = New-Object Microsoft.SharePoint.Client.ClientContext($siteurl)   
 
#Convert password into secured string  
$securedpw = ConvertTo-SecureString $password -AsPlainText -Force   

#Create the credentials object.
$cliCnt.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($userName, $securedpw) 

#Get the respective site and web  
$site = $cliCnt.Site  
$web = $site.RootWeb  
 
#Get all fields from the web  
$fields = $web.Fields   

#Load the web and fields  
$cliCnt.Load($web)  
$cliCnt.Load($fields)   

#Execute the query  
$cliCnt.ExecuteQuery() 

#Import the site columns CSV file  
$fields = Import-Csv -path 'E:\PowerShellScripts\sitecolumns.csv' # give here .csv file path   

#Apply for each to the fields in the CSV file 
ForEach($field in $fields)  
{  
    #Create the respective fields
    try{
            #Prepare the fieldxml 
            $fieldXML = 
            "<Field Name='" + $field.Name + "' Type='" + $field.Type + "' DisplayName='" + $field.DisplayName + "' Group='" + $field.Group + "' Description='" + $field.Description + "'>
            </Field>"  
 
            #Create Site Column from fieldXML  
            $fields.AddFieldAsXml($fieldXML, $true, [Microsoft.SharePoint.Client.AddFieldOptions]::AddFieldToDefaultView)  
            
            #Load the fields and execute the query
            $cliCnt.Load($fields)  
            $cliCnt.ExecuteQuery()
        } #try
    #Catch the errors
    catch{ 
            #Print the error details
            Write-Host "An error occurred:"
            Write-Host $_

            #Add the error details in a CSV file and continue create other site columns.
            Add-Content -Path "E:\PowerShellScripts\errorslog.csv" -Value $_
            continue
            } #catch 
}  
#foreach

.

Hope this article will help us to create site columns in a SharePoint Online site from CSV through PowerShell.

Also get my article updates on my social media handles.

LinkedIn – https://www.linkedin.com/in/khasim-shaik-8784a1232/

Twitter – https://twitter.com/KhasimShaik2009

Facebook – https://www.facebook.com/profile.php?id=100078255554660

Thank you for your support, will catch up with new article soon.

Keep learning and keep smiling.

Thanks.

Khasim Shaik

SharePoint & Power Platform Developer at OS InfoTech

You may also like...

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: