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

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