Google Sheets – Non-Deterministic Parameters to Custom Function

google sheetsgoogle-apps-scriptgoogle-sheets-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?

Best 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;
}
Related Topic