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

| improve this answer | |
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
| improve this answer | |
  • its giving me below error on $calcField = [Microsoft.SharePoint.Client.ClientContext].GetMethod("CastTo").MakeGenericMethod([Microsoft.SharePoint.Client.FieldCalculated]).Invoke($context, $field) – Rahil 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 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 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.