Google Sheets Match Breakdowns and Component OPRs

The next version of my google sheets OPR calculator is below, with the following features:

  • Automatically imports qualification match score breakdowns for any event this year
  • Calculates OPR and 8 component OPRs (note: all are for point values except for teleop_fuel_low and teloep_fuel_high which are fuel counts due to the data reported)
  • Nicer/more intuitive interface (hopefully)
  • All run locally in a google spreadsheet

Link: https://docs.google.com/spreadsheets/d/1uXSoyYmlOQfV4NCz4uOggjCpt39-QxJtAdsHGYKNYAg/edit?usp=sharing

To use: file > make a copy
To edit the code: tools > script editor
To add an event: add_events_here tab > enter event code in that box > press the button (or edit the existing tabs, but be sure to stick to the same format)
To get match breakdowns / OPRs: go to event tab > press that button

Things I will be trying to add (but that I’m not sure when I’ll have time to):

  • Include playoff match data
  • Add ability to run functions on mobile devices
  • Only add new matches rather than deleting/reprinting everything if data has been pulled before
  • Pull match data live for all events (also depends on whether I can confirm if I’m correctly checking the last-modified header so I’m not constantly getting data from TBA)

I tested this out with some of the week 1 events and think I fixed all the bugs that came up, but if you find any others (or have any other feedback / requests for other additions) please let me know.

You’re my hero.

1 Like

This is amazing, thank you! :slight_smile:

I will definitely be using this this weekend. Great work!

I’ve been running an ImportJSON custom script/function which is constantly updating. I ran out of fetches per day with Google Sheets so I’ve been trying all kinds of convoluted workarounds.

This is amazing - thank you!

This is great. Fills the Ed Law void.

Rachel, this is awesome.

Could you add a foulpoints_opr column.

Also Getting point values for the columns wasn’t too hard, you just need to add a few formulas. I did it for MI Southfield on my own copy of the spreadsheet.

I actually also fouls both earned and committed. See https://docs.google.com/spreadsheets/d/1QVVeT5HT8avOMLegl-sJHllJDPr2kPLRaT6bzfd_zuM/edit?usp=sharing

This is really great. Thanks for doing this!

component OPR should be abbreviated cOPR and pronounced “copper”

Thanks everyone! I’m glad this is useful to you–it’s been something I’ve been trying to do for a long time. If there are any other requests for things you’d like added, let me know. I’m working on the things mentioned in my first post and hopefully some of then (especially running from phones) will be ready for week 2 events.

Google sheets should allow 20,000 URL fetches per day with a with a regular gmail account, which comes out to about 1 fetch request every 2 min for 30 events/week, so I don’t think you should hit a limit unless you’re hitting the 100mb/day limit? Either way, I’m glad this is useful for you.

Source: https://developers.google.com/apps-script/guides/services/quotas

Sorry for the delay–just got around to adding that, and they should be in columns K and L in the OPR pages. Your changes are a nice way of doing it, but I added those fields into the script itself in case anyone else wants it (I also find the pages run faster with fewer formulas, but that might just be me)

Converting fuel count to fuel points is easy, but I’m not sure which ones make more sense. I decided to stick with fuel count since it’s the format FIRST provides the data in, so I thought it’d make more sense when comparing against the match breakdowns. I can change it, or add both if that would be helpful though.

I apologize if there was anyone trying to copy the sheet while I was updating it. Hopefully I was fast enough that no one copied it while it was temporarily broken, but if your copy isn’t working, please try making a copy of it now.

I’ve been looking for something food related, but that works too.

Yeah - apparently the way it was set up, it was constantly fetching which could add up quickly. This combined with grabbing images quickly exceeded some quota.

The way I was solving this until I saw your answer was to have an empty box and when an “X” was placed in the box, the fetch would invoke - so - I would only need to do this once per day on average. Your solution is much better, though.

