Select Random cell that is not Blank-CodePudding

I am having a problem that my brain cannot figure out.

I have a script that I copied and modified that will Randomly select a cell that is not blank from reassign!A1:A10

in reassign Tab I have a Query that will filter another sheet if a checkbox is ticked

so in reassign!A1:A10 it will depend if how many checkbox are ticked

the output is it only selects the first cell which is A1

Script:

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("reassign");
  var range = sheet.getRange("A1:A10");
  var values = range.getValues();
  var newValue = "";

  for(var i = 0; i < values.length; i  ) {
    if(values[i][0] != "") {
      newValue = values[i][0];
      break;
    }

  }

  sheet.getRange("B2").setValue(newValue);
}

I am running out of ideas. Sorry

Thanks in advance

I tried researching for solutions, but I really can't figure it out.

CodePudding user response：

The explanation that you have provide is not clear to me at all. But perhaps this small change might make a difference.

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("reassign");
  var range = sheet.getRange("A1:A10");
  var values = range.getValues();
  var newValue = "";
  for(var i = 0; i < values.length; i  ) {
    if(values[i][0] !== "") {
      newValue = values[i][0];
      break;
    }
  }
  sheet.getRange("B2").setValue(newValue);
}

CodePudding user response：

This Script selects randomly but sometimes it selects blank cells

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("reassign");
  var range = sheet.getRange("A1:A10");
  var values = range.getValues();
  var newValue = values[Math.floor(Math.random()*values.length)][0];
  sheet.getRange("B2").setValue(newValue);
}