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
team2
...
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
blue
...
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
blueScore
...
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();
}
```