Set a color in conditional format by formula

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

5 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()
17 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.

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

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

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.

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 !

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!

What do you mean by colors that work?

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.

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

@Paul_Danyliuk, you’re right. For whatever reason I couldn’t get other hex colors to work for me the other day, but all values seem to be working now. :man_shrugging:

Since this is post already contains a list of hidden functions, I want to add some info about the non-hex colors.
(For example, to use in Button()).

List of available color (case-sensitive):

  • Red
  • Orange
  • Yellow
  • Green
  • Blue
  • Purple
  • Pink
  • Gray

Providing an incorrect color name will permanently crash your doc, even if you haven’t finished typing in the formula. (The doc can be recovered with version history).

That’s why it’s important to add the ending " after the color is fully typed in:

And not type like this:

P.S.
In a few cases, such as buttons, you can also change the font color independently from the main color by using the _Color function as suggested in this post.

Yeah, that one bit me as well.

Another interesting “property” of the BUTTON() (and probably some other artificial elements) - double clicking it can crash the document. (You can simply reload for it to work again, but it’s still pretty annoying.)
And this happens even if the button is disabled.

When does this happen?
So far I noticed this when an artificially created button is displayed inside a Text column. My guess is that the button clicking action somehow collides with the double-click action to edit formula.

Solution 1
This is the less pretty solution out of the two in this post, but I’m going to show it anyway in case the other one stops working.

Change column type
Using certain column types can prevent crashing - slider, scale, date and time.
This also works for list, lookup from table and people column, as long as they have “allow multiple selections” set to true.

Unfortunately, this solution works only for pure buttons, so they cannot be in a list or combined with anything else via Concatenate().
What’s more, the type mismatch causes error (red triangle and hover-over bubble) to be displayed in that column at all times.

Wrap everything with a button
The basic idea behind this is to move the formula to a label of dummy button. As it turns out, the buttons still work when they are a part of a label. But again, an error will be always visible.
It is also a good idea to disable the dummy button to prevent flashing whenever you click on it or any other sub-buttons.

Example formula:

And by setting background color to white in conditional formatting you can even hide the button in the background. (Unfortunately, any text will be lighter and even using _Color() won’t change it back to black).

Wrap text with a pseudo-select
There actually is a way to make the text darker again - if you wrap your text in a SELECT() it’s not affected by the disabled button.

An important thing to note about using SELECT() - it’s first argument (i.e. text that will be displayed) must be a column reference, using a string won’t work. However, referenced column doesn’t have to be a select column and can be of any type.

Here is an example formula:

And the result looks like this:

Solution 2
This solution is slightly simpler and doesn’t display an error in the rows.

Changing column to button
Create a standard button column and make it so the buttons are always disabled.
Then put the combined buttons and text into the button label.

And by applying the conditional formatting and wrapping text in pseudo-selects (as it was explained in the previous solution), you can make it completely seamless.

And a final overview of how this solution works:

2 Likes

Dear @Filmos,

Great input, I see you have a serious skill set and valuable inputs.:gem:
Would you mind to put this in a seperate post and mention (@) one of the Codans so they can evaluate your input?

Moved list of hidden formulas to this post, as we have slightly gone off-topic from the original question.