Help me crowdsource college football data!

I mentioned it in another thread, but as a side project, I'm starting to build out a predictive model to identify what factors affect recruiting success, and quantify how much each factor affects recruiting. The end goal would be able to say "VT needs to hire coaches with X experience and raise Y more dollars in order to recruit a class with a ranking/rating equal to Z." Obviously, there's a lot of data I need in order to create this model, and I'm hoping I can crowdsource some (most) of it.

One of the factors I'm interested in researching is location, specifically:

  • How close is a school located to quality recruits?
  • How close is a school located to rival schools (aka how close is a given school's recruiting competition)?

To collect data on these questions, I've created a google spreadsheet with two tabs:

  • Tab 1 is meant to capture the distance between a recruit's high school and a given P5 university.
  • Tab 2 is meant to capture the distance between each P5 university.

Here's where you come in - I need help populating thousands of cells in my document. How do you find this distance you ask? So far, I've been doing it manually via google maps. For example:

  1. Paste 'A C Flora (Columbia SC)"' (from cell A2) into google maps
  2. Paste 'Boston College' (from cell B1) into google maps (double check to make sure it's the right place
  3. Click Map
  4. At the intersection of the two boxes, type in the shortest mileage route by driving distance (for this example, 915 miles)

Here's my spreadsheet, anyone with this link should be able to edit it, and add info to it. I know this painful work - if anyone knows of a quicker way to capture this data, I am all ears.

Finally, as a quick aside, yes, there is a lot more other than just location data that I plan to analyze (coaching experience at different levels, coaching salaries, athletic budget, etc). This is a starting point to see how crowdsourcing this data will go.

Forums: 
DISCLAIMER: Forum topics may not have been written or edited by The Key Play staff.

Comments

This should be scriptable. You should be able to use the Google Maps and Sheets API to get and set what you need provided you have location information to query.

Have a good resources for learning how to write scripts?

Twitter me

I have very little coding experience - I'm not sure how to use that API to auto-populate all the cells of a spreadsheet. Open to learning though.

Twitter me

Hey so I have good news and bad news. The good news is it looks like the google maps api hypothetically could get us exactly what we need. It looks like you can basically ask for directions between two addresses and it will tell you the difference, just as if you were using the google maps website. The bad news is it costs money. Apparently google spiked their prices a few months ago and developers are pissed. https://ppc.land/google-increases-prices-of-google-maps-api-and-develope...

If my math is right you get the first 40,000 api calls in a month "free" and then you have to start paying a little bit. That may sound like a lot but to get the distance between the 65 colleges you'd need to make 4,225 calls and to get the distance between the 65 colleges and 2246 high schools you'd need to make 145,990. That's not including any errant calls made to test the program. Any thoughts on how to pare that down? Also feel free to check my math.

https://cloud.google.com/maps-platform/pricing/sheet/