Google Form Vote Counter

[zoraster]

Okay, so I've built a google form and spreadsheet that will do vote counts. Before the game each player gives a pin code to the mod who enters it in.

Votes are submitted by a google form here: https://docs.google.com/forms/d/e/1FAIp ... Q/viewform

It then allows the mod to output something like this every so often:

VOTING FORM

Current Vote Count

Please note that votes are not listed chronologically here.

Most Up to Date Votes

Previous 25 Vote Transcript

Spoiler: 25 Vote Transcript

Text Vote Count

Spoiler: Text Vote Count (For Vote Analysis)

Name Votes
Zoraster 2 Vi hitogoroshi
Vi 1 Zoraster
SpyreX 0
Gordon 0
Frank 0
hitogoroshi 0
Melvin 0
Sherry 0
Equinox 0
Untrod Tripod 0
Cameron 0
Elway 0
William 0

VOTING FORM

Two major problems at the moment and one minor one I'd like help with:

Major:
1. When the hammer is reached, the mod won't know about it if a vote is changed. The mod could go back and figure it out from the transcript, but this is designed to make life easier and be foolproof. Is there a google spreadsheet thing I can do to have a switch go if a certain value is reached that won't go back if the number goes down? In other words, I'd like Cell A1 (or whatever) to contain the # needed to lynch. IF Vote Total Cell is ever equal to or greater than A1, B1="Yes" EVEN IF Vote Total Cell is currently below A1.

2. The charts will automatically update based on current votes. The solution is to provide each time a text version of the vote count, which isn't hard to do (just a copy and paste job into the vote count post template), but it's not elegant. Not sure there's a good solution to this.

Minor:

The vote count doesn't show chronological votes. In the example, Vi will ALWAYS be to the left of hitogoroshi. This isn't a huge problem as time stamps and transcripts are provided, but it does remove one nice feature of hand-done vote counts.

---

It can work for up to 30 players with minor adjustments.

[Zachrulez]

Looking through formulas on excel it looks like you can use an "if" function on a formula. So if you get to the lynch threshold it's meant to display an alert. I don't know if the alert stays if the lynch threshold is broken though. Not sure if it translates to google's system either.

http://ccm.net/faq/1114-basic-excel-for ... f-function

[zoraster]

yeah the problem isn't the if formula. it's that it'll display "Hammer" or whatever I want if and only if the value CURRENTLY is at hammer. Given that the vote count is in real time, anyone who votes off the hammered person will cause it not to read "Hammer" even though the hammer has been reached.

[Kagami]

Make a vb function that writes "hammer" to an arbitrary cell. Mod deletes the message on new day

[Kagami]

2 can be done with straightforward string manipulation functions

[zoraster]

can you walk me through those? I'm not much of a coder. I can give access to the current spreadsheet iteration.

Where I'd like to put the hammer trigger is in a sheet called

Vote Count Sheet 2

Cell C3.

---
I'm not sure what you mean by "straightforward string manipulation formulas"

[zoraster]

Right now my hammer trigger (that only shows when it is CURRENTLY hammer) formula is:

=IF(COUNTIF(B4:B34,">"&'Player Names'!E1)=0,"No Hammer","HAMMER HAMMER HAMMER")

Where B3:B34 is the vote per person, 'Player Names'!B1 is a cell that gives the number required to lynch (defaults to Players Alive/2 but can be manually set to something else).

[Kagami]

Doing 1 with a typical formula is impossible (I think), because excel wants doesn't want to have its current state be dictated by anything other than what it currently is. The solution is to write a VBA formula that says something like "If <cell> > <threshold> Then <hardcoded cell index> = "HAMMER!".

If I understand 2, it can be done with string formulas. It's just going to end up being a giant set of &s, ifs, and maybe some string trimming.

[zoraster]

What are you trying to do to solve 2? Becuase I feel like #2's problem is very similar to #1s in that it's about capturing a certain point in time.

[Kagami]

Aa, I see what you're saying, you want to keep a snapshot of all the VCs as they occur.

One solution is very similar to 1, you'll need to write a VBA function that looks for a specific range of cells, finds where the historical VCs end, pushes them all down, then writes the current VC to the top.

[Kagami]

