Solved: Conditional Formatting by formula using absolutely ANY color

[Jono_Bouwmeester]

This is my first post in the community, and I’m super stoked to be giving back a little bit. Hopefully someone will find this useful

I discovered a hidden formula last night, and saw that there’s nothing in the Coda documentation or in the Community posts about it, so thought I’d whip up a doc to help explain what I discovered. I’ve certainly been having a ton of fun with it today.

The _GRADIENT() formula allows you to apply conditional formatting to tables far easier and with much more control than Coda’s conditional formatting editor.

Here are some perks:

• Use literally any color you want.
• Apply hundreds of colors to your tables with a simple copy-paste instead of setting up individual rules one-by-one.
• Your own Color Scales, with meaningful color stops that present your data better to your users.
• And finally, a little insight into how Coda stores colors internally, and how we can use this for some cool UI tricks.

There’s a lot to go into, so I think I’ll just share a link to my doc:

Welcome · Dynamic Conditional Formatting

[Christiaan_Huizer]

wonderful contribution to make better use of colors!, thx @Jono_Bouwmeester

[Paul_Danyliuk]

OOOOH SHIIIII~~~~

This is an AMAZING find! Thank you!

I last looked through the hidden formulas a week ago or so. This must be new, or else I’m wondering how I missed it! So far I’ve been using the “stock” gradients for color scales: setting e.g. min=0, max=359, and then choosing fitting numbers to select colors for lookup chips. But obviously that’s too limited, and the stock gradients are not even uniform in luminosity.

P.S. And welcome to the Community as well!

P.P.S. I believe you don’t have to ParseJson now — you can now formulaically construct an object using an Object("colorId", "Blue", "shade", "Medium") hidden formula.

P.P.P.S. What’s especially mindblowing about this is that the _Gradient() formula is designed to be used within the conditional format formula — i.e. in place of all those Date < Today() and so on, which is completely different semantics. For me as a developer this is a curious implementation choice: they retrofitted the formula column to add color scales. I’m wondering how you even found it.

[Jono_Bouwmeester]

Ahh I’m totally geeking out, Paul and Christiaan you guys have been such a massive help learning the ins and outs of Coda. You’re both in my “Codans I dig” bookmarks folder Totally flattered you took the time to reply to my post.

Please shout if you think I can improve the doc at all, I tried to explain this the best I could, but I haven’t written any tutorials before so I have no idea if I’ve been clear.

Thanks again for taking the time to reply, very happy to finally contribute to the community.

[Daniel_Stieber]

Incredibly cool finding and a great doc to present it. I was expacting a little demo, but you really got deep into it!

[Agile_Dynamics]

@Jono_Bouwmeester ,

well done indeed. firstly for finding the hidden function and working out how it works. and secondly for sharing those details in such a clear manner. impressive.

but i’ve a question to ask you treasure hunters out there… how do you FIND these hidden formulas in the first place?

that’s the deeper secret knowledge that separates you wizards from us mere muggles.

is it simply a matter of hitting ctrl_j in chrome and snooping around?

as obiwan said in star wars…
“view the source, luke,
view the source!”

max

[lars_hofstetter_10TIMES_io]

Love it!! Thanks a lot. The cards with the slider are amazing!

[Jono_Bouwmeester]

Hi Max

I actually got lucky finding this one. I added a color scale to a table and where it says “min” and “max” in the conditional format editor, by mistake my chubby finger entered a letter (not a number).

Doing that, the rule generated a formula which included Gradient and ParseJson with the colorId and shade. That started the ball rolling and the rest was just fooling around and seeing what worked.

No Jedi tricks this time

[Jono_Bouwmeester]

One thing I’ve found really helps lately is viewing the underlying object. I think I got this from Paul. The formula is

thisRow.[ColumnName].Merge() + “”

[Hendrik_TnB]

Holy s**t ! This is awesome! thanks man!

[Connor_McCormick1]

@Jono_Bouwmeester that is such an impressive doc showcase and great find! Amazing work!

[Jono_Bouwmeester]

Hi Paul, sorry for the late reply, I didn’t previously see your PS’s.

Thanks for the heads up on the Object() formula, that’s going to come in handy!

I’m also a developer and fully agree - a really strange implementation. I’m guessing that’ll change before it ends up on the official formula list.

To answer your question about how I found this, I mentioned in a reply to Max that it was the result of a clumsy typo. Entered a non-number into the “Min” input when adding a Color Scale, and when I pressed enter, the color scale’s formula editor updated itself. Not a very elegant investigation, haha.

Recorded a quick GIF:

Clumsy fingers discovery

[anon43902428]

This has been great (thanks, Jono!), but does anyone know of a way to have Conditional format pull a named formula? When I call a named formula (which resides on the canvas) in Conditional format, it complains that “thisrow” is unknown—an error on the canvas, understandably, but not in the context of the Conditional format. Ways forward?

[Evan_Price1]

Wow thank you @Jono_Bouwmeester , can’t believe I’m only finding this now, it’s so helpful!

Cool to see another saffer on coda forums, and thanks for such an awesome explainer doc!

[Michael_Weaver]

That’s very neat, thanks!

Is there any way to dynamically change text colour using this, rather than background? I’d like to have a dynamic progress bar - it seems to be based off text colour. Or dynamically changing Scale icons would be handy too.

[Michael_Weaver1]

Nevermind.

[mrbrownjeremy]

EDIT: I figured it out—see reply in next message

Is it correct that the below thread from 2022 contains the only way to also change the text color to something with more contrast for darker colors? Unfortunate if so.

@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!

[mrbrownjeremy]

I found out how to control the text color in Jono’s source doc—I’m not sure why I originally assumed the style was added manually. It is, in fact, also controlled via conditional formatting, just via a separate rule, which seems workable.

See rule: All Rows with custom Font color set to white and Apply To set to “Name”

With that, I also need to echo how grateful and impressed I am at the work that went into the doc!