How to make an automatic team maker app using Apps Script

In this post, we’re going to look at a script to be able to make an automatic team maker which makes 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.

POST UPDATED – 8/10/2023 – WITH A NEW MORE STREAMLINED VERSION OF THE SCRIPTS.

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.

getTeamPlayers – This gets just the players from the teams.

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.   if (e.source.getActiveSheet().getName() === "TEAMS"
3.     && e.range.getA1Notation() === "E1"
4.     && 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.

L2: 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.

L3: We also check to see if the cell edited is E1.

L4: 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 2 to 4 and run it from the editor or from a menu.

Getting the sheets and players

 6.  const ss = SpreadsheetApp.getActiveSpreadsheet();
 7.  const shPlayers = ss.getSheetByName("PLAYERS");
 8.  const shTeams = ss.getSheetByName("TEAMS");
 9.  const data = shPlayers.getRange(1, 1, shPlayers.getRange("A1").getDataRegion().getLastRow(), 2).getValues();

L6: Get the the active spreadsheet.

L7-8: Get the sheets PLAYERS and TEAMS.

L9: Get the list of players that are going to play and their ratings.

Shuffling the players and calculating the team score difference

11.  let shuffledPlayers = shuffle(data);
12. 
13.  shuffledPlayers = calcScoreDiff(shuffledPlayers);

L11: Call the shuffle function to shuffle the list of players randomly. Pass the list of players stored in the data variable to it. This then gets returned and stored in the variable shuffledPlayers.

L13: Call the calcScoreDiff function and pass shuffledPlayers to it. It then returns the shuffled players where each team is equal.

Making the two teams

15.    //Get 2 teams
16.    let team1Length = Math.round(shuffledPlayers.length / 2);
17.    let team2Length = Math.trunc(shuffledPlayers.length / 2);
18.    const team1 = shuffledPlayers.splice(0, team1Length);
19.    const team2 = shuffledPlayers.splice(0, team2Length);
20.
21.    const t1 = getTeamPlayers(team1);
22.    const t2 = getTeamPlayers(team2);

L16: 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.

L17: 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.

L18-19: Then we splice the shuffledPlayers array, putting X players in team1 and X in team2. So, now we have two arrays of players.

L21-22: 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. We call the getTeamPlayers function on both teams and store the players in t1 and t2 respectively.

Getting the two teams’ totals

24.   const t1Total = getTeamTotal(team1);
25.   const t2Total = getTeamTotal(team2);

L24-25: 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

27.   //Add to sheet
28.   shTeams.getRange(2, 1, 15, 4).clearContent();
29.   shTeams.getRange(2, 1, team1.length, 1).setValues(t1);
30.   shTeams.getRange(2, 3, team2.length, 1).setValues(t2);
31.   shTeams.getRange(1, 1, 1, 5).setValues([["TEAM 1", t1Total, "TEAM 2", t2Total, "Done"]]);
32.  }
33. }

L28: First, we clear any previous teams from the sheet.

L29-30: Then we add the list of players for the two teams.

L31: 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”.

L32-33: Close the if statement from line 2 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.

35. //Shuffle players
36. function shuffle(data) {
37.   let shuffledPlayers = data
38.     .map(value => ({ value, sort: Math.random() }))
39.     .sort((a, b) => a.sort - b.sort)
40.     .map(({ value }) => value);
41.   return shuffledPlayers;
42. }

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.

L36: Set up the shuffle function and pass in the data variable.

L37: 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).

L38: This first step adds a randoms value to each of the array elements and stores it as an object.

L39: This sorts those random numbers. So, basically puts the elements in order.

L40-42: 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

44.  //Check score difference
45.  function calcScoreDiff(shuffledPlayers) {
46.    const midPoint = Math.ceil(shuffledPlayers.length / 2);
47.    const team1 = shuffledPlayers.slice(0, midPoint);
48.    const team2 = shuffledPlayers.slice(midPoint);

L45: Set up the function and pass in the shuffledPlayers array.

L46: 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.

L47-48: Get the first team of players, and the second team by using slice.

Creating the teams and calculating their total scores and the difference

49.    const t1Total = getTeamTotal(team1);
50.    const t2Total = getTeamTotal(team2);
51.    const scoreDiff = Math.abs(t1Total - t2Total);

L49-50: Get the team totals by calling the getTeamTotal function and passing each team of players.

L51: Work out the difference between the two team totals.

Check if score is fair, if not reshuffle the players

We now have our two teams and their respective scores but we need to check if they are fair. If they are within 1 I consider them fair. If they aren’t we need to reshuffle the teams again.

53.    if (scoreDiff < 2) {
54.      return shuffledPlayers;
55.    }
56.    else {
57.      shuffledPlayers = shuffle(shuffledPlayers);
58.      return calcScoreDiff(shuffledPlayers);
59.    }
60.  }

L53: Check to see if the score difference is less than 2.

L54-55: If it is, return the list of players.

L56-57: If it isn’t call the shuffle function again to make new teams.

L58: Then call this the calcScoreDiff function again (a recursive function).

L59-60: Close the else statement and function.

getTeamPlayers function

This function extracts the players’ names so that their scores are not seen when the teams are presented.

62.  //Get team players
63.  function getTeamPlayers(team){
64.    const teamPlayers = team.map((playerAndScore) => {
65.    return [playerAndScore[0]];
66.    });
67.    return teamPlayers;
68.  }

L63: Set up the getTeamPlayers function and pass in either team 1 or 2. Within this function we will refer to it as the generic team. For example, team1 looks like this.

L64-66: Use map to get the player’s name in each sub-array.

L67-68: Return it back to lines 21 and 22.

getTeamTotal function

This final function works out the how many points each team has by getting the player scores/ratings.

70.  //Get team totals
71.  function getTeamTotal(team) {
72.    return team.reduce((runningTotal, playerAndScore) => runningTotal + playerAndScore[1], 0);
73.  }

L71: Set up the getTeamTotal function. similar to the above function, pass in the team parameter, which will be either the team1 array or the team2 array from line 24 and 25.

L72-73: 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. It starts at 0 and adds the player’s score for each of the array elements. Then it returns that figure back to lines 24 and 25.

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 2-4, 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.

Automatic team maker in action

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 (you may wish to hide those, if you don’t want people guessing the indivdual ratings).

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.

More Apps Script ideas here.

a