Set a color in conditional format by formula

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

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:

1 Like

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.