The Blue Alliance API Google Sheets Addon

Hello,

The last two years 4944 has been using Google Sheets to run our scouting system. We’ve had our scouters using google forms for both pit scouting, and match scouting, which provides live updating data to our strategy team for match prep. We’ve also developed a system for including The Blue Alliance API data inside of our “Scouting App”. I figure I’d share our code in the hopes that it’ll help other teams who are using Google Sheets.

What we’ve developed is simply a google script file you can add on to any Google Sheets which will allow you to use new functions in any cell. Here are the functions currently supported:

tbaTeamsAtEvent(eventcode)

tbaTeamsAtEventSimple(eventcode)

tbaTeamStatus(eventcode, teamnum)

tbaTeamsOnAlliance(eventcode, matchcode, alliance)

returns A1, A2, A3
image

tbaTeamsInMatch(eventcode, matchcode)

returns R1, R2, R3, B1, B2, B3

tbaEventMatches(eventcode)

As a small note, I’m not sure how this will work in an ongoing event just yet.

There’s obviously way more information available in the TBA API than this currently supports, but these are the ones that provide information useful to 4944’s scouting program at least. If you have any requests, just reply here and I’d be happy to add it on if it’s feasible and I have time.

Setup is very easy! All you have to do is copy/paste code into the script editor on your sheet, generate an API key and paste it at the top of the script. Instructions and code are located here on GitHub.

Example: 4944’s applications of this in our Google Sheet Scouting App for the 2019 Utah Regional


Team Lookup - enter team number and see all match data (notes hidden since it sometimes mentioned other teams)

Match Prep - enter match number & match type and see data on all 6 teams



We also have a Match Prep Manual sheet (enter in 6 teams manually, good for comparing teams during a picklist meeting for example), as well as some picklist tools.

If you have any questions on setup or using these TBA API Google Sheet formulas, please reply or message me!

17 Likes

Thank you for this.

By request of @Kaitlynmm569, there’s a new function that can be used called tbaEventRankings(eventcode) that will return the ranking information at a specific event. This has been updated in the code on the GitHub.

tbaEventRankings(eventcode)

1 Like

Very cool!

Our team recently made our own app using the same importJSON function, but we didn’t create new functions like you did. Here’s our take on using the function. You can call up any event, All match data (including scores and teams), all teams at the event, including what awards they’ve won in the past and thier OPR, and their rankings at the event

We also compare our data semi-heavily to The Blue Alliance in this app. It was just created this off-season, so we haven’t tested it in real time, either.

There is sample data that we gathered from the 2019 Greater Pittsburgh Regional. Like you said, combining our data to TBA is A LOT of data, but it’s interesting to look at, that’s for sure!

I like how your app is laid out quite a bit, but it seems like a lot of information for sheets to process at each update. Is it pretty slow?

1 Like

My sheet used to use the exact same process that you use for looking up information from the API - importJSON calls with concatenated strings to form a URL. This obviously works and it allows you to call whatever you want in the API, but I decided to switch over to custom functions for a couple reasons:

  1. It allows you to simplify your calls by a LOT, and allows anyone, even those without an understanding of how the TBA API works, to understand what the functions mean.
  2. It allows me to insert whatever logic I want in-between the API call and the return to the sheet in JavaScript.

But honestly the main reason is because I shared this scouting app with multiple other teams this season, and I wanted to provide a good framework for teams to use without an extensive knowledge of how to use the TBA API or how to construct calls to it inside of Google Sheets.

My early versions of the app were SUPER slow. Slow enough that a change of the event code, match number, or team number would sometimes take up to a minute. This obviously isn’t ideal during match prep at an event or picklist meetings. Now, I can change event/team/match-number and have the sheet fully reloaded within seconds. The main thing that I’d recommend, which it looks like you’re already doing, is to make TBA calls in bulk on one sheet, then query/vlookup from other sheets wherever you need the TBA data. Originally on my team lookup I had importJSON calls for a bunch of individual cells for nickname, status, etc… Now I pull up all the information for every team at the event in one sheet and just use query functions to look those up from elsewhere.

