CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsformulas
Bill
Bill

Reputation: 1

Random string generator randomizing too often

I'm looking for input on a small random string generator project.

I'm trying to create a random string generator that puts three words together, where I can then save strings that I like to another sheet.

I'm using a basic script to assign a RANDBETWEEN formula to 3 specific cells, which works great to generate the strings, but it keeps recalculating with each additional step in the script it seems. I'd like to run one function then if I like the string I would run another function to store the string. However, when I try that it has already recalculated the RANDBETWEEN formulas and it saves something completely different.

I have an example sheet here:
https://docs.google.com/spreadsheets/d/1TWziyjjLQJJApkHCqrLzNGMFU0sf-vNEOOSatuhHURo/edit?usp=sharing

And here is the code I'm using for the "Go" and "Save" buttons, respectively:

function generateString() { 

  var ss = SpreadsheetApp.openById('1b9rP39sgZDOZqu7AmZhOxX9J8CMukmUw7NPY3Qzuq78'); 
  var sheet = ss.getSheetByName('Randomizer'); 
  var cell = sheet.getRange('D4');
  var cell2 = sheet.getRange('E4');
  var cell3 = sheet.getRange('F4');
  cell.setValue('=INDEX(A:A,RANDBETWEEN(1,counta(A:A)))'); 
  cell2.setValue('=INDEX(A:A,RANDBETWEEN(1,counta(A:A)))');
  cell3.setValue('=INDEX(A:A,RANDBETWEEN(1,counta(A:A)))');

  var cell4 = sheet.getRange('P4');
  cell4.copyTo (sheet.getRange ('P5'), {contentsOnly: true});  //an attempt to paste values to record the random string

}

function saveString() {

  var ss = SpreadsheetApp.openById('1b9rP39sgZDOZqu7AmZhOxX9J8CMukmUw7NPY3Qzuq78'); 
  var sheet = ss.getSheetByName('Randomizer'); //replace with source Sheet tab name
  var range = sheet.getRange('P4'); //assign the range you want to copy
  var data = range.getValues();

  var tss = SpreadsheetApp.openById('1b9rP39sgZDOZqu7AmZhOxX9J8CMukmUw7NPY3Qzuq78'); 
  var tsheet = tss.getSheetByName('Saved Strings'); //replace with destination Sheet tab name
  tsheet.getRange(tsheet.getLastRow()+1, 1, 1, 1).setValues(data);

}

Please let me know if anyone has some ideas on how to make this work properly. Thanks!

Upvotes: 0

Views: 1026

Answers (1)

Wicket
Wicket

Reputation: 38254

RANDBETWEEN the same as RAND are volatile functions. That means that their results changes every time that spreadsheet is recalculated.

If you need to keep the randomized to result be "freezed" for a while instead of this functions one alternative to consider is the use of a custom function as they are recalculated only when at least one of its arguments changes.

Related

Refresh data retrieved by a custom function in Google Sheet

Upvotes: 2

Related Questions