Assigned
Status Update
Comments
sc...@bryanp.com <sc...@bryanp.com> #2
Have you tried changing a parameter in the custom function with a time-based trigger? This example refreshes one with a manual menu click - http://stackoverflow.com/a/17347290/1480465
[Deleted User] <[Deleted User]> #3
That's an interesting solution for refreshing a single custom function. There's another method I've used for this use-case: you delete the function, flush the cell, then put the function back in.
Both these methods don't help in the situation where you'd like to recalculate a *whole* spreadsheet or sheet. And they're a bit hacky :). Specifically, to extrapolate either to a whole spreadsheet or sheet you'd need to:
1) manually find all the custom functions in the spreadsheet or sheet
2) use one of these methods to recalculate each function
3) hope things work
I include #3 b/c depending on the algorithm you use to recalculate each function with one of these methods, you might've just made thousands of unnecessary repeat calculations (which may be API calls).
It'd be far nicer to do something like Spreadsheet.recalculate() or Sheet.recalculate() and let Google Sheets do their magic for recalculations. Internally, Sheets must have methods to do this already.
At a high level from a user perspective the problem is pretty simple: in Excel to force recalculate a whole workbook (including custom functions) you do Control + Alt + Shift + F9... in Sheets there's no way.
Both these methods don't help in the situation where you'd like to recalculate a *whole* spreadsheet or sheet. And they're a bit hacky :). Specifically, to extrapolate either to a whole spreadsheet or sheet you'd need to:
1) manually find all the custom functions in the spreadsheet or sheet
2) use one of these methods to recalculate each function
3) hope things work
I include #3 b/c depending on the algorithm you use to recalculate each function with one of these methods, you might've just made thousands of unnecessary repeat calculations (which may be API calls).
It'd be far nicer to do something like Spreadsheet.recalculate() or Sheet.recalculate() and let Google Sheets do their magic for recalculations. Internally, Sheets must have methods to do this already.
At a high level from a user perspective the problem is pretty simple: in Excel to force recalculate a whole workbook (including custom functions) you do Control + Alt + Shift + F9... in Sheets there's no way.
ry...@google.com <ry...@google.com> #4
Thank you for the request. It has been filed internally.
tr...@gmail.com <tr...@gmail.com> #5
A corollary, please. Fix the now() function to once again work with the googlefinance() function. To whit, the expression =if(hour(now())>15,now(),now()-1) to calculate the date based upon the time of day fails in the expression =GoogleFinance(B3,B4,datevalue(B5),datevalue(B6),B7) where B6 is the end date. Before the market closes, B6 should evaluate to the the previous dage. After the market closes, it should return the current date. Thank you.
[Deleted User] <[Deleted User]> #6
I have the reverse problem with my add-on which implements custom functions that are refreshed for no reason. They only use data directly referenced in their parameters but are refreshed on opening the spreadsheet when nothing was changed causing havoc in all the other spreadsheets that reference their results...
The whole process often results in some data ending up unavailable because the user reached their API query limit due to all the unecessary API calls...
All of this to say that to fix these (non or excessive) refresh issues it should be better to add a special @parameter above the custom function definition to tell GAS how it should handle the function (refresh on opening, refresh only when the referenced data has changed, refresh on a ticker, etc...).
Because as the OP said for now custom function refreshing behaviour is inconsistent and unpredicatble making spreadsheet using GAS unreliable.
The whole process often results in some data ending up unavailable because the user reached their API query limit due to all the unecessary API calls...
All of this to say that to fix these (non or excessive) refresh issues it should be better to add a special @parameter above the custom function definition to tell GAS how it should handle the function (refresh on opening, refresh only when the referenced data has changed, refresh on a ticker, etc...).
Because as the OP said for now custom function refreshing behaviour is inconsistent and unpredicatble making spreadsheet using GAS unreliable.
ry...@google.com <ry...@google.com>
ti...@google.com <ti...@google.com> #7
kt...@factset.com <kt...@factset.com> #8
Has this been prioritized? We're trying to meet feature-parity with Excel, and the Google Sheets custom function behavior is very limiting. It would be great if we didn't have to hack around the problem.
je...@gmail.com <je...@gmail.com> #9
A kludgy solution is to add and remove a row to each sheet in your google app script enabled spreadsheet. This will fire a refresh event for every cell on the sheet! Use a time interval tigger (https://developers.google.com/apps-script/guides/triggers ) to fit your schedule needs.
This is a hack. It is sub-optimal. I would love a native way to tell custom functions when to update.
Code for the hack:
function refreshHack(e) {
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
for (var i=0 ; i<sheets.length ; i++) {
sheets[i].insertRowBefore(1)
sheets[i].deleteRow(1)
}
}
This is a hack. It is sub-optimal. I would love a native way to tell custom functions when to update.
Code for the hack:
function refreshHack(e) {
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
for (var i=0 ; i<sheets.length ; i++) {
sheets[i].insertRowBefore(1)
sheets[i].deleteRow(1)
}
}
al...@gmail.com <al...@gmail.com> #10
Yes, I upvote this feature request. It limits using custom functions and makes user resort to hacks that may break, especially when the sheet is used by a user not aware of the structure of the hack.
xd...@gmail.com <xd...@gmail.com> #11
I could see SpreadsheetApp.flush()
also triggering a recalculation. That would be nice.
ma...@gmail.com <ma...@gmail.com> #12
Hi. Any update on a native refresh function?
cr...@gmail.com <cr...@gmail.com> #13
Please don't make SpreadsheetApp.flush()
recalculate values. It's a terrible idea to introduce new functionality to a function like that. It would break or slow down multiple scripts that I have made. It'd be great to have a new SpreadsheetApp.recalculate()
, but please don't change have existing functions work.
dd...@gmail.com <dd...@gmail.com> #14
Is there a way to recalculate a specific cell, instead of the entire sheet? Even via a UI affordance would be OK.
The use case is executing in parallel numerous custom functions that make API calls. Some of them will fail with "Exceeded maximum execution time". The user wants to recalculate only those cells.
jo...@telenetwork.com <jo...@telenetwork.com> #15
I have experience with complex custom functions failing. The way I give them a kickstart is to edit, make the formula a label by adding a single quote in the front, save, then edit again, remove the single quote and the custom function seems to work. One can make this a macro to streamline, although I have not tried.
Description
An increasing number of custom functions aren't just running a script with basic logic, but they're running a script that makes an API call somewhere else. Often times these API calls bring data into a spreadsheet from external systems. For instance, you can imagine a custom function =RECENTUBERS() that returns a list of your most recent Uber rides. Or =STOCKPRICE("GOOG") that returns the current price of a ticker.
The issue is that spreadsheet recalculation for custom functions is unpredictable. Imagine a spreadsheet that only has a single function in A1: =STOCKPRICE("GOOG"). When would that spreadsheet recalculate? It's hard to tell. But a user might want it to recalculate every minute or every hour.
I highly recommend implementing a standard way to force recalculation a whole spreadsheet and a single sheet. This may be as simple as Spreadsheet.recalculate() and Sheet.recalculate() methods. This way, developers can build functionality for keeping spreadsheets up-to-date with external data services, rather than just keep up to date with state changes in the spreadsheet itself. In the case of the stock price example, state will never change in the spreadsheet but data outside of the spreadsheet is changing constantly.
Just like Google Sheets acknowledged that people want spreadsheets in the cloud, a change like this would acknowledge that people have data in the cloud, not just in spreadsheets :). I'd love to see it!
To subsequent readers: If you are also interested in this requested
feature, please
click the star next to the issue number.