◈ Technical Documentation
A brief technical breakdown for people who want to dive into the code. Tinker at your own risk.
✱ Spreadsheet proficiency is assumed; array forumlae and query language are used.
Contents
- Config
- Player List
- Player Dropdown
- Vote Input
4.1 Inputs
4.2 Canonize names, resolve unvotes
4.3 Formatting
4.4 Counting & tiebreaking
4.5 Info & debug
- Compute
- Output
- Todo List
△ Back to Page Top
1. Config
Nothing interesting worth documenting here, just a bunch of self-explanatory inputs for configuration/customization.
▲ Technical Documentation Contents
△ Back to Page Top
2. Player List
Player roster management. Nominally accommodates up to 49 player slots.
Col A: <input> player roster at start of day phase.
Col B: <input> mid phase replacements.
Col C: "Current" player — canonical list of current player names; used for computation purposes.
C2 =IF(LEN(B2)=0,A2,B2)
If no replacement, use original player name (A2), else use replacement player name (B2)
Col D: "Display" — for presentation purposes, wagon will display as "ReplacementName (OriginalName)".
D2 =IF(LEN(B2)=0,A2,B2&" ("&A2&")")
If no replacement, just use original player name (A2), else concatenate text as per format described above
▲ Technical Documentation Contents
△ Back to Page Top
3. Player Dropdown
This is a hidden sheet; generates the list of player names used for data validation in the vote input sheet.
Dropdown lists for voter/votee are populated/validated against here.
Cell A1: "no-lynch" if permitted by config, else empty.
A1 =IF(Config!$B$6="Yes","no-lynch","")
Cell A2 (up to A99):
A2 =SORT(UNIQUE(FILTER({'Player List'!A2:A;'Player List'!B2:B},NOT(ISBLANK({'Player List'!A2:A;'Player List'!B2:B})))))
Agglomerates nonempty cells in Cols A & B of the player list, removes duplicates as a precaution, sort alphabetically.
Cell A100: never used. The theoretical extreme case of 49 original players and 49 replacements will only occupy A2:A99.
▲ Technical Documentation Contents
△ Back to Page Top
4. Vote Input
Besides being the primary point of input, this sheet also does a lot of the heavy lifting.
Nominally accepts up to 499 vote changes.
This section is organized by functional/logical order, so do not be alarmed that columns are not in alphabetical order.
4.1 Inputs
Col A: <input> post URLs.
Col B: <input> voter.
Populated from / validated against the "Player Dropdown" sheet (A2 onwards).
Col C: <input> votee.
Populated from / validated against the "Player Dropdown" sheet — including A1: no-lynch appears if permitted by config.
4.2 Canonize names, resolve unvotes
Col E: row number.
Used as unique identifier. This is necessitated by the possibility of multiple vote changes in single post, which means that post URL / post number are not suitable for UID purposes.
Col F: stdB: canonical voter name.
F2 =IF(LEN(B2)=0,"", QUERY('Player List'!A$2:C,"select C where A='"&B2&"' or B='"&B2&"'",FALSE) )
The voter username in Col B may be the original player, or a replacement — standardize against the canonical list of current player names for computation purposes.
Col G: stdC: canonical votee name.
G2 =IF(LEN(C2)=0,"", IF(C2="no-lynch",C2, QUERY('Player List'!A$2:C,"select C where A='"&C2&"' or B='"&C2&"'",FALSE) ))
Similar to Col F above, except that "no-lynch" may also be an option.
Col K: unvote post row number.
K2 =IF(OR(ISBLANK(C2),ISNA(MATCH(F2,F3:F,0))),"", E2+MATCH(F2,F3:F,0) )
Check to see if this voter (F2) cast an unvote or a different vote later on (F3:F), thereby cancelling this vote.
If so, compute the row number where the unvote occurs.
Col L: unvote post link.
L2 =IF(LEN(K2)=0,"", INDEX(A:A,K2))
Grab the post URL of the unvote post, where applicable.
Col D: unvotee.
D2 =IF(OR(ISBLANK(A2),ISNA(MATCH(E2,K:K,0))),"", INDEX(C:C,MATCH(E2,K:K,0)) )
Col K detects unvotes. To see if this row causes an unvote, look for the current row number (E2) in Col K.
If a match is found, then this row causes an unvote. The player who gets unvoted on this row is the player who got voted in the earlier row that has this row as its unvote post.
Col H: stdD: canonical unvotee name.
H2 =IF(LEN(D2)=0,"", IF(D2="no-lynch",D2, QUERY('Player List'!A$2:C,"select C where A='"&D2&"' or B='"&D2&"'",FALSE) ))
Like Col G above.
4.3 Formatting
Col M/N: formatted vote for wagon.
N2 =IF(ISBLANK(C2),"", IF(LEN(L2)=0,"","[s]") & "[url="®EXEXTRACT(A2,"/forum/.*$")&"]"&B2&"[/url]" & IF(LEN(L2)=0,", ","[/s][url="®EXEXTRACT(L2,"/forum/.*$")&"][small] (X)[/small][/url], ") )
Voter name (B2) is wrapped in bbcode for display in the votecount wagon, with link to vote post URL (A2).
Strikeout and unvotepost link added if this vote was later cancelled by an unvote or a different vote.
URLs are canonized to absolute-path URL format using regex.
Col X: formatted vote for EoD vote list.
X2 =IF(ISBLANK(A2),"", "[*][url="®EXEXTRACT(A2,"/forum/.*$")&"]"&B2&" -> "&IF(LEN(C2)=0,"--",C2)&"[/url]" )
Generates formatted "Voter -> Votee" bbcode list item for EoD spoilered list;
URL canonized to absolute-path URL format using regex.
4.4 Counting & tiebreaking
Col O: active vote.
O2 =IF(LEN(L2)=0,G2,"")
If this vote is active (not cancelled by an unvote), put the votee name here, else leave blank.
This is used by the "Compute" sheet to count the number of active votes on each wagon.
Col P: votee - find prev vote.
P2 =IF(LEN(C2)=0,"", IFERROR(QUERY(E:H,"select max(E) where G='"&G2&"' and E<"&E2&" label max(E) ''")) )
Get the row number of the most recent row (max(E)) before this one (E2) in which the votee of this row (G2) was voted (G).
Col Q: votee - find prev unvote.
Q2 =IF(LEN(C2)=0,"", IFERROR(QUERY(E:H,"select max(E) where H='"&G2&"' and E<"&E2&" label max(E) ''")) )
Get the row number of the most recent row (max(E)) before this one (E2) in which the votee of this row (G2) was unvoted (H).
Col S: unvotee - find prev vote.
S2 =IF(LEN(D2)=0,"", IFERROR(QUERY(E:H,"select max(E) where G='"&H2&"' and E<"&E2&" label max(E) ''")) )
Get the row number of the most recent row (max(E)) before this one (E2) in which the unvotee of this row (H2) was voted (G).
Col T: unvotee - find prev unvote.
T2 =IF(LEN(D2)=0,"", IFERROR(QUERY(E:H,"select max(E) where H='"&H2&"' and E<"&E2&" label max(E) ''")) )
Get the row number of the most recent row (max(E)) before this one (E2) in which the unvotee of this row (H2) was unvoted (H).
Col R/U: votee/unvotee wagon size.
R2 =IF(LEN(C2)=0,"", IF(ISBLANK(P2),1, IF(P2>Q2, INDEX(R$1:R1,MATCH(P2,E:E,0))+1, INDEX(U$1:U1,MATCH(Q2,E:E,0))+1) ) )
U2 =IF(LEN(D2)=0,"", IF(S2>T2, INDEX(R$1:R1,MATCH(S2,E:E,0))-1, INDEX(U$1:U1,MATCH(T2,E:E,0))-1) )
Number of votes on the votee/unvotee respectively, up to and including this row.
In the case of the votee (R2), start the count at 1 if no previous vote exists (ISBLANK(P2)).
Otherwise, for both votee/unvotee, get their previous wagon size (INDEX into R$1:R1 / U$1:U1) based on whether they were most recently voted or unvoted (P2>Q2 ; S2>T2), and update the count accordingly (+1/-1).
Col V/W: tiebreaking value for votee/unvotee.
V2 =IF(LEN(C2)=0,"", -COUNTIFS(R$2:R2,"="&R2, C$2:C2,"<>''") )
W2 =IF(OR(LEN(D2)=0,U2=0),"", COUNTIFS(U$2:U2,"="&U2, D$2:D2,"<>''") )
The COUNTIFS returns a number which means "this is the nth time that someone attained the wagon size (R2/U2) by being (voted/unvoted)". For the votee, make the number negative. Recall the tiebreaking rules. Between wagons of equal size:
- if both got there by being voted, lynch the earlier one
- if both got there by being unvoted, lynch the later one
- if one was voted up and the other unvoted down, lynch the unvoted one
All of which translates into simply picking the one with the largest tiebreaker value as calculated here.
4.5 Info & debug
Col I/J: running tally.
I2 =IF(LEN(C2)=0,"", G2&" ("&R2&")" )
J2 =IF(LEN(D2)=0,"", H2&" ("&U2&")" )
Information at-a-glance: display the wagons affected by this vote/unvote as a running tally with updated number of votes.
Debugging aid: sanity check against votecount output, as it is calculated using a different approach (Compare Col O).
▲ Technical Documentation Contents
△ Back to Page Top
5. Compute
A misnomer; the function that this sheet serves is really closer to "collate" than "compute".
Col A: canonical player list.
A2 ={SORT(UNIQUE(FILTER('Player List'!C2:C,NOT(ISBLANK('Player List'!C2:C)))));IF(Config!$B$6="Yes","no-lynch",IFERROR(1/0))}
Canonical list of current player, sorted alphabetically, plus no-lynch if allowed.
Col B: player name for display.
B2 =IF(ISBLANK(A2),"", IF(A2="no-lynch",A2, VLOOKUP(A2,'Player List'!C$2:D, 2, FALSE) ))
"Display" version of player name
Col C: Had votes?
C2 =IFERROR(IF(ISBLANK(A2),"", NOT(ISNA(MATCH(A2,'Vote Input'!G$2:G,0))) ))
Search the vote sheet to determine if the player has ever had any votes on them.
Still returns TRUE after being unvoted back to zero votes.
Determines if player will show up as a wagon in votecount.
Col D: Votes.
D2 =IF(C2, COUNTIF('Vote Input'!O:O,A2) ,"")
Number of active votes on the player (current wagon size).
Col E: Last change.
E2 =IF( OR(D2="",D2=0) , "" , QUERY('Vote Input'!E:U,"select max(E) where (G='"&A2&"' and R="&D2&") or (H='"&A2&"' and U="&D2&") label max(E) ''",0) )
Get the row number of the most recent row (max(E)) where the player was voted or unvoted.
Col F: Tiebreak value.
F2 =IF( LEN(E2)=0 , "" , IF( INDIRECT("'Vote Input'!G"&E2)=A2 , INDIRECT("'Vote Input'!V"&E2) , INDIRECT("'Vote Input'!W"&E2) ) )
With row number of last change (E2) in hand, grab the associated tiebreak value (V/W) based on whether that last change was a vote or unvote.
Col H: Votelist.
H2 =IF(C2, CONCATENATE(FILTER( IF(Config!$B$7="Yes",'Vote Input'!N:N,'Vote Input'!M:M) ,'Vote Input'!G:G=A2)) ,"")
Collate all the votes ever cast on the player from the vote input sheet.
Col G: Wagon text.
G2 =IF(C2, "[b]"&B2&"[/b] ("&D2&") : "&MID(H2,1,LEN(H2)-2) ,"")
Put the whole wagon text together for the player.
Col J: Vote active.
J2 =IF(ISBLANK(A2),"", IFERROR(QUERY('Vote Input'!A:L,"select F where A!='' and F='"&A2&"' and G!='' and L='' limit 1",false)) )
Player name shows up here if they are currently voting, i.e. it is possible to find an entry in the vote input list where they (A2) are the voter (F) with non-empty votee (G) — not an explicit ##unvote — that has not been subsquently cancelled by another unvote (L).
Col I: Not voting.
I2 =IFERROR(IF(OR(ISBLANK(A2),A2="no-lynch"),"", IF(EXACT(A2,J2),"",A2&", ") ))
If player name doesn't show up exactly in Col J, then they are not voting, so player name shows up here instead and will later be collated into the non-voter list. Some extra handling for "no-lynch" who isn't actually a player that can vote.
▲ Technical Documentation Contents
△ Back to Page Top
6. Output
Everything is concatenated together in one huge formula to produce the output text.
1| A1=TRANSPOSE(SPLIT(
2| REGEXREPLACE(Config!B1,"(?m)^$"," ") &CHAR(10)&
3| JOIN(CHAR(10),IFERROR(SORT( FILTER(Compute!G2:G,Compute!C2:C), FILTER(Compute!D2:D,Compute!C2:C),0, FILTER(Compute!F2:F,Compute!C2:C),0 ))) &
4| IFERROR(IF( COUNTA(Compute!I2:I)=0,"", CHAR(10)&" "&CHAR(10)&"[b]Not voting[/b] (" & COUNTIF(Compute!I2:I,"?*") & ") : " & MID(CONCATENATE(Compute!I2:I),1,LEN(CONCATENATE(Compute!I2:I))-2) )) &CHAR(10)&" "&CHAR(10)&
5| "[blue][b]Currently, " & ARRAY_CONSTRAIN(IFERROR(SORT( FILTER(Compute!A2:A,Compute!C2:C), FILTER(Compute!D2:D,Compute!C2:C),0, FILTER(Compute!F2:F,Compute!C2:C),0 )),1,1) & " is set to be lynched![/b][/blue][hr][blue][b]The day phase will end at [u][date]"&Config!B2&"[/date][/u], about XX hours from this post."&CHAR(10)&"[big][b][countdown]"&Config!B2&"[/countdown][/b][/big] remain as you are reading this." &CHAR(10)&" "&CHAR(10)&
6| "Remember, voting is mandatory! Place your votes " & IF( Config!B3="game thread", "in this thread.", "in the [url="®EXEXTRACT(Config!B4,"/forum/.*$")&"]voting thread[/url]." ) &CHAR(10)&" "&CHAR(10)& "If you see any vote out of place, "&Config!B5&"[/b][/blue]" &CHAR(10)&" "&CHAR(10)&" "&CHAR(10)&" "&CHAR(10)&
7| "[hr][spoiler=Details][list]" &CHAR(10)& JOIN(CHAR(10),'Vote Input'!X2:X) &CHAR(10)& "[/list][/spoiler]"
8| ,CHAR(10)))
Line 2: Votecount header.
The regex replace quashes any stray blank lines above/below the header.
Line 3: Wagons are sorted and displayed here.
Wagons (Compute!G2:G) taken from the "Compute" sheet for display if they ever had any votes (Compute!C2:C).
Sort primary by number of votes (Compute!D2:D), secondary by tiebreak value (Compute!F2:F).
Line 4: Non-voting list.
Grab, count, and format the list of non-voters (Compute!I2:I).
Line 5: Footer text.
Get the player scheduled for lynch by sorting wagons (similar to Line 3) and grabbing first player.
Display they deadline and countdown text.
Line 6: Voting instructions.
Lines 1+7:
Takes the single huge multi-line string of text, and chunks it out into one cell per line (SPLIT on line feed).
This is a workaround for preventing stray quotes around the text when copied.
▲ Technical Documentation Contents
△ Back to Page Top
7. Todo List
Roadmap of things I might work on
- documentation: workaround for skipping post link input if backlinks are not in use
- feature: improved support for majority lynch & instant majority lynch games
(additional configuration options & appropriate footer text)
- optimization: replace tiebreak value with +/-rownum to reduce unnecessary calculations
▲ Technical Documentation Contents
△ Back to Page Top
EPT![[image loading]](https://s4.postimg.org/s03dz9dil/vc_scr.png)
![[image loading]](https://i.creativecommons.org/l/by-nc-sa/4.0/88x31.png)