Last minute update, hopefully in time for week 2 events:

  • Editing from mobile devices (at least android devices) should be working
  • Issue with clearing data on OPR sheet fixed

Notes:

  • Use the “run function” box under the “status” box

  • If you already made a copy of the spreadsheet and don’t want to make another copy, try following the steps below (note that this only adds the editing from phones and not the OPR range thing):

  • Open the spreadsheet > Tools > Script Editor > paste the code at the end of this post onto the top of the script that opens up > Save

  • In script editor > Edit > Current project’s triggers > Add a new trigger > [in the three dropdown boxes click these three things] myOnEdit, From spreadsheet, On edit > Save

Hopefully there are no more bugs since I don’t think I’ll be able to make any more changes before week 2 competitions are over. However, if there are, let me know here (or find me at CVR if you’ll be there) and I’ll try to fix it. It’s running fine (slow but working) from the Sheets app on my android phone, but I don’t have access to any iPhones to test on.

Also, if you’re wondering why I didn’t just use the onEdit(e) function, it’s because for some reason google doesn’t allow UrlFetchApp.fetch from within onEdit, despite explicitly stating it should be allowed, so you have to set up a custom trigger (this confusion was a large part of the reason I didn’t manage to get this working earlier)

https://developers.google.com/apps-script/guides/services/authorization?
https://code.google.com/p/google-apps-script-issues/issues/detail?id=185


function myOnEdit(e) {
  var sheetName = e.source.getActiveSheet().getSheetName();
  var cellValue = e.range.getValue()
  if(sheetName.indexOf("_") != -1)
    return;
  if(e.range.getA1Notation() != "B4")
    return;
  e.range.clearContent();
  if(cellValue == "update data only")
    getMatchData();
  else if(cellValue == "update data and calculate OPR")
    getMatchDataAndOpr();
}

If anyone is trying to run it from mobile, you need to set the trigger yourself even if you made a copy of the sheet. The instructions in my post above should work, but you do need to do this from a computer. (The sheets app doesn’t let you edit scripts) Sorry for not realizing this - just tried this at CVR with a copy and realized it didn’t copy the trigger.

Trying to run my copy to get CVR OPR, getting the attached error for OPR calc.
I am running from a laptop.





Not enough matches have been played yet for OPR to be calculated. If it still doesn’t work later, or if you’re getting the same error with other events that have finished/played more matches, please let me know.

Hey there, question about component OPRs. It seems like they do not sum up to the total OPR. For example, with 254, auto_opr + teleop_rotors_opr + teleop_fuel_low_opr + teleop_fuel_high_opr + ready_for_take_off_opr + fouls_earned_opr = 211.5. However 254’s opr is just 150.15. I was under the impression that component OPR is additive, (and auto_opr = auto_mobility_opr + auto_rotors_opr + auto_fuel_opr). However, if it is not, I’m curious why there’s such a disparity between real OPR and summed components.
http://i.imgur.com/RYNpAFG.png

OPR should be first calculable after teams/2 matches, rounded up.

Have you looked into WMPR stats for this game? wgardner has a great paper on here about different statistical models for FRC and FTC, including WMPR, OPR, and the MMSE model that TBA now uses for /insights.

I ran WMPR and OPR against MIWAT quals, and WMPR was 85% accurate, while OPR was only 77.5% accurate.

Teleop fuel low and teleop fuel high have units of fuel quantity, not units of points/pressure.

Would you mind sharing your testing methodology in more detail? My tests last year found that WMPR was too overfit to have any useful predictive power.

Absolutely. Attached are the results of running the following code, with minor analysis:

import sys
import requests
import numpy as np

from enum import Enum

BASE_ROUTE = "http://www.thebluealliance.com/api/v3"
AUTH_KEY =  PUT YOUR TBA AUTH KEY HERE ]]

class ModelType(Enum):
    SIMPLE = 'simple'
    KEYS = 'keys'

