Answered

SharePoint Rest API - Update liste column formatting / How?

(0) Share

Report

Posted on by stpauli

Hi,

I can see that the column have a "customformatter" attribute, but I am unable to access it and write some JSON formatting to it.

It says when using _api/Lists/GetByTitle('listname')/Fields/getbytitle('field'):

"An unexpected 'StartArray' node was found when reading from the JSON reader. A 'PrimitiveValue' node was expected."

I do not want to use the _api/web/Fields/createfieldasxml api as I find the xml a bit cumbersome and I just want to copy the json that I have tried and tested in the SharePoint GUI to my powerautomate flow.

I have done it successfully with applying JSON formatting to the whole list, but not to a single column. Anyone that has a sample that would do that ?

Any help would be appreciated.

Best,

Udo

Categories:

General topics

I have the same question (0)

All responses (11)

Answers (2)

Sort by

Expiscornovus 33,874 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
Hi @stpauli,

Your json might not be valid in the body field. I normally escape the quotes in the CustomFormatter value (formatting json). What did you use, can you share your json code?

Below is an example of how I would update an existing field.

In this example I am setting the background colour and text colour to red when a field called FormattingField has a value which is equal to Test

URI

_api/web/lists/getbytitle('@{variables('ListName')}')/fields/getbytitle('@{variables('FieldName')}')

Headers

{ "Accept": "application/json;odata=verbose", "Content-Type": "application/json;odata=verbose", "If-Match": "*", "X-HTTP-Method": "MERGE" }

Body

{ "__metadata": { "type": "SP.Field" }, "CustomFormatter": "{\"elmType\":\"div\",\"style\":{\"box-sizing\":\"border-box\",\"padding\":\"0 2px\",\"overflow\":\"hidden\",\"text-overflow\":\"ellipsis\"},\"attributes\":{\"class\":{\"operator\":\":\",\"operands\":[{\"operator\":\"==\",\"operands\":[\"[$FormattingField]\",\"Test\"]},\"sp-css-backgroundColor-BgCoral sp-css-borderColor-CoralFont sp-field-fontSizeSmall sp-css-color-CoralFont\",\"\"]}},\"txtContent\":\"[$FormattingField]\",\"templateId\":\"ConditionalColumn\"}" }

Was this reply helpful? Yes No
stpauli 24 on at

Like (0)

Report
Copy link

Link copied!
Thank you @Expiscornovus for trying to help.

this is my JSON that I am trying to add to a field:

{ "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json", "elmType": "div", "attributes": { "class": "ms-fontColor-themePrimary ms-fontSize-m" }, "children": [ { "elmType": "span", "style": { "padding-right": "10px" }, "attributes": { "iconName": "Comment" } }, { "elmType": "span", "txtContent": "=if([$_CommentCount] == '' , 0 ,[$_CommentCount])" } ] }

as mentioned I have used it formatting a full list view using this api: _api/Lists/GetByTitle('listname')?['body/data/200']}')/Views('viewname') where I put this in the body successfully for formatting it:

{ 'CustomFormatter':'{ "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/row-formatting.schema.json", "additionalRowClass": { "operator": ":", "operands": [ { "operator": "==", "operands": [ { "operator": "%", "operands": [ "@rowIndex", 2 ] }, 0 ] }, "sp-css-backgroundColor-BgLightGray30", { "operator": ":", "operands": [ { "operator": "==", "operands": [ { "operator": "%", "operands": [ "@rowIndex", 2 ] }, 1 ] }, "sp-css-backgroundColor-noFill", "" ] } ] } }' }

I will look through you post and check if I will be successful with that. Thks

Was this reply helpful? Yes No
stpauli 24 on at

Like (0)

Report
Copy link

Link copied!
Ok I was able to post your body (adpating it to one of my field names) to my list. which means that my body message is not being accepted but the rest like header, api url etc should be fine. However, I am unclear what is wrong with my JSON. Here a screenshot. I also tried escaping the double quotes, however, it not help either:

body text:
{ "__metadata": { "type": "SP.Field" }, "CustomFormatter": "{ \"elmType\": \"div\"," \"attributes\": { \"class\": \"ms-fontColor-themePrimary ms-fontSize-m\" }, \"children\": [ { \"elmType\": \"span\", \"style\": { \"padding-right\": \"10px\" }, \"attributes\": { \"iconName\": \"Comment\" } }, { \"elmType\": \"span\", \"txtContent\": \"=if([$_CommentCount] == '' , 0 ,[$_CommentCount])\" } ]}" }

