Discussion: Shifting some load off the judges.

[covaithe]

Hmm. How's this: (thinking out loud here)

1. Alice fills out the form to submit her new level 1 character, Noobicus Maximus. Yay! Nobody gets emailed. Noobicus gets added the the spreadsheet, which looks like this (it can have more columns, but these are the ones I want to see as part of the workflow).

   Date      Char      Player   Status1  Reviewer1  Status2  Reviewer2  Comments
---------------------------------------------------------------------------------------------
   6/1/2011  Noobicus  Alice

2. Barbara has some free time and wants to review characters. She goes to look at the spreadsheet. She clicks on Noobicus' link. She finds some mistakes and makes some comments on the wiki page (which causes Alice to get an email). She marks Noobicus as "not approved" and puts her name down as reviewer 1, and explains why:

   Date      Char      Player   Status1      Reviewer1  Status2  Reviewer2  Comments
---------------------------------------------------------------------------------------------
   6/1/2011  Noobicus  Alice    Needs work  Barbara                        left comments on wiki

3. Charlie has time to help, too. He looks at the spreadsheet, sees that Noobicus has some mistakes, and reviews someone else instead.

4. Alice gets the email, and fixes the mistakes. Somehow she edits the spreadsheet. (I don't like this much; is there a way to do it with forms? I'd prefer if players didn't have to directly edit the spreadsheet, only reviewers.) It looks something like this:

   Date      Char      Player   Status1      Reviewer1  Status2  Reviewer2  Comments
---------------------------------------------------------------------------------------------
   6/1/2011  Noobicus  Alice    fixed        Barbara                        left comments on wiki

5. Charlie looks at the spreadsheet, sees that Noobicus has fixed the mistakes, and looks him over and decides to approve him. He comments on the wiki (and Alice gets emailed) and edits the spreadsheet, thus:

   Date      Char      Player   Status1      Reviewer1  Status2  Reviewer2  Comments
---------------------------------------------------------------------------------------------
   6/1/2011  Noobicus  Alice    fixed        Barbara    Approved  Charlie

Or maybe this instead, might be better:

   Date      Char      Player   Status1      Reviewer1  Status2  Reviewer2  Comments
---------------------------------------------------------------------------------------------
   6/1/2011  Noobicus  Alice                            Approved  Charlie

6. Dan comes to review characters too, sees that Noobicus is ready, and approves him. He comments on the wiki (so Alice is emailed), moves Noobicus into the Approved Characters category, and edits the spreadsheet:

   Date      Char      Player   Status1      Reviewer1  Status2  Reviewer2  Comments
---------------------------------------------------------------------------------------------
   6/1/2011  Noobicus  Alice    Approved     Dan        Approved  Charlie

I think something like that would work as a workflow, if we can sort out step 4. I'd prefer to avoid a situation where every player has to have a google account to edit the spreadsheet. That could become chaos in a hurry.

Is it possible to do clever things with this spreadsheet, like filter it to hide rows where both statuses are approved, or sort and separate it in nontrivial ways, like this:

   Date      Char       Player   Status1      Reviewer1  Status2  Reviewer2  Comments
---------------------------------------------------------------------------------------------
Ready for approval:
   6/1/2011  Noobicus   Alice                            Approved  Charlie   
   6/6/2011  Testocles  Bob      Approved     Dan        
   6/9/2011  Aa'ia'la   covaithe 

Needs work:
   5/7/2011  Quagmire   covaithe                         mistakes  Charlie   you're an idiot

Approved: 
   1/1/1999  Lauto      renau1g  Approved     covaithe   Approved  THB
   1/2/1999  ...

 

[ScorpiusRisk]

I believe the ability to update an existing entry via form is possible, but will require a little scripting. Let me look into it, and determine whether I'm smart enough to do so.

 

[ScorpiusRisk]

Well that didn't take long. The answer is an outstanding NO. When using a form to populate a spreadsheet, it must happen sequentially. So either we allow players to edit the spreadsheet, or we change the way we look at the sheet.

So basically a there's only a spot for one reviewer and one status.

1. Alice creates/updates character sheet. Alice submits form.
2. Barbara checks spreadsheet. Barbara marks herself as reviewer on Alice's.
3. Barbara follows link. Sees she is the 1st reviewer. Finds mistakes. Writes comments. This emails Alice.
4. CRAZY IDEA WARNING: Before Barbara leaves the character sheet, she hits Follow at the bottom of the page.
5. Barbara marks spreadsheet status as Changes Needed. Everyone ignores this line from now on.
6. Alice gets email. Alice fixes the spreadsheet, this emails Barbara. Alice resubmits the form. This puts Alice at the bottom of the list.
7. Barbara gets email. She reviews corrections. She thinks everything looks Dandy, and gives her approval. She does not edit the spreadsheet. She may now Unfollow Alice's PC, to stop receiving the emails.
8. Charlie checks the spreadsheet, and repeats steps 2-7. Since he is the 2nd reviewer he moves Alice's PC to Approved Characters.

That could be it. If we don't include anymore steps, the spreadsheet doesn't give us a list of approved characters. I think that would be fine. This sheet is for things that need to be done.

We could, alternatively ask the 2nd reviewer to do 1 more thing.
1. When he does the 2nd approval he scans the sheet (or does a Ctrl-f) for other copies of Alice's PC. He deletes those extra rows, so only the most recent submission is present. He marks the status as approved.

 

[ScorpiusRisk]

Downsized Form
https://spreadsheets.google.com/spreadsheet/viewform?formkey=dFQweHctZXpYbFZIREFHR2tNaXFrNEE6MA

Sheet
https://spreadsheets.google.com/spreadsheet/ccc?key=tT0xw-ezXlVHDAGGkMiqk4A&authkey=CIax988B#gid=0

 

[covaithe]

Hmm. Interesting. I think we're getting closer. How about this:

1. Alice submits form.
2. Barbara finds mistakes, comments on the wiki (emailing Alice) and marks the row as Not approved. That row is done and gone; and can be hidden by a filter. Barbara does not get any emails.
3. Charlie checks the spreadsheet, does not see Alice's character (because her only submission is hidden by a filter).
4. Alice fixes mistakes, resubmits. This goes in as a new row in the spreadsheet.
5. Charlie finds the row, goes to the sheet, and approves. While on the sheet, he notices that there were comments from a previous reviewer, and double checks those to make sure they have been addressed. Charlie edits the spreadsheet, putting his name in the 1st approver column.
6. Dan finds the row and approves. He says so in the wiki, which emails Alice. He puts his name in the 2nd approver column. Doing so causes the row to be hidden by another filter (or maybe moved to a lower section by sorting).

 

[ScorpiusRisk]

Why do we have 2 approver columns if only one ever gets used per row?

 

[covaithe]

Hmm. Well, we need a way to distinguish rows that have one approval from rows that have two approvals. I suppose I didn't actually say so, but it was in my mind that this last workflow preserved the list of who was approved and by whom. Hidden by filters, yes, but retrievable.

I don't insist on that. If people think that it's not useful to keep that info, the second approval could be a checkbox instead.

 

[ScorpiusRisk]

I'm trying to figure out how to hide rows conditionally. I'm thinking that even if a row is hidden we should be able to use the search function for its contents.

So yeah, and addition column to be marked if it is the final approval might be useful if we wanted to find something after the fact.

 

[ScorpiusRisk]

I've found a script that should allow a row to be hidden conditionally, but I can't get it to work. Do we have anyone who knows scripts better than I?

I have confirmed that if a row was hidden it would be visible when searching.

 

[covaithe]

What kind of script is it?

In the ~15 mins of playing I've done (on a copy of your spreadsheet), I was thinking of going about it by making a computed column that had an if/else chain in it (e.g. "if C2 = 'Approved' and C3 = 'Approved' then 'Approved' else if C2 = 'Approved' then 'One approval' else...") and then just doing simple filtering on the value of that computed column. But I didn't have time to dig into the docs far enough to see if that kind of computed column is possible.