Creating List and List Columns from CSV file

######################### Add SharePoint PowerShell Snapin ###############################################
 
 if ( (Get-PSSnapin -Name Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue) -eq $null )
 
 {
 
 Add-PSSnapin Microsoft.SharePoint.Powershell
 
 }

######################## Set Execution Path ################################################
 
 $scriptBase = split-path $SCRIPT:MyInvocation.MyCommand.Path -parent
 
 Set-Location $scriptBase
 
 ################################# End of Set Execution Path #################################
 $Lists=$null
#Function for Creating List
function CreatingList([string] $ListName)
{
try
{
#Create list with "custom" list template
$ListInfo = New-Object Microsoft.SharePoint.Client.ListCreationInformation
$ListInfo.Title = $ListName
$ListInfo.TemplateType = "100"
$List = $Context.Web.Lists.Add($ListInfo)
$List.Description = $ListName
$List.Update()
$Context.ExecuteQuery()
Write-Host "List Name:" $ListName "created successfully"
}
catch{

Write-Host $_.Exception.Message -ForegroundColor Red
}

}




#Function for Creating Columns based on condition
function CreateField([string] $ListName,[string] $ColumnName,[string] $ColumnInternalName,[string] $DataTypes,[string] $DatatypeOptions,[string] $LookupListNM,[string] $LookupColumnNM)
{
 #Retrieve lists
$Lists = $Context.Web.Lists.GetByTitle($ListName)
$Context.Load($Lists)
$Context.ExecuteQuery()
$lisfield=$Lists.Fields
$Context.Load($lisfield)
$Context.ExecuteQuery()

 try
            {
          
  $Lfield = $lisfield.GetByInternalNameOrTitle($ColumnName)
  $Context.Load($Lfield)
  $Context.ExecuteQuery()

  Write-Host "Exception calling "ExecuteQuery" with "0" argument(s): "A duplicate field name "$ColumnName" was found."so create site column in different name" -ForegroundColor Red

  }
 catch
   {



#Write-Host $Lists.Title
try{

if($DataTypes-eq "singleline")
{

#SingleLine
$Lists.Fields.AddFieldAsXml("<Field Type='Text' DisplayName='$ColumnName' Required='True' MaxLength='255' Name='$ColumnInternalName' />",$true,[Microsoft.SharePoint.Client.AddFieldOptions]::AddFieldToDefaultView)
$Lists.Update()
$Context.ExecuteQuery()
Write-Host "ColumnName:" $ColumnName "Type: SingleLine of Text field created successfully"
}
if($DataTypes-eq "multipleline")
{

#MulipleLine
if($DatatypeOptions -eq "plaintext")
{
#PlainText
$Lists.Fields.AddFieldAsXml("<Field Type='Note' DisplayName='$ColumnName' Required='FALSE' NumLines='6' RichText='FALSE'
Sortable='FALSE' Name='$ColumnInternalName'/>",$true,[Microsoft.SharePoint.Client.AddFieldOptions]::AddFieldToDefaultView)
$Lists.Update()
$Context.ExecuteQuery()
Write-Host "ColumnName:" $ColumnName "Type: MultiLine of Plain Text field created successfully"
}
if($DatatypeOptions -eq "richtext")
{
#RichText
$Lists.Fields.AddFieldAsXml("<Field Type='Note' DisplayName='$ColumnName' Required='FALSE'   NumLines='6'
RichText='TRUE' RichTextMode='Compatible' Sortable='FALSE' Name='$ColumnInternalName' />",$true,[Microsoft.SharePoint.Client.AddFieldOptions]::AddFieldToDefaultView)
$Lists.Update()
$Context.ExecuteQuery()
Write-Host "ColumnName:" $ColumnName "Type: MultiLine of Rich Text field created successfully"
}
if($DatatypeOptions -eq "enhancedrichtext")
{
#EnhancedRichText
$Lists.Fields.AddFieldAsXml("<Field Type='Note' DisplayName='$ColumnName' Required='FALSE'  NumLines='6' RichText='TRUE'
RichTextMode='FullHtml' IsolateStyles='TRUE' Sortable='FALSE'  Name='$ColumnInternalName' />",$true,[Microsoft.SharePoint.Client.AddFieldOptions]::AddFieldToDefaultView)
$Lists.Update()
$Context.ExecuteQuery()
Write-Host "ColumnName:" $ColumnName "Type: MultiLine of EnhancedRich Text field created successfully"
}

}

#Choice
if($DataTypes -eq "choice")
{
if($DatatypeOptions -eq "Dropdown")
{
#echo dropdown
#Dropdown
$Lists.Fields.AddFieldAsXml("<Field Type='Choice' DisplayName='$ColumnName' Required='FALSE' Format='Dropdown'
FillInChoice='FALSE' Name='$ColumnInternalName'>
            <Default>MyChoice1</Default>
            <CHOICES>
                <CHOICE>MyChoice1</CHOICE>
                <CHOICE>MyChoice2</CHOICE>
            </CHOICES>
           </Field>",$true,[Microsoft.SharePoint.Client.AddFieldOptions]::AddFieldToDefaultView)
$Lists.Update()
$Context.ExecuteQuery()
Write-Host "ColumnName:" $ColumnName "Type: Choice field in Dropdown created successfully"
}
if($DatatypeOptions -eq "Radiobutton")
{
#echo "radio"
#Radiobutton
$Lists.Fields.AddFieldAsXml("<Field Name='$ColumnInternalName' Type='Choice' DisplayName='$ColumnName' Format='RadioButtons'
        FillInChoice='FALSE' >
            <Default>MyChoice1</Default>
            <CHOICES>
                <CHOICE>MyChoice1</CHOICE>
                <CHOICE>MyChoice2</CHOICE>
            </CHOICES>
        </Field>",$true,[Microsoft.SharePoint.Client.AddFieldOptions]::AddFieldToDefaultView)
$Lists.Update()
$Context.ExecuteQuery()
Write-Host "ColumnName:" $ColumnName "Type: Choice field in RadioButton created successfully"
}
if($DatatypeOptions -eq "Checkboxes")
{
#echo "Check"
#Checkboxes(allow multiple selection)
$Lists.Fields.AddFieldAsXml("<Field Name='$ColumnInternalName' Type='MultiChoice' DisplayName='$ColumnName' FillInChoice='FALSE'>
                    <Default>MyChoice1</Default>
                    <CHOICES>
                        <CHOICE>MyChoice1</CHOICE>
                        <CHOICE>MyChoice2</CHOICE>
                    </CHOICES>
                </Field>",$true,[Microsoft.SharePoint.Client.AddFieldOptions]::AddFieldToDefaultView)
$Lists.Update()
$Context.ExecuteQuery()
Write-Host "ColumnName:" $ColumnName "Type: Choice field in Checkbox created successfully"
}

}
if($DataTypes -eq "number")
{
#Number Field
$Lists.Fields.AddFieldAsXml("<Field Type='Number' DisplayName='$ColumnName' Required='FALSE' Name='$ColumnInternalName'/>",$true,[Microsoft.SharePoint.Client.AddFieldOptions]::AddFieldToDefaultView)
$Lists.Update()
$Context.ExecuteQuery()
Write-Host "ColumnName:" $ColumnName "Type: Number field created successfully"
}
#Date&Time
if($DataTypes -eq "datetime")
{
if($DatatypeOptions -eq "Dateonly")
{
#DateOnly
$Lists.Fields.AddFieldAsXml("<Field Type='DateTime' DisplayName='$ColumnName' Format='DateOnly'   Name='$ColumnInternalName'/>",$true,[Microsoft.SharePoint.Client.AddFieldOptions]::AddFieldToDefaultView)
$Lists.Update()
$Context.ExecuteQuery()
Write-Host "ColumnName:" $ColumnName "Type: Date Only field created successfully"
}
if($DatatypeOptions -eq "Date&Time")
{
#DateandTime
$Lists.Fields.AddFieldAsXml("<Field Type='DateTime' DisplayName='$ColumnName' Format='DateTime'   Name='$ColumnInternalName'/>",$true,[Microsoft.SharePoint.Client.AddFieldOptions]::AddFieldToDefaultView)
$Lists.Update()
$Context.ExecuteQuery()
Write-Host "ColumnName:" $ColumnName "Type: Date and Time field created successfully"
}

}
if($DataTypes -eq "yesrno")
{
#Yes r No
$Lists.Fields.AddFieldAsXml("<Field Type='Boolean' DisplayName='$ColumnName' Name='$ColumnInternalName'>
           <Default>1</Default>
           </Field>",$true,[Microsoft.SharePoint.Client.AddFieldOptions]::AddFieldToDefaultView)
$Lists.Update()
$Context.ExecuteQuery()
Write-Host "ColumnName:" $ColumnName "Type: Yes or No field created successfully"
}
if($DataTypes -eq "peoplergroup")
{
#People or Group
if($DatatypeOptions -eq "PeopleOnly")
{
#PeopleOnly
$Lists.Fields.AddFieldAsXml("<Field Type='User' DisplayName='$ColumnName' List='UserInfo' ShowField='ImnName'
UserSelectionMode='PeopleOnly' UserSelectionScope='0' Name='$ColumnInternalName'/>",$true,[Microsoft.SharePoint.Client.AddFieldOptions]::AddFieldToDefaultView)
$Lists.Update()
$Context.ExecuteQuery()
Write-Host "ColumnName:" $ColumnName "Type: People Only field created successfully"
}
if($DatatypeOptions -eq "People&Group")
{
#People&Group
$Lists.Fields.AddFieldAsXml("<Field Type='User' DisplayName='$ColumnName' List='UserInfo' ShowField='ImnName'
UserSelectionMode='PeopleAndGroups' UserSelectionScope='0' Name='$ColumnInternalName'/>",$true,[Microsoft.SharePoint.Client.AddFieldOptions]::AddFieldToDefaultView)
$Lists.Update()
$Context.ExecuteQuery()
Write-Host "ColumnName:" $ColumnName "Type: People and Group field created successfully"
}

}
if($DataTypes -eq "hyperlinkRpicture")
{
if($DatatypeOptions -eq "Hyperlink")
{
#Hyperlink
$Lists.Fields.AddFieldAsXml("<Field Type='URL' DisplayName='$ColumnName' Format='Hyperlink' Name='$ColumnInternalName'/>",$true,[Microsoft.SharePoint.Client.AddFieldOptions]::AddFieldToDefaultView)
$Lists.Update()
$Context.ExecuteQuery()
Write-Host "ColumnName:" $ColumnName "Type: Hyperlink field created successfully"
}
if($DatatypeOptions -eq "Picture")
{
#Picture
$Lists.Fields.AddFieldAsXml("<Field Type='URL' DisplayName='$ColumnName' Format='Image' Name='$ColumnInternalName'/>",$true,[Microsoft.SharePoint.Client.AddFieldOptions]::AddFieldToDefaultView)
$Lists.Update()
$Context.ExecuteQuery()
Write-Host "ColumnName:" $ColumnName "Type: Picture field created successfully"
}


}
if($LookupListNM -ne $null)
{
try{
$LookupLisNamecon = $Context.Web.Lists.GetByTitle($LookupListNM)
$Context.Load($LookupLisNamecon)
$Context.ExecuteQuery()
}
catch
{}

if($DataTypes -eq "lookup")
{
 try
            {
          
  $Lfield = $lisfield.GetByInternalNameOrTitle($ColumnName)
  $Context.Load($Lfield)
  $Context.ExecuteQuery()

  Write-Host "Exception calling "ExecuteQuery" with "0" argument(s): "A duplicate field name "$ColumnName" was found."so create site column in different name" -ForegroundColor Red

  }
 catch
   {
#Lookup
$castToMethodGeneric = [Microsoft.SharePoint.Client.ClientContext].GetMethod("CastTo")
$castToMethodLookup = $castToMethodGeneric.MakeGenericMethod([Microsoft.SharePoint.Client.FieldLookup])



#$internalName = "LookupWithStaticName"
$displayName = $ColumnName
$displayFieldForLookup = $LookupColumnNM

$lookupFieldXML = "<Field DisplayName='$ColumnInternalName' Type='Lookup' Mult='TRUE' />"
$option = [Microsoft.SharePoint.Client.AddFieldOptions]::AddFieldToDefaultView

$newLookupField = $Lists.Fields.AddFieldAsXml($lookupFieldXML, $true, $option)
$Context.Load($newLookupField)
$lookupField = $castToMethodLookup.Invoke($Context, $newLookupField)
$lookupField.Title = $displayName
$lookupField.LookupList = $LookupLisNamecon.Id
$lookupField.LookupField = $displayFieldForLookup

$lookupField.Update()
$Context.ExecuteQuery()
Write-Host "ColumnName:" $ColumnName "Type: Lookup field created successfully"
return
}
}
}

}
catch{

Write-Host ($_.Exception.Message + "so create column in different name") -ForegroundColor Red
}
}
}


#Add references to SharePoint client assemblies and authenticate to Office 365 site - required for CSOM
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
Add-Type -Path "c:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Taxonomy.dll"

 $question = Read-Host "Are you proceed with On-Premises or Office365? If On-Premises Type ON Else Type OFF for Office365"


 If (($question -eq "ON") -or ($question -eq "on") -or ($question -eq "On") -or ($question -eq "oN"))
{

$SiteURL = Read-Host -Prompt "Please enter the Site URL"
$CSVFileName=Read-Host -Prompt "Please enter Your CSV file Name"
#Bind to site collection
$Context = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)


if (!$Context.ServerObjectIsNull.Value)
{
    Write-Host "Connected to SharePoint On-Premises site: '$siteUrl'" -ForegroundColor Green
   
    $web = $Context.Web  
    $Context.Load($web)  
    $Context.ExecuteQuery()
 
}
# retrieve all site columns (fields)
$web = $Context.Web   
$fields = $web.Fields        
$Context.Load($web)       
$Context.Load($fields)
$Context.ExecuteQuery()

}


Elseif (($question -eq "OFF") -or ($question -eq "off") -or ($question -eq "Off") -or ($question -eq "oFF"))
{
$User = Read-Host -Prompt "Please enter your Login Name"
$Password = Read-Host -Prompt "Please enter your password" -AsSecureString
$Creds = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($User,$Password)
$SiteURL = Read-Host -Prompt "Please enter the Site URL"
$CSVFileName=Read-Host -Prompt "Please enter Your CSV file Name"


#Bind to site collection
$Context = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
$Creds = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($User,$Password)
$Context.Credentials = $Creds

if (!$Context.ServerObjectIsNull.Value)
{
    Write-Host "Connected to SharePoint Online site: '$siteUrl'" -ForegroundColor Green
   
    $web = $Context.Web  
    $Context.Load($web)  
    $Context.ExecuteQuery()
 
}
# retrieve all columns (fields)
$web = $Context.Web   
$fields = $web.Fields        
$Context.Load($web)       
$Context.Load($fields)
$Context.ExecuteQuery()



}
Else
{
    Write-Host -BackgroundColor White -ForegroundColor Red "INVALID ENTRY! Please try again."
    Break
    #Exit
}

###################Creating List##################################

 $creatinglistCSV = $scriptBase + "\" + $CSVFileName
 
 import-csv $creatinglistCSV |select -first 1 | where {
 
 CreatingList $_.ListName
 
 }


 #################Creating Site columns##########################################################
 
 $SiteColumnCreationDetailsCSV = $scriptBase + "\" + $CSVFileName
 
 import-csv $SiteColumnCreationDetailsCSV | where {
 
 CreateField $_.ListName $_.ColumnName $_.ColumnInternalName $_.DataTypes $_.DatatypeOptions $_.LookupListNM $_.LookupColumnNM

 }

 Sample CSV file: