Storing number produced by RAND function

Question

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.

Answer 1

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 s = SpreadsheetApp.getActiveSheet();
  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 s = SpreadsheetApp.getActiveSheet();
  var v = s.getRange("B2").getValue();
  var t = SpreadsheetApp.getActiveRange();
  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().

Answer 2

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 sheet = SpreadsheetApp.getActiveSheet();
  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}
  }
}