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

Eiim · August 23, 2018, 12:38pm

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)

nuclearnerd · August 23, 2018, 1:11pm

Ahmazing!

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

ngreen · August 23, 2018, 3:52pm

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.

Eiim · August 23, 2018, 5:38pm

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.

jaredhk · August 23, 2018, 7:53pm

Brilliant. This makes me obsolete.

AllenGregoryIV · August 23, 2018, 8:00pm

This is very useful, thanks for making this.

jjsessa · August 23, 2018, 8:04pm

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!

Skyehawk · August 24, 2018, 12:49am

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

ngreen · August 24, 2018, 1:25am

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.

jaredhk · August 24, 2018, 1:41am

Me too actually. Hadn’t tried that yet.

ngreen · August 24, 2018, 2:27am

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

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

shouldn’t be there.

github.com

Eiim/tba-requests/blob/master/TBA.gs

// Begin Base Scripts
/**
 * Checks if a value is hexadecimal.
 *
 * @param {text} c The value to test.
 * @return TRUE/FALSE.
 * @customfunction
 */
function isHex(c) {
  try {
    if (/[0-9a-f]+/.exec(c.toLowerCase())[0].length === c.length) {
      return true 
    } return false
  }
  catch (err) {
    return false
  }
}
/**
 * Converts epoch (unix) time is seconds to GMT-style date object.

This file has been truncated. show original

Eiim · August 24, 2018, 7:43am

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

Billfred · August 24, 2018, 7:47am

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

lynca · August 24, 2018, 2:41pm

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

ngreen · August 24, 2018, 3:03pm

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.

jaredhk · August 24, 2018, 4:52pm

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

ngreen · August 31, 2018, 6:31pm

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.

Attention · September 21, 2018, 1:15pm

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?

kcbimonte · October 2, 2018, 2:43pm

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

Eiim · January 3, 2019, 11:57pm

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