Automation rules are great, but at times I have wished to have the automation rules trigger as fast as formulas. The most tantalizing use case for this is two-way editable lookups. (I also list four more potential use cases in the above link.)
Now I have figured out way to do exactly that, albeit in a hacky way with some caveats (listed below). What it does is allow you to set up automation rules just as flexible (if not moreso) than native automation rules, except the rules react within a fraction of a second to any row changes rather than waiting for tens of seconds before the user sees any update.
Check out the demo below for some examples:
Essentially, it’s a button push-back loop that runs several times a second. Every iteration also pushes a list of other buttons (the rules) which in turn push buttons on rows that actually contain the code to run.
The pushback button action is
_Delay(loopButton, 0). Without the
Delay wrapper, the tab seems to accumulate calculations that block user interaction once you click “stop”. I don’t know why the delay helps but it does.
I also put a kill switch in there, and a
Stop button to manage the kill switch for safety.
In general, a rule consists of:
- a “shadow copy” of the column you are watching for changes
- an action to perform on row change
- in the case of multi-select lookups, probably two actions to account for items added and items removed from the list.
- a “disable if” rule similar to
thisrow.column = thisrow.shadow_column
- that’s important to avoid infinite loops
Take a look at the logic in the button columns for a more detailed look into how these rules work. It does get complicated quite fast, but once you write one or two rules, it’s mostly copy-paste to write more rules.
- The biggest caveat is that the tab crashes after about an hour (in my limited testing) of running. After that, you just reload the tab and press the button again.
- The second caveat is that the “rules” are quite complicated to write (compared to native automation rules.
- Requires at least two extra columns per automation rule
- This may have an impact on doc performance and/or tab CPU usage, but I haven’t noticed anything yet. (Maybe someone knows how to measure that.)