Note: This answer was only valid for the old Google Spreadsheets. Currently, non-deterministic functions like
NOW() is not allowed as arguments of custom functions. Trying do to so results in the error message "This function is not allowed to reference a cell with
Functions in Google Apps Script are supposed to be deterministic. Since your custom function has no parameters, it will return the same result every time.
See Issue 2573: Math.random() doesn't recalculate as a custom spreadsheet formula
In order to produce a new random number each time, you would think it should work to pass the current timestamp as a parameter:
However, this function seems to never return. 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.
So you would have to settle for something that does not need to update that often, like
This would return a new random value every day of the month.
Note that if you use this formula in more than one cell, it will return the same random value for all cells. To avoid that, you could pass the current cell coordinates as parameters, too:
=f(day(now()), 2, 4)
I have set up an example spreadsheet, feel free to take a look.
Instead of introducting new parameters, you might have luck trying to change the setting in File → Spreadsheet settings → Recalculation to On change and every minute.
At least this should work for
RAND(), see the documentation.
Currently, this gives me an error message, so I'm not able to see if this works.