Refresh data retrieved by a custom function in Google Sheet
Solution 1
Ok, it seems like my problem was that google behaves in a weird way - it doesn't re-run the script as long as the script parameters are similar, it uses cached results from the previous runs. Hence it doesn't re-connect to the API and doesn't re-fetch the price, it simply returns the previous script result that was cached.
See more info here(Add a star to these issues, if you're affected):
and Henrique G. Abreu's answer
My solution was to add another parameter to my script, which I don't even use. Now, when you call the function with a parameter that is different than previous calls, it will have to rerun the script because the result for these parameters will not be in the cache.
So whenever I call the function, for the extra parameter I pass "$A$1". I also created a menu item called refresh, and when I run it, it puts the current date and time in A1, hence all the calls to the script with $A$1 as second parameter will have to recalculate. Here's some code from my script:
function onOpen() {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var entries = [{
name : "Refresh",
functionName : "refreshLastUpdate"
}];
sheet.addMenu("Refresh", entries);
};
function refreshLastUpdate() {
SpreadsheetApp.getActiveSpreadsheet().getRange('A1').setValue(new Date().toTimeString());
}
function getPrice(itemId, datetime) {
var headers =
{
"method" : "get",
"contentType" : "application/json",
headers : {'Cache-Control' : 'max-age=0'}
};
var jsonResponse = UrlFetchApp.fetch("http://someURL?item_id=" + itemId, headers);
var jsonObj = eval( '(' + jsonResponse + ')' );
return jsonObj.Price;
SpreadsheetApp.flush();
}
And when I want to put the price of item with ID 5 in a cell, I use the following formula:
=getPrice(5, $A$1)
When I want to refresh the prices, I simply click the "Refresh" -> "Refresh" menu item.
Remember that you need to reload the spreadsheet after you change the onOpen()
script.
Solution 2
You're missing the fastidious caching bug feature. It works this way:
Google considers that all your custom functions depend only on their parameters values directly to return their result (you can optionally depend on other static data).
Given this prerequisite they can evaluate your functions only when a parameter changes. e.g.
Let's suppose we have the text "10" on cell B1, then on some other cell we type =myFunction(B1)
myFunction will be evaluated and its result retrieved. Then if you change cell B1 value to "35", custom will be re-evaluated as expected and the new result retrieved normally. Now, if you change cell B1 again to the original "10", there's no re-evaluation, the original result is retrieved immediately from cache.
So, when you use the sheet name as a parameter to fetch it dynamically and return the result, you're breaking the caching rule.
Unfortunately, you can't have custom functions without this amazing feature. So you'll have to either change it to receive the values directly, instead of the sheet name, or do not use a custom function. For example, you could have a parameter on your script telling where the summaries should go and have an onEdit
update them whenever a total changes.
Solution 3
What I did was similar to tbkn23. This method doesn't require any user action except making a change.
The function I want to re-evaluate has an extra unused parameter, $A$1. So the function call is
=myFunction(firstParam, $A$1)
But in the code the function signature is
function myFunction(firstParam)
Instead of having a Refresh function I've used the onEdit(e) function like this
function onEdit(e)
{
SpreadsheetApp.getActiveSheet().getRange('A1').setValue(Math.random());
}
This function is triggered whenever any cell in the spreadsheet is edited. So now you edit a cell, a random number is placed in A1, this refreshes the parameter list as tbkn23 suggested, causing the custom function to be re-evaluated.
Solution 4
There are settings where you can make NOW()
update automatically:
Solution 5
If your custom function is inside a specific column, simply order your spreadsheet by that column.
The ordering action forces a refresh of the data, which invokes your custom function for all rows of that column at once.
Related videos on Youtube
tbkn23
Updated on July 08, 2022Comments
-
tbkn23 almost 2 years
I've written a custom Google Apps Script that will receive an
id
and fetch information from a web service (a price).I use this script in a spreadsheet, and it works just fine. My problem is that these prices change, and my spreadsheet doesn't get updated.
How can I force it to re-run the script and update the cells (without manually going over each cell)?
-
Timothy Johns over 6 yearsNot missing anything; it might help to go upvote this feature request in Google Issue Tracker: issuetracker.google.com/issues/36763858
-
Timothy Johns over 6 yearsFor those encountering similar (defined and logical, but sometimes unfortunate) behavior, it might help to go upvote this feature request in Google Issue Tracker: issuetracker.google.com/issues/36763858.
-
Aerials over 4 yearsHere is a simple answer I did.
-
-
Whelkaholism over 12 yearsHmmm, that is annoying, but thankyou for the answer! I have got a nasty workaround by adding a dummy parameter to the function; if I then pass a cell reference to that I can force the data to refresh by just incrementing a number in that cell. Don't suppose there is anyway I can create a refresh button that will do that on a click, is there? That would be fine, it doesn't need to be automatic really, just easy and obvious.
-
Henrique G. Abreu over 12 yearsWell, you can create a button, actually a drawing and assign an script function to it, that will increment you dummy parameter cell that will force the update.
-
tbkn23 about 11 yearsThat would be just great, except it doesn't work... Reloading the page doesn't refresh the values. Moreover, deleting the cell and re-entering the same function call, still keeps the old value. If I call the same function exactly from another cell, it will show the new value, but not in the old cell.
-
Advanced over 10 yearswhy not using now() as an additional parameter?
-
tbkn23 over 10 yearsYou see, just like my function will not be re-evaluated because it's parameters haven't changed (ie the values of the cells), now() will not be re-evaluated either since it has no parameters, hence it's return value will not change and so my function's parameters will not change. Also, using now() would cause my function to re-evaluate all the time which is a bit heavy considering that it generates several HTTP calls...
-
Arsen Ibragimov over 8 yearsit doesn't work anymore. You'll see the error "This function is not allowed to reference a cell with NOW(), RAND(), or RANDBETWEEN()" if you try to do it in this way
-
Arsen Ibragimov over 8 yearsit doesn't work anymore. You'll see the error "This function is not allowed to reference a cell with NOW(), RAND(), or RANDBETWEEN()" if you try to do it in this way
-
Jon over 8 yearsWorks great. Downside is, undo (ctrl+z) history is messed up.
-
Enissay about 8 yearsNice trick with an annoying downside as Jon pointed out... I hope someone will improve it :-)
-
Rubén over 7 years
NOW()
is a built-in function, not a custom function. -
Thaina Yu over 7 years@Rubén The point is you could include NOW() function in any custom function you want to have it update
-
Rubén over 7 yearsAt this time custom functions arguments should be deterministic, in other words, they don't allo NOW() as argument. See developers.google.com/apps-script/guides/sheets/functions
-
Shawn Hoover about 7 yearsGood find. I had this problem while using named ranges as input. Using your answer, I figured out it's often good enough to pass a dummy sum over the range of inputs, as in "sum(B:D)", where rows B-D are in the range looked up by the custom function. Changing any cell will trigger the sum to change and the custom function to refresh. By the way, it seems the custom function does not even have to declare the ignored parameter.
-
Jonas D. about 7 years@Rubén it is very similar but with a good nuisance; instead of using the active sheet it uses a defined sheet name, improving your history
-
GrayedFox over 6 yearsA shame that this is still the best solution I found to this problem. Rather than Google just allowing us to disable cache for particular function calls, we arbitrarily increase what is being stored in the cache just to make sure we don't get a cached value... Still, thanks for post,
-
Woody Payne about 6 yearsSmall and pedantic caveat with this answer; in the incredibly unlikely event Math.random returns the same number, on that occasion it will not update, as that particular number has already been cached.
-
Stefano Giacone about 6 yearsIt throws an error if you try to use NOW() inside a custom function
-
Aerials over 4 yearsTry using a parameter for your custom function like this example
-
StevenWhite about 4 yearsLooks like
addMenu
is deprecated: developers.google.com/apps-script/guides/menus -
Sanfer over 3 yearsonly works if the sheet isn't already ordered
-
Ismail over 3 yearsx = 20+ // minutes
-
bobpaul over 3 yearsOK. So to use this, I'd make an
onChange()
function like you have, substitutingSHEETNAMES
with the name of my custom function that needs to be re-run and replacingGRID
with the change type I want the trigger to run on (exEDIT
). ... But is the.replaceAllWith()
call correct? I think you'd need a back-reference to the group in original query like'=SHEETNAMES(\1' + (Math.floor.....)
. And do you need to register theonChange(e)
function, or does google do that automatically due to the function name/signature? -
TheMaster over 3 years@bobpaul You need to register the trigger manually. See the
@see
link in the code. Thesheetnames
custom function doesn't take any parameters that change the output(e
is unused). So I didn't include a backreference. But if you have any non-optional parameters, you do need to change the regex as needed. -
Sandwich over 2 yearsDoesn't work at scale and in highly dynamic situations unfortunately.