CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
MJM
MJM

Reputation: 57

Non Repeated Random value from Range

Need to amend my script so that the random values generated from my range won't repeat when the function "myFill" is triggered. The range currently has 32 values - but only 5 non repeated values are required each time when triggered. Those values can be included again next time the function is triggered.

Link attached.

As an overview, the Script below is used in conjuction with these formula on the spreadsheet 

   Cell B2 =myFill(1,5,"H2:H33",J18) 
   Cell B3 =iferror(ArrayFormula(vlookup(B2,$H$2:$I$33,2,0)))

Appscript:

    function tst(){
    var rows = 1
    var cols = 5
    var range = "H2:H33"
    myFill(rows, cols, range)
    }

   function myFill(rows, columns, range) {
   var ss= SpreadsheetApp.getActiveSpreadsheet();
   var sheet = ss.getActiveSheet();
   Logger.log(rows)
   Logger.log(columns)
   Logger.log(range)
   var rng = sheet.getRange(range).getValues()
   var output1 = [], l = rng.length;
   for(var i=0; i<rows; i++) {
   var output2 = [];
   for(var j=0; j<columns; j++) {
   output2.push(rng[Math.floor(Math.random() * l)][0]);
   }
   output1.push(output2);
    }   
   return output1;  
   }

Random Value Test

Upvotes: 2

Views: 1246

Answers (3)

Max Makhrov
Max Makhrov

Reputation: 18707

I wondered if it's possible to do it with formulas. The resulting formula is:

=ArrayFormula(VLOOKUP(QUERY(UNIQUE(RANDBETWEEN(ROW(INDIRECT("A1:A"&COUNTA(A:A)*10))^0,COUNTA(A:A))),"limit 5"),{ROW(INDIRECT("A1:A"&COUNTA(A:A))),FILTER(A:A,A:A<>"")},2,0))

Please look at this Sample file with explanations on how the formula works.

Upvotes: 1

Jack Brown
Jack Brown

Reputation: 5892

This code will give you unique set of letters i.e. no repeating set for each run. However, in my brief executions of the script never once encountered a repeat random number anyway! 

function myFill(rows, columns, range) {
  var ss= SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  Logger.log(rows)
  Logger.log(columns)
  Logger.log(range)
  var rng = sheet.getRange(range).getValues();
  var output1 = [], l = rng.length;
  for(var i=0; i<rows; i++) {
    var output2 = [];
    for(var j=0; j<columns; j++) {
      var isUnique = false
      do {
      var index = Math.floor(Math.random() * l) 
      if(rng[index][0] !== 0){  //Check if the value has been already been used. 
        output2.push(rng[index][0]);
        rng[index][0] = 0; // Set a used value to zero
        isUnique = true; 
      }
      } while (!isUnique) 
    }
    output1.push(output2);
  }   
  return output1;  
}

The way I achieve to get unique letters is by setting the value of the rng array to 0 if that particular value at that index has already been used. Then check index each time to check if it is zero if it not then use that value else generate a new random index.

Upvotes: 0

Cooper
Cooper

Reputation: 64042

Well I'm not sure what your trying to accomplish because your not doing anything with the return from myFills but this is a little more likely to work for you. Your were using "rng" as a range but getValues returns an array (2D). BTW random does not mean non repeating.

function tst()
{
    var rows = 1;
    var cols = 5;
    var range = "H2:H33";
    myFill(rows, cols, range);
}

function myFill(rows, columns, range) 
{
   var rows = (typeof(rows) !== 'undefined')? rows : null; 
   var columns = (typeof(columns) !== 'undefined')? columns : null; 
   var range = (typeof(range) !== 'undefined')? range : null; 
   var sheet = SpreadsheetApp.getActiveSheet();
   var rngA = sheet.getRange(range).getValues();
   var outputA = []; 
   var l = rngA.length;
   for(var i=0; i<rows; i++) 
   {
     outputA[i] = [];
     for(var j=0; j<columns; j++) 
     {
       outputA[i][j]=Math.floor(Math.random() * l);
     }
   }   
   return outputA;  
}

Upvotes: 0

Related Questions