Tuesday, August 13, 2019

Update Calculated Column periodically without editing any items in Sharepoint using PowerShell


One of my recent activity was to automate sending email to some Manager’s to provide their approval for their reportees to extend their employment contract for next one year. This can be done using SharePoint workflow, but the challenge is we are not privileged to use the SharePoint Designer.

So, we decided to use the PowerShell script to trigger an email using client context. Then we created required list columns and employee information’s and I created three calculated columns along with that,
  • ·         First is for to get the number of days between last renewal date with today’s date (Today () – last renewal column)
  • ·         Second is for to round the number to nearest digit (= Round (Calucaltedcolumn1,0))
  • ·         Final calculated column is to indicate which items are crossed 365 days and it will set a Flag as Remind as a column value (=IF (second calculated column =< 365),” Remind”, “No”)).

But we were not realized that calculated column will be populate the value when the list items gets modified, since we are using Today() in formula. We can edit the list item using PowerShell but we required Modified date for some reason to track the approval process.

To overcome that, I used below snippet code to update calculated column without editing any items, what it will do it will just update the mentioned column and the it will populate the value.
Clear-Host
Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"

$clientContext = New-Object Microsoft.SharePoint.Client.ClientContext "Site URL"

$listname ="List Name"


                $field = $List.Fields
                $ListField= $clientContext.Load($field)
                $clientContext.ExecuteQuery()
                $ColumnField = $field | select -ExpandProperty InternalName
                $Columnvalue = @("Column1","Column2","Column3")
                Foreach($ColumnName in $Columnvalue)
                {
                    If($ColumnField.Contains($ColumnName))
                    {
                             #Get the List column
                 $ListColumn = $field.GetByInternalNameOrTitle($ColumnName)
                        $ListColumn.Update()               
                        $clientContext.ExecuteQuery()
                         Write-host "List Column Updated Successfully!"
                    }
                    else
                    {
                            Write-host "List Column not found!"
                    
                  }

No comments:

Post a Comment