Using the FRC event API with google sheets

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?

2 Likes

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

1 Like

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.

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.

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.

I still use TBA Requests. https://github.com/Eiim/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

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.