SharePoint Online: Bulk Add Multiple Fields to List from a CSV using PowerShell
Requirement: Create multiple fields in a SharePoint Online list from a CSV file.
PnP PowerShell to Create Multiple Columns in a List from a CSV File
Have you ever had to create multiple columns in a SharePoint Online list? Well, PowerShell can be a great solution if you need to quickly add a large number of fields to a list, and it can be done without having to use the user interface. This post will show you how to use PowerShell to bulk-add multiple fields from a CSV file to a SharePoint Online list. We will cover the different types of columns that are available and how to add them to your list using PowerShell scripts.
#Parameters
$SiteURL = "https://crescent.sharepoint.com/sites/PMO"
$ListName = "Projects"
$CSVFilePath = "C:\Temp\FieldTemplate.csv"
Try {
#Connect to the site
Connect-PnPOnline -Url $SiteURL -Interactive
#Get the List
$List = Get-PnPList -Identity $ListName
#Get Data from the CSV file
$CSVData = Import-Csv -Path $CSVFilePath
#Process each row in the CSV
ForEach($Row in $CSVData)
{
Try {
Write-host "Adding Column '$($Row.DisplayName)' to the List:" -f Magenta
#Check if the column exists in list already
$Fields = Get-PnPField -List $ListName
$NewField = $Fields | where { ($_.Internalname -eq $Row.Internalname) -or ($_.Title -eq $Row.DisplayName) }
If($NewField -ne $NULL)
{
Write-host "`tColumn $Name already exists in the List!" -f Yellow
}
Else
{
#Create the field based on field type
Switch ($Row.Type)
{
'Single Line of text' {
Add-PnPField -List $ListName -Type Text -DisplayName $Row.DisplayName -InternalName $Row.Internalname -Required:([System.Convert]::ToBoolean($Row.IsRequired)) -AddToDefaultView | Out-Host
}
'Multiple lines of text' {
Add-PnPField -List $ListName -Type Note -DisplayName $Row.DisplayName -InternalName $Row.Internalname -Required:([System.Convert]::ToBoolean($Row.IsRequired)) -AddToDefaultView | Out-Host
}
'Number' {
Add-PnPField -List $ListName -Type Number -DisplayName $Row.DisplayName -InternalName $Row.Internalname -Required:([System.Convert]::ToBoolean($Row.IsRequired)) -AddToDefaultView | Out-Host
}
'Person or Group' {
Add-PnPField -List $ListName -Type User -DisplayName $Row.DisplayName -InternalName $Row.Internalname -Required:([System.Convert]::ToBoolean($Row.IsRequired)) -AddToDefaultView | Out-Host
}
'Date and Time' {
Add-PnPField -List $ListName -Type DateTime -DisplayName $Row.DisplayName -InternalName $Row.Internalname -Required:([System.Convert]::ToBoolean($Row.IsRequired)) -AddToDefaultView | Out-Host
}
'Yes or No' {
Add-PnPField -List $ListName -Type Boolean -DisplayName $Row.DisplayName -InternalName $Row.Internalname -Required:([System.Convert]::ToBoolean($Row.IsRequired)) -AddToDefaultView | Out-Host
}
'Currency' {
Add-PnPField -List $ListName -Type Currency -DisplayName $Row.DisplayName -InternalName $Row.Internalname -Required:([System.Convert]::ToBoolean($Row.IsRequired)) -AddToDefaultView | Out-Host
}
'Choice' {
Add-PnPField -List $ListName -Type Choice -Choices @($Row.Data.Split(",")) -DisplayName $Row.DisplayName -InternalName $Row.Internalname -Required:([System.Convert]::ToBoolean($Row.IsRequired)) -AddToDefaultView | Out-Host
}
'Hyperlink or Picture' {
Add-PnPField -List $ListName -Type URL -DisplayName $Row.DisplayName -InternalName $Row.Internalname -Required:([System.Convert]::ToBoolean($Row.IsRequired)) -AddToDefaultView | Out-Host
}
'Managed Metadata' {
Add-PnPTaxonomyField -DisplayName $Row.DisplayName -InternalName $Row.Internalname -TermSetPath $Row.Data -List $ListName -Required:([System.Convert]::ToBoolean($Row.IsRequired)) -AddToDefaultView | Out-Host
}
'Lookup' {
#Get Lookup options - first part represents the Lookup Parent list, second part for lookup field in the parent list
$LookupOptions = $Row.Data.Split(";")
Add-PnPField -List $ListName -Type Lookup -DisplayName $Row.DisplayName -InternalName $Row.Internalname -Required:([System.Convert]::ToBoolean($Row.IsRequired)) -AddToDefaultView | Out-Host
Set-PnPField -List $ListName -Identity $Row.Internalname -Values @{LookupList=(Get-PnPList $LookupOptions[0]).Id.ToString(); LookupField=$LookupOptions[1]}
}
Default {
Write-host "`tColumn Type '$($Row.Type)' not Found!" -f Red
}
}
}
}
Catch {
write-host -f Red "`tError Adding Column '$($Row.DisplayName)' to List:" $_.Exception.Message
}
}
}
Catch {
write-host -f Red "Error:" $_.Exception.Message
}
You can download the CSV file from here:
Ensure there are no “Space” characters in the Display Name or Intername fields in the CSV to avoid special characters in the field names.
Nice script, the only thing is I get a the error message:
Adding Column ” to the List:
Column Type ” not Found!
Vey useful script. Thank you.