Set a color in conditional format by formula

Showcase Tips and Hacks
1

Excel has color scale option in conditional format. I think Coda can give this functionality and even go further, if we can set a color using some formula, e.g. HSV values based on current row data.
conditional_format2%5B1%5D

5 Likes
2

This would be great. I had to use the rectangle function as a way to display a color block in a cell. Example Rectangle (20,20, "#00FF00). It would be better if on a column named RGBHex you could format it to “Color”.

3

Using a few conditional formatting rules for each step in the scale you want, you could use PercentileRank() to put something together to at least of some sort of scale. Agree that this could be better but in a pinch it’s what I’ve found works. :slight_smile:

1 Like
4

I haven’t found the way to change cell background via formula, but as for the others —

  • You can programmatically set text color with a hidden _Color(text, color) function;
  • You can draw data bars of arbitrary color and length with Rectangle(...), e.g. see how I did something similar here.

In fact, let me quickly put something together.

1 Like
5

Where can I get the list of all hidden formulas?)

6

An excellent question, the answer to which I would also be interested in knowing. :slight_smile:

2 Likes
7

@Nick_Milner me too.

Here’s the one that I found by trial-and-error:

  • _Color()
  • _Highlight() — for some reason this one doesn’t let you set the color :confused: it’s always yellow
  • _Bold()
  • _Underline()
  • _Strikethrough()

Didn’t find italic :confused:
These can be nested. And to link these together and with unformatted text you’ll have to use Concatenate()

6 Likes
8

@Denis_Peshekhonov @Nick_Milner — I dug into Coda’s source code (JS) and here’s the most complete list of hidden functions that there can ever be.

  • _Bold()
  • _Code() — should apply code styling but doesn’t
  • _Color()
  • _Highlight() — no way to specify color though
  • _Italicize()
  • _RichText() — seems to be useless, basically does the same as Concatenate()
  • _Strikethrough()
  • _Underline()

Next ones I haven’t tested, just listing here (case-insensitive). Some are experimental and may not work at all.

  • _MergeConditionalFormats — “Deeply merges objects”
  • _PROJECT_COLUMN_FOR_ROWIDS — “Filters rows in a table to the set of rows with ids matching the values”
  • _HASH_MATCH — “Filters on column equivalence on some or all matching columns”
  • _TRUE — not sure why it exists along with just True().
  • _RandomImpl — “Generate a random number”
  • _CREATE_ERROR — “Create an error”
  • _DELAY — “Returns a value after some delay”
  • _FAKE_ARRAY — “Returns an array with a schema”
  • _FAKE_OBJECT — “Returns an object with a schema”
  • _WRAP_INVALID_FORMULA — “Used to wrap an invalid user formula so the invalid input does not break AST parsing.”
  • _MERGE — “Deeply merges objects”
  • _DEREF_OBJECT — “Dereferences the given path”
  • _DEREF — “Dereferences the given input with the given reference”
  • _DEREF_OPT — “Dereferences the given input with the given reference”
  • _ROW_KEY — “Returns the reference key for the given row”
  • _SORTED_SEEK — “Filters on column equivalence on some or all matching columns”
  • _RANDOMIZE_CURRENTLOCATION — “Random travel”
  • FormatCurrency()
  • FormatDateTime()
  • FormatDuration()
  • FormatNumber()
  • FormatPercent()
  • FormatTime()
  • ATTACHMENTCONTROL
  • BUTTON
  • CHECKBOX
  • DATEPICKER
  • DATERANGEPICKER
  • FORMULACONTROL
  • MULTISELECT
  • SELECT
  • SCALE
  • SLIDER
  • HTML()
  • BLOB — for system use only
  • STDEV and STDEVP
  • Identity
  • ToHtml
  • Project — “Projects one or more columns from the given result”
  • CurrentLocation
  • RegexExtract

There are also some undocumented (and perhaps experimental) actions:

  • _NoOp() — no operation to use in IF statements for an action
  • DuplicateSection()
  • Xhr() — make an XHR request to an URL with arbitrary method and body
  • RefreshPack()
  • StartSync()
  • FinishSync()

I also found some experimental formulas for charts — although those use ugly Google Charts API and not Coda’s default Highcharts. I suggest you don’t use these unless Codans give us a leave to do so:

  • PieChart()
  • LineChart()
  • BarChart()
  • PieChartAggregate()
  • Histogram()

Also using Google APIs:

  • GoogleDirections()
  • GoogleMap()
  • LaTeX()
30 Likes
9

Great work, thank you a lot!

1 Like
10

P.S. I forgot Activate() action (brings referenced element into focus) — I thought it was documented.

3 Likes
11

This could be handy.

12

Pretty cool to see the list here but I would like to advice you not to not rely on these in general since they can break at any point of time. They are hidden and experimental as we are still working with them and can delete them without any warning.

7 Likes
13

Wow. Just tried this out. Absolutely amazing.

One thing i would ask is for relative sizing, i.e. 100% vs 950px. That way it looks good on both mobile and desktop.

But wow.

14

@Paul_Danyliuk
Do you have any ideas on how to sue the Xhr function ?
What would the syntaxe for a POST request ?

15

I tested it and it’s not working yet. It would send those request through Coda API (kind of a proxy), but there’s just one thing… the API is not existing yet.

16

I used this API (https://coda.io/developers/apis/v1beta1) to build a 1-way sync between two coda apps but yeah I can’t use it.
Would be really useful though !

17

I’ve been playing with the _Color() function a little bit. For reference, here’s the colors I’ve found that work so far:

Red: #AE1616
Green: #3E8A61
Blue: #4E71B5

Where the function would be _Color("#212121",text).

Thanks!

18

What do you mean by colors that work?

19

At least as far as I could tell, regular hex values don’t work for that function (color does not change), but if you look up the hex values of the pre-set text colors in the Coda formatting bar, those seem to work.

20

Any color works (or at least used to work at some point) for me, not sure why only some are working for you @Brian_Seidman