I'm thinking in excel, btw. I just realized we're talking about sheet, and I'm not really sure how scripting works there.

[Kagami]

function writeToMagicCell() {
SpreadsheetApp.getActiveSheet().getRange('K6').setValue('o hi there');
}

^ this is the kind of thing you'll have to do.

[zoraster]

Google Spreadsheets uses Google Apps script, which is based on Javascript

[Zachrulez]

yeah the problem isn't the if formula. it's that it'll display "Hammer" or whatever I want if and only if the value CURRENTLY is at hammer. Given that the vote count is in real time, anyone who votes off the hammered person will cause it not to read "Hammer" even though the hammer has been reached.

I feel like there's a way to have a triggered output that goes when a threshold is hit and then ignores all future states, but it's been a long time since I studied excel so I'm not sure if I'm remembering wrong or if it's something I pulled off with a buggy formula...

[PokerFace]

You may want to reach out to thesp if he still frequents the forums. He made a vote counter you could get code help from
http://www.robandkriskris.com/votecounter/
http://forum.mafiascum.net/viewtopic.php?f=5&t=39491

[Realeo]

^- And it still works.

[zoraster]

Yeah. That's not what I'm looking for

[Zachrulez]

Yeah my understanding is that the advantage this one would offer is a dynamic live updating of votes. (Which is why the pitfall of the hammer condition being removed is an issue.)

Edit: Oh, I just looked at the form and it looks like you would vote with the form rather than on site? It's a different way of going about it but that actually would offer some advantages in that it basically eliminates any element of fake voting that has happened on the site in the past.

[Realeo]

Why I feel that using this idea, there is a higher chance of accidental hammering?

[zoraster]

Why I feel that using this idea, there is a higher chance of accidental hammering?

Oh, there absolutely is if for no other reason than MS's post system stops you from posting when there's another post.

But that can be addressed in game rules.

[Realeo]

Ah, so you vote on the Google Form and on thread for double counting? Makes sense.

[zoraster]

eh, probably not. Part of the reason to do this is to make vote counts more accurate and nearly instant. I suspect people would OFTEN vote both places but the only official one would be through the form.

What I meant by "rule" is you could put into place a rule that if a vote is the hammer placed within 5 minutes of the L-1 vote, a certain amount of time is given for the hammer voter to move off. Or some such. If that's a concern.

One other way to address it would simply be to use a different style of game, which is to make hammers not automatically end the day. Perhaps ending 24 hours after the Hammer is noted by the mod or something if and only if the hammer is still there.

[Kison]

I believe there is an onSubmit event you can have trigger when a Google Form is submitted. You may be able to have it scan the sheet, determine whether a hammer already has been placed, and either block the submission or perform some additional write to the sheet in that event.

[PeregrineV]

Major:
1. When the hammer is reached, the mod won't know about it if a vote is changed. The mod could go back and figure it out from the transcript, but this is designed to make life easier and be foolproof. Is there a google spreadsheet thing I can do to have a switch go if a certain value is reached that won't go back if the number goes down? In other words, I'd like Cell A1 (or whatever) to contain the # needed to lynch. IF Vote Total Cell is ever equal to or greater than A1, B1="Yes" EVEN IF Vote Total Cell is currently below A1.

You could have a log that completes each time a vote occurs?

https://docs.google.com/spreadsheets/d/ ... sp=sharing

Check the hammer condition each time a vote is placed.

[eagerSnake]

Major:
1. When the hammer is reached, the mod won't know about it if a vote is changed. The mod could go back and figure it out from the transcript, but this is designed to make life easier and be foolproof. Is there a google spreadsheet thing I can do to have a switch go if a certain value is reached that won't go back if the number goes down? In other words, I'd like Cell A1 (or whatever) to contain the # needed to lynch. IF Vote Total Cell is ever equal to or greater than A1, B1="Yes" EVEN IF Vote Total Cell is currently below A1.

Simple just make it so when hammer is reached it changes the cell that contains the formula that detects when the hammer is reached so it won't change the cell back if someone unvotes

i.e. in cell C6 put the formula that if any of cells B4:B34 becomes greater than the hammer number then delete the formula in c6 and change cell c6 to the word hammer, or even simpler just have it write the word hammer in cell c7 if any B4:B34 ever reaches the hammer number