Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Google sheets mutually exclusive numerical order random number generator

Bit of a mouthful that one... I'm currently using the script below to generate a random number between 1 and 200 in cells I6:M6. For my intended purpose this script has two problems (neither of which are major but I would like some "polish".

Problem 1) not-mutually exclusive. This is the biggest issue as each of the five numbers MUST be unique. It isn't particularly likely that they will be repeated, but it is somewhat annoying when it does happen.

Problem 2) I have absolutely no idea how or if its even possible to have these random numbers generated and then placed into I6:M6 in numerical order, I6 being the smallest.

I'm a complete amateur and JavaScript (and any other code) and I've only managed to cobble this together from info I've found elsewhere, but I have a very vague understanding of how it works.

///////////////////////////////////////

function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [ {name:"Roll Dice",functionName:"rollDice"} ];
  sheet.addMenu("Script", entries);
};

function rollDice() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("sheet1 (New)");
    var cell = sheet.getRange("I6");
  cell.setValue( Math.floor((Math.random()*200)+1) );
    var cell = sheet.getRange("J6");
  cell.setValue( Math.floor((Math.random()*200)+1) );
    var cell = sheet.getRange("K6");
  cell.setValue( Math.floor((Math.random()*200)+1) );
    var cell = sheet.getRange("L6");
  cell.setValue( Math.floor((Math.random()*200)+1) );
    var cell = sheet.getRange("M6");
  cell.setValue( Math.floor((Math.random()*200)+1) );

};

///////////////////////////////////////
like image 760
Luke Freeman Avatar asked Jan 18 '20 13:01

Luke Freeman


People also ask

Can you make a random number generator in Google Sheets?

Click on a cell in Google Sheets where you want to insert a random number. Type in the function: =RANDBETWEEN(1, 10) Press the Return key. This should display any random integer between 1 and 10 in the same cell.

How do you generate a unique random number?

In a column, use =RAND() formula to generate a set of random numbers between 0 and 1.


2 Answers

Why a script at all? This simple formula in I6 should do it:

=TRANSPOSE(SORT(SORTN(SEQUENCE(200),5,0,RANDARRAY(200),1)))
like image 149
MattKing Avatar answered Oct 12 '22 12:10

MattKing


I added a sort function into the mainLoop function, should sort numerically now.

function myFunction() {
  mainLoop()
}

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("sheet1");

function generateNumber(){
return Math.floor((Math.random()*200)+1)
}

function setValue(_val, _address){
   var cell =  sheet.getRange(_address)
   cell.setValue(_val)
}

function generate(){
  var array = []
  while(array.length<5){
    var rand = generateNumber()
    var isUnique = array.indexOf(rand) === -1
    if(isUnique){
      array.push(generateNumber())
    }

  }
  return array
}

function mainLoop(){
    var array = generate()
      array.sort(function(a, b) {
  return a - b;
});
    var addresses = ['I6','J6','K6','L6','M6']
    addresses.forEach(function (_address,_index){
        setValue(array[_index],_address)
    })
}
like image 2
Raphael Castro Avatar answered Oct 12 '22 12:10

Raphael Castro