Status Update
No update yet.
Comments
as...@google.com
Thank you for your feedback Scott.
We are looking into this.
Best,
Anton
We are looking into this.
Best,
Anton
gr...@gmail.com
any ETA on this? I'd love to see it work properly
bu...@gmail.com
I have the same problem - is there a way to fix it?
sc...@gmail.com
As a workaround, find a way to store your remote data on a local sheet. In my case, I had to write some code to periodically update my local sheet to keep it current. It updates every time I open the spreadsheet, which is deemed sufficient for my purposes.
Define a named range that encompasses all of your imported data. Next, hide the worksheet, since you don't need to see it.
Finally, adjust your custom functions to take the named range you defined as an argument. You don't have to do anything with that rsnge; just referencing it is sufficient. Now, when you import fresh data, the data in the named range will change and any formulas referencing that range will be recalculated.
Example: instead of =totalhours(D2:D7) do =totalhours(D2:D7,somerange)
Define a named range that encompasses all of your imported data. Next, hide the worksheet, since you don't need to see it.
Finally, adjust your custom functions to take the named range you defined as an argument. You don't have to do anything with that rsnge; just referencing it is sufficient. Now, when you import fresh data, the data in the named range will change and any formulas referencing that range will be recalculated.
Example: instead of =totalhours(D2:D7) do =totalhours(D2:D7,somerange)
jo...@gmail.com
I have the same issue in Chrome on Ubuntu linux.
I'm simply trying to reseed a random number, so I can't use the workaround referencing changing data. I tried it with the hidden range including a RAND() function of its own, but it created a circular function-updating effect that normally took several seconds to resolve or would timeout.
I'm simply trying to reseed a random number, so I can't use the workaround referencing changing data. I tried it with the hidden range including a RAND() function of its own, but it created a circular function-updating effect that normally took several seconds to resolve or would timeout.
ko...@gmail.com
zi...@cloudware360.com.pe
None of these appear to be real issues. Custom ss functions are deterministic. They will only recalc if one lf their parameters change, thus the old value gets cached.. You are not supposed to rererence anything outside the In Params if you want to use it on a cell formula.
he...@gmail.com
I still think this is nog functioning properly: I created a simple custom function to count characters in a textstring.
I also added a parameter to determine whether this has to be case sensitive of not: I added this paramater in calling sheet and pass it to the function via a parameter.
The first time i change the parameter everything works out fine, but when the same value (0 or 1) is used again the function isn't re-evaluated anymore. Even with different a different parameter value.
I also added a parameter to determine whether this has to be case sensitive of not: I added this paramater in calling sheet and pass it to the function via a parameter.
The first time i change the parameter everything works out fine, but when the same value (0 or 1) is used again the function isn't re-evaluated anymore. Even with different a different parameter value.
zi...@cloudware360.com.pe
to the coment above: its still by design. Its not that the function gets re-evaluated when you change parameters. See what a deterministic function means. Given the same input, it will give you the same output. You cant have outside dependencies, it must only depend on its parameters. In your case, it depends on an outside string thats not in the parameters.
This issue should be closed. Its also a dup of
This issue should be closed. Its also a dup of
ar...@trbls.com
please fix this
ba...@gmail.com
This really limits the usability of custom functions to get at data, like the sheet name or a remote value, automatically. For example, naming the sheet after a person (or other query parameter), then using =QUERY(..., CONCAT(...), ...) is a very nice way to allow users to create custom query pages by copying a sheet and changing its name. Please allow us to mark a function declaration or call in such a way that reloading the sheet forces a recalculation. Thanks.
[Deleted User] <[Deleted User]>
I had a custom function that depended on values in a configuration sheet. Scott's workaround of passing in the named range of the configuration sheet work well for me.
sc...@menta.com
The inability to get a sheet name is kind of crazy. When you can't code what your eyes see right in front of you, that's rough. Users need intuitive solutions, and it'd be nice to be able to code so that sheet names -- renamed at users' discretion -- could be active variables in code. The names need to matter as DATA -- not just eye candy.
br...@gmail.com
I just ran into this issue.
I created a function to do a complex switch statement. But Sheets cached the results of the parameters (which had errored out), and it is ignoring the updates that I've made to the function. So myFunc( 150, true) retains a cache result of an error yet any variation on that like myFunc(149, true) returns a valid result even though it follows the same code path.
The sheet is shared with another person who is actively using the file, which may be why reopening the file persists the incorrect cached values.
I've implemented the work around that Scott.Se mentioned above with the Named Ranges, which has fixed the results.
I would expect the behavior to be that when a function is saved (even if there are only cosmetic edits to spacing) that all cells that reference the function are re-evaluated.
I created a function to do a complex switch statement. But Sheets cached the results of the parameters (which had errored out), and it is ignoring the updates that I've made to the function. So myFunc( 150, true) retains a cache result of an error yet any variation on that like myFunc(149, true) returns a valid result even though it follows the same code path.
The sheet is shared with another person who is actively using the file, which may be why reopening the file persists the incorrect cached values.
I've implemented the work around that Scott.Se mentioned above with the Named Ranges, which has fixed the results.
I would expect the behavior to be that when a function is saved (even if there are only cosmetic edits to spacing) that all cells that reference the function are re-evaluated.
ek...@google.com
la...@gmail.com
Any updates on this? I want the function to automatically call the API every 10 seconds
bi...@gmail.com
Hi,
Any updates on this? Or at least a workaround besides the one provided by Scott. Something like using flush in the code or some thing.
Thank you.
Any updates on this? Or at least a workaround besides the one provided by Scott. Something like using flush in the code or some thing.
Thank you.
ch...@flyweight.io
+1
jr...@redhat.com
Hi,
There is any update on this? This is impacting my code... I need updated data from Spreadsheet
There is any update on this? This is impacting my code... I need updated data from Spreadsheet
ma...@testijad.ee
jo...@orpinel.com
How come the GOOGLEFINANCE function does update periodically without changing any params? That's what I want from my custom functions, just expire the cache every minute or allow me to configure that somehow.
That said, an obvious workaround is to receive a dummy param in your fn and send it a random number from some cell that your script updates periodically.
pa...@gmail.com
I also have this issue, and I also do not have access to see the duplicated issue #36752433 for some reason. Any chance someone can open access to 36752433?
Description
1. Define a custom function that uses data passed in from the parameters, as well as data from some other source (such as global configuration data living on a different sheet).
2. Use that function somewhere.
3. Change the data from the other source.
What is the expected output? What do you see instead?
I expect to see the custom function recalculated. If I change the contents of a cell referenced by the function, it is recalculated and displays the correct results. If I change the cell *back* to its original value, the *old* calculation is used, and is not recalculated.
On which browser & OS? Firefox 7.0.1 on Linux (Ubuntu 11.10).
Please provide any additional information below.
You can find an example spreadsheet here:
There are two issues here. Ideally, there should be a way to force a recalculation, since it's not possible to automatically determine programmatically whether a recalculation is necessary. However, if that's deemed unworkable, then at the very least, a change in a cell referred to by a function should unconditionally be recalculated, even if there's already a cached return value available.