Scouting Data made easy - Connecting TBA to Google Sheets

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.

  1. 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.

  2. Create a blank google sheet. Give it whatever name you want.

  3. Click on Tools -> ScriptEditor

  4. Click Create Script for Spreadsheet

  5. Name the script “ImportJSON.GS” instead of “Code.gs”

  6. Delete the placeholder content and paste the code from this script.

  7. 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.

13 Likes

This is very cool! I would love to do something like this next year!

This is awesome, Tyler – thank you so much for posting. I was able to pull data into Google Sheets and then into Tableau to start analyzing. SUPER COOL!!! You made my day :grinning: :+1:

1 Like

Thanks a ton for this! Our district shifted to Chromebooks a few years ago and I am not as proficient at sheets as I am to excel. This will really streamline things.

I keep receiving a formula parse error when importing the =json code

Can you give me a bit more information? We’re you able to follow the steps? If you’d like, private message me a link to your file and I can try to troubleshoot with you.

This sounds like you’re using =json. The formula is =IMPORTJSON and the syntax is =IMPORTJSON("URL", optional_params).

So a basic request to find the info about Team 1836, for example, would be =IMPORTJSON("https://thebluealliance.com/api/v3/team/frc1836?X-TBA-Auth-Key=your-key-here").

If you put in a valid formula like that, and you’re still getting a Parse error, my bet is that the script isn’t properly authorized. To fix this, go to script editor (the same place where you added the importJSON.gs code, select the ImportJSON function from the menu, and click the play button. Follow the steps for the authorization flow. You’ll get an error in that tab, but that’s fine. Refresh your sheet and the formula should work.

If you’ve tried all that and it’s still not working, please share an editable version of your Google Sheet and I (or someone else) can take a look.

We were able to get it fixed - When I pasted in the initial prompt I didn’t use the preformatted text, and it messed up the quotes. It used the slanted “” instead of the straight " ". Sheets doesn’t like that.

1 Like

If anyone here has experience with formatting JSON in a google sheet, i need help. Ive used TBA API in the past, so i dont need help in that aspect, but i want to take the raw data it gives and format it nicely, similar to how @WillNess was able to pull from the API and remove things like “frc” in front of the team numbers, and the wacky formatting in the raw version of pulling a team status.

For example, here’s the raw data:

And here’s what @WillNess’s function gives:
image
(His only gives the “Overall Status” column, and removes all the wacky <b> stuff)

Yes, i checked, this pulls from the same URL. I tried to move around his script and try different things, but nothing worked how i wanted it. If anyone has any experience with pulling and formatting JSON into a sheet, please reach out to me
:)

Thank you!
Kaitlyn

My workaround for it was to leave all of the raw data more or less alone, and format it in a second column beside the data. It slows it down, but is useful. Useful things could be =CONCAT()or =SPLIT() to remove some of the funny formatting stuff. I have a couple of examples in my Deep Space Spreadsheet to see some of the places I used it - especially the “TBA DATA- DON’T TOUCH!” spreadsheet.

Does that help?

1 Like

In order to get rid of the HTML tags still inside the data, or “frc” before teams numbers, I’d suggest using this.

1 Like

To provide some more context, TBA is returning the formatted HTML for that field which Google Sheets doesn’t parse. A script can parse it which is why you’re not seeing the ‘SUBSTITUTE’ formula being used. But that is your best workaround.