mardi 25 juin 2019

How to randomize names without repeating its name in a row when you click again the randomize function?

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.

Data Data

 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