A couple things that I’d suggest based on what I can see from your sheet:

  1. Store your match/other data in other Google sheets files, then pull it into your current sheet using a “importrange()” function call. In the importrange function, you can pass it the ID of a completely different Google Sheet and import from there. If you’re pulling data in through Google forms, this will allow you to automate the import process instead of copy/paste. Even if you’re not using Forms, it’ll allow you to paste your data in a completely different file. This has allowed us to simplify our “Control Panel” to this: (censored IDs because the sheets are public for the function to work)

    This also allows us to quickly switch between datasets from different events easily. Adding in data validation w/ dropdown could allow you to possible switch between these even easier if that was a feature you’d want.
    The id of a sheet can be found in the URL:
  2. Your sheet has lots of good picklist tools. Rankings, averages, etc… I’d suggest making a match prep view (similar to the one in my original post) that will allow you to use this information to form match strategy. I originally made this app to help us run our picklist meetings easier, but has led us to having much more success in our matches due to informed strategy. The ability this year to quickly see cycle averages, cycle maximums, and notes was extremely helpful. Google sheets runs great on most cell phones, allowing us to pull up whatever information we want on the fly.

If you’d like a walkthrough, or have any questions just let me know - I’m happy to help!

2 Likes

I like the idea of your match prep sheet, and I’ll definitely look into storing data on a different sheet - I didn’t think of that. Does that speed it up at all?

I’m going to work with my team to make something similar - thank you for sharing!

I don’t think so. The main thing that it helps us out with is since our pit scouting & match scouting google forms are automatically populating their own Google Sheets, by importing from those sheets it is automating our data importing process. This is very nice during the middle of an event since it guarantees you’ll always have the most up to date info.

What’s also nice about this method is that it limits the amount of modifications you need to do to your main scouting app during an event. Limiting inputs by the user (strategy team, scouting team) always helps. One thing I still need to do is limit where users can actually edit the sheet because usually at least one time per event we’ll have a student/mentor break a formula or mess up something by altering a cell they shouldn’t have. By storing your data in other sheets, you won’t risk breaking your scouting app if the data gets pasted in incorrectly.

So I’ve been using your add-on and I found that I would like these two features implemented:

  • Team Numbers of Playoff Alliances
  • Rank of each Team (Maybe in tbaTeamsAtEvent and/or tbaTeamsAtEventSimple)

I also can’t figure out how the teams are sorted by in tbaTeamsAtEvent and that’s been bugging me a lot.

Otherwise, I love this Addon, it’s so much better than copy pasting data from TBA to google sheets and I will be definitely using this for the rest of my FRC career.

Teams are sorted by their team number alphabetically. This is why for example if you use tbaTeamsAtEvent at 2019code you see 1584, then 159, then 1619.

I believe this is implemented above with the tbaEventRankings function (see my reply or the GitHub page). Or are you requesting something else?

I’ll try to work on this :+1:

Thank you!

No this is perfect. I guess I missed this one.

Thank you again!!!

This can now be done with:

tbaPlayoffInfo(eventcode)

You can also get just the teams in the playoffs with this function:

tbaPlayoffAlliances(eventcode)

Does this take into account alliances with a backup bot?

It does not, but I’d be happy to add that if you can think of a good way to present it. Essentially the TBA API gives 2 values if there’s a backup bot: bot_in and bot_out.
Ex: You have an alliance of teams A, B, and C. B gets replaced by D. Here are the values the API gives me:
bot1 - A
bot2 - B
bot3 - C
bot_out - B
bot_in - D

I can easily enough just replace bot2 with bot_in (in this case), but then you lose bot2. The only other option I can think of would be to add two new columns for bot_in and bot_out but it’ll make the return look a lot more clunky.

Thoughts?

You could do this pretty cleanly using four columns. If there’s no backup bot, just leave the 4th column blank. If there is a backup bot, put it in the 4th column and put a “*” after the “bot_out” team. This also handles the 4 robot alliances at champs well.

Great idea. For both tbaPlayoffAlliances and tbaPlayoffInfo there is now a 4th column to represent bots. In the case of a 4 robot alliance the 5 columns will be: Alliance Number, Bot 1, Bot 2, Bot 3, Bot 4. In the case of a 3 robot alliance event, the 5 columns will be: Alliance Number, Bot 1, Bot 2, Bot 3, Backup, and the team number being replaced will have a * appended to their number.

Here is tbaPlayoffAlliances being used at 2 different events:
image
image

2 Likes

error

I just copied in the most recent code from Git and got this error. Is this an issue on my end, or have you seen this already?

I have not seen that already - thanks for bringing it to my attention. The reason the error was happening there was because for this specific event the TBA API call result did not contain the “name” field for each alliance that it should contain. In the event that this happens again, it’ll just assume the list of alliances are in correct order and grab the alliance number from it’s order in the list instead. It works correctly for 2019bc now.

New code is already pushed to the GitHub!

1 Like

Here is a little demo I made that uses the tbaPlayoffInfo function to automatically visualize the playoff status at an event. All that a user needs to do is type in the event code.

Here is a link to view this.

3 Likes