Don’t want to make an app, and are sick of manually entering data from TheBlueAlliance to a google sheet? I’m here to help! I’ve found that making a separate app can be intimidating to new students that want to work with data, so here’s a way to integrate raw data from TBA that can then be analyzed however you want! We use this to live update OPR’s, rankings, and upcoming match schedules to predict matchups.
Once you get it set up, you can make a second sheet and pull data however you want, once it’s all in the file!
Here are all of the steps you need to follow to pull data from The Blue Alliance and import it into google sheets.
-
Get a TBA activation Key:
All you need to do is log into your TBA Account and scroll down to “Read API Keys”, add a description, and click add! Learn more about Data and TBA from the blogpost here. -
Create a blank google sheet. Give it whatever name you want.
-
Click on Tools → ScriptEditor
-
Click Create Script for Spreadsheet
-
Name the script “ImportJSON.GS” instead of “Code.gs”
-
Delete the placeholder content and paste the code from this script.
-
Click Save
Now, you’re all setup and ready to import data!
You should now have the =importJSON() function at your disposal in google sheets. It needs 3 parameters: URL, Query, and ParseOptions.
-
URL: This is the URL that you can get from TBA for the specific data you need. Check out the API here. What you’ll need to type is something like this: “https://www.thebluealliance.com/api/v3/event/2019ohmv/teams?X-TBA-Auth-Key=PASTEYOURKEYHERE” - be sure to replace “PASTEYOURKEYHERE” with your own key from TBA’s API!
-
Query: This is where you will pull your specific data. Say I wanted to see ONLY the team numbers and team nicknames. My parameter would say “/nickname,/team_number” WITH NO SPACES. Take a look at the API to see exactly what the column headers are all called.
-
ParseOptions: This is if you want to get fancy with the data and do things like remove headers or styling. I leave it blank, so it looks like 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-TBA-Auth-Key=MYKEYHERE”, “/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. Notice that it’s sorted in order as if the team number is text, and not numerical:
Limitations
Once the data is in, you cannot modify it. It has to sit where it is, and can’t be sorted. I suggest leaving the data where it sits and creating a different sheet to make the rest.
It’ll take some exploring, but you can do it! There is SO MUCH DATA on TBA, all you need is a little curiosity and you can do anything! Thanks to the website here for starting my inspiration, and to “bradjasper” for keeping the ImportJSON code up to date so that I can use it for FRC!
Team 3324 intends to use this for next year’s scouting app. Here’s what we had from Deep Space. Feel free to explore, copy, and use! Instead of manually entering TBA data, we will be live pulling it using the API.
If you have questions or are stuck, please let me know. If I have time as the school year winds down, I’ll try to make a video showing how I’ve used it.
