RESEARCH: Determine Gsheets (and Excel) formula re-calculation behavior

[wanderslth]

Determine cell re-calculation parameters for both Gsheets and Excel and report back to the group.

TL;DR: it appears gsheets caches formula results and so if something changes on the backend, gsheets won't show updates, even if you click 'enter' again on the formula. HOWEVER -- there are certain things that appear to break cache (like deleting the sidepanel); need to determine these behaviors for sake of users.

Summary:

For purposes of pricing, our server getting hit and for clarity to end users, we need to understand when a cell with an =FLEX() formula is being re-calculated (ie, when the sheet is requesting something new of Flex to return).

Background:

When Dave and I were fooling around with the plug-in, I noticed that when you "Remove" the plug-in (via Add-ons > Manage) from the app, all cells with an =FLEX() calculation went from displaying the request (in this case "HELLO WORLD" array) to displaying and error (in this case #NAME?).

Gsheets will auto-refresh certain formulas (NOW, TODAY, RAND, RANDBETWEEN) 'on change' by default. "On change" means any activity in any cell. So, if you have =now() in a cell it will populate with time/date. The cell will continue displaying that time and date until something changes in the spreadsheet. So, if a few mintues later, you add 'test' to completely unrelated cell, the =now() formula will auto recalculate. See File > Spreadsheet Settings > Calculation for the drop-down options (for instance, it'll let you auto-refresh without activity every hour).

The concern is that the act of removing the plug-in causing #NAME? errors is also an 'on change' type of behavior. If so, our pricing doesn't make sense (there would be tons of executions every time the sheet is touched) and our servers will get slammed.

Upon further research, it is also entirely possible that Gsheets is caching results and only refreshing cells when ??? action is taken. For example, see this stackshare post with a person having the opposite issue (he wants his cell to refresh, but google's cache is overriding his desire -- so he has to trick google):

https://stackoverflow.com/questions/17341399/refresh-data-retrieved-by-a-custom-function-in-google-sheet/37829242

Here's another explanation on the recalculate behavior:

https://stackoverflow.com/questions/9022984/script-to-summarise-data-not-updating/9023954#9023954

In this case, it appears that gsheets is caching data until a parameter changes. In fact, I just tested this with our hello world:

1. Create pipe with the hello world.
2. Refresh plug-in to show your pipe.
3. Enter formula in any cell. 'H-E-L-L-O-W-O-R-L-D" array will display.
4. Go back to pipe and change script to 'H-E-L-L-O-W-O-R-L-Z"
5. Go back to gsheets and copy and paste the formula into a new cell. 'H-E-L-L-O-W-O-R-L-Z" array will display.
6. Go to the cell where you entered your formula in Different results when running a pipe from the command bar "Save" button and the "Run Pipe" button #2 above. Click on cell and click ENTER key.
7. 'H-E-L-L-O-W-O-R-L-D" array will display.
   ...
   One workaround:
   ...
8. Go to the cell where you entered your formula in Different results when running a pipe from the command bar "Save" button and the "Run Pipe" button #2 above. Copy the formula. Delete the formula and click ENTER key. empty cell will display
9. Paste formula into cell and click enter. 'H-E-L-L-O-W-O-R-L-Z" array will display. (of course, this is merely re-entering the formula into a blank cell)
   ...
   Another workaround:
   ...
10. Add a reference cell parameter to the function: `=FLEX(name, A1), where A1 is a number like 1000. To refresh the formula, you simply increment the value to 1001 (note that you can't go back to 1000, or it will pull the original cached data back).
    ...
    Another workaround (in testing):
    ...
11. Add a reference cell parameter to the function: =FLEX(name, A1), where A1 is the formula TODAY()`. The formula should refresh every day and thereby automatically refresh your formula when you log in. Apparently now() and rand() are unavailable to be used in this context with custom formulas.

TODO:

Determine the limits of the behavior for auto-recalculation as well as when you lose your cache (upon deleting app -- but what about hard signing out (as opposed to current persistence) or losing cookies or after certain # of days, etc.)