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.

Column Formatting

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.

columnformattingexample

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.

The setup

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.

foodchoicefield

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.

usingvariables

4. Run the Power Automate Flow and check the list you have added your column to.

5. Use the show/hide columns option
showhidecolumns

6. Add your column to your preferred view
addtoview

7. Use the edit in grid view to see your formatting in action.
editingridview

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.

Happy testing!

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.