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) );
};
///////////////////////////////////////
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.
In a column, use =RAND() formula to generate a set of random numbers between 0 and 1.
Why a script at all? This simple formula in I6 should do it:
=TRANSPOSE(SORT(SORTN(SEQUENCE(200),5,0,RANDARRAY(200),1)))
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)
})
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With