#GOOGLE SHEETS RANDOM PICKER UPDATE#
So you would have to settle for something that does not need to update that often, like =f(day(now())) I'm guessing this is because the return value of NOW() changes every millisecond, and it takes more than one millisecond to do the calculation. However, this function seems to never return. In order to produce a new random number each time, you would think it should work to pass the current timestamp as a parameter: =f(now()) See Issue 2573: Math.random() doesn't recalculate as a custom spreadsheet formula Since your custom function has no parameters, it will return the same result every time. Trying do to so results in the error message "This function is not allowed to reference a cell with NOW(), RAND(), or RANDBETWEEN()".įunctions in Google Apps Script are supposed to be deterministic. Currently, non-deterministic functions like NOW() is not allowed as arguments of custom functions. Note: This answer was only valid for the old Google Spreadsheets.
#GOOGLE SHEETS RANDOM PICKER CODE#
The following code will update the specified single cell reference every time time that an user edits the spreadsheet with a random number generated by a the Math.random(). Google Apps Scripts has two kind of events, simple and installable.
It's worth to bear in mind that custom functions are recalculated only when one of their arguments changes, by the other side their arguments should be deterministic, so using functions like NOW(), TODAY(), RAND() and RANDBETWEEN() will return an error even if they are nested in other functions spreadsheet functions or included indirectly through references. In cases where it's required to do some complex calculations that are not possible or efficient with built-in functions, one alternative is to use Google Apps Script. ExplanationĪs the OP mentioned, Google Sheets has a built-in function to return a random numbers between 0 and 1: RAND.
Instead of using a custom function use a built-in function when possible or use a function triggered by an event.
0 kommentar(er)
