Automating the 40 man roster

Hey guys, at this point I’m not even sure if it is possible but I started to automate the process of setting the 40 man roster (based on Trey’s roster guideline (great article btw @LuckyStrikes) , even if it is just to get an indication of major holes, or how the team looks as an actual roster rather than a list of players on the Ottoneu Roster overview.

Basically the user needs to put in the league and the team number (dropdown based on league). The rosters will then be loaded, most valuable positions assigned (based on C, SS, 2B, 3B, OF, and 1B), ranked per position and then assigned to the 40 man roster. Prospects definitely are still in the works.

The file is in Google sheets here. (PS: the blue / orange indicates if a player is on the 40 man roster)

My question to you is if you know if something like this already exists or has been tried before, and if it can be done in a valuable manner? Right now I’m just learning Google Sheets, and I don’t have to manually create my own 40-man roster, so I’ll get something out of it regardless.


PS I got most info from @LuckyStrikes , @eamuscatuli and Tanner Bell (is he even playing Ottoneu?)


This is excellent, thanks RJ!

Cheers Graham.
I have made some updates today to load in more league info (budget and number of players per team, etc.) and keeper indicator so the 40-man roster will only be filled with keepers. This shows me where my gaps are, which I then can use to look at the free agents per position for the selected league.

Next step is to add auction value guesstimates based on the empty spots in the league per position based on the available players per position. I just need to figure out how to load the free agents per league from the Fangraphs website (it loads the first 30 but I cannot find a way to make that more than 30; I tried the typical &pages1_50 etc. but no luck so far.) And now my whole Google sheet stopped loading, so time to o over home and leave it for tonight / tomorrow.


1 Like

Brief update here; I have started working on a different sheet as I was taking a new approach that doesn’t use as many IMPORT calls; the current report was getting too slow. Here are some screenshots of the new one:

There is a lot of overlap with Justin’s Surplus Calculator (which by building my sheet I now understand far better and appreciate even more @eamuscatuli). I need to double check everything but Keepers can be indicated and changed manually to change the tables.

The 40 man rosters gives me an idea of my team and other’s team are, of course more valuable after January.

It also shows me, based on rough calculations what positions will be high in demand (based on Justin’s numbers for players required to meet position caps) and the last two tabs show available Free Agents in the specific league (Majors and Minors).

I need to do some checking before posting, although I still may decide to hold off until after the auction (since @Lt_Fluffski is in one of my leagues… :kissing_smiling_eyes:)

1 Like

Hey, RJ. When I went to use the older version of your sheet, it wouldn’t load my league or roster into the sheet. I added my league # into the correct cell as well. Any idea what I could be doing wrong? Also, any ETA on the updated version? It looks pretty cool.