I have a problem with Google Spreadsheets: I create a custom function using the Script Editor and it only runs OK if the parameters I pass are known (constant) values.

If the parameters are non-deterministic functions, I get the Thinking... text and the evaluation is never completed.

By non-deterministic functions, I mean functions whose result may differ every time it's called (for example: Rand(), Now()).

To illustrate this, I have two examples:

1. Function to add 1 to a number

Here's the code:

function plusOne(i) {
  return i + 1;

As expected, if I enter =plusOne(5) in a cell, I get the result 6.

However, if I enter =plusOne(rand()) I expect to get a random result between 1 and 2 (as the documentation states that rand() "Returns a random number between 0 and 1")

The formula =plusOne(rand()) never finishes evaluation and the cell remains in the "Thinking..." state.

2. Function to get the Month from a Date

function gimmeMonth(date) {
  return date.getMonth() + 1;

Again, if I call this with a constant parameter it works fine. For example, if I enter 2012-08-31 in cell A1, the formula =gimmeMonth(A1) returns the expected result 8.

However, =gimmeMonth(now()) never gets evaluated, although the builtin month() function works fine in this case (=month(now()) returns the expected result).

Why I need this

I need to be able to add a non-deterministic parameter in my custom function calls in order to avoid cache.

Has anyone else had this problem? Anyone managed to solve it?

  • Interesting! If I put =rand() in A1 and =plusOne(A1) in A2, the number in A1 changes about every 5 seconds. I have no idea what is going on. Dec 19, 2012 at 16:43
  • 1
    @WilliamJackson I noticed that every time a formula is evaluated, all non-deterministic functions are re-evaluated as well. Perhaps something fails and it retries every 5 seconds, triggering the rand() to yield a new value. Anyway, it's really weird. Dec 19, 2012 at 17:03

1 Answer 1


I've made some research and it seems like an old problem, even from old MS Excel versions.

I think the best/easiest way is to create your own randomization function. This way random value won't be regenerated. You use it the same way as before =plusOne(myRandom())

function plusOne(i) {
  return i + 1;

function myRandom() {
  # Some function
  return Math.random();

You could also shorten it =myRandom()

function myRandom() {
  # Some function
  return Math.random() + 1;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.