the error is:
Invalid JSON. A comma character ',' was expected in scope 'Object'. Every two elements in an array and properties of an object must be separated by commas.

Was this reply helpful? Yes No
Verified answer

Expiscornovus 33,874 Most Valuable Professional on at

Like (2)

Report
Copy link

Link copied!
Hi @stpauli,

It looks like your first line (the one with elmtype div) has an extra quote at the end of the line directly after the comma character. It looks like this turns it into invalid json. Can you remove that character?

Can you try the below instead?

{ "__metadata": { "type": "SP.Field" }, "CustomFormatter": "{ \"elmType\": \"div\", \"attributes\": { \"class\": \"ms-fontColor-themePrimary ms-fontSize-m\" }, \"children\": [ { \"elmType\": \"span\", \"style\": { \"padding-right\": \"10px\" }, \"attributes\": { \"iconName\":\"Comment\" } }, { \"elmType\": \"span\", \"txtContent\": \"=if([$_CommentCount] == '' , 0 ,[$_CommentCount])\" } ]}" }

Was this reply helpful? Yes No
stpauli 24 on at

Like (2)

Report
Copy link

Link copied!

Oh, I cannot believe it...😲. yes, you are right. thanks so much!

Was this reply helpful? Yes No
stpauli 24 on at

Like (0)

Report
Copy link

Link copied!
Hi @Expiscornovus ,

do you have a chance to help me once more with one of my struggles?....I am trying to enable rich text for multiline fields, but I was not successful doing it. I see the attribut richtext exists, but I am unable to set it to true somehow. I used this (and variants of this) JSON:

JSON:

{ "__metadata": { "type": "SP.Field"}, "FieldTypeKind": 3, "Title": "Story", "DefaultValue": "text here..." }

Tried amongst others just adding the attribute to the JSON
{ "__metadata": { "type": "SP.Field"}, "FieldTypeKind": 3, "Title": "Story", "RichText": true, "DefaultValue": "text here..." }
Error:
The property 'RichText' does not exist on type 'SP.Field'. Make sure to only use property names that are defined by the type.

Thanks so much.

Was this reply helpful? Yes No
Verified answer

Expiscornovus 33,874 Most Valuable Professional on at

Like (2)

Report
Copy link

Link copied!
Hi @stpauli,

Can you chance the SP.Field type value to SP.FieldMultiLineText?

Try something like below

{ "__metadata": { "type": "SP.FieldMultiLineText"}, "FieldTypeKind": 3, "Title": "Story", "RichText": true, "DefaultValue": "text here..." }

Was this reply helpful? Yes No
stpauli 24 on at

Like (0)

Report
Copy link

Link copied!

Ok thanks @Expiscornovus that works. I thought, I tried it yesterday, but it failed...but must have done something other wrong I guess - probably wrote "Richtext" instead of "RichText" 🙄. The only thing I am wondering now about is why setting richtext to true is not the same as enabling this button - which provides a richer editing as well:

Was this reply helpful? Yes No
Expiscornovus 33,874 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!
Hi @stpauli,

Like the error mentioned, the RichText property is not available on the SP.Field type of fields. That's why you cannot set it when using the SP.Field type, it is simply not listed as a property.

The property 'RichText' does not exist on type 'SP.Field'. Make sure to only use property names that are defined by the type.

See a list of properties for the SP.Field below in the Microsoft docs. Some of these properties are read-only btw.

https://learn.microsoft.com/en-us/previous-versions/office/sharepoint-visio/jj245246(v=office.15)

That RichText property is available on the SP.FieldMultiLineText class:

https://learn.microsoft.com/en-us/previous-versions/office/developer/sharepoint-2010/ee551695(v=office.14)

Was this reply helpful? Yes No
stpauli 24 on at

Like (0)

Report
Copy link

Link copied!

sorry for being unclear yes, i tried also "SP.FieldMultiLineText", but failed due to some other spelling mistake...any clue why the ui/ux is different when enabling it via SP directly oder programmatically ? Thks

Was this reply helpful? Yes No