How to automate randomizing 46 names to create 46 x 6 unique rows and columns in Google sheet?

Question

I am working with automation in Google sheet. Can you help me?

This problem is for sending surveys to 46 people. Each people needs to rate 5 people from those 46 people.

Requirements:

1. 1 rater, for 5 uniques ratees
2. No duplicate name per row (it should be 6 unique names in a row)
3. No duplicate name per column (it should be 46 unique names per column)

Expected output is for us to create 46x6 random names with no duplicates in row and columns.

- -

Answer 1

Flow:

If a unique matrix across and below can be created, then it's values can be used as keys to the actual name array.

• Create a 2D number array with length = number of rows
• Loop through required number of columns and rows
• Create a temporary array (tempCol) to store current column data
• Fill the array with random numbers
• Use indexOf to figure out if any random numbers are already present in the currentrow/ current column, if so, get a new random number.
• In random cases, where it's impossible to fill up the temporary column with unique random numbers across and below, delete the temporary column and redo this iteration.

Snippet:

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++]; //[[0],[1],[2],...]
  });
  var currRandNum;
  var getRandom = function() {
currRandNum = Math.floor(Math.random() * numRows);
  }; //get random number within numRows
  while (numCols--) {//loop through columns
getRandom();
for (
  var row = 0, tempCol = [], iter = 0;
  row < numRows;
  ++row, getRandom()
) {//loop through rows
  if (//unique condition check
    !~output[row].indexOf(currRandNum) &&
    !~tempCol.indexOf(currRandNum)
  ) {
    tempCol.push(currRandNum);
  } else {
    --row;
    ++iter;
    if (iter > maxIter) {//reset loop
      iter = 0;
      tempCol = [];
      row = -1;
    }
  }
}
output.forEach(function(e, i) {//push tempCol to output
  e.push(tempCol[i]);
});
  }
  return output;
}
console.info(getRandUniqMatrix(6, 46));

var data1d = data.map(function(e){return e[0]});
var finalArr = getRandUniqMatrix(6, 46).map(function(row){return row.map(function(col){return data1d[col]})}); 
destSheet.getRange(1,1,finalArr.length, finalArr[0].length).setValues(finalArr);

Answer 2

The OP wants to create a review matrix in which the names of the reviewed employees are chosen at random, the reviewer cannot review themselves, and the matrix is completed for 46 employees.

Based on previous code, this version builds an array of employee names for each row, in which the name of the reviewer is not included in the array. Five names are chosen at random and applied to the reviewer. The loop then repeats through each of the 46 employees.

For example, in the first round of reviews, "name01" is omitted from the array of employees from which the "reviewees" are randomly chosen. In the second round, "name01" is included, but "name02" is excluded from the array of employees. And so on, such that in each case, the array of employees used for the random selection of five reviews is always 45 names in length, and excludes the name of the reviewer.

The random selection of names to be rated does not ensure an equal and even distribution of reviews among employees. Though each employee will conduct 5 reviews, some employees are reviewed more than 5 times, some less than 5 times, and (depending on the alignment of the sun, the moon and the stars) it is possible that some may not be selected for review.

function s05648755803(){

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetname = "Sheet3";
  var sheet = ss.getSheetByName(sheetname);

  // some variables
  var randomcount = 30; // how many random names
  var rowstart = 7; // ignore row 1 - the header row
  var width = 5;    // 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, 1, thelastrow - rowstart+1);
  //Logger.log("DEBUG: range = "+datarange.getA1Notation());//DEBUG
  var data = datarange.getValues();
  //Logger.log("data length = "+data.length);
  //Logger.log(data);

  var counter = 0; 
  var newarray = [];
  for (c = 0;c<46;c++){
    counter = c;

    for (i=0;i<data.length;i++){    
      if(i!=counter){
        newarray.push(data[i]);
      }
    }

    //Logger.log(newarray);
    var rowdata = [];
    var results = selectRandomElements(newarray, 5);  
    Logger.log(results)
    rowdata.push(results);
    var newrange = sheet.getRange(rowstart+c, 3, 1, 5);
    newrange.setValues(rowdata);  

    // clear the arrays for the next loop
    var newarray=[]; 
    var rowdata = []
  }
}

/*
// selectRandomElements and getRandomInt
// Credit: Vidar S. Ramdal
// https://webapps.stackexchange.com/a/102666/196152
*/


function selectRandomElements(fromValueRows, count) {
  var pickedRows = []; // This will hold the selected rows
  for (var i = 0; i < count && fromValueRows.length > 0; i++) {
    var pickedIndex = getRandomInt(0, fromValueRows.length);
    // Pick the element at position pickedIndex, and remove it from fromValueRows. See https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/splice
    var pickedRow = fromValueRows.splice(pickedIndex, 1)[0];
    // Add the selected row to our result array
    pickedRows.push(pickedRow);
  }
  return pickedRows;
}

function getRandomInt(min,
max) { // From https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Math/random
  min = Math.ceil(min);
  max = Math.floor(max);
  return Math.floor(Math.random() * (max - min)) + min;
}

---

Screenshot#1

---

Screenshot#2

---

Answer 3

Try this. Satisfies all the three requirements.

HTML/JS:

<html>
<title>Unique Employees</title>

<head>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.0/jquery.min.js"></script>
</head>
<table id="survey_table" border="1" width="85%" cellspacing="0">
    <thead>
        <th>Rater</th>
        <th>Ratee1</th>
        <th>Ratee2</th>
        <th>Ratee3</th>
        <th>Ratee4</th>
        <th>Ratee5</th>
    </thead>
    <tbody id="table_body">

    </tbody>
</table>

<script type="text/javascript">
    function arrayRemove(arr, value) {

        return arr.filter(function(ele) {
            return ele != value;
        });

    }

    function getRandomInt(rm_row, rm_col) {
        var temp_arr = [];
        for (var k = 1; k <= 46; k++) {
            temp_arr.push(k);
        }

        for (var k = 0; k < rm_row.length; k++) {
            temp_arr = arrayRemove(temp_arr, rm_row[k]);
        }

        for (var k = 0; k < rm_col.length; k++) {
            temp_arr = arrayRemove(temp_arr, rm_col[k]);
        }

        var rand = temp_arr[Math.floor(Math.random() * temp_arr.length)];

        return rand;

    }

    function exclude_num(row_unq, col_unq) {
        var rand_int = getRandomInt(row_unq, col_unq);

        if (!row_unq.includes(rand_int) && !col_unq.includes(rand_int)) {

            arr_row.push(rand_int);
            return rand_int;
        } else {
            return exclude_num(arr_row, arr_cols);
        }
    }

    for (var i = 1; i <= 46; i++) {
        var arr_row = [];
        arr_row.push(i);

        var table_html = '<tr id="Row' + i + '">';

        for (var j = 1; j <= 6; j++)

        {
            if (j == 1) {
                table_html += '<td class="Column' + j + ' cells_unq">' + i + '</td>';
            } else {
                var arr_cols = []
                $('.Column' + j).each(function() {
                    arr_cols.push(Number($(this).text()));
                });
                var num = exclude_num(arr_row, arr_cols);
                table_html += '<td class="Column' + j + ' cells_unq">' + num + '</td>';
            }

        }

        table_html += '</tr>';
        var row_html = $('#table_body').html();
        $('#table_body').html(row_html + table_html);
    }
    $('.cells_unq').each(function() {
        temp_text = $(this).text();
        $(this).text('Name' + temp_text);
    });
</script>
<style type="text/css">
    td {
        text-align: center;
    }
</style>

</html>