New progressbar color

[Valentin_Serry]

Hello.

What is the formula to change color based on value like that ?

Thanks.

[Thierryvm]

Hello @Valentin_Serry ,

Here is an example of how to set up a progressive bar, but there are many other ways to do it

Sincerely,

Thierry

[Paul_Danyliuk]

Ummm… I can’t find a way to do that. The video was probably just marketing, and the slider’s color is static.

There must be an underlying control object behind the progress bar and I’m trying to find a way to dig into it but with no result so far.

[Paul_Danyliuk]

P.S. Am I not Paul?

This requires black magic though: you need to take the control object of a Slider and mix in a few properties to turn it into a progress bar.

Here’s the single formula. Don’t forget to set column type to Progress Bar. This is a hacky trick that may break any moment (and can even break your doc) so use at your own risk:

ParseJSON(
  '{"o":'
  + Slider(thisRow.[Slider input])._Merge(
      Object(
        "value", thisRow.[Slider input],
        "properties", Object(
          "displayType", "Progress",
          "color", SwitchIf(
            thisRow.[Slider input] < 20, "Red",
            thisRow.[Slider input] < 50, "Orange",
            thisRow.[Slider input] < 80, "Yellow",
            "Green"
          )
        )
      )
    ) + ""
  + '}'
)._Deref_object("o")

Welcome to the Community, @Valentin_Serry!

[Thierryvm]

Hello,

Sorry, I forgot the other formulas

Sincerely,

Thierry

[Valentin_Serry]

Merci beaucoup @Thierryvm !

[Valentin_Serry]

Thanks you @Paul_Danyliuk !

[Scott_Collier-Weir]

Hey there!

You can also set the color of a progress bar with normal conditional formatting.

To set the color of the bar, set the conditional format to change text color (not background color) and it should work for you!

[Agile_Dynamics]

@Paul_Danyliuk, my hero, you have done it yet again! Well done indeed!

I am particularily impressed because the ‘color:’ property in the ‘properties:’ object of the slider control does NOT appear in the JSON of a regular progress bar (as seen by _Merge()) !!

So you must have experimented to find it (or am I missing something here?)

Anyway, I had to roll up my sleeves and put on my coding hat to figure out what you are doing in the formula you posted. Deep voodoo magic indeed. Well done sir.

To help others from having to wade in waist deep to the nested entrails of it all, here is my humble attempt to understand what is going on in Paul’s formula…

(the //comments are not part of the formula, just my notes)

ParseJSON(    // setup an object called 'o' to extract at the end of the formula
    '{"o":'    // this starts the json for the object 'o'
    + Slider(thisRow.[Slider input])    // creates a slider object to modify below
    ._Merge(    // merge the slider object properties with those below
        Object(    // construct an object to hold 'value' and 'properties' key/value pairs
            "value", thisRow.[Slider input], // replaces the value property with the slider's value
            "peoperties", Object(    // replace the 'properties' attribute with the following object
                "displayType", "Progress", // sets the type to 'Progress'
                "color", SwitchIf( // sets the 'color' property depending on the value
                    thisRow.[Slider input] < 20, "Red",  // 0..19 => red
                    thisRow.[Slider input] < 50, "Orange",  // 20..49 => orange
                    thisRow.[Slider input] < 80, "Yellow",    // 50..79 => yellow
                    "Green"  // otherwise green
                )  // end of switchif
            ) // end of object for 'properties'
        ) // end of all properties to merge
    ) + ""  // end of _merge (needs +"" to convert it to json text)
+ '}'  // add the final close brace to the json string in parsejson
)  // end of parsejson - returns the object we have modified
._Deref_Object("o") // extracts the contents of object 'o' which is the progress bar we want

To understand this fully, we need to parse the whole JSON definition of a progress bar object as follows;

(again the //coments are just my own notes

{    // begin the json object for a progress bar
    "type":"control",    // it is a control interface
    "value":16,  // this is the current value, taken from thisRow.[Slider input]
    "error":null,  // then a bunch of coda internals we dont care about...
    "subtype":"slider",
    "valueRefs":[
        "cell:grid-9vfIw7eC9T:c-LYw8o4YTGm:i-Pia6tiJNtR:false"
    ],
    "properties":{ // this is the properties object we do need to change
        "min":0, // the min, max, and step we leave alone..
        "max":100,
        "step":1,
        "displayType":"Progress", // we set this in the formula
        "color":"Red"  // and set the color using the SwitchIf() formula
    }
}

@Paul_Danyliuk will correct me if I got anything amiss

Hope this helps

Max

[Breno_Nunes]

Great solution, @Scott_Collier-Weir .
It’s what everyone should use.
Thanks.

[Agile_Dynamics]

yes indeed, well done @Scott_Collier-Weir, that is way more convenient !

[Paul_Danyliuk]

Oh gosh. I swear I was testing Conditional Formatting the first thing and it didn’t work!

Now that I tested it once again, here’s the catch: conditional formatting only works if you set progress bar color to Blue (the first one / default one!) I probably had it changed to something else and conditional formatting didn’t have effect over that other color.

I feel dumb now but this behavior is also very strange. Conditional formatting should override any color, not just blue.

[Agile_Dynamics]

well paul, dont feel bad. it hadnt occured to me to even try conditional formatting.

and…

having to parse out your formula has actually taught me some new stuff that i wouldnt have encountered otherwise.

so…

there was some good from it all in the end

max

[Paul_Danyliuk]

Oh well, my trick is still relevant if you only want to recolor the progress bar but keep the label grey:

(Left: cond fmt. Right: my trick)

[Valentin_Serry]

Thanks @Scott_Collier-Weir !

It’s exactly that ! Not a very intuitive approach.

Thanks a lot ! Have a good day.

[Paul_Danyliuk]

@Agile_Dynamics

So you must have experimented to find it (or am I missing something here?)

I opened the JS debugger (developer tools) and set a breakpoint in a few random places that seemed responsible for drawing a progress bar. Before that I discovered that Progress Bar was just a subtype of Slider, so it was easier to locate the former. Then when a breakpoint triggered, I looked through the (obfuscated) scope variables to find the one that revealed a progress bar descriptor. That way I learned that there were extra properties color and displayType: "Progress".

As for _Merge(), I never used it to combine objects before so it was an intuitive thing to try and use it this way. It worked as expected — deeply merging nested objects’ properties. I remember there was a thread in the Community asking what Merge was doing exactly — I can’t find the thread but above is the answer

[Katy_Turner]

I am so impressed by the creativity on this thread! As some good news, progress bar color gradients are now available, and you can choose from several options in the column or control settings. You’ll hear more from the team on this soon, but letting you know in the meantime.

[Thierryvm]

Hello @Katy_Turner

Here’s a great news for the look of the progress bars and when about the colors for the text. I speak here of course of additional colors to the sixteen already present ^^

[Paul_Danyliuk]

And if you don’t like the stock options, you can do some trickery to get custom gradients

Launched: Progress bar column type and more features for project trackers

Meanwhile my previous hacking efforts didn’t go in vain — I just black magicked custom gradients: Style options are smooth and segmented. Colors can be HEX or named {colorId, shade} Coda colors (light/dark mode adaptable). For solid colors just use "type" : "SolidColor", "colorId" : "NamedColorId". Hex won’t work here, only named colors.