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.
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 -
This also works for
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
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.
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:
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: