Is there anyone that could share an example of a Google Sheets App Script that uses the Robotevents API? I’m a “stack overflow” style of programmer. 
I’ve looked through a few of the posts on the forum but can’t get it to work.
OK… So I got it to work.
This will pull the skills info for the IQ Teams in the North/Central FL region. It will also retrieve the teams registered for the state tournament already.
Then the list on the left are the teams that would be pulled thorugh skills if the season were to end today.
The RE API doesn’t have access to stop times or timestamps for skills, so I can’t properly enforce the tiebreakers.
BUT it’s pretty cool. With the Google Sheets custom functions, I just write an apps script and then I can call it directly from Google Sheets.
I have:
=getTeamsFromEvent(“RE-VIQRC-23-2712”)
This will return the list of registered teams from that event. This fills down.
I also wrote:
=getSkillsFromTeam(“11029S”)
This will return the team’s [bestSkills, auto, driver, bestAuto, bestDriver]. This fills across. It just depends on how you format your arrays to determine how it comes out.
There are a couple of things that were a little difficult, but most of the tools are there to make it work.
Can you explain a bit more how you got this working? I’ve used Google sheets to make a tool that tells you how many matches each team has to go until their next match for judging but this requires you to input the teams list and match schedule manually. I think this could simplify it a lot and has a lot of potential for future tools.
As an alternative to Sheets, robotevents.com provides CSV or XLS outputs from most of the screens. Then it’s a 5 minute project using your favorite database tool .
I’m sure there’s a meme for this…
Asks for example code —> Doesn’t provide example once they figure it out
Lol here you go!!!
// getREData(url) will return a JSON for the url sent. See the API to get those together. This will not handle multi page data yet, but that wouldn't be too hard to do. I use "&per_page=250" and with this work I'm not worried about hitting the 250 limit. (But I should do it correctly and the additional pages.) If someone does it send it my way to add it in.
function getREData(url) {
var headers = {
"headers":{
"accept": "application/json",
"Authorization": "Bearer ###TOKEN###"}
};
var response = UrlFetchApp.fetch(url, headers);
var content = response.getContentText();
var data = JSON.parse(content);
return(data);
}
// This is just a testing function that I used as I wrote the code.
function retestcode() {
temp = getSkillsFromTeam("11029A"); // Not my school anymore, but my first team to qualify to WORLDS.
Logger.log(temp)
}
// This will return an array goven the team number: [[robotSkills,autoPart,driverPart,bestAuto,bestDriver]]
function getSkillsFromTeam(teamNumber){
// Turn the team number into their ID
var teamCode = getREData("https://www.robotevents.com/api/v2/teams?number%5B%5D=" + teamNumber + "&myTeams=false&program%5B%5D=41&&per_page=250");
teamCode = parseInt(teamCode['data'][0]['id']);
// Get all the skills scores for that team by ID
var listSkills = getREData("https://www.robotevents.com/api/v2/teams/" + teamCode + "/skills?season%5B%5D=180&per_page=250");
listSkills = listSkills['data'];
// Get the events that the team went to from that list. I could do that with a seperate call but I already have the data from the scores. You need to match the programming and skills scores to add them together. I don't think this would work with leagues.
var eventList = [];
for (var i = 0; i < listSkills.length; i++) {
if( !eventList.includes(listSkills[i]['event']['id'])) {eventList.push(listSkills[i]['event']['id']);}
}
// I then go through each event, pull out the scores for that event, then add them together and save them if it's a better score. I'm not worried about tiebreakers at this point, especially because we don't have the data needed to do that properly. I assume there's some way to do a pivot table and just sort it but I don't know how. This wasn't too hard though, just clunky. We return a -1 if we don't find a skills score.
var robotSkills = -1;
var driverPart = -1;
var autoPart = -1;
var thisdriver = -1;
var thisauto = -1;
var bestAuto = -1;
var bestDriver = -1;
for (var i = 0; i < eventList.length; i++) {
for (var j = 0; j < listSkills.length; j++) {
if(listSkills[j]['event']['id'] == eventList[i]) {
if(listSkills[j]['type'] == "programming") {
thisauto = listSkills[j]['score'];
if (thisauto > bestAuto) {bestAuto = thisauto}
} else {
thisdriver = listSkills[j]['score'];
if (thisdriver > bestDriver) {bestDriver = thisdriver}
}
}
}
if(thisdriver + thisauto > robotSkills) {
var robotSkills = thisdriver + thisauto;
var driverPart = thisdriver;
var autoPart = thisauto;
}
}
return [[robotSkills,autoPart,driverPart,bestAuto,bestDriver]];
}
// This function will take an RE code and return a list of teams.
function getTeamsFromEvent(reCode){
// Turn the code into an event ID.
var eventcode = getREData("https://www.robotevents.com/api/v2/events?sku%5B%5D=" + reCode + "&myEvents=false");
eventcode = parseInt(eventcode['data'][0]['id']);
// Get the teams from the event.
var eventTeams = getREData("https://www.robotevents.com/api/v2/events/"+eventcode+"/teams?myTeams=false&per_page=250");
eventTeams = eventTeams['data'];
var teamList = [];
// Iterate through the list to get just the teams.
for (var i = 0; i < eventTeams.length; i++) {
teamList[i] = eventTeams[i]['number'];
}
return teamList;
}
That’s what I’ve done in the past, but then it requires maintenance. When I open the sheet this morning was automatically refreshing everything. It probably took me more time to figure it out then I would have collectively taken to get the CSV files over the next several years, so part of me just wanted to figure it out.
If it’s done automatically it’s also going to be more accurate. Of course that doesn’t really take into account that for this particular project I can’t even get the information needed to make it accurate. But it does take human error out of the process.
Something not everyone knows - Tournament Manager hosts the data locally at the ip address of the server as a simple web page. If you know that address you can make standard spreadsheet data queries to get things like team list, schedule, results.
Agree and really like what you have created!! Your solution is much better! I was just mentioning the availability of CSV alternative for those that may not have sufficient programing skills.