Solved: Conditional Formatting by formula using absolutely ANY color

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 :slight_smile:

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:

19 Likes

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

OOOOH SHIIIII~~~~ :astonished: :astonished: :astonished:

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.

2 Likes

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 :slight_smile: 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.

2 Likes

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

1 Like

@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

1 Like

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

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 :slight_smile:

1 Like

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() + “”

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

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

1 Like

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. :sweat_smile:

Recorded a quick GIF:

Clumsy fingers discovery
4 Likes