In this post, we’re going to look at a script to be able to make even teams from a selection of players. I play football every Sunday and every week my friends sign up to play and most weeks there are different players playing, so we end up having to make the teams, and trying to do it as fairly as we can, which inevitably takes some time.
So, I decided to use my Apps Script skills to create a team maker which uses players’ ratings to then create two teams which have a total rating within one point of each other, i.e. roughly equal.
If the teams created aren’t fair, the script carries on until it finds two teams that are, then displays those teams on a Google Sheet.
Google Sheet Set Up
There are two sheets, one called TEAMS and one called PLAYERS.
The TEAMS sheet is where the players for each team will be displayed, and where the list of players is entered via drop-down menus. As I wanted to be able to do this on my mobile, there’s a drop-down menu to select “Make”, which will run the script.
The PLAYERS sheet stores the rating of all the possible players on the right, and on the left it looks up the scores of the players that have been entered on the TEAMS sheet. You could code this instead, but for me a simple VLOOKUP formula was good enough.
I hide this sheet, as I don’t want my friends to know the ratings I’ve put!
The script
Functions
OK, let’s take a look at the script which makes the teams. There are five functions:
makeTeams — Main script
shuffle — This shuffles the list of players
calcScoreDiff — This gets the total scores of both teams and returns the difference in the totals
notFair — If the two totals aren’t within 1 point, this function is run to reshuffle the teams, and to get the team totals again.
getTeamTotal — This gets the team totals
makeTeams function
This function is the main function, which will get the data from the Google Sheet, call the other functions, and display the teams and team totals on the TEAMS sheet.
Running the script from a certain cell
1. function makeTeams(e) {
2.
3. if (e.source.getActiveSheet().getName() === "TEAMS"
4. && e.range.getA1Notation() === "E1"
5. && e.value === "Make") {L1: Call the makeTeams function and pass in the event parameter, e, from the onEdit trigger that we will set up later.
L3: We don’t want to run this function with just any edit to the spreadsheet, so here, we check to see if the source in the event is the current spreadsheet, and from that the active sheet is called TEAMS.
L4: We also check to see if the cell edited is E1.
L5: We also only want to run it if the option selected is “Make”. If all three are true we run the rest of the script.
Alternatively, you could remove lines 3 to 5 and run it from the editor or from a menu.
Getting the sheets and players
7. const ss = SpreadsheetApp.getActiveSpreadsheet();
8. const shPlayers = ss.getSheetByName("PLAYERS");
9. const shTeams = ss.getSheetByName("TEAMS");
10. const data = shPlayers.getRange(1, 1, shPlayers.getRange("A1")
11. .getDataRegion().getLastRow(), 2).getValues();
12. let team1 = [];
13. let team2 = [];L7: Get the the active spreadsheet.
L8–9: Get the sheets PLAYERS and TEAMS.
L10–11: Get the list of players that are going to play and their ratings.
L12–13: Set up arrays to store the players for each team.
Shuffling the players and calculating the team score difference
15. let shuffledPlayers = shuffle(data);
16. let scoreDiff = calcScoreDiff(shuffledPlayers);
17.
18. if (scoreDiff > 1) {
19. shuffledPlayers = notFair(data);
20. }L15: Call the shuffle function to shuffle the list of players randomly. This then gets returned and stored in the variable shuffledPlayers.
L16: Call the calcScoreDiff function and pass shuffledPlayers to it. It then returns the difference between the total scores of both teams and this is stored in scoreDiff.
L18: Then check to see if the difference is greater than 1. You can set this to whatever you like.
L19: If the difference is greater than 1, then call the notFair function and pass through the players in the data variable.
L20: Close the if statement.
Making the two teams
22. //Get 2 teams
23. let team1Length = Math.round(shuffledPlayers.length / 2);
24. let team2Length = Math.trunc(shuffledPlayers.length / 2);
25. team1 = shuffledPlayers.splice(0, team1Length);
26. team2 = shuffledPlayers.splice(0, team2Length);
27.
28. const t1 = team1.map((playerAndScore) => {
29. return [playerAndScore[0]];
30. });
31.
32. const t2 = team2.map((playerAndScore) => {
33. return [playerAndScore[0]];
34. });L23: To divide the shuffledPlayers array into two we need to know how players there are, then split it . First, get the shuffledPlayers length divide it by 2, then round it to the nearest integer. So, 11 players will return 6 in the first team, whereas 10 players will return 5.
L24: Similarly, for the second team use trunc to round down to the nearest integer. So, if there are 11 players, it will return 5, and if there are 10, it will also be 5. So, in total we have the number of players playing.
L25–26: Then we splice the shuffledPlayers array, putting X players in team1 and X in team2. So, now we have two arrays of players.
L28: Next, I don’t want to show the players’ rating on the TEAMS sheet, so we need to remove the ratings from the array. We can easily do this by iterating over the array using map.
L29–30: Return the first part of each array element, i.e. the player’s name and store them in t1.
The team1 array looks like this, i.e. with both the player and their rating:
Then after the map, t1 contains just the players’ names:
L32–34: We do the same for the team2 array.
Getting the two teams’ totals
36. const t1Total = getTeamTotal(team1);
37. const t2Total = getTeamTotal(team2);L36–37: Call the getTeamTotal function to calculate the total points per team and pass the teams to it, then store it in t1Total and t2Total.
Now, we add the teams and their totals to the TEAMS sheet.
Adding the teams and totals to the sheet
39. //Add to sheet
40. shTeams.getRange(2, 1, 15, 4).clearContent();
41. shTeams.getRange(2, 1, team1.length, 1).setValues(t1);
42. shTeams.getRange(2, 3, team2.length, 1).setValues(t2);
43. shTeams.getRange(1, 1, 1, 5).setValues([["TEAM 1", t1Total, "TEAM 2", t2Total, "Done"]]);
44. }
45. }L40: First, we clear any previous teams from the sheet.
L41–42: Then we add the list of players for the two teams.
L43: Then we add the totals. I’ve included the headers too, so it can be added to the sheet in one go. It also changes the drop down menu back to “Done”.
L44–45: Close the if statement from line 3 and the function.
shuffle function
OK, now let’s look at the other functions that are called. Firstly, the shuffle function, which shuffles the list of players that are playing.
47. //Shuffle players
48. function shuffle(data) {
49. let shuffledPlayers = data
50. .map(value => ({ value, sort: Math.random() }))
51. .sort((a, b) => a.sort - b.sort)
52. .map(({ value }) => value);
53. return shuffledPlayers;
54. }I got this chunk of code from Stackoverflow here: https://stackoverflow.com/questions/2450954/how-to-randomize-shuffle-a-javascript-array
It’s an example of not reinventing the wheel, and finding a chunk of code that meets the purpose you’re looking for. This whole function randomizes the elements in an array and returns the randomized array.
L48: Set up the shuffle function and pass in the data variable.
L49: Here, we’re going to return the shuffled players to the variable shuffledPlayers. We have three methods chained to the data variable (map, sort, map).
L50: This first step adds a randoms value to each of the array elements and stores it as an object.
L51: This sorts those random numbers. So, basically puts the elements in order.
L52–54: This removes those random numbers, so we’re left with just the player and rating again, which gets stored in shuffledPlayers. Close the function.
calcScoreDiff function
Next, let’s look at the function which creates the two teams and works out the difference between the total scores of the two teams.
Setting up the team and total arrays
56. //Make two teams, set up team totals & get score difference
57. function calcScoreDiff(shuffledPlayers) {
58.
59. let team1 = [];
60. let team2 = [];
61. let team1Total = 0;
62. let team2Total = 0;
63.
64. let numOfPlayers = shuffledPlayers.length;
65. let team1Length = Math.round(numOfPlayers / 2);L57: Set up the function and pass in the shuffledPlayers array.
L59–60: Create two empty arrays to store the teams.
L61–62: Set up the team totals at 0. This is important as we may need to reset the totals if the two teams aren’t fairly balanced.
L64: Get the number of players. Our numbers can vary between 8 and 12, so we need to adapt to that.
L65: Get the length of the first team, which is the total divided by 2 rounded to the nearest integer. We’ll use this to divide the players into two teams.
Next, let’s divide the teams into two and at the same time calculate the total score for each team.
Creating the teams and calculating their total scores
67. shuffledPlayers.forEach((sPlayer, s) => {
68. if (s < team1Length) {
69. team1.push(sPlayer);
70. let pl1Score = sPlayer[1];
71. team1Total = team1Total + pl1Score;
72. }
73. else if (s >= team1Length) {
74. team2.push(sPlayer);
75. let pl2Score = sPlayer[1];
76. team2Total = team2Total + pl2Score;
77. }
78. });L67: Use forEach to loop through the shuffledPlayers array.
L68: Check if the current index is less than the team 1 length. If it is, the player and their rating will go in team 1.
L69: Push the player into the team1 array.
L70: Get the player’s rating/score, which is the second element.
L71–72: Add that player’s score to the team 1 total and close the if statement.
L73: If the index is equal or greater than the team 1 length, put them in team 2.
L74: Push the player and their rating in team2 array.
L75: Get their rating.
L76: Add that player’s score to the team 2 total.
L77–78: Close the else if statement and the forEach method.
Calculating the difference in team scores
80. let scoreDiff = Math.abs(team1Total - team2Total);
81. return scoreDiff;
82. }L80–82: work out the difference between the team 1 total and the team 2 one. Store it in scoreDiff, then return it back, and close the function.
notFair function
Now, let’s look at the function that is run if the two teams have a difference between their total scores of more than 1. This is how we can keep randomizing the teams until we get a fair match.
84. //Check if teams are fair, if not reshuffle teams
85. function notFair(data) {
86. shuffledPlayers = shuffle(data);
87. scoreDiff = calcScoreDiff(shuffledPlayers);
88.
89. if (scoreDiff > 1) {
90. notFair(data);
91. }
92. return shuffledPlayers;
93. }L85: Set up the notFair function and pass in the data array.
L86: Call the shuffle function and pass the data array to it. The returning result will be stored in shuffledPlayers.
L87: Call the calcScoreDiff function to work out the total score difference between the two teams, passing in the shuffledPlayers array. The returning result is stored in scoreDiff.
L89: Check to see if the score difference is more than 1.
L90–91: If it is, call this function again, which will call the shuffle and calcStoreDiff functions again, until the difference in scores is 1 or less. Close the if statement.
L92–93: Return the shuffledPlayers array and close the function.
getTeamTotal function
The final function works out the how many points each team has by getting the player scores/ratings.
95. //Get team total
96. function getTeamTotal(team) {
97. const tTotal = team.map((playerAndScore) => {
98. return playerAndScore[1];
99. }).reduce((runningTotal, playerScore) => {
100. return runningTotal + playerScore;
101. });
102. return tTotal;
103. }L96: Set up the getTeamTotal function. Pass in the team parameter, which will be either the team1 array or the team2 array from line 36 and 37.
L97–98: Now, get just the player’s score, as remember we have a player and score together in an array.
L99–101: We then use the reduce method to combine the running total to the current player score and then finally end up with just one total for the team.
L102–103: We then return that total figure and close the function.
Running the script from an onEdit trigger
I want to be able to run the script from my mobile and one simple way to do that is to set up an onEdit trigger, which will run it if the Google sheet is edited. As we saw in lines 3–5, we can control which sheet, cells and values, trigger this.
To set up the trigger, go to the Triggers page (clock icon on the left). Then click “Add Trigger”.
Then set the trigger up as below, i.e. running the makeTeams function, with an On edit event, then click Save.
Running the script
On the TEAMS sheet, enter which players are playing in column F.
Then, select Make from the drop-down menu in cell E1. This will run the script and the teams will appear in columns A and C. Plus, the scores for each will be in cells B1 and D1.
Now, we spend less time choosing teams at the start of the game, and can just get on and start playing.
You can make a copy of the Google Sheet which contains all the code here.
Want to learn more about Google Workspace and Apps Script? The books below are available on Amazon.