dimanche 9 juin 2019

Random Unique Names with 46 rows and 6 columns

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

Rules:

1. 1 rater, for 5 uniques ratees
2. No duplicates
3. Not allowed to rate yourself

See image for visualization

Sir Tedinoz help me to have an idea, yet the final output is not yet the desire but it is almost there.

function random() {

  var ss = SpreadsheetApp.getActive().getSheetByName("Emails");

  // get the employee names
  var thelastrow = ss.getLastRow();
  var datarange = ss.getRange(2, 2, thelastrow - 1);
  var datavalues = datarange.getValues(); // the column of employees

    // some variables
  var randomcount = 276; // 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 rows = 46; // 30 employees/divided by 6 names per row; yes, this could be calculated!

  var results = selectRandomElements(datavalues, randomcount);

  var datacell = [];
  for (r = 0; r < rows; r++) {
        var thisrow = 2 + r;
        for (i = 0; i < width; i++) {
      var datarow = [];
      var dat = i + (6 * r);
            datacell.push(results[dat]);
    }
    // Logger.log("DEBUG: status: row =" + r + ", width = " + i+", datacell = "+datacell);//DEBUG
    datarow.push(datacell);
    var datacell = [];  
    var newratersrange = ss.getRange(thisrow, 3, 1, width);
    newratersrange.setValues(datarow);
  }
}


/*
// 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;
}

This is the current result so far:

Expected output is for us to create 46x6 random names with no duplicates




Aucun commentaire:

Enregistrer un commentaire