Function which replaces itself with the output of its contents

Question

In Google Sheets, there are multiple methods to convert a cell formula into its output value, but I have not found a function which performs this.

For example, I'd like to run:

=FORMULA2VALUE(RAND())

which would produce a random number and immediately make that number not only the displayed contents of the cell, but the true contents of the cell as well, preventing the cell from immediately overwriting itself with a new random number on the next trigger of RAND() (which is the next change to the sheet).

Does such a function exist?

score 1 · Answer 1 · answered Aug 03 '22 at 22:37

1

this is possible only with a script:

function RI(min, max) {
  return Math.floor(Math.random() * (max - min + 1)) + min;
}

example of a random number between 1 & 20:

=RI(1; 20)

if for some reason you can't use scripts in your project then follow the white fox into the forest of magic

answered Aug 03 '22 at 22:37

player0

124,011
12
67
124

1

Me Seraching for the help for `RI` function in Google Docs Editors Help O.O – Osm Aug 03 '22 at 22:51
If I macro a `clearContent()` and then repopulate the same cell with the same function, I get the same output each time. Do you know why that is? – kando Aug 04 '22 at 15:18
@kando no idea, sorry. I am not good with scripts, apologies. – player0 Aug 04 '22 at 18:34

score 0 · Answer 2 · answered Oct 16 '22 at 02:58

Google Sheets hasn't built-in function as the one that you are looking for. Google Apps Script might be used to create custom functions but these kind of scripts can't overwrite the cell that contains the formula

Using Google Sheets together with Google Apps Script you might create a Google Sheets macro or a regular script, i.e. to calculate a random number and insert it over the selected cell.

Resources

Function which replaces itself with the output of its contents

2 Answers2