2022 Google Sheets OPR Calculator

The 2022 version of my google sheets match breakdown / component opr calculator is here!
2022 Component OPR Calculator - Google Sheets


Similar to the 2020 and earlier versions, it has tabs to:

  • Pull qualification match data
  • Calculate OPR, select component OPRs, and taxi/endgame averages
  • Lookup a team and pull breakdowns from their previous matches
  • Lookup a match and pull OPRs/averages for the teams, and breakdowns for all 6 teams

New to this year:

  • Ability to add just certain tabs:
    When adding a new event, you can add just the functionality you want by deselecting the others
    image

  • Every tab can be run separately:
    OPR and breakdown tabs are run through the checkbox at the top. Lookup tabs are run by typing in a new match or team number

  • Functions to pull certain stats directly into the spreadsheet:
    getTeams(), getQuals(), getQualsAlliances(), getQualsSchedule(), getOpr() are now directly accessible from the spreadsheet itself
    These are always accessible in copies of the calculator, and can be added to any other spreadsheet as a library (instructions in the next post)

I unfortunately wasn’t able to test it as thoroughly as I have in the past, so if you notice any issues when using it please let me know.

17 Likes

Spreadsheet functions:

I added five functions that can be called directly from spreadsheet cells. These functions are always accessible from any copy of the calculator, even if all original sheets are deleted.

# returns the team list for the event_key
# event_key needs to match TBA's event_key format, e.g. 2022week0
=getTeams(event_key)

# returns the list of quals in format [[qm1], [qm2], ...]
# optional: if team_number is included, returns list of quals where team_number plays
=getQuals(event_key, team_number)

# returns the list of quals alliances in format [[R1, R2, R3], [B1, B2, B3]]
# optional: if team_number is included, returns list of alliances which team_number plays on
=getQualsAlliances(event_key, team_number)

# returns the list of quals matches in format [[qm1, R1, R2, R3, B1, B2, B3], ...]
# optional: if team_number is included, returns list of matches which team_number plays on
=getQualsSchedule(event_key, team_number)

# returns OPRs for the event_key
# if scoreName is not included, regular OPR (totalPoints) is returned
# if scoreName is the name of a match breakdown component, that respective cOPR is returned
# if scoreName is "auto low", "auto high", "teleop low", or "teleop high", that cOPR (the summation of the 4 related breakdown components) is returned
# if scoreName is "taxi", the taxi % is returned
# if scoreName is "endgame", the four endgame % are returned
# if scoreName is "all", [OPR, taxi %, auto low, auto high, teleop low, teleop high, endgame %] are returned in that order
=getOpr(event_key, scoreName)

These can also be added to any other spreadsheet by opening App Script


Pasting the following code into the editor (in Code.gs):

/**
 * Returns the eTag and content text from a TBA API endpoint
 * 
 * @param {str} url          API endpoint to fetch
 * @return {[str, Object]}   ETag and fetched content
 */
function tbaQuery(url) {
  var baseUrl = 'https://www.thebluealliance.com/api/v3/'; 
  var options = {
    "headers" : {'X-TBA-Auth-Key': 'dpDzbLXkdyLTauG975fM97WEMVJthwoRjJdDd5YxtVvS4bF2PMELVsvUCbh622sE'}
  }
  var httpResponse = UrlFetchApp.fetch(baseUrl + url, options);
  
  var lastMod = httpResponse.getHeaders()['ETag'];
  var contentText = JSON.parse(httpResponse.getContentText());
  return [lastMod, contentText];
}

/**
 * Returns a column array of team numbers from a given event
 * 
 * @param {str} event_code   Event to get team numbers for
 * @return {Array}           Nx1 array of team numbers
 */
function getTeams(event_code) {
  var [,data] = tbaQuery('event/' + event_code + '/teams/simple');
  var teams = data.map(a => a.team_number); // array of team numbers
  teams.sort((a,b) => a - b); // sorted numerically

  return teams
}

/**
 * Returns an array of the qualification match schedule in format [qm1, R1, R2, R3, B1, B2, B3]
 * 
 * @param {str} event_code   Event to get schedule for
 * @param {int} team_number  Optional: team number to get schedule for. If undefined, all matches are returned
 * @return {Array}           Nx7 array of match numbers and team numbers
 */
function getQuals(event_code, team_number) {
  var data;
  
  if(typeof team_number !== "undefined")
    [,data] = tbaQuery('team/frc' + team_number + '/event/' + event_code + '/matches/simple');
  else
    [,data] = tbaQuery('event/' + event_code + '/matches/simple');

  if(data.length == 0)
    return;

  var matches = data.filter(a => a.comp_level == 'qm'); // keep quals matches only
  matches.sort((a, b) => a.match_number - b.match_number); // sort by match number

  matches = matches.map(a => 'qm' + a.match_number)

  return matches;
}

/**
 * Returns an array of the qualification alliances in format [R1, R2, R3],  [B1, B2, B3] 
 * 
 * @param {str} event_code   Event to get alliances for
 * @param {int} team_number  Team number to get alliances for. If undefined, both alliances for all matches are returned
 * @return {Array}           Nx3 or 2Nx3 array of team numbers
 */
function getQualsAlliances(event_code, team_number) {
  var data;
  
  if(typeof team_number !== "undefined")
    [,data] = tbaQuery('team/frc' + team_number + '/event/' + event_code + '/matches/simple');
  else
    [,data] = tbaQuery('event/' + event_code + '/matches/simple');

  if(data.length == 0)
    return

  var matches = data.filter(a => a.comp_level == 'qm'); // keep quals matches only
  matches.sort((a, b) => a.match_number - b.match_number); // sort by match number

  var matchData = alliancesFromEventData(matches);

  if(typeof team_number !== "undefined")
    matchData = matchData.filter(a => a.includes(String(team_number)))

  return matchData;
}

function alliancesFromEventData(data) {
  var alliances = [];
  data.forEach(matchRawData => {
    alliances.push(matchRawData['alliances']['red']['team_keys'].map(key => key.substring(3)));
    alliances.push(matchRawData['alliances']['blue']['team_keys'].map(key => key.substring(3)));
  })
  return alliances;
}

function scoreFromEventData(data, scoreNames) {
  if(Array.isArray(scoreNames) == false)
    scoreNames = [scoreNames]

  var scores = [];
  data.forEach(matchRawData => {
    scores.push(scoreNames.map(s => matchRawData['score_breakdown']['red'][s]));
    scores.push(scoreNames.map(s => matchRawData['score_breakdown']['blue'][s]));
  })

  return scores;
}


/**
 * Returns an array of the qualification match schedule in format [qm1, R1, R2, R3, B1, B2, B3]
 * 
 * @param {str} event_code   Event to get schedule for
 * @param {int} team_number  Optional: team number to get schedule for. If undefined, all matches are returned
 * @return {Array}           Nx7 array of match numbers and team numbers
 */
function getQualsSchedule(event_code, team_number) {
  var data;
  
  if(typeof team_number !== "undefined")
    [,data] = tbaQuery('team/frc' + team_number + '/event/' + event_code + '/matches/simple');
  else
    [,data] = tbaQuery('event/' + event_code + '/matches/simple');

  if(data.length == 0)
    return;

  var matches = data.filter(a => a.comp_level == 'qm'); // keep quals matches only
  matches.sort((a, b) => a.match_number - b.match_number); // sort by match number

  matches = matches.map(a => {
    var allianceData = ['qm' + a.match_number]; // qm#
    allianceData = allianceData.concat(a['alliances']['red']['team_keys'].map(key => key.substring(3))); // R1, R2, R3
    allianceData = allianceData.concat(a['alliances']['blue']['team_keys'].map(key => key.substring(3))); // B1, B2, B3
    
    return allianceData;
  })

  return matches;
}

/**
 * Returns OPRs from qualification matches that have been played
 * 
 * @param {str} event_code        Event to get OPRs for
 * @param {str, Array} scoreName  Optional: score breakdown to use for cOPR. If undefined, returns totalPoints OPR.
 * @return {Array}                Column 1: teams. Column 2-N: OPRs
 */
