Using the FRC event API with google sheets

[Omer8624]

Hi! For the scouting i need to get the event schedule (which team plays which match) to a google sheet, how can i do that with the FRC event API?

[lynca]

I would recommend using the BlueAlliance API Developer APIs - The Blue Alliance

[jaredhk]

Preview: ScoutLess 2022, a way to scout less, letting the data work for you

For years, the FIRST (and through it, TBA) API has provided extremely helpful match breakdowns. Yet, every year, I see teams scouting that same data. We don’t have the API match breakdown for this year yet, but it will likely tell us what each robot did in auto AND what each robot did in the endgame. That means the only quantitative thing left for scouts is game pieces scored.

Why I'm not waiting till the API comes out

I know teams are hard at work developing their scouting systems whether…

This does exactly that. You’re free to use this sheet once I update it with the 2022 spec.

If you’re looking to learn how to do it yourself, understand that the API will return a JSON format which Google Sheets cannot natively import. You’ll want to create a custom script (there are many available online for copy pasta) that allows you to import the JSON. From there, it’s just a matter of figuring out which endpoints you want to import to get matches @ event.

[mmongar]

Yes, I did something similar using Google Sheets. I loaded a ImportJSON function from some other developer and then modified the Java Script to work with TBA. I created tabs for the endpoints of interest and a final tab called TeamMatchSpecific to split and merge the data into a single area to use as a datasource in Data Studio or Tableau. The column headers will update to the new game when there is TBA data available for 2022.

TBA Data Download for FIRST Robotics 2020 Game

I have seen a lot on this topic last year but not so much this year so I thought I would post. This is the first time I have integrated to the TBA API to populate a Google Sheet with all the event specific data so I can integrate with Tableau or Data Studio. I used the ImportJSON.gs script and loaded that into my Google Sheet under Tools -> Script Editor. Original Script can be found at https://github.com/bradjasper/ImportJSON I then added a function at the end to pass the TBA Token appropriat…

[mmongar]

One of the tabs is called event teams and it does populate the teams associated to an event like 2022mokc. The rest are empty of course. The tab TeamEventMatchSimple should have the match and the teams associated as soon as TBA has it available.

[ngreen]

I still use TBA Requests. GitHub - Eiim/tba-requests: The source code and help document for TBA Requests.

Basically, you install the add-in app in the chrome marketplace (see extension menu). Then you have to create an account on thebluealliance.com and request your API key, which you’ll use in the sheet.

There is more info here about usage: TBA Request Help - Google Docs

edit: I usually put this function in A1: TBASetKey(key), replace key with your own. Then I usually do a TBAMaxSeason() to see if I get a result before using.

You can make custom queries with TBACustom(query, nav) and then use the nav part to parse the JSON that gets returned. It is not always easy to figure exactly what to put, because you end up with arrays plus key:value pairs to parse.

The query for custom stuff is the same as the v3 API query: APIv3 - The Blue Alliance

Particularly for matches teams were in at an event, this query returns all the matches for that team: TBATeamEventMatchKeys(team, event): Returns a list of match keys which a team competed in at a specific event.

Here is an example

[ngreen]

It seems to get match information that the parsing a custom match function is pretty accessible. Here is a more typical match schedule setup:

I used index to select which of the team_keys to place in each cell. Although you could just transpose the result array.

You can also something like a RIGHT function to strip off the “frc” in the keys if you’d like.