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 cube low, auto cube mid, auto cube high, auto cone low, auto cone mid, auto cone high, teleop cube low, teleop cube mid, teleop cube high, teleop cone low, teleop cone mid, teleop cone high`, that associated cOPR is returned
# if scoreName is "auto", the auto docked and engaged % are returned
# if scoreName is "mobility", the auto mobility % are returned
# if scoreName is "endgame", the endgame park, docked, and engaged % are returned
# if scoreName is "all", [OPR, auto %, mobility %, all auto and teleop cube/cone cOPRs, and 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):
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', 'auto', 'mobility', 'auto cube low', 'auto cube mid', 'auto cube high', 'auto cone low',
'auto cone mid', 'auto cone high', 'teleop cube low', 'teleop cube mid', 'teleop cube high', 'teleop cone low', 'teleop cone mid',
'teleop cone 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);
// 2023 specific
var autoChargeStation = scoreFromEventData(matches, ['autoChargeStationRobot1', 'autoChargeStationRobot2', 'autoChargeStationRobot3']);
var autoBridgeState = scoreFromEventData(matches, ['autoBridgeState']);
var auto = [];
autoChargeStation.forEach((arr, i) => {
if(autoBridgeState[i][0] == 'Level')
auto.push(arr.map(a => a === 'Docked' ? 'Engaged' : a)); // replace Docked with Engaged if Level
else
auto.push(arr);
})
// auto cube and cone
var autoCommunity = scoreFromEventData(matches, ['autoCommunity']);
var autoCubeLow = autoCommunity.map(a => a[0]['B']).map(a => a.filter(a => a === 'Cube').length);
var autoCubeMid = autoCommunity.map(a => a[0]['M']).map(a => a.filter(a => a === 'Cube').length);
var autoCubeHigh = autoCommunity.map(a => a[0]['T']).map(a => a.filter(a => a === 'Cube').length);
var autoConeLow = autoCommunity.map(a => a[0]['B']).map(a => a.filter(a => a === 'Cone').length);
var autoConeMid = autoCommunity.map(a => a[0]['M']).map(a => a.filter(a => a === 'Cone').length);
var autoConeHigh = autoCommunity.map(a => a[0]['T']).map(a => a.filter(a => a === 'Cone').length);
var teleopCommunity = scoreFromEventData(matches, ['teleopCommunity']);
var teleopCubeLow = teleopCommunity.map(a => a[0]['B']).map(a => a.filter(a => a === 'Cube').length);
var teleopCubeMid = teleopCommunity.map(a => a[0]['M']).map(a => a.filter(a => a === 'Cube').length);
var teleopCubeHigh = teleopCommunity.map(a => a[0]['T']).map(a => a.filter(a => a === 'Cube').length);
var teleopConeLow = teleopCommunity.map(a => a[0]['B']).map(a => a.filter(a => a === 'Cone').length);
var teleopConeMid = teleopCommunity.map(a => a[0]['M']).map(a => a.filter(a => a === 'Cone').length);
var teleopConeHigh = teleopCommunity.map(a => a[0]['T']).map(a => a.filter(a => a === 'Cone').length);
// endgame
var endgameChargeStation = scoreFromEventData(matches, ['endGameChargeStationRobot1', 'endgameChargeStationRobot2', 'endgameChargeStationRobot3']);
var endgameBridgeState = scoreFromEventData(matches, ['endGameBridgeState']);
var endgame = [];
endgameChargeStation.forEach((arr, i) => {
if(endgameBridgeState[i][0] == 'Level')
endgame.push(arr.map(a => a === 'Docked' ? 'Engaged' : a)); // replace Docked with Engaged if Level
else
endgame.push(arr);
})
var scores = [];
scores.push(['teams'].concat(teams));
scoreName.forEach(s => {
if(s == 'endgame') {
scores.push(['Endgame park'].concat(team_avg(teams, alliances, endgame, 'Park').flat()));
scores.push(['Endgame docked'].concat(team_avg(teams, alliances, endgame, 'Docked').flat()));
scores.push(['Endgame engaged'].concat(team_avg(teams, alliances, endgame, 'Engaged').flat()));
}
if(s == 'auto') {
scores.push(['Auto docked'].concat(team_avg(teams, alliances, auto, 'Docked').flat()));
scores.push(['Auto engaged'].concat(team_avg(teams, alliances, auto, 'Engaged').flat()));
}
else if(s == 'mobility') {
scores.push(['Auto mobility'].concat(team_avg(teams, alliances, scoreFromEventData(matches, ['mobilityRobot1', 'mobilityRobot2', 'mobilityRobot3']), 'Yes').flat()));
}
else if(s == 'auto cube low') {
scores.push(['Auto cube low'].concat(opr(A, autoCubeLow).flat()));
}
else if(s == 'auto cube mid') {
scores.push(['Auto cube mid'].concat(opr(A, autoCubeMid).flat()));
}
else if(s == 'auto cube high') {
scores.push(['Auto cube high'].concat(opr(A, autoCubeHigh).flat()));
}
else if(s == 'auto cone low') {
scores.push(['Auto cone low'].concat(opr(A, autoConeLow).flat()));
}
else if(s == 'auto cone mid') {
scores.push(['Auto cone mid'].concat(opr(A, autoConeMid).flat()));
}
else if(s == 'auto cone high') {
scores.push(['Auto cone high'].concat(opr(A, autoConeHigh).flat()));
}
else if(s == 'teleop cube low') {
scores.push(['Teleop cube low'].concat(opr(A, teleopCubeLow).flat()));
}
else if(s == 'teleop cube mid') {
scores.push(['Teleop cube mid'].concat(opr(A, teleopCubeMid).flat()));
}
else if(s == 'teleop cube high') {
scores.push(['Teleop cube high'].concat(opr(A, teleopCubeHigh).flat()));
}
else if(s == 'teleop cone low') {
scores.push(['Teleop cone low'].concat(opr(A, teleopConeLow).flat()));
}
else if(s == 'teleop cone mid') {
scores.push(['Teleop cone mid'].concat(opr(A, teleopConeMid).flat()));
}
else if(s == 'teleop cone high') {
scores.push(['Teleop cone high'].concat(opr(A, teleopConeHigh).flat()));
}
else {
scores.push([s].concat(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
