Powershell script to create calculated field in Sharepoint-CodePudding

I have a calculated column in a Sharepoint list using this formula.

=CONCATENATE("<a href='https://apps.powerapps.com/play/qqq?FieldID=",[Field Number],">",Title,"")

How do I create a field like this using Pnp. I've tried this, but it fails.

 Add-PnPField -List $ListName -DisplayName 'PowerAppLink' -InternalName PowerAppLink -Type Calculated -Formula "CONCATENATE(`"`<a href='https://apps.powerapps.com/play/qqq?CABID=",[CAB Number],"`>`",Title,"`</a`>")" -AddToDefaultView

The error message is Error adding field: Cannot convert 'System.Object[]' to the type 'System.String' required by parameter 'Formula'. Specified method is not supported.

CodePudding user response：

Use a verbatim string literal (' instead of ") - then the only characters you need to escape are ':

$expression = 'CONCATENATE("<a href=''https://apps.powerapps.com/play/qqq?CABID=",[CAB Number],"''>",Title,"</a>")'

Add-PnPField -List $ListName -DisplayName 'PowerAppLink' -InternalName PowerAppLink -Type Calculated -Formula $expression -AddToDefaultView

CodePudding user response：

First, make sure that the Calculated column formula is correct. You can create a Calculated column in the list through SharePoint UI to see if the formula works normally.

Eg:

Formula:

=CONCATENATE("<a href=''https://apps.powerapps.com/play/qqq?CABID=",[CAB Number],"''>",[Title],"</a>")

Then, please run the below PNP Powershell as an admin

$SiteURL = "https://echodu.sharepoint.com/sites/TeamSite1"

$ListName= "ListD"
  
Connect-PnPOnline -Url $SiteURL -Credentials (Get-Credential)

$ex = '=CONCATENATE("<a href=''https://apps.powerapps.com/play/qqq?CABID=",[CAB Number],"''>",[Title],"</a>")'

Add-PnPField -List $ListName -Type Calculated -InternalName "PowerAppLink" -DisplayName "PowerAppLink" -Formula $ex -AddToDefaultView