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.
About the code:
- It can generate randomize names with no duplicates per row, name1-name46.
- It can generate randomize name per column, name 1-46, all name1-46 were in each column
- When you rerun the given code, there is a possibility for the name in each row to appear again in the same row.
Problem: 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);
}
//Credits to: TheMaster
//@see https://stackoverflow.com/a/56588581
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. In realworld application in sending survey, we don't want to rate the same person next month or until 6th month.
