Building a Better Google Sheets Draft Board
Using Google Sheets to run an offline fantasy draft
I started playing fantasy baseball in 2012 and realized how much better it was than fantasy football. Where fantasy football pretty much hinges on the draft and hoping your team stays healthy for the season, fantasy baseball is flexible. You can rebuild mid-season, make a run, and win with offense and defense. It seems to be a never-ending well of strategy. After my first year of fantasy baseball, my days of playing fantasy football ended altogether.
The first few years of our fantasy baseball league were spent in a 10-team redraft league on Yahoo with standard categories. That league grew to 12 managers then to 14, and from 5x5 categories to 10x10 categories (we even spent a year with 11x11). In 2016, we expanded from a redraft format to a keeper league, with the addition of a prospect system tracked in Google Sheets. Around 2018 we decided to leave the Yahoo draft client and start using Google Sheets for all our drafts. Google Sheets allowed us to completely customize the rules of the draft, it allowed off-season draft pick trading, and finally, we were able to draft prospects. Since then the draft sheet has come a long way, so today I want to discuss how we pick our keeper players and draft clients we use now.
The Draft Board - What you see
The Draft Board contains the full list of draftable players and ranks, along with the stats from the past year. During a draft, Picks are made on the Draft Tracker next to the manager's name. The Draft Board updates with each draft pick and shows the pick number and the Manager who picked the player. Draft participants can select their pick on their turn or have picks added by one admin.
For our drafts, my league uses a discord chat to talk and type picks into the discord channel, and then an admin records the picks on the Draft Tracker tab. Picks are added to the Best Available tab, which updates to show only the undrafted players.
Draft Framework - What you don’t see
These significant components allow everyone to see what's going on and follow the draft.
- Player Data holds the list of players and their stats [Hidden Sheet]
- Admin holding place for formulas and other data to make the draft sheet work [Hidden Sheet]
- Drafter Tracker Where draft picks are entered
- Draft Board, Best Available, Results present the draft results in different ways and present the data to the managers.
The flow works like this:
Query + Logic - The Functions that make it work
This is not a tutorial, just an overview of how it works. If you are interested, let me know in the comments and I can put together a full tutorial. If you have used Query in Google Sheets, you already have a pretty good idea of how this works.
For getting started, here is a great beginner's guide to the query formula:
https://www.benlcollins.com/spreadsheets/google-sheets-query-sql/
So what’s so great about Query?
QUERY allows you to select data ranges by column and add rules or logic to what you are grabbing.
So if I select a player in the Draft Tracker tab next to a manager name, I can use a QUERY function in the Player Data tab to basically ask this: Look at the Draft Tracker tab, if there is a player’s name next to a managers name, then display the manager name next to that player’s name on the Player Data Tab.
Function: =query('Draft Tracker'!$B$2:$I$368, "Select H Where I = """&H3&"""",0)
That will allow for a relationship between picked players and managers. Now drafted players can be sorted out to present the Best Available list. Using QUERY we can look at the Player Data tab, pull a list of all players, without a team assigned to them, sort by draft rank, and create a Best Available list that updates as players are drafted.
Function: =query('Player Data'!D1:AM, "Select L, I, J, K, N, AJ, O, P, Q, R, S, T, U, V, W, X, AK, Z, AA, AB, AC, AD, AE, AF, AG, AH, AI where D is null and L is not null ORDER BY L ASC",0)
This is way better than searching down a long list of keepers and drafted players to find the best first baseman left.
Where to Go Next
This format works great with a lot of information in a small package. Doing everything in Google Sheets allowed my league to incorporate unique rules that have made the league weird but competitive and fun each year. Best of all it solved all of the issues we used to have with the standard Yahoo draft client.
Google Sheets opened up our league draft to improve each year. Here are some ideas I hope to incorporate in future drafts:
- Combine everything on 1 page so you don’t have to jump to new tabs.
- Create a way for managers to pick players from the Draft Board view.
- Add a way for each user to know when they are on the clock to pick.
If you have any ideas to improve this draft sheet let me know below in the comments.