How to create a list with many choice fields, without having to do it one by one?

Question

I need to create a SharePoint list with ~200 columns. Each column has a different name, and they all are choice types (with the same choice list values; same default value). Is there a way to do it in any way except to do it one by one, manually?

I managed to define the list via import of Excel file, but then all the columns are single-text-line. I need a Choice...

Michael

Answer 1

This can be done with PowerShell. Here is some sample code that I used. This must be run on one of the SharePoint servers. If you are using SharePoint Online, it will need to be converted to client side code.

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

Function Add-FieldToList($SiteURL,$ListName, $FieldName, $FieldType, $IsRequired, $Choices, $Default)
{

    #Set the Error Action
    $ErrorActionPreference = "Stop"

    Try {

        #Get the List

        $List = (Get-SPWeb $SiteURL).Lists.TryGetList($ListName)

        #Check if List with specific name exists
        if($List -ne $null)
        {
            if(!$List.Fields.ContainsField($FieldName))
            {
                #Add columns to the List
                if ($FieldType -eq [Microsoft.SharePoint.SPFieldType]::Choice)
                {
                    $newFieldName = $List.Fields.Add($FieldName,$FieldType,$IsRequired,$False,$Choices)
                }
                else
                {
                    $newFieldName = $List.Fields.Add($FieldName,$FieldType,$IsRequired)
                }

                if ($FieldType -eq [Microsoft.SharePoint.SPFieldType]::Note)
                {
                    $newField = $List.Fields.GetFieldByInternalName($newFieldName)
                    $newField.RichText = $false
                    $newField.Update()
                }

                if ($FieldType -eq [Microsoft.SharePoint.SPFieldType]::DateTime)
                {
                    $newField = $List.Fields.GetFieldByInternalName($newFieldName)
                    $newField.DisplayFormat = "DateOnly"
                    $newField.Update()
                }

        if ($Default -ne $null)
        {
                    $newField = $List.Fields.GetFieldByInternalName($newFieldName)
                    $newField.DefaultValue = $Default
                    $newField.Update()
        }

                #Update the List
                $List.Update()

                write-host "New Column '$FieldName' Added to the List!" -ForegroundColor Green
            }
            else
            {
                write-host "Field '$FieldName' Already Exists in the List" -ForegroundColor Red
            }
        }
        else
        {
            write-host "List '$ListName' doesn't exists!" -ForegroundColor Red
        }
    }
     catch {
        Write-Host $_.Exception.Message -ForegroundColor Red
    }
    finally {
        #Reset the Error Action to Default
        $ErrorActionPreference = "Continue"
    }
}

#Parameters

$SiteURL="https://yoururl/yoursite"
$ListName = "Your List"

$Choices = New-Object System.Collections.Specialized.StringCollection
$Choices.Add("Yes")
$Choices.Add("No")
$Choices.Add("N/A")
$FieldType = [Microsoft.SharePoint.SPFieldType]::Choice
$FieldName="Your Field Name"
$IsRequired = $False
Add-FieldToList $SiteURL $ListName $FieldName $FieldType $IsRequired $Choices "N/A"

Answer 2

I would also look at PNP tools they have many functions built in already (https://docs.microsoft.com/en-us/powershell/sharepoint/sharepoint-pnp/sharepoint-pnp-cmdlets?view=sharepoint-ps).

I have used the tool to create lists in the past example below:

Add-PnPField -List $NameOfSite -DisplayName "Date of Entry" -InternalName "DateOfEntery" -Type DateTime -AddToDefaultView

Add-PnPField -List $NameOfSite -DisplayName "Date Required" -InternalName "DateRequired" -Type DateTime -AddToDefaultView

Add-PnPField -List $NameOfSite -DisplayName "Requester" -InternalName "Requester" -Type User -AddToDefaultView

Add-PnPField -List $NameOfSite -DisplayName "Assigned To" -InternalName "AssignedTo" -Type User -AddToDefaultView

Add-PnPField -List $NameOfSite -DisplayName "Item Details" -InternalName "ItemDetials" -Type Note -AddToDefaultView

Add-PnPField -List $NameOfSite -DisplayName "Response/Notes" -InternalName "ResponseNotes" -Type Note -AddToDefaultView

Add-PnPField -List $NameOfSite -DisplayName "SRC Status" -InternalName "Status" -Type Choice -AddToDefaultView -Choices "In progress","Completed"