# Storing number produced by RAND function

In cell B2 I have the formula:

``````=INT(RAND()*100+1)
``````

Using a script, is there any way I can copy the current results of this RAND function into another cell on the sheet? I want to copy the absolute value, not just mirror the current value.

All attempts I've come up with cause the function to generate a new random number, store that value, then generate yet another random value in cell B2 due to the page being updated.

Ultimately I'd like to click a button that runs a script to store the current value of B2 into another cell.

• lol, no. Sorry if I wasn't specific enough. I want to store the current value of B2 in an absolute way so that it doesn't change. Ultimately I'm wanting to create a history of random values generated, but that will come later if I can get this first part figured out. Oct 20, 2017 at 17:26

Using a script, is there any way I can copy the current results of this RAND function into another cell on the sheet?

No, there isn't.

The following script should add the value of B2 to C2, but instead adds a new calculated value.

``````function freeze1(){
var v = s.getRange("B2").getValue();
s.getRange("C2").setValue(v);
}
``````

The following script should add the value of B2 to the active range, but it adds a new calculated value

``````function freeze2(){
var v = s.getRange("B2").getValue();
t.setValue(v);
}
``````

On both cases, a third value is calculated "after" the script runs.

An alternative could be the use of a script to calculate the random number instead of a built-in RANDOM() spreadsheet function. The JavaScript command is Math.random().

• The value that the script stores is not the current value of B2. When the script runs, it's generating a new random value and storing that number, not the current value of B2. Oct 20, 2017 at 18:36
• Let me further clarify. Lets say the current value of B2 is 10. When you click the button to run the script, the value stored in C2 is not 10, but some other random number. Then B2 is randomly generated again due to the spreadsheet update (this part is fine). Oct 20, 2017 at 18:41
• Not the answer I was hoping for, but thank you for your time! Oct 20, 2017 at 19:17

Ruben's answer is technically correct. However, I found a workaround that does what I need. Since I couldn't get the current value of the cell that was previously produced by the RAND function, I just masked it using the random number generated when the custom function below was ran and stored it internally as var V (I moved the RAND function to cell K1). This way that particular value doesn't change while I run the rest of the function.

It also shows a "history" of previous numbers chosen.

``````function randomize(){
var end = sheet.getLastRow() + 1;
var v = sheet.getRange("K1").getValue();
sheet.getRange("B2").setValue(v);
for (var i = 2; i < end; i) {
var value = sheet.getRange(i, 3).getValue();
if (value == "") {
sheet.getRange(i, 3).setValue(v);
i = end;
} else {i = i + 1}
}
}
``````
• The order of answer changes so it's better to include a link rather than making position relative references (above, below, the other, ...) :)
– Rubén
Oct 26, 2017 at 0:26
• @Rubén Thanks for the advice. This was my first post to stackexchange and I'm learning the ropes. Oct 26, 2017 at 15:26