I’m the scouting captain for my team. Our school recently gave every student a chromebook and I am taking advantage of that by creating a spreadsheet for the entire scouting team. Scouts (Both Pit and Stands) will input scouting data and the spreadsheet will compile that data by creating a profile for every team at tournaments as well as creating leader boards that select the best teams in every category (Examples include best at placing hatch panels on rocket or best at scoring points during sandstorm).
My current problem involves me trying to sort pit scouting data to a specific team profile for a specific team. I linked a Google Forum to the spreadsheet. Can’t post more than one image since I an considered a new member.
Here’s what a single team profile looks like. The purple is data collected by the pit scouts, the blue the stand scouts:
The pit scouts will most likely not visit the first team numerically when pit scouting, so I need to figure out how to assign the correct data to the correct team. I also don’t think that the pit scouting data will come in in just one day, So I can’t just do =SORT(‘Pit Scouting Data’!B2:B70) because say if we didn’t scout team 4480’s pit, all teams that follow after 4480 numerically that we scouted will be assigned to the wrong profile, showing the wrong data. I can’t figure out how to create formulas that will look for a team number on the pit scouting sheet and assign the pit scouting data for that team to the correct team profile.
You can have a if statement to check if they are the same and if not.
I’d be willing to help you work out your specific issues (I’m pretty good at google sheets!)… but before going down that route, have you considered how you’ll be accessing this from the venue? You will not have access to wifi for your chromebooks, which could pose a big issue - I don’t believe google forms works offline, and even if it did, you wouldn’t be able to sync the input from multiple laptops together until you got back to the hotel, which diminishes the value of the scouting (We enjoy using it to prep for qual matches, in addition to alliance selection).
1 Like
To add to that, you could do everything offline on different devices and then sync the data to one main computer as an export rather than a live connection.
That way, you can make sure that you have all the info at one time and can correctly populate each team page.
I would look into using the Query function - it’s a very powerful tool which might help you solve your problems.
For organizing the actual data, I would recommend having two separate data tabs your info feeds into. One for pit scouting, a second for stand scouting. Since the data collected is different, this makes sense. You can then have a page like the image you selected - pick the team number in one cell, and then have formulas in the other cells to find that team’s info.
You’ll have the ability to have multiple rows per team in both data sheets. So, depending on which piece of data it is, you’ll want to sum, average, count, etc that column for the team. If you use the FILTER function, you can do that. Something like:
=sum(filter(‘Data1’!B:B,‘Data1’!A:A=A1))
Other functions to check out: query and vlookup
If you want more specific help, shoot me a PM. If you share the sheet with me, I can add recommendations and assist directly in it (and, if anything crops up at competition, I’ll be in the area - volunteering at Northern Lights and North Star, just across the hall/street from your two events!).
I have a spreadsheet I created for FTC that might give you some ideas for how to go about this. I use query and vlookup to take care of the individual team profiles.
Here’s the link so you can take a look: https://docs.google.com/spreadsheets/d/1He_9gCWEKNFCWjsI9JY2JcCFdHX1zZe6xrY3VWo0bAA/edit?usp=drivesdk
PM me if you have any questions. Hope this helps!
Also, one unrelated thing to consider if you’re relying on Chromebooks for match scouting: figure out what you’re going to do when the batteries die (because it’s never “if the batteries die” in important situations).
You may want to source an inverter and plan on borrowing one or two of your older robot batteries in order to be able to charge them from your seat in the stands.