Picking Random Cell, Ignoring empty

user_2459668 0
Viewed 802

My coding skills aren't the best, but I know somewhat how to use excel/sheets.

I am trying to get a cell to select a random cell in a column, but ignore cells that are empty. So fare, I can get it to select a random cell, but how do I make it continue if the cell is empty?

More specifically, I had an idea for a game to play at an event based on the Magic the gathering game mode: Horde, and use Google Sheet as the "enemy" deck. The below link is just sample. Not the actual idea.

https://docs.google.com/spreadsheets/d/1XKMCguvpI_4PDtBYVXa4_FaH7O3WgckRWYbQ75_5F7E/edit?usp=sharing

google-sheets-formula google-apps-script google-sheets filter random
3 Answers

Answer:

You can define the column as a range, push only non-empty cells to an array and select a random element.

Code:

Under the assumption that the column you want to select is column A in a sheet called Sheet1, and starts from A:

function selectRandomCell() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var range = ss.getSheetByName("Sheet1").getRange("A1:A").getValues();
  var values = [];
  
  for (var i = 0; i < range.length; i++) {
    if (range[i][0] == "") {
      continue;
    }
    else {
      values.push(range[i][0]);
    }
  }

  return values[Math.floor(Math.random() * values.length)]);
}

You can change the range as you need, however.

user_115514680

you can use: 

=INDEX(SORT(FILTER({RANDARRAY(G3:G), G3:G}, L3:L=TRUE)), 1, 2)

to get random ID 1 or ID 9

0

=TRUE can be changed to <>"" to simulate empty cell

for puppy name:

=VLOOKUP(INDEX(SORT(FILTER({RANDARRAY(G3:G), G3:G}, L3:L=TRUE)), 1, 2), G:H, 2, 0)

or just:

=INDEX(SORT(FILTER({RANDARRAY(G3:G), H3:H}, L3:L=TRUE)), 1, 2)
user_56326290 
function pickANonEmptyRandomCellFromARange() {
  const ss=SpreadsheetApp.getActive();
  const sh=ss.getActiveSheet();
  const rg=sh.getDataRange();
  const vs=rg.getDisplayValues();
  let v=[];
  //This loops through all of the rows r stands for the row element  is the index
  vs.forEach(function(r,i){
    //this loops through each column and c stands for the cell element  j is the index
    r.forEach(function(c,j) {
      //this asks if the cell is not blank thee push the A1Notation into an array
      if(c){v.push(sh.getRange(i+1, j+1).getA1Notation());}
    });
  });
  //This is a dialog which displays v[random index] which is the A1 Notation of the selected element. 
  SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutput(v[Math.floor(Math.random()*v.length)]), 'Random Non Empty Cell');
}
user_72150910
Related