Process:
- Extract sensor IDs (first split columns on "(" then removed ")" - there are probably better ways)
- Add Record ID
- Count number of sensors
- Generate rows for each sensor
- Append with URL for data
- Replace "303" in URL with each sensor ID
- Download data for each sensor
- Split to rows (on "\n")
- Cache data - the total was approaching 1GB!
- Filter out data beginning with "#"
- Filter data to capture most recent 7 days (I used 2018-01-23 so I could compare my results to the "real answer")
-- Something went wrong with the filters but I still got there - no time to debug as the overall solution still worked - and I should be working right now too!)
- Text to columns on ","
- Flag the most recent 7 days of data (since my filter failed somewhere)
- Multi row tool to add 7 days of snow accumulation
- Filter for 2018-01-23
- Sort by Snow accumulation (descending)
- Select first record
- Add points on Lat and Long
- Append with Ski Area data
- Calculate distance from sensor to Ski Area
- Sort by distance (ascending)
- Select first record
- Sensor 840 (Upper San Juan) had 1.4 inches of snow accumulation and was closest to Wolf Creek
Not quite the same (1.4 vs 1.3) as official answer, but I think I got the process reasonably there