0

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; 
}
0

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()));
}    
  • Thank you for replying. I think there is a couple of typos though. (Line 7: shufflesheet to shuffleSheet) & (Line 10: a1Values to a1values). After these changes the script worked for me. It does its job and shuffles all of the sheets but sometimes there is a duplicate. What I want to do is shuffle one sheet at a time, not all at once. Sorry I wasn't clear about that. – NinjaMotoTheXVI Jul 21 '17 at 0:11
  • Please edit the question to state precisely what the script should do. What are the duplicates that you see that are to be avoided? You said that Sheet1!A1 should not be equal to other A1 cells, but there was nothing about Sheet2!A1 being different from Sheet3!A1, for example. – user135384 Jul 21 '17 at 0:52
0

So I asked the same question on reddit and someone went and made a whole different spreadsheet for me. Here is the thread.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.