Google Spreadsheet generate random numbers with Math.random()

Question

I'm trying to write a script function, which calls the Javascript Math.random() to generate a random number between [0, 1), like this:

function f() {
  return Math.random();
}

and I call it from a cell:

However, each time I refresh the spreadsheet (using F5 or Ctrl+R), it returns the same number.

The reason I'm not using the spreadsheet RAND() function is I want to do some complicated computation in f(). I have tried to pass RAND() as a parameter from spreadsheet and return the parameter, but in that case, this function never returns:

Is there a way I can generate a refreshable random number from inside a function?

Answer 1

Short answer

Instead of using a custom function use a built-in function when possible or use a function triggered by an event.

Explanation

As the OP mentioned, Google Sheets has a built-in function to return a random numbers between 0 and 1: RAND.

In cases where it's required to do some complex calculations that are not possible or efficient with built-in functions, one alternative is to use Google Apps Script. It's worth to bear in mind that custom functions are recalculated only when one of their arguments changes, by the other side their arguments should be deterministic, so using functions like NOW(), TODAY(), RAND() and RANDBETWEEN() will return an error even if they are nested in other functions spreadsheet functions or included indirectly through references.

Google Apps Scripts has two kind of events, simple and installable. See https://developers.google.com/apps-script/guides/triggers/ for details.

Example

The following code will update the specified single cell reference every time time that an user edits the spreadsheet with a random number generated by a the Math.random().

var reference = 'A1';

function onEdit(e) {
  SpreadsheetApp
    .getActiveSheet()
    .getRange(reference)
    .setValue(Math.random());
}

References

• Custom Functions in Google Sheets - Google Developers
• Issue 246: Script function does not recalculate - google-apps-script-issues
• Refresh data retrieved by a custom function in google spreadsheet - Stack Overflow

Answer 2

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 NOW(), RAND(), or RANDBETWEEN()".

---

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:

 =f(now())

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

=f(day(now()))

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.

Answer 3

Generating random number Add-on may help to generate random numbers in google spreed sheet

For detailed information click here