TBA Requests - Google Sheets add-on for Blue Alliance data

I’ve just made and published TBA Requests, a Google Sheets add-on that allows you to easily access data from thebluealliance.com within Google Sheets. I know many teams, including my own this year, use Google Forms for scouting and Google Sheets for data analysis. One downside of this approach has always been that Blue Alliance data was difficult to use. This add-on tries to solve this problem. If you try it out, let me know what you think of it! (The code’s also on Github, btw)

8 Likes

Ahmazing!

(says the data nerd who hasn’t bothered to learn the TBA API yet)

Good work.

I started doing something: https://docs.google.com/spreadsheets/d/1xrRHQizVFv6at7IUVMlsIOO-aMpnaFNkyTlJ509shzM/edit?usp=sharing

I decided to try to make a twitter list using the TBA twitter accounts: https://twitter.com/FIRST_Team_1108/lists/frc-team-tweets/members

There is a Zapier integration pulling the Twitter accounts from the Google Sheet (“Twitter List”). https://zapier.com/apps/google-sheets/integrations/twitter/4027/add-new-google-sheets-rows-to-a-twitter-list

One bottle neck was not seeing a straightforward way to get all the active team numbers. There may be a way to do it if I dug in, but it’d be something nice to have sometimes. Same with a way to get all the event keys for a given year. It might help to show a couple example of using the custom queries, which might solve this.

For now, I just queried the teams at the Greater Kansas City regional for this list. It actually turned out I wasn’t following 3 local teams.

Just found out that TBA does support finding all events and teams in a year. I’ll definitely work on those given how useful they could be in various situations. I like the Zapier integration idea as well. I think a Twitter/Facebook/etc mass follow list for all FRC could be valuable for certain people/organizations/teams that want to be inclusive in helping out all teams.

Brilliant. This makes me obsolete.

This is very useful, thanks for making this.

As someone who has used TBA API in google sheets this addition would be much appreciated. This could really enable a lot of teams to create some awesome scouting data apps!

Just dropping this in here, sorry I can’t supply more context at the moment. Just do a google search for TBA Python support layer.

Using python & google sheets in harmony

Cool, complex but cool.

Something I’d like to try is to periodically run a python script to pull data from the Sheet and produce charts for the scouts and drive team. Last couple years I’ve had AppSheet apps, which use Sheets and Drive. This add-in looks helpful to get match data into Sheets and the Appsheet app can be used by scouts for data input (on phones – or synced later). The python script will create graphs, save them to the drive and update the Sheet. There are several places run a periodic script from, but I recall the IBM cloud virtual kit having that ability – maybe something like this.

Some reason TBACustom is returning blank for me. I was trying “=TBACustom(“teams/1”,“team_number”)”, for example.

Me too actually. Hadn’t tried that yet.

Looking at code, I suspect this (Line 657):

return JSON.stringify(json[0]‘blue_score’])

shouldn’t be there.

Correct! Not sure why that’s there. I’ll update the add-on and Github today.

I don’t have an immediate need for this spreadsheet, but I am so looking forward to it next season!

This is a great first step for people looking to dive more into TBA API.

The TBA Request Help document was helpful.

I also like the idea of people sharing their TBA Google Sheet Template.

I made an Event List Example

Oops, didn’t notice I shared my TBA API authorization key. Looking over your document reminded me. Something to think of before sharing.

I like the idea of having some template.

You can revoke that key from your myTBA account if you’re concerned that it has been copied.

I hadn’t had a chance to re-examine how my ‘nav’ term in the TBACustom function is wrong. I can get the raw JSON string by leaving it off, but I don’t have luck with the strings I’ve tried for ‘nav’. Forewarning, I’ve always had a little confusion on JSON parsing, so it is very possibly my mistake.

I keep trying things like “=TBACustom(“teams/1”,“team_number”)”, where team_number would be the nav term. Is there an additional layer in the JSON I’ve missed. I’ve tried some things to no avail.

I’m not sure how this works. The help document just says that after I type in =TBASetKey(w/ my key), I should be able to use the other functions. So far it’s just returned “There was an error retrieving the data”. Am I doing something wrong?

Nice addon, reminds me of the same code library that team 59 did a couple months back. GAS-TBA_APIv3-Library

Did you enclose your key in quotes? Google Sheets requires string parameters be sent in quotes.