Set a color in conditional format by formula

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

2 Likes

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”.

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

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

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

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

@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()

4 Likes

@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()
11 Likes

Great work, thank you a lot!

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

1 Like

This could be handy.

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.

3 Likes

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.