2020 google sheets match breakdown & OPR calculator

The 2020 version of my google sheets match breakdown / component opr calculator is linked below:

tinyurl.com/2020oprcalc

.

Similar to the 2017, 2018, and 2019 calculators, it does the following:

  • pulls all qualification match data
  • calculates opr, component oprs, and team/alliance averages

Changes for 2020 include:

  • team lookup tab:
    • pulls match breakdowns for all matches a specific team was in
  • calculated fields include:
    • opr
    • elo (calculated per event, so it’ll differ somewhat from Caleb’s calculations)
    • auto line / hang averages (true averages, since these fields are tracked per team)
    • auto and teleop lower/inner/outer ball opr
    • % of matches stage 1/2/3 was activated
    • % of matches the team hung and rung was level
  • a bunch of backend changes:
    • migrated to V8 runtime
    • all opr calculations are now done locally in the script

Most of the backend code has changed, and I tried my best to test it with week 0 data, but if you run into any issues please let me know and send a screenshot of the error if possible.

Thanks to Eugene, Solomon, Adam, and Sophie for their input/advice/testing.

18 Likes

Love the older sheets, but I didn’t find them until after the season. For the new sheet, if I select an event in the future (which most obviously are), and I click on the checkbox, it doesn’t do anything. Is that because there is not event data there yet?

I’m not able to get any events, including Week 0, to create new tabs.

2 Likes

I found it takes a little bit to load after hitting the button. I am not sure how many seconds I waited but I hit the button a few times thinking it was unresponsive. It might have been a full minute to load.

Is the button on the first sheet still there? It took a while to run for me, ~30 sec, and I had to click it twice – once to give permissions, and then once to actually run. It’ll delete itself once it’s done setting up the other sheets.

If that still doesn’t work, try adding this trigger manually:

  1. tools > script editor
  2. edit > current project’s triggers
  3. create a new trigger
  4. choose which function to run > myOnEdit, select event type > On edit
  5. give the sheet permissions

It’s possible that some g suites accounts don’t allow you to give additional permissions to sheets, but as far as I can tell, all personal gmail accounts and the default education accounts should work.

1 Like

Thanks, it’s working now. I guess I should have waited longer, but the internet here at home runs slow sometimes, so I’m going to blame that. :slight_smile:

I keep getting this error about 10-15 seconds after clicking the setup button:

Did it ask you for permissions before you got this error? I tried running it a few times without being able to duplicate it.

No - did not ask for permissions.

Maybe I am missing the request? Is it a pop-up?

Yeah, it should be a pop up

Just a quick info: The “auto inner port” is coded for total teleop points in “team lookup”

2 Likes

Thanks for catching that, just fixed it. All new copies of the sheet should be correct.

A couple questions. What is OPR and is it possible to rename the tabs at the bottom without the data/script stuff being messed up?

OPR is “Offensive Power Rating”
I think this has been the best explanation i’ve seen for the technical side for how it works and is calculated.

I cannot answer the next question.

As mentioned above, OPR is a linear regression calculation. It’s calculated on the assumption that each team has an average point contribution, and the alliance score is equal to the sum of those contributions:

red1opr + red2opr + red3opr = redScore
blue1opr + blue2opr + blue3opr = blueScore
(repeated for every match)

Since these equations don’t have a perfect solution, we use the least squares regression method to find the best estimate for the oprs.

TBA has post that explains more of the math here: https://blog.thebluealliance.com/2017/10/05/the-math-behind-opr-an-introduction/

You can’t rename the tabs without changing code. If you’d like it renamed to something specific I can probably tell you what to change, but you’ll have to repeat the steps for each event you add.

Not sure if youre aware, but I just made a copy (literally 30 seconds ago) of the sheet and when I went to add an event, got this


after accepting, the rest seemed to work fine. Not sure if this was supposed to be removed, or if Im actually just supposed to suppress the message, but I figured id let you know

Oops, forgot to update the sheet version when I changed it. If you made a copy in the last 3 hours, change “current version” to v1.0 (cell B1 on add_events_here).

1 Like

V1.1 is up, adding teams’ rank and ranking score to the OPR tab. Please continue sending me suggestions of features you’d like to see, either here on in a DM.

Note on version naming:
Integer increments indicate a bug fix
Decimal increments are generally feature updates

You can see the update you’ll get by making a new copy of the sheet by comparing the current to latest versions (top left of “add_events_here” tab)

There is an issue with OPR calculations at certain events – SCMB and CTNCT at least – where match data is pulled but OPR is not calculated. If you notice a similar issue with other events please let me know.