Tracking weekly picks for NFL games

This code is for a spreadsheet that tracks weekly picks for NFL games. Four friends and I pick winners for every NFL game each week. Suppose there are 18 games being played this week; for each game, we all pick a winner, and also a level of confidence in that winner. We rank each game 1 to 18 based on our confidence-- so if a really good team plays a really bad team, we'll pick the good team for 18; if two teams are evenly matched, we'll pick a team for 1. (Each rank may only be used once each week per player.) If our team wins, we get the number of points we put on the game. If our team loses, we get no points.

The spreadsheet displays all of the games each week, and we enter our rankings under our column in the row of the corresponding team (see spreadsheet), and on the same side as the team we want to win. Then when a game is over, we just have to enter "w" or "l" in the appropriate column, and all player's scores are automatically calculated. The code also attempts to inform us as soon as a player is eliminated from contention for top score of the week.

Here is the currently implemented method of setting an elimination status:

• Start by taking the current scores into account.

• Consider each player vs each other player (aka opponent) one at a time.

• If there is ever a time when a player cannot beat one or more of his opponents, then he is eliminated.

To check whether he can beat an opponent is tricky, though-- because the best case scenario for a player is not necessarily getting all of his picks right. Suppose they both pick the same team, but the opponent ranks that game higher-- it's actually better for the player (at least against this opponent) for the team he picked to lose.

So instead, I calculate a best possible scenario score (again, for each player against each individual opponent). It starts with each player's current score. Then for all unplayed games, if the player and the opponent picked opposite sides, then the player's pick is added to

You can get rid of repetition in several places.

The long if statement could be replaced with this:

if (lastCellModified.indexOf(['A10', 'A11', 'A12', 'A13', 'A14', 'A15', 'A16', 
    'A17', 'A18', 'A19', 'A20', 'A21', 'A22', 'A23', 'A25']) > -1) {

or this:

if (lastCellModified.length == 3 && lastCellModified >= "A10" && lastCellModified <= "A25")

Setting variables for players and opponents can be reduced to:

var pScore = sheetArray[player][1];
var leftPCol = player % 5 + 1;
var rightPCol = 13 - leftPCol;
var pElimRow = player;

The list of sheet.getRange('D2') etc. could be replaced with a loop:

for (var i = 1; i < 6; i++) {
    sheet.getRange("D" + (i + 1)).setValue(sheetArray[i][3]);
}

and similarly for the ss.getRange... list:

for (var i = 1; i < 8; i++) {
    var range = ss.getRange('G' + (i + 1));
    range.copyFormatToRange(sheet, 7, 15, i, 1);
    range.copyValuesToRange(sheet, 7, 15, i, 1);
}

See the full code with a few other changes here

EDIT: I looked at this in a bit more detail. Ultimately what I think you need is to go through every possible combination of outcomes for the remaining games, and check who is the winner under each scenario, and eliminate the players who are not winners under any scenarios. (There might be some shortcut method, but as you have already noted, your solution of comparing pairs of players does not quite do it.) This does involve looking at 2 to the power of N scenarios where N is the number of remaining games. That should be manageable for, say, 16 games, although it may get a little slow. I've altered the script to do this here but couldn't figure out how to test it.

Whether you use this or not, I recommend using the first part of that code, which reads the players' current scores, their picks, and their bets, into arrays with meaningful names at the beginning. This simplifies the rest of the code a lot as you can deal with those arrays instead of having to keep thinking about what reference you need in the spreadsheet.

StackExchange Code Review Q#19856, answer score: 3