Google Sheets – Calculate Score and Ranking

google sheetsgoogle-apps-script

I want to create a Google Spreadsheet which will work as a ranking system for a game.

  • One sheet, "Ranking", should display the current ranking of the players, based on their points.
  • Another sheet, "Matches", could be used to add matches.

Each match has one winner and one loser, where the winner should earn 10% of the loser's points (the loser does not lose any points).

The points in the "Ranking" sheet should be updated as new matches are added (but points should obviously only be added once per match).

View a simple example spreadsheet here:
https://docs.google.com/spreadsheet/ccc?key=0AqNThcZwHAYldEMzYUJmTU1TUW5WaVNSUlJhY1BGT0E&usp=sharing

Any tips on how this could be achieved?

Best Answer

This calls for a Google Apps Script, but not a very complicated one.

First, you need to decide a starting point for each player. Let's say each player starts on 100 points.

The following updateRankings function iterates through all matches, and calculates match points for each match. Player ranking points are updated after each match.

var MATCHES_SHEET_NAME = "Matches";
var RANKING_SHEET_NAME = "Ranking";
var RANKING_PLAYER_NAME_COL_IDX = 0;
var RANKING_PLAYER_POINTS_COL_IDX = 1;
var RANKING_INIT_POINTS = 100;
var WINNER_COL_IDX = 0;
var LOSER_COL_IDX = 1;
var MATCH_POINTS_COL_IDX = 2;
var MATCH_POINTS_PCT = 10;

function updateRankings() {
  var matchesRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(MATCHES_SHEET_NAME).getDataRange();
  var matchesValues = matchesRange.getValues();
  var rankingRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(RANKING_SHEET_NAME).getDataRange();
  var rankingValues = rankingRange.getValues();
  var players = {};
  for (var i = 1; i < rankingValues.length; i++) { // Collect player names
    var rankingRow = rankingValues[i];
    var playerName = rankingRow[RANKING_PLAYER_NAME_COL_IDX];
    players[playerName] = RANKING_INIT_POINTS; // Each player starts at 100 points
  }

  for (var i = 1; i < matchesValues.length; i++) { // Iterate through all matches
    var matchRow = matchesValues[i];
    var winnerName = matchRow[WINNER_COL_IDX]
    var loserName = matchRow[LOSER_COL_IDX];
    var matchPoints = players[loserName] * MATCH_POINTS_PCT / 100; // Loser's ranking points * 10%;
    players[winnerName] += matchPoints; // Add points to winner's ranking
    matchesValues[i][MATCH_POINTS_COL_IDX] = matchPoints; // Set match points column
  }

  // Update the ranking sheet with new ranking points
  for (var i = 1; i < rankingValues.length; i++) {
    var rankingRow = rankingValues[i];
    var playerName = rankingRow[RANKING_PLAYER_NAME_COL_IDX];
    rankingValues[i][RANKING_PLAYER_POINTS_COL_IDX] = players[playerName];
  }

  // Finally, write the updated value arrays back to the sheet
  matchesRange.setValues(matchesValues);
  rankingRange.setValues(rankingValues);
}

You need to decide when the script should run (how often the ranking should be updated). Take a look at my example spreadsheet, where I have created a button that runs the script when clicked.

Related Topic