[nuclearnerd]

You have to make a private copy of the sheet in order to be able to view / edit the code. I haven't looked at the algorithm myself, but I've pasted the code below.

BTW, thanks for this - it's nice to have a portable calculator to use. I've been meaning to write one forever, but I never get to it and have to rely on other people posting OPR databases to CD.

function onEdit(e) {
  var s = e.source.getActiveSheet();
  if(e.range.getA1Notation() == 'L14' && s.getSheetName() == 'data') {
    if(e.value == 'calculate OPR') {
      calculateOPR();
    }
    else if(e.value == 'update OPR') {
      updateOPR();
    }
    else if(e.value == 'clear data') {
      clearData();
    }
    e.range.clearContent();
  }
}

// *** create custom menu ***
// same functionality as drop down menu or buttons
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('OPR calculations')
  .addItem('calculate OPR', 'calculateOPR')
  .addItem('update OPR', 'updateOPR')
  .addItem('clear data', 'clearData')
  .addToUi();
}


// *** calculates OPR from scratch ***
// input: alliances and scores in data tab:
// red1 red2 red3 blue1 blue2 blue3 redScore blueScore
// misc: sparse matrix and scores in data tab:
// team1 team2 team3 team4 ... teamN allianceScore      (teamX either 0 or 1 depending on whether they played that match)
// output: teams and OPRs, sorted ascending
// team1 OPR1
function calculateOPR() {
  var data = SpreadsheetApp.getActive().getSheetByName('data');
  var misc = SpreadsheetApp.getActive().getSheetByName('misc');
  var OPR = SpreadsheetApp.getActive().getSheetByName('OPR');
  
  var numMatches = data.getDataRange().getNumRows()-1; // number of matches
  var values = data.getRange(2, 1, numMatches, 8).getValues(); //get all data
  var scores = new Array();
  var teams = new Array();
  var alliances = new Array(3);
  for(var x = 0; x < 3; x++) {
    alliances[x] = new Array(numMatches*2);
  }
  var c = 0;
  var d = 0;
  
  // clear previous data
  clearCells();

  // create team, alliances, and score matrices
  for(var x = 0; x < numMatches; x++) {
    // list of all unique teams: team1 \n team2 \n ...
    for(var y = 0; y < 6; y++) {
      var unique = true;
      for(var z = 0; z < d+1; z++) {
        if(values[x][y] == teams[z]) {
          unique = false;
          break;
        }
      }
      if(unique == true) {
        teams[d] = values[x][y];
        d++;
      }
    }
    // list of all alliances: red \n blue \n ...
    for(var y = 0; y < 3; y++) {
      alliances[y][2*x] = values[x][y];
    }
    for(var y = 3; y < 6; y++) {
      alliances[y-3][2*x+1] = values[x][y];
    }
    // list of all scores: redScore \n blueScore \n ...
    for(var y = 6; y < 8; y++) {
      scores[c] = values[x][y];
      c++;
    }
  }
  
  // sort teams
  teams.sort(function(a, b) {return a - b;})
  // print out T matrix
  for(var x = 0; x < teams.length; x++) {
    OPR.getRange(x+2, 1).setValue(teams[x]);
  }
  
  // print out A matrix
  for(var x = 0; x < numMatches*2; x++) {
    for(var y = 0; y < teams.length; y++) {
      misc.getRange(x+1, y+1).setValue(0);
      for(var z = 0; z < 3; z++) { 
        if(alliances[z][x] == teams[y]) {
          misc.getRange(x+1, y+1).setValue(1);
          break;
        }
      }
    }
  }
  
  // print out b matrix
  for(var x = 0; x < numMatches*2; x++) {
    misc.getRange(x+1, teams.length+1).setValue(scores[x]);
  }
  
  // create and print formula
  var formula = createFormula(teams.length, numMatches);
  OPR.getRange(2, 2).setFormula(formula);
}


// *** updates OPR ***
// must have previously calculated with all teams
function updateOPR() {
  var data = SpreadsheetApp.getActive().getSheetByName('data');
  var misc = SpreadsheetApp.getActive().getSheetByName('misc');
  var OPR = SpreadsheetApp.getActive().getSheetByName('OPR');
   
  var numOldMatches = (misc.getDataRange().getNumRows())/2; // number of matches in previous calculation
  var numNewMatches = data.getDataRange().getNumRows() - 1; // current number of matches
  var numTeams = OPR.getDataRange().getNumRows() - 1; // number of teams
  
  
  var teams = OPR.getRange(2, 1, numTeams, 1).getValues();
  var matches = data.getRange(numOldMatches+2, 1, numNewMatches-numOldMatches+1, 8).getValues();
  
  // update A matrix
  for(var x = 0; x < numNewMatches-numOldMatches; x++) {
    for(var y = 0; y < numTeams; y++) {
      misc.getRange(2*numOldMatches + 2*x + 1, y+1).setValue(0);
      misc.getRange(2*numOldMatches + 2*x + 2, y+1).setValue(0);
      for(var z = 0; z < 3; z++) {
        if(matches[x][z] == teams[y]) {
           misc.getRange(2*numOldMatches + 2*x + 1, y+1).setValue(1);
        }
      }
      for(var z = 3; z < 6; z++) {
        if(matches[x][z] == teams[y]) {
          misc.getRange(2*numOldMatches + 2*x + 2, y+1).setValue(1);
        }
      }
    }
    misc.getRange(2*numOldMatches + 2*x + 1, teams.length+1).setValue(matches[x][6]);
    misc.getRange(2*numOldMatches + 2*x + 2, teams.length+1).setValue(matches[x][7]);
  }
  
  var formula = createFormula(numTeams, numNewMatches);
  OPR.getRange(2, 2).setFormula(formula);
}


// *** clears all non-header cells (data, misc, OPR tabs) ***
function clearData() {
  var data = SpreadsheetApp.getActive().getSheetByName('data');
  var rows = data.getDataRange().getNumRows();
  
  clearCells();
  data.getRange(2, 1, rows, 8).clearContent();
}


// input: integer (0, 1, ... 25, 26, 26, etc.)
// output: letter (A, B, ... Z, AA, AB, etc.)
// can only take up to ZZ
function columnToLetter(num) {
  var letter, letter1, letter2;
  if(num < 26) {
    letter = String.fromCharCode(65 + num);
  }
  else {
    letter1 = String.fromCharCode(65 + num/26 - 1);
    letter2 = String.fromCharCode(65 + num%26);
    letter = letter1 + letter2;
  }
  return letter;
}

// input: number of teams, number of matches
// output: formula
function createFormula(numTeams, numMatches) {
  var aRange = 'misc!A1:' + columnToLetter(numTeams-1) + numMatches*2;
  var bRange = 'misc!' + columnToLetter(numTeams) + '1:' + columnToLetter(numTeams) + numMatches*2;
  var formula = '=mmult(minverse(mmult(transpose(' + aRange + '), ' + aRange + ')), mmult(transpose(' + aRange + '), ' + bRange + '))';
  return formula;
}


// clears data from misc and OPR tabs (for calculations)
function clearCells() {
  var misc = SpreadsheetApp.getActive().getSheetByName('misc');
  var OPR = SpreadsheetApp.getActive().getSheetByName('OPR');
  var rows = OPR.getDataRange().getNumRows();
  
  misc.getDataRange().clearContent();
  OPR.getRange(2, 1, rows, 2).clearContent();
}