Google-sheets – How exactly does sharing work in Google Drive

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.

You can achieve this by using an array of importrange() functions. This cannot import the formatting information, which is not possible without Apps Script, but it will pull in multiple sheets and aggregate them into one list, which you can then sort with an additional formula. Keep in mind that each imported sheet will have to have the same number of columns. For example, if you have four sheets to import with 4 columns each, the import formula will be as follows:

={
importrange("url_to_sheet_1", "Approved!$A:$D");
importrange("url_to_sheet_2", "Approved!$A:$D");
importrange("url_to_sheet_3", "Approved!$A:$D");
importrange("url_to_sheet_4", "Approved!$A:$D")
}