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.

bulk create multiple fields in sharepoint list using powershell

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.

Salaudeen Rajack

Salaudeen Rajack - SharePoint Expert with Two decades of SharePoint Experience. Love to Share my knowledge and experience with the SharePoint community, through real-time articles!

2 thoughts on “SharePoint Online: Bulk Add Multiple Fields to List from a CSV using PowerShell

  • Nice script, the only thing is I get a the error message:
    Adding Column ” to the List:
    Column Type ” not Found!

    Reply
  • Vey useful script. Thank you.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *