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
