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.

[Ether]

Originally Posted by Rachel Lim

I finally had some time to go through them, and combined both suggestions (or at least think I did) in the code below.

Hi Rachel.

I think you missed the boat here.

Your code has three passes through the matches, and the final pass has an inner loop which loops through the teams.

The pseudo-code I posted does a single pass through the 8col match data, with no inner loops in that single pass. That single pass accomplishes everything you need.

Give it a try!

[Rachel Lim]

Originally Posted by Ether

Hi Rachel.

I think you missed the boat here.

Your code has three passes through the matches, and the final pass has an inner loop which loops through the teams.

The pseudo-code I posted does a single pass through the 8col match data, with no inner loops in that single pass. That single pass accomplishes everything you need.

Give it a try!

Yep...I realized what you meant after I posted it.

I also figured out what's taking so much time: it's the repeated .getRange().setValue(). There's another function, setValues() that allows me to print the entire array at once that really speeds it up.

However, the array must be a consistent size, so I had to keep the loop to create T so I could initialize A. It also seems like fill() doesn't work in scripts, so I had to loop through T to set A to zero, otherwise when I printed out A it'd be filled with "NOT_FOUND". I did cut out the loop for b (not sure why I had it there in the first place).

Although I did manage to implement your pseudo-code, the only method I found to make that work would be to print A directly (instead of creating the array first), which took longer than looping. It also results in the OPRs not being sorted, which is a convenience I'd sacrifice a bit of time for.

Hopefully that now addresses your point? I'll post my updated code once I clean it up a bit.

[Ether]

Originally Posted by Rachel Lim

the array must be a consistent size, so I had to keep the loop to create T so I could initialize A.

Can't you simply initialize A with a single command (no looping) to a size greater than what you expect is needed? And when you so initialize it, doesn't it set all the array elements to zero (some languages do)?

It also seems like fill() doesn't work in scripts,

I find it hard to imagine the script language provides no way to zero an array without looping through all its elements.

so I had to loop through T to set A to zero

why do you need to "loop through T" to set A? Just initialize the entire array A to zero before beginning the processing of the 8col data

otherwise when I printed out A...

I meant to ask this earlier: What does "print out" mean in this context, and why do you need to do that?

It also results in the OPRs not being sorted, which is a convenience I'd sacrifice a bit of time for.

Please read my earlier post#15 in this thread.

Hopefully that now addresses your point?

Not really. But if you're satisfied with what you have I won't bug you

[Rachel Lim]

Originally Posted by Ether

Can't you simply initialize A with a single command (no looping) to a size greater than what you expect is needed? And when you so initialize it, doesn't it set all the array elements to zero (some languages do)?

I could do something like set A to the number of matches. I'm not sure if it's really worth it, or how much longer it'd take to set that many cells, but I guess that's the alternative.

Array values are set to null (I think--if I set a cell value to that, it comes out as either "undefined" or "NOT_FOUND" depending on whether I set an individual cell or range)

I find it hard to imagine the script language provides no way to zero an array without looping through all its elements.

I think javascript has 2 ways to do that: fill() and apply(), neither of which seems to work in google scripts.

why do you need to "loop through T" to set A? Just initialize the entire array A to zero before beginning the processing of the 8col data

Each row of A is T elements long, and I need to set each value to 0. I can't find another way to do it (see above).

I meant to ask this earlier: What does "print out" mean in this context, and why do you need to do that?

Setting the cells in a certain range to those values (using .getRange().setValues()). Basically google sheets has matrix formulas but google scripts doesn't seem to have an equivalent, so it's easier for me to put the matrices into a sheet, set the formula, and do the final calculations directly in the spreadsheet.

Please read my earlier post#15 in this thread.

I know I can sort it after, or set an equation to create another column sorted, but it makes it more complicated.

Not really. But if you're satisfied with what you have I won't bug you

I understand the reason to optimize it further, but in this case I think I'll spend the time adding in some additional functionality (like importing data automatically, multiple components, etc.). I never intended this to replace other, faster methods of calculating OPR, since other languages (especially matlab/octave) are just inherently faster at these calculations. I was mainly looking for an easy method where I could ask someone at a competition to run it without them needing to do more than type in values / press a button, and get a sorted list out of it.

This has been an interesting conversation though, so thank you for that.



I will be updating the posted spreadsheet with the new script soon (currently cleaning it up a bit, but it should be up by tonight) for anyone who wants it.