Google Sheets OPR Calculator

[Rachel Lim]

I've been trying to find an easy way to calculate OPR (type in data, press a button, get OPRs) that would run on any device without requiring you to buy/download any program. After testing a couple of different methods, I ended up finding a way to run it entirely in google sheets, by making use of a combination of google scripts and sheets' array formulas.

Link to sheet:
https://docs.google.com/spreadsheets...it?usp=sharing (make a copy of it to edit)

Example input:
- green box = where data goes
- orange boxes = ways to run program (I was playing around with different ways to run custom functions, but they all do the same thing)
- Note: the gray "run function" box is the only way to run it on mobile


Example output:



Misc notes:
- It's definitely not fast, although using the update button instead of calculating it from scratch each time really helps
- Running all MICMP data (102 teams, 204 matches) took about 45sec. For reference, doing the same calculations in matlab (reading in data and outputting to a text file) took around 0.013sec
- Times are from my laptop. I didn't time it, but it's definitely slower on my phone
- I believe the first size issue you'll hit is the 255 team limit (256 is the maximum number of columns per tab, and 1 column is needed for the score vector), although it is possible you'll hit the cell size limit before that



I'm planning a couple of additions, including adding the ability to calculate multiple component OPRs at once, trying to clean up the script and see if it can be sped up, and seeing if I can do a get request in the sheet itself and automatically populate the data. I'll also work on a game-specific one next year if I have time.

If you have any other suggestions on what else would be helpful, let me know and I'll try to add it. Also, if you find any bugs in it please let me know--I tested it several times, but it's entirely possible something slipped through.