Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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?

share|improve this question
    
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
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. –  w0lf Dec 19 '12 at 17:03
add comment

1 Answer 1

up vote 1 down vote accepted

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

Ps. My first reply on Stack :)

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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