Does anyone know how to use VBA inside excel to get data from The Blue Alliance and then dump it into a spreadsheet directly? Right now we use javascript to get JSON file from TBA. Then we dump the JSON file into an online converter and save as xls. We then copy the xls into the event spreadsheet file and finally do data manipulation and such. If I could eliminate any of these steps it would be wonderful. If I could launch a script inside excel and go to a worksheet with the data already there it would be amazing. Any help would be greatly appreciated. Thanks in advance.
Looks like these links might he helpful:
I pull data from TBA directly into excel with vba in my event simulator. You can use that directly or if you want a different request, you can see how I structure and parse requests in the simulateEvent code. Key things you need are:
In vba, enable the following in Tools → references:
Add the vba-json module to your project: GitHub - VBA-tools/VBA-JSON: JSON conversion and parsing for VBA
Create a TBA Auth Key: Developer APIs - The Blue Alliance
Create a WinHttpRequest object:
Assign your request to the object:
http_matches.Open “GET”, “http://www.thebluealliance.com/api/v3/event/2020ndgf/matches”, False
Add the TBA Auth Key (use your own key though, not this one):
http_matches.SetRequestHeader “OPAQcOwlQKX5NwMAl7gYZrs58eanDSi8ZLQXVlOEYc0”, AuthKey
Send the request:
http_matches.Send
Parse the response into an array:
Set Match_List = ParseJson(http_matches.responseText)
Then you can put the data from that array right into an excel sheet.
Let me know if you have any questions.
2 Likes
Thanks for the input. I look forward to looking at your simulator.
I have been able to get the team list files for a given event right off TBA and into excel. This is major progress.
I am trying to the match results the same way. I suppose the match results has nested arrays or something in the JSON. In the past I have used an online JSON converter which gave me the conversion in a spreadsheet. For the matches each match has two rows in excel. Blue is always first and red is always second. All of the generic info about the match is only on the blue line. Once in excel I can copy the generic stuff to the red line as well and then manipulate however I wish. I can get the info (unsaved JSON I think) and parse it. I can put all the match number in a column on a spreadsheet. I assume I can do that to the other generic values. Anything related to blue or red I cannot seem to being able to reference correctly.
alliance__| alliance__|__team_keys__001 match_number
blue frc1111. 1
red. frc1112
The above is what the excel looks like from the conversion. There are team keys 002 and 003. Match_number is several columns away.
How do I address or reference or whatever to get to frc1111? I keep playing with ITEM(" ") and can’t make it work. I want to grab frc1111 and put it in a cell.
Note also that the JSON file does not have any of the alliance stuff in there. I suppose that gets added during the parse but don’t know that for sure.
If it were easier I would also be OK with dumping the results on a spreadsheet tab but don’t know how to that either. Is the parsed file just an array? How do I know or where can I get the size if it is?
I am getting close to getting where I want to go. I would greatly appreciate any help you might have to get me over what I hope is this last hurdle. Thanks in advance.
Sub match_import_example()
AuthKey = “OPAQcOwlQKX5NwMAl7gYZrs58eanDSi8ZLQXVlOEYc0XFsAI75rNWiDI5ryhol6P” '2020 Event Simulator
Set Match_List = New Collection
Dim http_matches As Object
Set http_matches = CreateObject(“WinHttp.WinHttpRequest.5.1”)
http_matches.Open “GET”, “http://www.thebluealliance.com/api/v3/event/2020ndgf/matches”, False
http_matches.SetRequestHeader “X-TBA-Auth-Key”, AuthKey
http_matches.Send
Set Match_List = ParseJson(http_matches.responseText)
Set Match_1_Data = Match_List(1)
Set Blue_Data_Match_1 = Match_1_Data(“score_breakdown”)(“blue”)
blue_auto_points = Blue_Data_Match_1(“autoPoints”)
blue_endgame_points = Blue_Data_Match_1(“endgamePoints”)
Sheets(1).Cells(3, 2).Value = blue_auto_points
Sheets(1).Cells(3, 3).Value = blue_endgame_pointsEnd Sub
Here’s some code to do a simple import of match data.
And here’s the excel file it runs in: https://github.com/inkling16/MiscellaneousStatisticsProjects/raw/master/match_import_example.xlsm
If you go into this code and set a breakpoint, you can see how to reference the data in the Locals window by expanding out Blue_Data_Match_1.
I’ve always ever just put the data into sheets one point at a time, just cycling through with for loops. There may be a way to dump more data all at once but I can’t help much on that front so you’d have to figure that out yourself. 
Thanks again for your help. I looked at your simulator earlier today and figured out how to get it working. What you just sent might be more elegant so I will check it out. Thanks again.
You have been so helpful I almost hate to ask this but figured you could get me to the answer as fast as anyone. I want to be able to get match data from TBA for matches 30-32 only without taking the time to process through the other 90 matches and then filter it out. Can I do that some how in the GET request?
I suppose the other thing that might work is the last modified thing. I don’t remember the exact title right now. How does that work? I assume that a request comes back with a date/time stamp. If you use it then it filters out anything that has not be modified (or whatever) since that time. Is this correct? I need better understanding of how to do this in VBA.
If you have other methods I am certainly open to looking at those as well.
Thanks again.
Oh no worries, I’m happy to help. Check out the apidocs for the blue alliance here: APIv3 - The Blue Alliance
That has a list of all of the get requests you can make and what the responses will look like. It sounds like you might want the “​/match​/{match_key}” request. For example:
https://www.thebluealliance.com/api/v3/match/2020orore_qm30/simple
I had good results with the alliance data (red and blue team lists) in terms of manipulation. I attempted to do the same thing with the score_breakdown elements. When I run the code all of the score_breakdown values come back empty even though I know that isn’t true. Are “alliance” and “score_breakdown” at the same level? From your example above it appears that they are equal so I can’t figure out why one works and other does not. I assume I typed “score_breakdown” wrong but can’t seem to find it and it isn’t that complicated. Any other thoughts?
I have looked at the API doc on TBA. I still don’t understand how to use “last-modified” or “if-modified-since”. I assume that “last-modified” is a time stamp for the last edit of a particular event. How do I get to it? I would also guess that I should save that value somewhere and send it back as the compare value with “if-modified-since”. How do I do that?
I am still having difficulty pulling only a single match. In the example you sent about this you used the simple version. I think I want the full version so I leave simple off the end. Why did matches in the schema change to match in your example? Can I add a match number in a request header? I cannot get what you sent to work.
Thanks again for your help.
Progress is good!
For this request: https://www.thebluealliance.com/api/v3/match/2020orore_qm30, yes, "alliance and “score_breakdown” are at the same level. If you just click right on that link, you can see how the response will be structured in your web browser, including the “levels”.
These headers are not required, let’s solve your other problems first before worrying about this one.
Correct, this request: https://www.thebluealliance.com/api/v3/match/2020orore_qm30 should give you the full match data for that match only.
No, the match number will be included directly in the request url, you cannot add it as a separate header.
Which thing that I sent? The full excel file here? The suggested request here?
Here is a demo macro that selects match data from a single match, let me know if that works for you: https://github.com/inkling16/MiscellaneousStatisticsProjects/raw/master/single_match_import_example.xlsm
I am a new user and they won’t let me upload a file. Can I email you my files?
Of course, my email is [email protected]
1 Like
I just sent the email. Thanks again.
This topic was automatically closed 365 days after the last reply. New replies are no longer allowed.