Good day. I am making a program to send monthly survey for IT employees with 46 members. Given the code in google app script in google sheet, how could I limit the names of ratees to appear again on the same row when I run randomize function in the 2nd to 6th times. Thank you.
Requirement:
Given the data below, ratee 1-5 should not appear again in the same row when you run randomize function 2nd-6th times.
function randomize() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheetname = "Emails";
var sheet = ss.getSheetByName(sheetname);
// some variables
var randomcount = 2; // how many random names
var rowstart = 2; // ignore row 1 - the header row
var width = 6; // how many names in each row - 1/rater plus 5/ratee
var thelastrow = sheet.getLastRow();
//Logger.log("DEBUG:last row = "+thelastrow)
// get the employee names
var employeecount = thelastrow-rowstart+1;
//Logger.log("DEBUG: employee count = "+employeecount);//DEBUG
// get the data
var datarange = sheet.getRange(rowstart, 3, thelastrow - rowstart+1);
//Logger.log("DEBUG: range = "+datarange.getA1Notation());//DEBUG
var data = datarange.getValues();
//Logger.log("data length = "+data.length);
//Logger.log(data);
var data1d = data.map(function(e){return e[0]});
var finalArr = getRandUniqMatrix(5, 46).map(function(row){return row.map(function(col){return data1d[col]})});
sheet.getRange(2,4,finalArr.length, finalArr[0].length).setValues(finalArr);
}
function getRandUniqMatrix(numCols, numRows) {
var maxIter = 1000; //Worst case number of iterations, after which the loop and tempCol resets
var output = Array.apply(null, Array(numRows)).map(function(_, i) {
return [i++];
});//[[1],[2],[3].....]
var getRandom = function() {
return Math.floor(Math.random() * numRows);
};//getrandom number within numRows
while (numCols--) {//loop through columns
for (
var row = 0, currRandNum = getRandom(), tempCol = [], iter = 0;
row < numRows;
++row
) {//loop through rows
//unique condition
if (!~output[row].indexOf(currRandNum) && !~tempCol.indexOf(currRandNum)) {
tempCol.push(currRandNum);
} else {
currRandNum = getRandom();//get a new random number
--row;
++iter;
if (iter > 1000) {//reset loop
iter = 0;
tempCol = [];
row = -1;
}
}
if (row + tempCol.length + 1 === numRows * 2) {//last row, Combine output+tempCol
output.forEach(function(e, i) {
return e.push(tempCol[i]);
});
}
}
}
return output;
}
console.info(getRandUniqMatrix(5, 46));
Expected outcome:
Totally different names per row of ratees when you run randomize function in the 2nd,3rd,4th,5th and 6th times.
Aucun commentaire:
Enregistrer un commentaire