# Non-deterministic parameters to custom function

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. – William Jackson Dec 19 '12 at 16:43
@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. – w0lf Dec 19 '12 at 17:03

## 1 Answer

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;
}
-