feedbackpartners.gs

// This Apps Script is for Google Sheets, and assigns random feedback partners from a list of names in Column A.
// You can use it to organize a feedback group. 
// 
// numPartners: number of feedback partners to assign. Defaults to 2.
// nameStartRow: the row that the name list starts on. Assumes names are in Column A. Defaults to row 3.
// outputStartCol: feedback partners will be outputted into every other column starting from outputStartCol. Defaults to Column F.
function assignFeedbackPartners(numPartners = 2, nameStartRow = 3, outputStartCol = 6) {
  // Get the active spreadsheet and the active sheet
  const sheet = SpreadsheetApp.getActiveSheet();
  
  // Get all names from column A starting from row 3
  const namesRange = sheet.getRange("A" + nameStartRow + ":A");
  const names = namesRange.getValues().filter(row => row[0] !== "").map(row => row[0]);
  const numPeople = names.length;

  if (numPeople < numPartners + 1) {
      SpreadsheetApp.getUi().alert(
        'Error: Not enough names. Reduce number of feedback partners.'
      );
      return;
  }
  
  // Array to store all rounds of assignments
  let allAssignments = new Array(numPartners).fill(null).map(() => []);
  
  // Function to check if assignment is valid for a specific round
  function isValidAssignment(personIndex, assignedIndex, roundIndex, existingAssignments) {
    // Check if the person is different from the assigned, accounts for multiple participation
    if (personIndex === assignedIndex || names[personIndex] === names[assignedIndex]) return false;
    
    // Check if the assigned person is already used in this round
    if (existingAssignments.includes(assignedIndex)) return false;
    
    // Check if this pair has been used in any previous round
    for (let i = 0; i < roundIndex; i++) {
      if (allAssignments[i][personIndex] === assignedIndex 
          || names[allAssignments[i][personIndex]] === names[assignedIndex]) return false;
    }
    
    return true;
  }
  
  // Assign partners for each round
  for (let round = 0; round < numPartners; round++) {
    let attempts = 0;
    let validRound = false;
    
    while (!validRound && attempts < 1000) {
      validRound = true;
      allAssignments[round] = [];
      
      for (let i = 0; i < numPeople && validRound; i++) {
        let personAssigned = false;
        let innerAttempts = 0;
        
        while (!personAssigned && innerAttempts < 100) {
          const randomIndex = Math.floor(Math.random() * numPeople);
          
          if (isValidAssignment(i, randomIndex, round, allAssignments[round])) {
            allAssignments[round][i] = randomIndex;
            personAssigned = true;
          }
          innerAttempts++;
        }
        
        if (!personAssigned) {
          validRound = false;
        }
      }
      attempts++;
    }
    
    if (!validRound) {
      // If we couldn't find a valid assignment after max attempts, alert the user
      SpreadsheetApp.getUi().alert(
        'Error: Could not find valid assignments. Try reducing the number of partners or increasing the pool of names.'
      );
      return;
    }
  }
  
  // Write assignments to the sheet
  for (let round = 0; round < numPartners; round++) {
    const targetColumn = outputStartCol + (round * 2); // Start at column E (5) and skip one column each time
    for (let i = 0; i < numPeople; i++) {
      let personIndex = allAssignments[round][i]
      sheet.getRange(i + nameStartRow, targetColumn).setValue(names[personIndex]);
    }
  }
}