1

I want to update formula of calculated column using PnP or powershell. please note that i am using SharePoint Online. i tried below but its giving me error. please help.

PS C:\Windows\system32> Set-PnPField -List "CalculateDays" -Identity "MyFormula" -Values @{Formula="=222"}
WARNING: No property 'Formula' found on this field. Value will be ignored.
2

We can update the SchemaXml property to achieve it.

Connect-PnPOnline –Url https://xxx.sharepoint.com/sites/lz –Credentials (Get-Credential)

$listName="CalculateDays"
$fieldName="MyFormula"
$formula="=222"

$field = Get-PnPField -List $listName -Identity $fieldName
[xml]$schemaXml=$field.SchemaXml
$schemaXml.Field.Formula=$formula
Set-PnPField -List $listName -Identity $fieldName -Values @{SchemaXml=$schemaXml.OuterXml}

enter image description here

1

Set-PnPField uses Field type i.e Microsoft.SharePoint.Client.Field to internally set the property values.

This does not expose the Formula property of Calculated field which is of type Microsoft.SharePoint.Client.FieldCalculated.

So, you need to use a bit of CSOM jugglery to cast it to Calculated Field type and to get it working with PnP PowerShell as in the below mentioned code sample:

$context = Get-PnPContext
$field = Get-PnPField -List "CalculateDays" -Identity "MyFormula"
$calcField = [Microsoft.SharePoint.Client.ClientContext].GetMethod("CastTo").MakeGenericMethod([Microsoft.SharePoint.Client.FieldCalculated]).Invoke($context, $field)
$calcField.Formula = <your formula>
$calcField.Update()
$context.Load($calcField);
Invoke-PnPQuery
  • its giving me below error on $calcField = [Microsoft.SharePoint.Client.ClientContext].GetMethod("CastTo").MakeGenericMethod([Microsoft.SharePoint.Client.FieldCalculated]).Invoke($context, $field) – Rahil Jan Muhammad Aug 31 '18 at 7:17
  • Exception calling "Invoke" with "2" argument(s): "Object does not match target type." At line:1 char:1 + $calcField = [Microsoft.SharePoint.Client.ClientContext].GetMethod("C ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : TargetException – Rahil Jan Muhammad Aug 31 '18 at 7:17
  • works at my end ! Can you check if you are using the correct list and field internal names ? – Gautam Sheth Aug 31 '18 at 7:24
  • everything is correct but i am still getting an error. do i have to install any library/sdk before i run this powershell. all my other PnP commands are working fine – Rahil Jan Muhammad Sep 5 '18 at 6:28
  • I would suggest that you install SPO CSOM SDK and also ensure that you are using PowerShell v3.0 or above – Gautam Sheth Sep 5 '18 at 6:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.