vendredi 15 février 2019

How to add a dependency for an array that is shuffled?

I have a script that shuffles the values in an array. These values represent people who are available. When they are not available, they are removed from the array. I am looking to make this array "dynamic" in that it adjusts the range based on the number of values in the sheet.

I've tried creating a named range but this doesn't eliminate the manual task of changing the named range size. Removing a person from the array will cause the blank cell to be shuffled in with the other values. I've tried experimenting with the .filter() function but I am fairly amateur when it comes to JS and apps script.

function myFunction() {
  var ss =     SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Test Sheet");
  var candidates = ss.getRange("Test Sheet!B2:B10").getValues();
  var arrayValues = ss.getRange("Test Sheet!F1:F12").getValues();


  for (var x = 0; x < arrayValues.length; x++) {
    var range = ss.getRange(1, 6, 12);
    var array = range.getValues();                          
    shuffleArray(array);
    range.setValues(array);
  } 
}
function shuffleArray(array) {
    for (var i = array.length - 1; i > 0; i--) {
        var j = Math.floor(Math.random() * (i + 1));
        var temp = array[i];
        array[i] = array[j];
        array[j] = temp;
  }
}

Either making the range dynamic or ignoring the blank cells in the array is my goal.

Aucun commentaire:

Enregistrer un commentaire