class DetailType(Enum):
    ALLIANCES = 'alliances'
    DISTRICT_POINTS = 'district_points'
    INSIGHTS = 'insights'
    OPRS = 'oprs'
    RANKINGS = 'rankings'


def format_route(route, **kwargs):
    for arg in kwargs:
        route = route.replace("<{}>".format(arg), kwargs[arg])
    return route


def call_tba(route):
    return requests.get(BASE_ROUTE + route, headers={'X-TBA-Auth-Key': AUTH_KEY})


def get_tba_matches(event_key, model_type):
    return call_tba(format_route('/event/<event_key>/matches/<model_type>',
                                 event_key=event_key, model_type=model_type.value))


def get_tba_teams(event_key, model_type):
    return call_tba(format_route('/event/<event_key>/teams/<model_type>',
                                 event_key=event_key, model_type=model_type.value))

def get_tba_event_detail(event_key, detail_type):
    return call_tba(format_route('/event/<event_key>/<detail_type>',
                                 event_key=event_key, detail_type=detail_type.value))

def get_tba_event_list(year, model_type):
    return call_tba(format_route('/events/<year>/<model_type>',
                                 year=year, model_type=model_type.value))

def get_tba_match_detail(match_key):
    return call_tba(format_route('/match/<match_key>',
                                 match_key=match_key))

def teams_that_played(match_simple):
    teams = ]

    for match in match_simple:
        teams.extend(match'alliances']'red']'team_keys'])
        teams.extend(match'alliances']'blue']'team_keys'])

    return list(set(teams))


def build_base_matrices(quals, team_keys):
    Ar = ]
    Ab = ]
    Mr = ]
    Mb = ]

    for match in quals:
        _ar = [0]*len(team_keys)
        _ab = [0]*len(team_keys)

        red = match'alliances']'red']
        blue = match'alliances']'blue']

        for team in red'team_keys']:
            _ar[team_keys.index(team)] = 1

        for team in blue'team_keys']:
            _ab[team_keys.index(team)] = 1


        Ar.append(_ar)
        Ab.append(_ab)
        Mr.append([red['score']])
        Mb.append([blue['score']])

    return np.array(Ar), np.array(Ab), np.array(Mr), np.array(Mb)

def print_teams_and_stat(stat, team_keys, team_stats):
    stats = {team_key: power for (team_key, power) in
             zip(team_keys, team_stats)}

    print('Team	:    {}
----------------------'.format(stat))
    for team in stats:
        val = stats[team][0]
        div = str(int(val))
        rem = str(val)[len(div)+1:]:3]
        print(str.format('{: 5d}	:  {: >4s}.{}', int(team[3:]), div, rem))

def diag_solve(A, M):
    tmp_1 = np.matmul(np.transpose(A), A)
    tmp_2 = np.matmul(np.transpose(A), M)

    return np.matmul(np.linalg.inv(np.diag(np.diag(tmp_1))), tmp_2)

def inv_solve(A, M):
    tmp_1 = np.matmul(np.transpose(A), A)
    tmp_2 = np.matmul(np.transpose(A), M)

    return np.matmul(np.linalg.inv(tmp_1), tmp_2)

def pinv_solve(A, M):
    tmp_1 = np.matmul(np.transpose(A), A)
    tmp_2 = np.matmul(np.transpose(A), M)

    return np.matmul(np.linalg.pinv(tmp_1), tmp_2)

def sopr_solve(Ao, Ad, Mo):
    tmp_1 = np.matmul(np.transpose(Ao), Ao)
    tmp_2 = np.matmul(np.transpose(-Ao), Ad)
    tmp_3 = np.matmul(np.transpose(-Ad), Ao)
    tmp_4 = np.matmul(np.transpose(Ad), Ad)

    tmp_5 = np.concatenate((np.transpose(Ao), np.transpose(Ad)), axis=0)

    tmp_6 = np.concatenate((tmp_1, tmp_2), axis=1)
    tmp_7 = np.concatenate((tmp_3, tmp_4), axis=1)
    tmp_8 = np.concatenate((tmp_6, tmp_7), axis=0)

    tmp_9 = np.matmul(tmp_5, Mo)

    return np.matmul(np.linalg.pinv(tmp_8), tmp_9)