function getOpr(event_code, scoreName='totalPoints') {
  if(scoreName == 'all') {
    scoreName = ['totalPoints', 'taxi', 'auto low', 'auto high', 'teleop low', 'teleop high', 'endgame']
  }

  if(Array.isArray(scoreName) == false)
    scoreName = [scoreName]
  else
    scoreName = scoreName.flat(); // arrays read from function({a, b}) in as  [[a, b]]

  var [,data] = tbaQuery('event/' + event_code + '/matches');
  
  if(data.length == 0)
    return;

  var matches = data.filter(a => a.comp_level == 'qm' && a.score_breakdown != null); // keep quals matches only
  matches.sort((a, b) => a.match_number - b.match_number); // sort by match number
  
  
  
  var alliances = alliancesFromEventData(matches);
  var teams = [...new Set(alliances.flat())].sort((a,b) => a - b); // in case teams that registered didn't play
 
  var A = formA(teams, alliances);

  var scores = [];
  scores.push(teams);
  scoreName.forEach(s => {
    if(s == 'endgame') {
      scores.push(team_avg(teams, alliances, scoreFromEventData(matches, ['endgameRobot1', 'endgameRobot2', 'endgameRobot3']), 'Low').flat());
      scores.push(team_avg(teams, alliances, scoreFromEventData(matches, ['endgameRobot1', 'endgameRobot2', 'endgameRobot3']), 'Mid').flat());
      scores.push(team_avg(teams, alliances, scoreFromEventData(matches, ['endgameRobot1', 'endgameRobot2', 'endgameRobot3']), 'High').flat());
      scores.push(team_avg(teams, alliances, scoreFromEventData(matches, ['endgameRobot1', 'endgameRobot2', 'endgameRobot3']), 'Traversal').flat());
    }
    else if(s == 'taxi') {
      scores.push(team_avg(teams, alliances, scoreFromEventData(matches, ['taxiRobot1', 'taxiRobot2', 'taxiRobot3']), 'Yes').flat());
    }
    else if(s == 'auto low') {
      var autoLow = scoreFromEventData(matches, ['autoCargoLowerBlue', 'autoCargoLowerFar', 'autoCargoLowerNear', 'autoCargoLowerRed']);
      autoLow = autoLow.map(a => a.reduce((a, b) => a + b));
      scores.push(opr(A, autoLow).flat());
    }
    else if(s == 'auto high') {
      var autoHigh = scoreFromEventData(matches, ['autoCargoUpperBlue', 'autoCargoUpperFar', 'autoCargoUpperNear', 'autoCargoUpperRed']);
      autoHigh = autoHigh.map(a => a.reduce((a, b) => a + b));
      scores.push(opr(A, autoHigh).flat());
    }
    else if(s == 'teleop low') {
      var teleopLow = scoreFromEventData(matches, ['teleopCargoLowerBlue', 'teleopCargoLowerFar', 'teleopCargoLowerNear', 'teleopCargoLowerRed']);
      teleopLow = teleopLow.map(a => a.reduce((a, b) => a + b));
      scores.push(opr(A, teleopLow).flat());
    }
    else if(s == 'teleop high') {
      var teleopHigh = scoreFromEventData(matches, ['teleopCargoUpperBlue', 'teleopCargoUpperFar', 'teleopCargoUpperNear', 'teleopCargoUpperRed']);
      teleopHigh = teleopHigh.map(a => a.reduce((a, b) => a + b));
      scores.push(opr(A, teleopHigh).flat());
    }
    else {
      scores.push(opr(A, scoreFromEventData(matches, s)).flat());
    }
  })
  scores = transpose(scores);
  return scores;
}

Click the run button at the top – it will not be able to run properly, but should prompt you to give the script permissions. Once you’ve done that, you can run the functions from your sheet like normal
image

3 Likes

Looks very nice, though I don’t know how great of an idea it is to use the same TBA auth key for everyone and have it public. It might be safer to have each user register for their own key and input it when they initialize the sheet. I could be making something out of nothing though, maybe someone from TBA can weigh in @Eugene_Fang

1 Like

Can not add events. I followed the instruction but after entering the event code and clicking the check box nothing happens.

I found that after having the events sheet filled out and the checkbox checked I had to go back to the first page and click the permissions button again. Then when I went to the add events sheet it added or I had to uncheck and recheck the add event button.

1 Like

Hey, where do I find the event code? Is it on the frc website?

