Random Selection in a Column

user_14566169 0
Viewed 89

I've modified a code to select a data randomly from A Column cells. The problem I'm facing is, I'm only able to get the data from A1. and rest of the data are not showing. Where did I go wrong?

function getData () {
var SS = SpreadsheetApp.getActiveSheet()
var Avals = SS.getRange("A1:A").getValues();
var numberOfValues = Avals.filter(String).length;
var data = SS.getRange(1,1,numberOfValues).getValues();

for(var i = 0; i < data.length; i++) 
  { 
    var j = Math.floor(Math.random()*(data[i].length)); //method of randomization
    var element = data[i][j];


return element;

logger.log(element);

}
}
google-apps-script google-sheets javascript
1 Answers

Explanation:

  • getValues() returns a 2D array. In your case you have a single column, therefore data has the format of: [[a1],[a2],[a3],..]]. data[i] will give you a single element of this array. For example data[0] is [a1] and as you can understand [a1].length is equal to 1 regardles of the chosen index. In other words, data[i].length is 1 for every i and therefore you are always getting the first element.

  • It is also worth mentioning that you have a return statement inside the for loop and therefore i gets only the first value 0 since the function is terminated when reaches the return statement. You don't need a for loop if you want to get a single random element from the column.

Since data[i].length is equal to 1 this expression Math.random()*(data[i].length) returns a number between 0 and 1 but less than 1. Therefore Math.floor(Math.random()*(data[i].length)) always returns 0 and as a result data[0][0] is equal to the value of the first cell A1.

  • Please notice that logger.log(element) is wrong. It should be Logger.log(element); but you are not getting any error since return is before that line and the function never reaches that line.

  • Finally, I used flat() to convert the 2D array to 1D and therefore data.length returns the correct length of the array but also you can now index it with one variable.

Solution:

function getData () {
const SS = SpreadsheetApp.getActive();
const sh = SS.getActiveSheet();
const Avals = sh.getRange("A1:A").getValues();
const numberOfValues = Avals.filter(String).length;
const data = sh.getRange(1,1,numberOfValues).getValues().flat();
const j = Math.floor(Math.random()*(data.length)); //method of randomization
const element = data[j];
Logger.log(element);
return element;
}
user_112252910
Related