Create a Column in a Destination Site Using PowerShell in SharePoint Online

This blog will help you create a remaining column in a destination site using Powershell.

Introduction 

 

This blog will help you create the remaining columns in a destination SharePoint site using Powershell.

 

Sometimes we have to manually create many columns in the destination SharePoint sites, resulting in incorrect column creation or incorrect types of columns.

 

Otherwise, we save the list as a template with content and upload the same template in a different site to create a list. However, there are a couple of scenarios where we are not able to save the list as a template. In such cases, we can use the below script to create a column in the destination site after creating the list with a default column (Title).

 

There are prerequisites to run the script on Sharepoint online, as we are required to pass the credentials to authenticate them. It may help to refer to my blog, Copy List Items from One Site to Another With Versions using Powershell.

 

Copy and run the script by changing the required parameters.

#Set Parameters
$srcListSiteUrl = "Source site Url"
$SourceListName = "Source List Name"
$dstListSiteUrl = "Destination Site Url"
$TargetListName = "Destination List Name"

Complete script 

#Load SharePoint CSOM Assemblies
Add-Type -path 'C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll'
Add-Type -path 'C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll'

Function Is-Column-Exists()
{
    param
    (
        [Parameter(Mandatory=$true)] $TargetListFields,
        [Parameter(Mandatory=$true)] [string] $FieldName
    )
     #Check if the given field name
    $Field = $TargetListFields | where{$_.InternalName -eq $FieldName}
    if($Field)  { return $true } else { return $false}
}

Function Create-List-Columns()
{
     param
    (
        [Parameter(Mandatory=$true)] [string] $siteURL,
        [Parameter(Mandatory=$true)] [string] $destSiteURL,
        [Parameter(Mandatory=$true)] [string] $SourceListName,
        [Parameter(Mandatory=$true)] [string] $TargetListName
    )

    #Passing Credentials
    $credPath = 'D:\Arvind\safe\secretfile.txt'
    $fileCred = Import-Clixml -path $credpath
    $Cred = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($fileCred.UserName, $fileCred.Password)

    #Setup the source context
    $sourceCtx = New-Object Microsoft.SharePoint.Client.ClientContext($siteURL)
    $sourceCtx.Credentials = $Cred

    #Setup the destination Context
    $destCtx = New-Object Microsoft.SharePoint.Client.ClientContext($destSiteURL)
    $destCtx.Credentials = $Cred

    #Get the Source List and Target Lists
    $SourceList = $sourceCtx.Web.Lists.GetByTitle($SourceListName)
    $TargetList = $destCtx.Web.Lists.GetByTitle($TargetListName)

    #Load Source and Target Fields
    $SourceListFields = $SourceList.Fields
    $sourceCtx.Load($SourceListFields)

    $TargetListFields = $TargetList.Fields
    $destCtx.Load($TargetListFields)

    $sourceCtx.ExecuteQuery()
    $destCtx.ExecuteQuery()

    #Loop through each item in the source and Get column values, add them to target
    [int]$Counter = 1

    Foreach($SourceField in $SourceListFields)
    {
        #Handle Special Fields
        $FieldType  = $SourceField.TypeAsString
        #Skip Read only, hidden fields, content type and attachments and fields is not User fields
        If((-Not ($SourceField.ReadOnlyField)) -and (-Not ($SourceField.Hidden)) -and ($SourceField.InternalName -ne  "ContentType") -and ($SourceField.InternalName -ne  "Attachments") )
        {
            Write-Host "Source Field" $Counter ":" $SourceField.InternalName -ForegroundColor Yellow
            $Counter +=1
            #check if column name exist in Target fields
            $IsColumnExist = Is-Column-Exists -TargetListFields $TargetListFields -FieldName $SourceField.InternalName
            if($IsColumnExist)
            {
              write-host $SourceField.Title " Column Exists in Given Target list" $TargetListName -f Magenta
              Write-Host "--------------------"
              #Write-Host $SourceField.SchemaXml
              $TargetField = $TargetListFields | where{$_.InternalName -eq $SourceField.InternalName}
              #Get Source Field Type and Target Field type
              $SourceFieldType  = $SourceField.TypeAsString
              $TargetFieldType  = $TargetField.TypeAsString
              #Write-Host $TargetField.SchemaXml
              if($SourceFieldType -ne $TargetFieldType)
              {
                Write-Host "Source Column type are" $SourceFieldType "and Target Column are " $TargetFieldType " which are not same!." -ForegroundColor Red
                Write-Host "Hence changing the Targeted list column type" -ForegroundColor Red
                #Update the TargetField by replacing destination column schema with source column schema
                $TargetField.SchemaXml = $SourceField.SchemaXml
                $TargetField.Update()
                $destCtx.ExecuteQuery()
                Write-Host "Target Column Type Changed Successfully !.............." -f Green
              }
            }
            else
            {
                write-host $SourceField.SchemaXml " Column Not Exists in Given Target list" $TargetListName -f Red
                $FieldSchema = $SourceField.SchemaXml
                #Add Columns to the List

                $NewField = $TargetList.Fields.AddFieldAsXml($FieldSchema,$True,[Microsoft.SharePoint.Client.AddFieldOptions]::AddFieldInternalNameHint)
                $destCtx.ExecuteQuery()
                Write-host "New Column Added to the List Successfully!" -ForegroundColor Green
            }

        }

    }
}

#Set Parameters
$srcListSiteUrl = "Source site Url"
$SourceListName = "Source List Name"
$dstListSiteUrl = "Destination Site Url"
$TargetListName = "Destination List Name"

#Passing Credentials
$credPath = 'D:\Arvind\safe\secretfile.txt'
$fileCred = Import-Clixml -path $credpath

#Call the function to copy list items
Create-List-Columns -siteURL $srcListSiteUrl -destSiteURL $dstListSiteUrl -SourceListName $SourceListName -TargetListName $TargetListName

Note

 

If the remaining columns had any lookup column, then a corrupt lookup column will be created on the destination site. To correct the corrupt lookup column, please refer to my blog, Repair Lookup Column In SharePoint Online Using PowerShell.