Hello everyone,
I found this spreadsheet a while back that has some really confusing formulas that make it update constantly. I’m trying to understand how it works but I’m totally lost.
There are two main formulas that seem to work together:
Cell B3: =if(A3, if(A4^0, iferror(importdata("-"),{0;now()})))
This one looks at cell A4 and gives back an array with 0 and the current time.
Cell A4: =if(A3, if(iserror(B3),B5,{1,B5}))
This one checks cell B3 and returns an array with 1 and B5.
What’s weird is these two formulas reference each other in a circle. I have iterative calculation turned on in the settings. After that the whole sheet keeps recalculating over and over automatically.
I can’t figure out why this creates continuous updates. Also when I tried removing the importdata part from the first formula it stops working. Can someone break down what’s happening here step by step?
You’re observing a forced recalculation loop that utilizes Google Sheets’ iterative calculation feature. Both B3 and A4 establish a circular reference that cannot resolve, as each one relies on the other. The importdata function with the invalid URL is crucial here; it consistently produces an error, invoking the iferror fallback with each computation. If that error were absent, both formulas would stabilize and cease recalculating. The now() function provides current timestamps on each recalculation, resulting in ongoing changes. This approach is similar to techniques I’ve employed for real-time dashboards that require continuous updates without manual intervention, serving as a workaround for Google Sheets’ lack of an auto-refresh capability, though it indeed consumes processing resources continuously.
you’ve stumbled upon a neat trick with google sheets. the importdata with a bad url causes constant errors, making sheets keep trying to compute it. combine that with the circular ref and now() updating the time, and it just keeps going. great workaround!
The continuous recalculation happens because of a circular dependency that exploits Google Sheets’ error handling. When you enable iterative calculation, Sheets keeps trying to resolve the circular reference by calculating both formulas over and over - they never settle. The key trick here is importdata("-"), which throws an error since “-” isn’t a real URL. This makes iferror kick in and return {0;now()}, giving you a new timestamp each time. Since now() changes with every calculation, A4 recalculates, which triggers B3 again - creating an endless loop. If you removed the importdata part, the formulas would stabilize and stop recalculating. But that error is what keeps things moving, making this a clever hack for continuous spreadsheet updates.
This topic was automatically closed 4 days after the last reply. New replies are no longer allowed.