How to format a column via Power Automate
A lot of times you get really interesting questions in communities. That’s one of the reason I decided to help out more. One of them was how to add custom column formatting in SharePoint Online via the REST API within Power Automate.
In this example I will try and show you how you can add colours to options of a choice field in a SharePoint list. Normally this is something you can easily achieve via the Design Mode or the Advanced Mode editor in JSON. There are a lot of nice examples in this Use column formatting to customize SharePoint article.
In our example we will try and add a Food Choice column with three choice options: Pizza, Burger and Sushi. Each option will have it’s own custom column like below.
However, like asked in this thread, we want to automate the deployment of this. Obviously there are other techniques out there like PnP Provisioning. But in this article I will focus on using Power Automate to deploy the choice field with column formatting.
REST API to the rescue
After a bit of research I found the CreateFieldAsXml option for the creation of fields. By analyzing some manually steps and using the Developer toolbar of my browser I found the body for the POST request and the proper syntax for it.
This is the original foodchoicefieldcustomformatting.json file I am using for the example. The difference within Power Automate is that we are encoding that so that the body accepts it as valid and add some metadata to it.
1. Start with a new instant flow with a Manually trigger a flow trigger action.
2. Add a Send an HTTP request to SharePoint action. Use the POST method and use /_api/web/lists/getByTitle(‘ListName”)/fields/CreateFieldAsXml in the URI field.
3. Add the following thing to the Body field. Notice that I am using seperate variables for the three labels (ChoiceOne, Pizza) and for the three colours (ChoiceColorOne, sp-css-backgroundColor-red). I also added a variable for the choice field name.
4. Run the Power Automate Flow and check the list you have added your column to.
I want different colours
Obviously I can imagine you want to use different colours in your setup. Have a look at this site with all the Reusable SharePoint Online Classes for Modern UI by Denis Molodtsov. In my example I was using sp-css-backgroundColor-red for Pizza, sp-css-backgroundColor-green for Burger and sp-css-backgroundColor-yellow for Sushi.
That should be it with the setup. Start testing and let me know if this was useful.