Hello, I recently stumbled upon this post and tried to implement it, however, no matter what I try, I can only get the names of the teams and their number, no other info. Can somebody help me out with this? The link I’m using is https://www.thebluealliance.com/api/v3/event/2023ohmv/teams?X-TBAThe Blue Alliance, a site for FIRST Robotics Competition event data-Auth-Key=MYKEY.
1 Like
In the example they show this
example: Say I wanted the team numbers and names from the Miami Valley Regional from 2019. Here’s what I would put in cell A1: =ImportJSON(“https://www.thebluealliance.com/api/v3/event/2019ohmv/teams?X-TBAThe Blue Alliance, a site for FIRST Robotics Competition event data-Auth-Key=MYKEYHERE 36”, “/nickname,/team_number”, “”) This generates the spreadsheet below (the screenshot is clipped, it does list all teams), and sorts each query into a different column.
It looks like your missing somthing like:
/nickname,/team_number”, “”)
If you add something like that with specific items you want which you can find Here if you scroll down a bit
Thats the seocnd parameter of the function, I only added the first one (the link) here, the second one is “/nickname,/team_number,/rankings/rankings/extra_stats,/rankings/rank,/rankings/record/wins,/rankings/record/losses”
Third parameter is the same as the example they gave
Yeah it also shows when you click the link lol, so maybe a problem with how it’s formatted on your spreadsheet. Make sure it’s displaying everything in the cell
Just checked, no, there is nothing in those cells aside from the ones showing the number and the name
Strange idk then the only thing if try would be doing something like the example I have instead of what your trying rn
The syntax for the custom ImportJSON function is:
=ImportJSON(url, [query], [parseOptions]
URL: This needs to be any TBAThe Blue Alliance, a site for FIRST Robotics Competition event data endpoint for which you want to call data. You can read up on the endpoints at APIv3 - The Blue Alliance. The URL you picked, https://www.thebluealliance.com/api/v3/event/2023ohmv/teams?X-TBAThe Blue Alliance, a site for FIRST Robotics Competition event data-Auth-Key=YOUR-API-KEY-HERE, is valid.
Query: This is an optional parameter and the hardest one to get right, and I think this is where you’re getting stuck. If you omit this parameter (and just run =ImportJSON(url) without it, you’ll get back the full endpoint. This might be a large data set, but is probably a good starting point if you’re new to this!
If you do want to pull in certain parameters, you can, but only ones that are returned by a given API endpoint. The query parameter can only search within a returned JSON, not call for additional data.
You’re looking for the following endpoints:
/nickname
/team_number
/rankings/rankings/extra_stats
/rankings/rank
/rankings/record/wins
/rankings/record/losses
But you’re calling the /event/{event_key}/teams endpoint which returns a response according to the following schema:
{
"key": "string",
"team_number": 0,
"nickname": "string",
"name": "string",
"school_name": "string",
"city": "string",
"state_prov": "string",
"country": "string",
"address": "string",
"postal_code": "string",
"gmaps_place_id": "string",
"gmaps_url": "string",
"lat": 0,
"lng": 0,
"location_name": "string",
"website": "string",
"rookie_year": 0,
"motto": "string",
"home_championship": {}
}
]
You will notice that from your list, only nickname and team_number are included in this response.
I suspect you meant to call the /event/{event_key}/teams/statuses endpoint instead which returns data in the following schema:
{
"additionalProp1": {
"qual": {
"num_teams": 0,
"ranking": {
"matches_played": 0,
"qual_average": 0,
"sort_orders": [
0
],
"record": {
"losses": 0,
"wins": 0,
"ties": 0
},
"rank": 0,
"dq": 0,
"team_key": "string"
},
"sort_order_info": [
{
"precision": 0,
"name": "string"
}
],
"status": "string"
},
"alliance": {
"name": "string",
"number": 0,
"backup": {
"out": "string",
"in": "string"
},
"pick": 0
},
"playoff": {
"level": "qm",
"current_level_record": {
"losses": 0,
"wins": 0,
"ties": 0
},
"record": {
"losses": 0,
"wins": 0,
"ties": 0
},
"status": "won",
"playoff_average": 0
},
"alliance_status_str": "string",
"playoff_status_str": "string",
"overall_status_str": "string",
"next_match_key": "string",
"last_match_key": "string"
},
"additionalProp2": {
"qual": {
"num_teams": 0,
"ranking": {
"matches_played": 0,
"qual_average": 0,
"sort_orders": [
0
],
"record": {
"losses": 0,
"wins": 0,
"ties": 0
},
"rank": 0,
"dq": 0,
"team_key": "string"
},
"sort_order_info": [
{
"precision": 0,
"name": "string"
}
],
"status": "string"
},
"alliance": {
"name": "string",
"number": 0,
"backup": {
"out": "string",
"in": "string"
},
"pick": 0
},
"playoff": {
"level": "qm",
"current_level_record": {
"losses": 0,
"wins": 0,
"ties": 0
},
"record": {
"losses": 0,
"wins": 0,
"ties": 0
},
"status": "won",
"playoff_average": 0
},
"alliance_status_str": "string",
"playoff_status_str": "string",
"overall_status_str": "string",
"next_match_key": "string",
"last_match_key": "string"
},
"additionalProp3": {
"qual": {
"num_teams": 0,
"ranking": {
"matches_played": 0,
"qual_average": 0,
"sort_orders": [
0
],
"record": {
"losses": 0,
"wins": 0,
"ties": 0
},
"rank": 0,
"dq": 0,
"team_key": "string"
},
"sort_order_info": [
{
"precision": 0,
"name": "string"
}
],
"status": "string"
},
"alliance": {
"name": "string",
"number": 0,
"backup": {
"out": "string",
"in": "string"
},
"pick": 0
},
"playoff": {
"level": "qm",
"current_level_record": {
"losses": 0,
"wins": 0,
"ties": 0
},
"record": {
"losses": 0,
"wins": 0,
"ties": 0
},
"status": "won",
"playoff_average": 0
},
"alliance_status_str": "string",
"playoff_status_str": "string",
"overall_status_str": "string",
"next_match_key": "string",
"last_match_key": "string"
}
}
That will work with most of the queries you outlined, but importantly it does not include the nickname or some of the other data points you do want. You may need to mix and match.
Parse Options: The example you linked left this field as two sets of double quotation marks. This is electing not to put a value for the optional field. You can do this, or just leave it blank. I like to use noHeaders most of the time, and you can see all options here.
Tl;dr
Your URL doesn’t have the spesific items you’re looking for. Read the docs and find the endpoint you’re looking for, then try again.
P.S. I sent you a DM already, but highly recommend you take your API key out of your post.
4 Likes
thanks a lot, that did give me a lot more info, however, it didn’t give me the info I wanted the most like average points in autonomous and per game which I had heard the TBA API could give, a, I misremembering or is there indeed a way to get them?
In this case you would need to (likely) look up:
- Which matches the teams are in and put those in a table.
- Use something like the lookup function to generate the URL endpoints for those matches (now you have all the matches’ scores)
- calculate averages. (Although these may not be representative of the team and rather of the alliance they are on)
If instead you are referring CCWM, OPR, etc. then you should be able to pull that from TBA
Yes, that was my question, how can I pull up stats like OPR when my thing is only giving me info like team name, number, team key, city, etc, but no stats
To expand on @Skyehawk’s answer: find the endpoint you need on the API docs page and understand that no one endpoint will have ALL the data you need when running a complex query.
I highly recommend you start simple trying to pull one or two data points from the API as you get comfortable both with the API and parsing it using Google Sheets.
Then I’m back right to where I started, no matter what parameters I put in, I only get name and team number
Can I suggest that you go back and reread the posts in this thread? I (and others) am happy to clarify anything you may have questions about.
Each API endpoint only contains so much information. All the parameters can do is further filter down that information. If an endpoint provides name, location, and number and you wanted to filter out just the location, you could. But you can’t just look for OPR, for example, on an endpoint that doesn’t provide it.
Read the API docs and determine which endpoint has data you’re looking for, then pull in that data.
Yes, I’m an idiot, sorry for that, on a separate note, what does CCwms stand for?
Nobody is saying that, and you’re not. You asked a good question about leveraging data, and the answer is nuanced, so it’s important to take things step by step. Let us know where you’re getting stuck and we can help.
As for CCWM, here are TBA’s definitions which I think are helpful:
OPR (Offensive Power Rating) is very roughly 1/3 of a team’s average alliance scores. It estimates the team’s contribution to their alliance match scores, assuming team contributions are independent and ignoring opposing team contributions. Higher is better.
DPR (Defensive Power Rating) is very roughly 1/3 of a team’s average opposing alliance scores. It estimates the team’s contribution to opposing alliance match scores, assuming team contributions are independent and ignoring the opposing teams’ own contributions. Lower is better.
CCWM (Calculated Contribution to Win Margin) = OPR - DPR. A team’s expected net contribution to an alliance.
These stats are an interesting way to slice the data for teams at a particular event, but aren’t a perfect system for assessing a team’s performance or contribution.
Another thing, when I do the command =ImportJSON("https://www.thebluealliance.com/api/v3/event/2023ohmv/oprs?X-TBA-Auth-Key=MYKEY
it only gives me the CCWM, not the OPR and DPR, how could I get them, also, how can I get the points in auto either for a given match or in total/average? On the APi there seems to be no way to get them.
I ran this exact formula and it returned the OPR and DPR. The CCWMs are listed first, then the DPRs, and then the OPRs. The dataset is many columns long, so look to the end.
Ccwms Frc1014 -18.461409381871395
Ccwms Frc1038 11.747935642636653
Ccwms Frc128 -7.209814951514222
Ccwms Frc1317 6.423015727556386
Ccwms Frc144 -7.795329859152616
Ccwms Frc1708 -14.122358327031941
Ccwms Frc1732 36.45289211608133
Ccwms Frc1787 34.96075756333994
Ccwms Frc2051 -13.763170244829357
Ccwms Frc2252 27.28257051169436
Ccwms Frc2399 7.414991071697943
Ccwms Frc2603 22.3257262187966
Ccwms Frc2614 20.962564473419228
Ccwms Frc2783 2.733684816246489
Ccwms Frc3140 -4.502193363812363
Ccwms Frc3193 -6.127547933323423
Ccwms Frc3201 15.790756278470617
Ccwms Frc325 5.0635624991852195
Ccwms Frc3260 -1.9993467417564208
Ccwms Frc3324 -16.852159586871107
Ccwms Frc3484 -13.103057147870729
Ccwms Frc3492 -7.23340808502622
Ccwms Frc3504 -8.262121287365723
Ccwms Frc3814 3.221119025909366
Ccwms Frc3984 -2.4855683598660065
Ccwms Frc4020 39.23546565939853
Ccwms Frc4028 21.799851817395716
Ccwms Frc4085 -9.549677131690068
Ccwms Frc4121 2.4953558909060964
Ccwms Frc4145 3.3593892498795004
Ccwms Frc4269 -5.901506087908928
Ccwms Frc4467 20.44073642670284
Ccwms Frc4504 -16.773572120707247
Ccwms Frc451 -26.94440991135024
Ccwms Frc4601 -30.18784723416668
Ccwms Frc4611 25.3258451800003
Ccwms Frc5413 1.3104567265477858
Ccwms Frc5492 -15.005089597627888
Ccwms Frc554 -2.8054110084484876
Ccwms Frc5667 -13.845479008151605
Ccwms Frc5811 15.5797957332442
Ccwms Frc6032 4.046820845419108
Ccwms Frc6834 -25.618880623550133
Ccwms Frc6936 -25.676027861591173
Ccwms Frc695 18.50735259947441
Ccwms Frc6964 -3.839971695106115
Ccwms Frc7111 -20.4221483963378
Ccwms Frc7165 1.9522858050494947
Ccwms Frc7515 -11.176800306487845
Ccwms Frc9097 -16.734923838471293
Dprs Frc1014 35.577559439679554
Dprs Frc1038 27.942993079573206
Dprs Frc128 34.464207655691396
Dprs Frc1317 30.41439219583634
Dprs Frc144 36.94382343139129
Dprs Frc1708 37.15391712565559
Dprs Frc1732 27.15151177566957
Dprs Frc1787 12.100195806848134
Dprs Frc2051 32.399757041628966
Dprs Frc2252 26.755368274831362
Dprs Frc2399 35.762779903365065
Dprs Frc2603 15.234860728938795
Dprs Frc2614 32.27295213283178
Dprs Frc2783 21.548487458847557
Dprs Frc3140 36.087184481297136
Dprs Frc3193 22.153769190122
Dprs Frc3201 25.162509898059515
Dprs Frc325 35.16816421241899
Dprs Frc3260 26.4151001045053
Dprs Frc3324 35.37229818866089
Dprs Frc3484 29.134599034976482
Dprs Frc3492 35.968502788844546
Dprs Frc3504 35.24898852552275
Dprs Frc3814 35.60672735321988
Dprs Frc3984 28.345483545488513
Dprs Frc4020 9.939355868596913
Dprs Frc4028 32.10508544603889
Dprs Frc4085 23.23628207821966
Dprs Frc4121 28.006897315193374
Dprs Frc4145 20.899341080652725
Dprs Frc4269 25.991225694088122
Dprs Frc4467 33.02823713163925
Dprs Frc4504 28.982649334400776
Dprs Frc451 35.89949433314138
Dprs Frc4601 34.400791242335
Dprs Frc4611 10.211923145178757
Dprs Frc5413 22.29383387971981
Dprs Frc5492 38.59748816692665
Dprs Frc554 39.968116963060844
Dprs Frc5667 32.116970800489135
Dprs Frc5811 17.096231346853312
Dprs Frc6032 21.01285289163699
Dprs Frc6834 34.73548305691544
Dprs Frc6936 46.386681562096484
Dprs Frc695 32.7695556721226
Dprs Frc6964 25.843185816122872
Dprs Frc7111 35.68829922877669
Dprs Frc7165 31.592323431357634
Dprs Frc7515 33.44985481106217
Dprs Frc9097 25.428329213459996
Oprs Frc1014 17.116150057808124
Oprs Frc1038 39.69092872220985
Oprs Frc128 27.25439270417716
Oprs Frc1317 36.83740792339274
Oprs Frc144 29.14849357223868
Oprs Frc1708 23.031558798623653
Oprs Frc1732 63.60440389175094
Oprs Frc1787 47.06095337018808
Oprs Frc2051 18.63658679679963
Oprs Frc2252 54.037938786525714
Oprs Frc2399 43.177770975063
Oprs Frc2603 37.56058694773541
Oprs Frc2614 53.23551660625104
Oprs Frc2783 24.282172275094023
Oprs Frc3140 31.584991117484762
Oprs Frc3193 16.026221256798575
Oprs Frc3201 40.95326617653015
Oprs Frc325 40.23172671160418
Oprs Frc3260 24.415753362748873
Oprs Frc3324 18.520138601789828
Oprs Frc3484 16.031541887105746
Oprs Frc3492 28.73509470381831
Oprs Frc3504 26.986867238157043
Oprs Frc3814 38.82784637912926
Oprs Frc3984 25.8599151856225
Oprs Frc4020 49.17482152799541
Oprs Frc4028 53.90493726343459
Oprs Frc4085 13.686604946529581
Oprs Frc4121 30.50225320609949
Oprs Frc4145 24.258730330532217
Oprs Frc4269 20.089719606179205
Oprs Frc4467 53.46897355834211
Oprs Frc4504 12.209077213693543
Oprs Frc451 8.955084421791103
Oprs Frc4601 4.212944008168338
Oprs Frc4611 35.53776832517903
Oprs Frc5413 23.604290606267604
Oprs Frc5492 23.592398569298776
Oprs Frc554 37.16270595461238
Oprs Frc5667 18.27149179233752
Oprs Frc5811 32.676027080097526
Oprs Frc6032 25.059673737056084
Oprs Frc6834 9.116602433365298
Oprs Frc6936 20.71065370050532
Oprs Frc695 51.27690827159699
Oprs Frc6964 22.003214121016764
Oprs Frc7111 15.266150832438903
Oprs Frc7165 33.544609236407105
Oprs Frc7515 22.273054504574333
Oprs Frc9097 8.693405374988714
Use /event/{event_key}/matches for all matches at an event.
Use /match/{match_key} for a single match.
Use /team/{team_key}/matches/{year} for all of a given team’s matches in a given year.
Use /team/{team_key}/event/{event_key}/matches for all of a given team’s matches at a given event.
All of these will contain a score_breakdown which will include autonomous details. For example, if you hit https://www.thebluealliance.com/api/v3/match/2023cmptx_f1m2, you will get the following response (note all of the field prepended with auto which may contain what you’re looking for):
{
"actual_time": 1682205790,
"alliances": {
"blue": {
"dq_team_keys": [],
"score": 208,
"surrogate_team_keys": [],
"team_keys": [
"frc5460",
"frc125",
"frc870"
]
},
"red": {
"dq_team_keys": [],
"score": 209,
"surrogate_team_keys": [],
"team_keys": [
"frc1323",
"frc4096",
"frc4414"
]
}
},
"comp_level": "f",
"event_key": "2023cmptx",
"key": "2023cmptx_f1m2",
"match_number": 2,
"post_result_time": 1682206015,
"predicted_time": 1682206044,
"score_breakdown": {
"blue": {
"activationBonusAchieved": false,
"adjustPoints": 0,
"autoBridgeState": "Level",
"autoChargeStationPoints": 12,
"autoChargeStationRobot1": "None",
"autoChargeStationRobot2": "None",
"autoChargeStationRobot3": "Docked",
"autoCommunity": {
"B": [
"None",
"None",
"None",
"None",
"None",
"None",
"Cone",
"Cube",
"None"
],
"M": [
"None",
"None",
"None",
"None",
"None",
"None",
"None",
"None",
"None"
],
"T": [
"Cone",
"None",
"None",
"None",
"Cube",
"None",
"None",
"None",
"Cone"
]
},
"autoDocked": true,
"autoGamePieceCount": 5,
"autoGamePiecePoints": 24,
"autoMobilityPoints": 9,
"autoPoints": 45,
"coopGamePieceCount": 9,
"coopertitionCriteriaMet": false,
"endGameBridgeState": "Level",
"endGameChargeStationPoints": 30,
"endGameChargeStationRobot1": "Docked",
"endGameChargeStationRobot2": "Docked",
"endGameChargeStationRobot3": "Docked",
"endGameParkPoints": 0,
"extraGamePieceCount": 4,
"foulCount": 0,
"foulPoints": 5,
"g405Penalty": false,
"h111Penalty": false,
"linkPoints": 45,
"links": [
{
"nodes": [
0,
1,
2
],
"row": "Bottom"
},
{
"nodes": [
3,
4,
5
],
"row": "Bottom"
},
{
"nodes": [
6,
7,
8
],
"row": "Bottom"
},
{
"nodes": [
0,
1,
2
],
"row": "Mid"
},
{
"nodes": [
3,
4,
5
],
"row": "Mid"
},
{
"nodes": [
6,
7,
8
],
"row": "Mid"
},
{
"nodes": [
0,
1,
2
],
"row": "Top"
},
{
"nodes": [
3,
4,
5
],
"row": "Top"
},
{
"nodes": [
6,
7,
8
],
"row": "Top"
}
],
"mobilityRobot1": "Yes",
"mobilityRobot2": "Yes",
"mobilityRobot3": "Yes",
"rp": 0,
"sustainabilityBonusAchieved": false,
"techFoulCount": 0,
"teleopCommunity": {
"B": [
"Cube",
"Cone",
"Cone",
"Cube",
"Cube",
"Cube",
"Cone",
"Cube",
"Cone"
],
"M": [
"Cone",
"Cube",
"Cone",
"Cone",
"Cube",
"Cone",
"Cone",
"Cube",
"Cone"
],
"T": [
"Cone",
"Cube",
"Cone",
"Cone",
"Cube",
"Cone",
"Cone",
"Cube",
"Cone"
]
},
"teleopGamePieceCount": 27,
"teleopGamePiecePoints": 83,
"teleopPoints": 113,
"totalChargeStationPoints": 42,
"totalPoints": 208
},
"red": {
"activationBonusAchieved": false,
"adjustPoints": 0,
"autoBridgeState": "Level",
"autoChargeStationPoints": 12,
"autoChargeStationRobot1": "None",
"autoChargeStationRobot2": "Docked",
"autoChargeStationRobot3": "None",
"autoCommunity": {
"B": [
"None",
"None",
"None",
"None",
"None",
"None",
"None",
"None",
"None"
],
"M": [
"Cone",
"None",
"None",
"None",
"None",
"None",
"None",
"None",
"Cone"
],
"T": [
"None",
"None",
"None",
"None",
"Cube",
"None",
"None",
"Cube",
"Cone"
]
},
"autoDocked": true,
"autoGamePieceCount": 5,
"autoGamePiecePoints": 26,
"autoMobilityPoints": 9,
"autoPoints": 47,
"coopGamePieceCount": 9,
"coopertitionCriteriaMet": false,
"endGameBridgeState": "Level",
"endGameChargeStationPoints": 30,
"endGameChargeStationRobot1": "Docked",
"endGameChargeStationRobot2": "Docked",
"endGameChargeStationRobot3": "Docked",
"endGameParkPoints": 0,
"extraGamePieceCount": 6,
"foulCount": 1,
"foulPoints": 0,
"g405Penalty": false,
"h111Penalty": false,
"linkPoints": 45,
"links": [
{
"nodes": [
0,
1,
2
],
"row": "Bottom"
},
{
"nodes": [
3,
4,
5
],
"row": "Bottom"
},
{
"nodes": [
6,
7,
8
],
"row": "Bottom"
},
{
"nodes": [
0,
1,
2
],
"row": "Mid"
},
{
"nodes": [
3,
4,
5
],
"row": "Mid"
},
{
"nodes": [
6,
7,
8
],
"row": "Mid"
},
{
"nodes": [
0,
1,
2
],
"row": "Top"
},
{
"nodes": [
3,
4,
5
],
"row": "Top"
},
{
"nodes": [
6,
7,
8
],
"row": "Top"
}
],
"mobilityRobot1": "Yes",
"mobilityRobot2": "Yes",
"mobilityRobot3": "Yes",
"rp": 0,
"sustainabilityBonusAchieved": false,
"techFoulCount": 0,
"teleopCommunity": {
"B": [
"Cube",
"Cone",
"Cube",
"Cube",
"Cube",
"Cube",
"Cube",
"Cone",
"Cube"
],
"M": [
"Cone",
"Cube",
"Cone",
"Cone",
"Cube",
"Cone",
"Cone",
"Cube",
"Cone"
],
"T": [
"Cone",
"Cube",
"Cone",
"Cone",
"Cube",
"Cone",
"Cone",
"Cube",
"Cone"
]
},
"teleopGamePieceCount": 27,
"teleopGamePiecePoints": 87,
"teleopPoints": 117,
"totalChargeStationPoints": 42,
"totalPoints": 209
}
},
"set_number": 1,
"time": 1682201100,
"videos": [
{
"key": "D9ocK5JByBo",
"type": "youtube"
},
{
"key": "PFW2Sc3shaA",
"type": "youtube"
}
],
"winning_alliance": "red"
}
I don’t know exactly what your end goal with this project is, but you might check out this tool I’ve released the past couple of years. Feel free to reverse engineer it and ask any questions you may have. It’s spreadsheet based to pull some of the more basic data points: ScoutLess 2023: A way to leverage the API and scout less (not an app!)
I’ve made a lot of changes to the program based on suggestions and now it works almost perfectly. Just one question, for inputs such as the one that gives you the events that the team has gone to that year it gives you all the info in a single cell separated by commas, is there a way to separate them into different cells?