When you change any value in a cell, some functions automatically generate new update. One of these functions is NOW().
I currently have
=SECOND(NOW()) in A2. I also have a checkbox made with data validation in cell A1. Every time I toggle this checkbox on or off,
=NOW() automatically updates to display the current seconds.
However, I want it to keep updating for a set amount of time, defined by cell B1. For example, if B1 contains 5, then when I toggle the checkbox in cell A1, the
=NOW() function will constantly update for 5 seconds.
If I were to toggle the checkbox right as the minutes on my clock changed, then I would see A2 read: 0, then a second later 1, then another second later 2, then 3, and then 4, at which point it would stop updating until I toggled the checkbox again.
I've tried a variety of potential solutions to this problem, none of which seem to work. I even tried creating extensive lag through some query functions to cause the cell to delay approximately a second before updating again, however this simply created lag for 5 seconds at which point it displayed a 4, never showing the intermediate 1, 2, or 3. I suspect that this cannot be done with cell formulas, so my question is if I can write a script which will do this for me.
Note: This doesn't necessarily need to be returning the current seconds. All I am trying to achieve is a counter which increments approximately once every second (could be faster as long as steady).