I haven’t yet gotten to the bottom of it, but I believe that automation will fail when
a) it tries to read a “volatile” value (a result of Now(), Today(), or User(), which is calculated in a browser but not on Coda backend)
b) it tries to read a value that has pending edits to it (e.g. when you edit a cell and then try to use its value in the same automation run)
At least that was my assumption for a while, because I remember I saw both scenarios working for me at least once. So there must be something else to it.
Testing the rule works because testing happens in a browser where every step is calculated right away and your browser context can easily read all the fresh data. Backend processing (by automation bot) happens differently and only works with the latest available snapshot of data. It doesn’t spin up a browser just so that formulas would be recalculated during an automation run.
These are just my assumptions; Coda backend is a black box and I don’t think any engineer would shed much light on this because it’s a trade secret. I can only speculate based on my general knowledge about how software is usually built.