Google Sheets – Shuffling Function to Avoid Repeating First Cell

google sheetsgoogle-apps-script

I am trying to make an Overwatch team randomizer using Google Sheets. Here is my spreadsheet so far. I want to use this with five other people at once. Every player has their own sheet, they click the button, and a character is chosen for them. The button in sheet Player1 shuffles the rows in Sheet1, the number in cell Sheet1!A1 decides what character is chosen for Player1. The button in Player2 shuffles Sheet2, Player3 shuffles Sheet3, and so on. Each button has its own shuffle function, shuffleSheet1(), shuffleSheet2(), and so on.

Everything works how I want, except that when a Player shuffles, the character that they is chosen for them should not be a character that another player is already using. So if I shuffle Sheet4 and Sheet4!A1 ends up equal to Sheet1,2,3,5,6!A1, how can I have the shuffleSheet function repeat itself until Sheet4!A1 is NOT equal to Sheet1,2,3,5,6!A1?

Below is the function I use for Sheet1.

function shuffleSheet1() {   
  var ss = SpreadsheetApp.getActiveSpreadsheet();   
  var sheet = ss.getSheetByName("Sheet1");   
  var range = sheet.getDataRange();
  range.setValues(shuffleArray(range.getValues()));
}    

function shuffleArray(array) {
  var i, j, temp;   
  for (i = array.length - 1; i > 0; i--) {
    j = Math.floor(Math.random() * (i + 1));
    temp = array[i];
    array[i] = array[j];
    array[j] = temp;   
  }   
  return array; 
}

Best Answer

Here is a solution. I didn't include shuffleArray here because it stays exactly the same. shuffleSheet shuffles the range A1:A25 of whatever sheet it is given as a parameter; this is a much better approach than writing 6 nearly identical functions for 6 sheets.

The main function is repeatShuffling. It shuffles every sheet with the name listed in the array sheetNames. It also takes a note of A1 values after shuffling. If the A1 value of the first-named sheet is equal to A1 value elsewhere, the function repeatShuffling is invoked again. 

function repeatShuffling() {
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var sheetNames = ["Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6"];
  var a1values = Array(6);
  for (var i = 0; i < sheetNames.length; i++) {
    var sheet = ss.getSheetByName(sheetNames[i]); 
    shuffleSheet(sheet);
    a1values[i] = sheet.getRange("A1").getValue();
  }
  if (a1values.slice(1).indexOf(a1values[0]) != -1) {
    repeatShuffling(); 
  }  
}

function shuffleSheet(sheet) {   
  var range = sheet.getRange("A1:A25");
  range.setValues(shuffleArray(range.getValues()));
}
Related Topic