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