How to Update the Formula Field with the Formula on SharePoint 2013 using PowerShell

In Many situations, the formula fields will get updated based on the requirement change. For example, during the initial development phase, the formula for a field would have been assumed as something. During a period of time, this may get changed. At that time, we cannot delete and recreate this field, as this may cause a lot of effort. In that case, we need to update the field. Let us see how to do that using PowerShell.

##================================================================================================

## Description : Update the Site Columns

## Author : Sathish Nadarajan

## Date : 13-Aug-2015

##================================================================================================

# ============================================ Setup Input Paths ===========================================================

$Host.UI.RawUI.WindowTitle = "– Update SiteColumns –"

$StartDate = Get-Date

Write-Host -ForegroundColor White "————————————"

Write-Host -ForegroundColor White "| Update SiteColumns |"

Write-Host -ForegroundColor White "| Started on: $StartDate |"

Write-Host -ForegroundColor White "————————————"

$LogTime = Get-Date -Format yyyy-MM-dd_hh-mm

$LogFile = ".\UpdateSiteColumn-$LogTime.rtf"

#start-transcript $logfile

$scriptBase = split-path $SCRIPT:MyInvocation.MyCommand.Path -parent

Set-Location $scriptBase

$ErrorActionPreference = "Stop"

################################# Functions #####################################################

function AddPowerShellSnapin()

{

try

{

Write-Host "Adding PowerShell Snap-in" -ForegroundColor Green

# Try to get the PowerShell Snappin. If not, then adding the PowerShell snappin on the Catch Block

Get-PSSnapin "Microsoft.SharePoint.PowerShell"

}

catch

{

if($Error[0].Exception.Message.Contains("No Windows PowerShell snap-ins matching the pattern ‘Microsoft.SharePoint.PowerShell’ were found"))

{

Add-PSSnapin "Microsoft.SharePoint.PowerShell"

}

}

Write-Host "Finished Adding PowerShell Snap-in" -ForegroundColor Green

}

Function UpdateField ([string]$siteUrl)

{

$site = Get-SPSite -Identity $siteUrl

$web = $site.RootWeb

write-host "Updating the Formula" -fore yellow

$column = $web.Fields.GetFieldByInternalName("MyFormulaField")

if($column)

{

$column.Formula = "=TEXT([MyOtherField],""yyyy"")"

$column.update($true)

}

$web.Dispose()

$site.Dispose()

}

################### End of Functions ###############################################

try

{

AddPowerShellSnapin

$SiteColumnUpdateDetailsCSV = $scriptBase + "\" + "SiteColumnUpdateDetails.csv"

import-csv $SiteColumnUpdateDetailsCSV | where {

UpdateField $_.siteurl

}

Write-Host "Script Execution Completed Successfully" -ForegroundColor Green

}

catch

{

Write-Host "Custom Exception Happened on Main : " + $Error[0].Exception.Message -ForegroundColor Red

}

#stop-transcript

 ##================================================================================================
 ## Description	: Update the Site Columns 
 ## Author		: Sathish Nadarajan
 ## Date			: 13-Aug-2015
 ##================================================================================================
 
 # ============================================ Setup Input Paths ===========================================================
 
  
 $Host.UI.RawUI.WindowTitle = "-- Update SiteColumns --"
 
 $StartDate = Get-Date
 Write-Host -ForegroundColor White "------------------------------------"
 Write-Host -ForegroundColor White "| Update SiteColumns |"
 Write-Host -ForegroundColor White "| Started on: $StartDate |"
 Write-Host -ForegroundColor White "------------------------------------"
 
 $LogTime = Get-Date -Format yyyy-MM-dd_hh-mm
 $LogFile = ".UpdateSiteColumn-$LogTime.rtf"
 
 #start-transcript $logfile
 
 $scriptBase = split-path $SCRIPT:MyInvocation.MyCommand.Path -parent
 Set-Location $scriptBase
 
 $ErrorActionPreference = "Stop"
 
 
 
 ################################# Functions #####################################################
 
 function AddPowerShellSnapin()
 {
     try
     {
         Write-Host "Adding PowerShell Snap-in" -ForegroundColor Green
         # Try to get the PowerShell Snappin.  If not, then adding the PowerShell snappin on the Catch Block
         Get-PSSnapin "Microsoft.SharePoint.PowerShell" 
     }
     catch
     {
         if($Error[0].Exception.Message.Contains("No Windows PowerShell snap-ins matching the pattern 'Microsoft.SharePoint.PowerShell' were found"))
         {
             Add-PSSnapin "Microsoft.SharePoint.PowerShell"
         }
     }
     Write-Host "Finished Adding PowerShell Snap-in" -ForegroundColor Green
 
 }
 
 
 Function UpdateField ([string]$siteUrl) 
 {
     $site = Get-SPSite -Identity $siteUrl
     $web = $site.RootWeb 
 
     
     
 
     write-host "Updating the Formula"  -fore yellow
 
     $column = $web.Fields.GetFieldByInternalName("MyFormulaField")
         if($column)
         {
             
             $column.Formula = "=TEXT([MyOtherField],""yyyy"")"
             $column.update($true)
         }
         
    
         
     $web.Dispose()
     $site.Dispose()
 }
 
 
 ################### End of Functions ###############################################
 
  
 try
 {
      AddPowerShellSnapin
      
     $SiteColumnUpdateDetailsCSV = $scriptBase + "" + "SiteColumnUpdateDetails.csv"
 
     import-csv $SiteColumnUpdateDetailsCSV | where {
         UpdateField $_.siteurl 
     }
     Write-Host "Script Execution Completed Successfully" -ForegroundColor Green 
 }
 catch
 {
     Write-Host "Custom Exception Happened on Main : " + $Error[0].Exception.Message -ForegroundColor Red  
 }
 
 #stop-transcript
 

The script is self-explanatory and does not require any explanations I guess.

Happy Coding,

Sathish Nadarajan.