def event_prediction_accuracy(event_code):
    match_keys = get_tba_matches(event_code, ModelType.SIMPLE).json()
    team_keys = teams_that_played(match_keys)

    quals = [key for key in match_keys if key['comp_level'] == 'qm']

    Ar, Ab, Mr, Mb = build_base_matrices(quals, team_keys)

    Ao = np.concatenate((Ar, Ab), axis=0)
    Ad = np.concatenate((Ab, Ar), axis=0)
    Aw = np.subtract(Ar, Ab)
    Mw = np.subtract(Mr, Mb)

    Mo = np.concatenate((Mr, Mb), axis=0)

    Mwp = np.concatenate((Mw, -Mw), axis=0)

    Oave = sum(Mo)/(3 * len(Mo))

    # Contributions based on average offensive production
    # O_ave = diag_solve(Ao, Mo)
    # O_a = O_ave - 2*Oave

    # D_ave = diag_solve(Ad, Mo)
    # D_a = 3*Oave - D_ave

    # W_ave = np.add(O_a - Oave, D_a)

    # OPR
    O_opr = inv_solve(Ao, Mo)

    # CCWM
    # W_ccwm = inv_solve(Ao, Mwp)

    # WMPR
    W_wmpr = pinv_solve(Aw, Mw)

    # C_cpr = W_wmpr + Oave

    # DPR
    # D_dpr = inv_solve(Ad, Mo)
    # D_dprb = Oave - D_dpr


    # Simultaneous OPR, DPR
    # S_sopr = sopr_solve(Ao, Ad, Mo)
    # [O_sopr, D_sdpr] = np.split(S_sopr, 2)

    # W_swmpr = np.add(O_sopr - Oave, D_sdpr)

    # C_scpr = np.add(O_sopr, D_sdpr)

    # print_teams_and_stat("C_cpr", team_keys, C_cpr)


    wmprs = {team_key: power for (team_key, power) in zip(team_keys, W_wmpr)}
    oprs = {team_key: power for (team_key, power) in zip(team_keys, O_opr)}

    opr_acc = 0
    wmpr_acc = 0
    for match in quals:
        red_opr_score = sum([oprs[team] for team in match'alliances']'red']'team_keys']])
        blue_opr_score = sum([oprs[team] for team in match'alliances']'blue']'team_keys']])
        opr_prediction = red_opr_score > blue_opr_score

        red_wmpr_score = sum([wmprs[team] for team in match'alliances']'red']'team_keys']])
        blue_wmpr_score = sum([wmprs[team] for team in match'alliances']'blue']'team_keys']])
        wmpr_prediction = red_wmpr_score > blue_wmpr_score

        result = match'alliances']'red']'score'] > match'alliances']'blue']'score']

        opr_acc += 1 if opr_prediction == result else 0
        wmpr_acc += 1 if wmpr_prediction == result else 0

    print('{},{},{}'.format(event_code, opr_acc/len(quals)*100, wmpr_acc/len(quals)*100))

def mass_evaluation():
    event_keys = get_tba_event_list('2017', ModelType.KEYS).json()

    print('Event, OPR Accuracy, WMPR Accuracy')
    for event in event_keys:
        try:
            event_prediction_accuracy(event)
        except:
            pass



def main():
    if len(sys.argv) > 1:
        event_prediction_accuracy(sys.argv[1])
    else:
        mass_evaluation()


if __name__ == "__main__":
    main()

Based on the results, at 83% of events so far this year, WMPR has provided a better fit than OPR at predicting matches. Over all events this year, WMPR is 4.75% better at predicting match outcomes than OPR.

Results.csv (6.99 KB)


Results.csv (6.99 KB)