You can go to http://frc.events or https://thebluealliance.com/events like mentioned on the setup sheet.

I went through the script, got it running, and encountered a weird situation where it looks like the function isn’t passing a real TBA event code to TBA but will pass a bad one:

image

image

I’d love to get some insight here, as the potential to not have to hand-punch two teams’ match schedule next week is awful tempting.

@Billfred, easy fix. You’ll need to put the event code in quotes: =getTeams("2022caph").

1 Like

This is unfortunately a pretty common but inconsistent issue I’ve seen, the best solution is to try and click the setup button until the read_me tab moves to the end.

Yep, exactly as Jared said. All match codes should go in quotes, team numbers don’t need quotes though.

I read through the comments in the code blocks and missed that, but that did get it working.

New issue: I use GetQualsSchedule to pull the match numbers and alliances. I then put the match numbers in a drop-down for data validation, then VLOOKUP against that table of match numbers to get the participating teams in a deeper match preview.

But then I try to VLOOKUP information based on those team numbers, the error is the same every time: “Did not find value (team number) in VLOOKUP evaluation.” Verify there are no funky formulas altering team numbers, no change. Change the list of team numbers from copy-pasted ones to using the GetTeams script, nothing. I then try a dummy table in the same workbook, same error. I then change the leftmost cells in that dummy table from hand-typed team numbers to a formula that equals where the team number is part of a GetQualsSchedule output, and it magically starts working.

I’m quite confused on why this is the case.

Did you verify that the text is being stored in the same format in both your dropdown menu and the list of teams? For example, is it possible they are numbers in one and plain text in another?

If that’s the case, there are a few workarounds:

  • Manually reformat the data with dropdown menus
  • In the formula, convert both to numbers by either putting them inside of a TEXT formula =TEXT(original_value, "#") or adding a 0 =original_value+0
  • Use team keys instead of numbers, since they will always be interpreted as text. You can convert them as needed ="frc"&original_value

Obviously none of that is ideal, but a few tricks learned over years of trying to use FRC team numbers as text in Google Sheets, and crying a lot over it.


If not, possible it has to do with the T/F in the VLOOKUP. Try adding both true and false and see if either fixes it. This relates to whether the list is sorted or not, but I’ve found that adding it sometimes has unpredictable positive outcomes =VLOOKUP(og_formula, TRUE) =VLOOKUP(og_formula, FALSE).


If all else fails, I’d be curious if FILTER exhibits the same bad behavior as VLOOKUP. This could help isolate the issue.

Feel free to DM me with a link to your sheet – I’m known to troubleshoot Google Sheets for fun.

Sorry for the delay - just tried to replicate this, and it’s due to GetQualsSchedule returning the numbers as strings instead of numbers.

The simplest way to fix this with your setup is to change the second VLOOKUP to =vlookup(int(team_num), ...)

Another option is to copy all the data from GetQualsSchedule, paste it as values only (Ctrl + Shift + V), and then change the format to Number
image

I’ll fix this in a future rev and return all team numbers as numbers, currently getTeams returns numbers and the rest return strings, which was definitely not my intent.

Wow int is so much cleaner than +0. This is why Rachel’s sheets will always be > my sheets.

Thanks for posting this again! Cool to see it keep growing.

I also love having it available to check against my calculations. This year it helped me notice a flaw in how I was calculating climbing rates.

As a heads up, I’ve noticed issues with the data/OPR from at least the following events

  • 2022dc305 (CHS Greater DC #1) doesn’t have published schedules or scores
  • 2022txwac (FiT Waco), cargo was counted manually so the cargo COPRs which are all inaccurate. Overall OPR should have been unaffected
  • 2022mifor (FiM Calvin University), sf1m2 score total doesn’t match the breakdown, unsure if that occurred in any quals matches

If you’re comparing against Valor Scout, please be aware that my OPRs do include penalty points, and that the cargo COPRs are cargo count, not points.

Thank you for creating and sharing this tool!

Is it possible to add fouls for and against in the opr tab?

I’m trying to load gacmp and I’m having an issue. When I add the event, it creates all the tabs (I’m creating every tab). When I try to load the opr table, nothing happens. Can you confirm that gacmp is working. When I run the sheet on gaalb it works.

Will this work for the Michigan State Championship?