-1

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. – stirfry213 Oct 20 '17 at 17:26
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().

  • Unfortunately this isn't working. See the link below. docs.google.com/spreadsheets/d/… – stirfry213 Oct 20 '17 at 18:30
  • 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. – stirfry213 Oct 20 '17 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). – stirfry213 Oct 20 '17 at 18:41
  • 1
    Not the answer I was hoping for, but thank you for your time! – stirfry213 Oct 20 '17 at 19:17
1

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}
  }
}
  • 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 '17 at 0:26
  • @Rubén Thanks for the advice. This was my first post to stackexchange and I'm learning the ropes. – stirfry213 Oct 26 '17 at 15:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.