Google Sheets OPR Calculator

[Rachel Lim]

Originally Posted by Caleb Sykes

Here would be my suggestions for speeding up the unique team search and creating the A matrix:
[...]

Originally Posted by Ether

There should be no looping involved.

Here's some pseudo-code which creates A, b, and T with one non-looping pass thru the 8column data

[...]

Thank you both for your suggestions--I really appreciate the help. I finally had some time to go through them, and combined both suggestions (or at least think I did) in the code below. I'm not sure if this is what the aTeamCol[] did, but I created another array to map teams to indexes (i.e. teamIndex[teamNum] = index, where teams[index] = teamNum). The new times I logged were as follows (I'm excluding the initialization time since it varies greatly each time I run it, which I suspect it has to do with my wifi connection because it runs much faster at home than when I'm out):

printing T (creating full team list, sorting it, getting unique values, printing) is too fast to time manually
printing b and A: ~21sec to create/print both
printing formula: fast

One weird thing I noted is that the first 188 rows of A (first 94 matches) are printed very quickly, then there's a long pause (at least 15sec the last time I ran it) before the remaining lines are printed. I'm not sure what's behind this, but I'll look into it more.

function calculateOPR2() {
  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 alliances = data.getRange(2, 1, numMatches, 8).getValues(); //get all data
  var allTeams = new Array();
  var teams = new Array();
  var teamIndex = new Array();
  var c = 0;
  
  data.getRange(1, 14).setValue('done initializing');

  // list of all teams
  for(var x = 0; x < numMatches; x++) {
    for(var y = 0; y < 6; y++) {
      allTeams[c] = alliances[x][y];
      c++;
    }
  }
  
  data.getRange(2, 14).setValue('list of all teams');
  
  // sorts all teams
  allTeams.sort(function(a, b) {return a - b;})
  data.getRange(3, 14).setValue('list of sorted teams');
  
  // list of all unique teams;
  c = 1;
  teams[0] = allTeams[0];
  teamIndex[teams[0]] = 0;
  OPR.getRange(2, 1).setValue(teams[0]);
  for(var x = 1; x < allTeams.length; x++) {
    if(allTeams[x] != allTeams[x-1]) {
      teams[c] = allTeams[x];
      teamIndex[teams[c]] = c;
      OPR.getRange(c+2, 1).setValue(teams[c]);
      c++;
    }
  }
  
  data.getRange(3, 14).setValue('list of unique teams');
  
  for(var x = 0; x < numMatches; x++) {
    // create and set b
    misc.getRange(2*x+1, teams.length+1).setValue(alliances[x][6]);
    misc.getRange(2*x+2, teams.length+1).setValue(alliances[x][7]);
    
    // create and set A
    for(var y = 0; y < teams.length; y++) {
      misc.getRange(2*x+1, y+1).setValue(0);
      misc.getRange(2*x+2, y+1).setValue(0);
    }
    misc.getRange(2*x+1, teamIndex[(alliances[x][0])]+1).setValue(1);
    misc.getRange(2*x+1, teamIndex[(alliances[x][1])]+1).setValue(1);
    misc.getRange(2*x+1, teamIndex[(alliances[x][2])]+1).setValue(1);
    misc.getRange(2*x+2, teamIndex[(alliances[x][3])]+1).setValue(1);
    misc.getRange(2*x+2, teamIndex[(alliances[x][4])]+1).setValue(1);
    misc.getRange(2*x+2, teamIndex[(alliances[x][5])]+1).setValue(1);
  }
  
  var formula = createFormula(teams.length, numMatches);
  OPR.getRange(2, 2).setFormula(formula);
}

Originally Posted by Ether

I just ran that code on my 10-year-old desktop PC using a simple non-compiled (scripting) language.

It took 220 milliseconds to read your 8column data (102 teams and 204 matches)
from a disk file and populate A, b, and T.

I would hope Google's scripting language could do the same in a comparable time.

I'm not sure what the time to create the matrices is exactly, but I'll try to time the difference between creating them and printing them sometime soon. I don't know how much time is taken up by printing to the spreadsheet, but that may account for some of the differences, because I've run similar programs to this outside of google scripts, with even larger data sets, and it's significantly faster.