Add New Column in SharePoint Online List using Powershell
1. Create a CSV file add Column in below Format
Below are the supported type
|
| Text |
| MultiText |
| Choice |
| DateTime |
| User |
| UserMulti |
| Boolean |
| Number |
Default Value for Boolean is 0 or 1 mean No/Yes
and Choice Value will be in comma separated value like Approved,Pending,Rejected
Default Value for Choice is like Rejected
2. Copy below script and paste in .ps1 file
3. change the site url
4. changes the schema file path
5. Right click on script and Run with powershell
$contentFile ='E:\SharepointOnline\ListLibrarySchema.csv'
$CSVitems = Import-Csv $contentFile
$clientDll =[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client")
$runtimeDll = [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client.Runtime")
$url = "https://tenantName.sharepoint.com/sites/SiteName/"
$cred = get-credential
$clientContext = New-Object Microsoft.SharePoint.Client.ClientContext($url)
$credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($cred.username, $cred.password)
$clientContext.Credentials = $credentials
Write-Host "Connected to SharePoint site: '$Url'" -ForegroundColor Green
#Load List
$ListTitle ="CustomListName"
$List = $clientContext.Web.Lists.GetByTitle($ListTitle)
#Load List's exsiting Columns
$Fields = $List.Fields
$clientContext.Load($List)
$clientContext.Load($Fields)
$clientContext.ExecuteQuery()
FOREACH ($CSVitem in $CSVitems)
{
#Schema Parameter
$NewColumnName=$CSVitem.Name
$FieldType=$CSVitem.Type
$Required=$CSVitem.Required
$DefaultValue=$CSVitem.DefaultValue
$ChoiceOption=$CSVitem.ChoiceOption.Split("{,}")
#Check if the given field name
$Field = $Fields | where{$_.Title -eq $NewColumnName}
if(!$Field)
{
Write-Host "Column Adding...: '$NewColumnName'" -ForegroundColor yellow
if($FieldType -eq "Text")
{
# Single Line
$a=$List.Fields.AddFieldAsXml("<Field Type='Text' NumLines='6' Required='$Required' Name='$NewColumnName' DisplayName='$NewColumnName'/>",$true,[Microsoft.SharePoint.Client.AddFieldOptions]::AddFieldToDefaultView)
$List.Update()
$clientContext.ExecuteQuery()
Write-Host "Column Added: '$NewColumnName'" -ForegroundColor Green
}
if($FieldType -eq "MultiText")
{
# MultiLine
$a = $List.Fields.AddFieldAsXml("<Field Type='Note' Name='$NewColumnName' RichText='FALSE' DisplayName='$NewColumnName' Required='$Required' />",$true,[Microsoft.SharePoint.Client.AddFieldOptions]::AddFieldToDefaultView)
$List.Update()
$clientContext.ExecuteQuery()
Write-Host "Column Added: '$NewColumnName'" -ForegroundColor Green
}
if($FieldType -eq "Choice")
{
$OptionCollection=""
foreach($option in $ChoiceOption)
{
$OptionCollection+="<CHOICE>$option</CHOICE>"
}
#Choice field
$a = $List.Fields.AddFieldAsXml("<Field Type='Choice' Required='$Required' Name='$NewColumnName' DisplayName='$NewColumnName' >
<CHOICES>
$OptionCollection
</CHOICES><Default>$DefaultValue</Default></Field>",$true,[Microsoft.SharePoint.Client.AddFieldOptions]::AddFieldToDefaultView)
$List.Update()
$clientContext.ExecuteQuery()
Write-Host "Column Added: '$NewColumnName'" -ForegroundColor Green
}
if($FieldType -eq "DateTime")
{
# DateTime
$a = $List.Fields.AddFieldAsXml("<Field Type='DateTime' Name='$NewColumnName' DisplayName='$NewColumnName' Required='$Required' />",$true,[Microsoft.SharePoint.Client.AddFieldOptions]::AddFieldToDefaultView)
$List.Update()
$clientContext.ExecuteQuery()
Write-Host "Column Added: '$NewColumnName'" -ForegroundColor Green
}
if($FieldType -eq "User")
{
# SingleUser/People
$a = $List.Fields.AddFieldAsXml("<Field Type='User' List='UserInfo' Name='$NewColumnName' UserSelectionMode='PeopleOnly' Required='$Required' DisplayName='$NewColumnName'></Field>",$true,[Microsoft.SharePoint.Client.AddFieldOptions]::AddFieldToDefaultView)
$List.Update()
$clientContext.ExecuteQuery()
Write-Host "Column Added: '$NewColumnName'" -ForegroundColor Green
}
if($FieldType -eq "MultiUser")
{
# MultiUser/People
$a = $List.Fields.AddFieldAsXml("<Field Type='UserMulti' ShowField='ImnName' DisplayName='$NewColumnName' Name='$NewColumnName' Required='$Required' List='UserInfo' UserSelectionMode='PeopleOnly' Mult='TRUE' />",$true,[Microsoft.SharePoint.Client.AddFieldOptions]::AddFieldToDefaultView)
$List.Update()
$clientContext.ExecuteQuery()
Write-Host "Column Added: '$NewColumnName'" -ForegroundColor Green
}
if($FieldType -eq "Boolean")
{
# Yes/No #0 for No and 1 for Yes
$a = $List.Fields.AddFieldAsXml("<Field Type='Boolean' DisplayName='$NewColumnName' Required='$Required' Name='$NewColumnName'><Default>$DefaultValue</Default></Field>",$true,[Microsoft.SharePoint.Client.AddFieldOptions]::AddFieldToDefaultView)
$List.Update()
$clientContext.ExecuteQuery()
Write-Host "Column Added: '$NewColumnName'" -ForegroundColor Green
}
if($FieldType -eq "Number")
{
# Yes/No #0 for No and 1 for Yes
$a = $List.Fields.AddFieldAsXml("<Field Type='Number' DisplayName='$NewColumnName' Required='$Required' MaxLength='255' Name='$NewColumnName' />",$true,[Microsoft.SharePoint.Client.AddFieldOptions]::AddFieldToDefaultView)
$List.Update()
$clientContext.ExecuteQuery()
Write-Host "Column Added: '$NewColumnName'" -ForegroundColor Green
}
}
else
{
write-host "Field Exists already!" -ForegroundColor red
}
}
#Exit Window
Write-Host "Please enter to exit..."
Write-Output
Write-Host "End............"
