schmed / teamwork Goto Github PK

This is just a Google Apps Script that manages the interaction between a Google form and a Google spreadsheet. Players on an (Ultimate) team use the form to record "Teamwork" performed outside of practice, activities like conditioning, working individually or in small groups developing skills, and interacting with and supporting other team members socially.

License: Creative Commons Zero v1.0 Universal

JavaScript 97.80% GLSL 2.20%

teamwork's Introduction

teamwork.gs

We developed this system to support our high school Ultimate team, Air Traffic Control in 2020, partly to address the fact that in-person practices posed too great a risk for COVID-19 transmission. However, we hope it has great potential as a permanent part of our program, to help us address some of our team goals that have so far proved fairly elusive.

It's based on an idea our former captain, Jenna Krugler, brought to us from her college Ultimate team, the UC Berkeley Pie Queens. They use a similar system called "Workout Wars", employing a custom web development stack.

Record Teamwork form

Our team has a Google form players use to record Teamwork. A form submission automatically adds a row to the Teamwork sheet. Afterward, the onFormSubmit() function in teamwork.gs is called automatically, as it is the target of an SpreadsheetTriggerBuilder.onFormSubmit() Trigger.

onFormSubmit() function

After the player submits the form and data is added to the Teamwork sheet, this handler validates and normalizes the data in the new row, calculates and awards points for the activity, etc. A confirmation or failure email is also sent to the player.

Teamwork sheet

This sheet contains one row automatically added by each form submission, and then validated and augmented by the onFormSubmit() function. Here's a Sample Teamwork DB containing the Teamwork and other sheets (plus a Sample Google Apps Script): https://docs.google.com/spreadsheets/d/1vHSYvnnlqoL3YGjmkHvR6dMy5l8u7Lb84nXzM-Aufd0/edit?usp=sharing There are currently 10 columns:

Timestamp - of the form submission
Email - address uniquely identifying the player, to which the confirmation/failure email is sent
Player first name - who performed the Teamwork
Player last name - who performed the Teamwork
Date performed - of the Teamwork
Duraction Activity Category - if Teamwork is measured in 15-minute increments, with points awarded scaled by the duration
Duration - of the Teamwork in the Duration Activity Category
Other Activity Category - if Teamwork instead merits a certain number of points each time it is performed
Describe what you did (optional) - optional notes entered by the player. The script appends warnings and/or prepends error messages here.
Points awarded - The script calculates and adds this value to the sheet.

Activity Categories sheet

This sheet defines the set of Duration and Other activity categories presented on the form, including point values. There are currently 5 columns:

Category Name - ends up in Duration/Other Activity Category in Teamwork sheet
Description and Examples - if the form presents category options using radio buttons (MultipleChoiceItem Objects), then this text is appended to the category name and point value of each.
Unit - denominator of the category's point value
Points per Unit - numerator of the category's point value
Extra Notes - ideas, clarifications, etc.

The data here are not used by the script during form submission, nor by any downstream sheet calculations based on the points awarded. Instead, it represents a working area for future category revisions, read only by periodic manual invocations of the updateActivityCategories() function.

updateActivityCategories() function

This function (invoked manually) forces any updates that have been made to the Activity Categories sheet since its previous invocation to be reflected in the options presented to any player who subsequently fills out an instance of the form.

Source files

The following Google Apps Script files comprise the project. (Note: Google Apps Script is just JavaScript with many pre-loaded Google modules.)

teamwork.gs - Main program entry point, an onFormSubmit(e) Trigger target.
update_categories.gs - Administrative function that updates the activity category options in the Record Teamwork form.
reports.gs - Utilities for sending periodic Teamwork summaries to players.
test.gs - Entry point for running all unit tests, plus code to log environment properties.
dates.gs - Utilities for manipulating Dates and DateRange objects.
appsscript.json - Google OAuth 2.0 Scopes requied to do things like send confirmation emails.
form_access.gs - A file you must add to attach the script to your spreadsheet & form.
form_access_template.gs - A template for your form_access.gs source file.
leaderboard.gs - A time-based (aka clock) Trigger target that updates the Google Charts embedded on the ATC Teamwork Leaderboard Google Presentation.

teamwork's People

Contributors

Watchers

Forkers

aplater

teamwork's Issues

Category-specific confirmation email image

If we collected a bunch of category-specific GIPHY images, we could tailor the confirmation email to match the activity category that was submitted. This would increase the number of different prize opportunities we have available (e.g., the points winner in each category for a given week/month could choose the GIPHY for the following week/month).

I'd thought about placing these into a new column of the Activity Categories sheet, but that would force us to run updateActivityCategories() just to change one of the images. That's an awkward constraint, since this sheet might be in a half-revised state. Perhaps this is reason enough to keep two instances of this sheet: one active plus another draft still under development?

Trash talk form checkbox/text field?

The form could include a checkbox that, when selected, would automatically post a Slack/Discord message to the current leader warning him/her that the player making the submission is closing in. An extra text field might even allow customized trash talking to be incorporated.

Competition pool

A list of email addresses identifying players who have opted into competition and other public aspects of the incentives system we build around point totals. For example, we might divide this pool randomly into two groups each week that would compete to see which group can record the most total points.

We could award bonus points when a player first opts into the competition pool to incentivize giving it a try.

Player participation incentives

This is a grab bag of ideas for using point calculations to incentivize participation:

Point thresholds for awarding labels (e.g., lieutenant, thunder lizard, grand master)
Most improved player of the week (e.g., point count/percentage gains)
Longest streak of daily Teamwork
Winner for a given week/month could choose the GIPHYs to be included in the confirmation/failure emails to be used for the following week/month.
Winner for month/term/season could be awarded a team disc.
Winners could choose a method for abusing one of the coaches (e.g., forcing Coach Schneider to dye his hair or paint his nails a certain color).
See also Leader Board and Competition Pool.

Pre-select today's date when form is first displayed

It would be cool to automatically set the Date Performed value to the current date when the form instance is created, but that's only possible by embedding today's date in the pre-filled version of the form URL. I've included such a pre-filled link with tomorrow's date in the confirmation email. A link or button within a web application or a cell phone app would be able to supply today's date (though the former would add a step to the process for the player).

Note that Google forms doen't allow any custom code to run between the time that the form instance is created and when the user submits it (or even to prevent its submission, including adding a row to the associated spreadsheet).

Date validation assumes year 20 means 0020

The Google Forms date element apparently allows the user to enter "20" into the year portion, and in that case the year of the date is set to "0020". LAME!!

The script should catch this case and replace the year with "2020".

Request feature/report bug links

The links at the bottom of the form submission/failure emails could include links to Feature Request and Bug Report Google forms. Such links could be included within the form itself as well.

Alternately, there could just be some text suggesting that the player reply to the email to request a feature or report a bug (with simple sendmail links in the form).

It's typically very useful to scaffold such feedback so that you get enough information to reproduce a problem or understand what's actually being requested. However, it's probably enough to just start a dialog with the player, and I'm probably going to have to write up the issue here myself anyway.

Cell phone app?

If we moved from a Google form to a cell phone app, it would obviously open up all kinds of opportunities:

It would automatically handle all of our authentication concerns.
It could provide our own custom (albeit simple) platform for social interaction that was well-integrated with the other system features.
It would be easy to automatically pre-select the current date when making a Teamwork submission, include a date picking widget, etc.
Players could use the app to automatically measure durations (i.e., by tapping start & stop buttons, etc.)
It would be easier to incorporate recent Teamwork links to speed recording the most common submissions.

However, Chris looked into this and wasn’t surprised at the inherent difficulties (not to mention expense). This short video provides a good introduction: Mobile app makers in 100 seconds. The system would need to be proven successful (and the existence of an app essential) in order to justify app development. That said, requirements for coding aren’t really an issue, and there are apparently app templates that Chris could customize for our use.

Leaderboard

Post the top 3(?) players by points in the current week, month and/or season in a shared Google spreadsheet.

We think it would be best for players to opt into a Competition Pool of those interested in most public incentives, particularly those characterized by competition. Team members outside the Competition Pool would still receive incentives like prizes (or even shout outs?) for their participation, but we'd avoid singling out those who are unable to participate much.

Nick thinks it would be best for the leader boards to be category-specific. There are currently 13 categories, but we could combine "meta-categories" (e.g., all the ones related to throwing) into one leader board.

Teamwork program Google document

We'll need one document that describes the Teamwork system and how ATC is using it at the moment. It would provide both an introduction to the system and a user guide for the Record Teamwork form, etc. Here are a few specific goals:

A complete list of the current activity categories, perhaps with more detail and examples than what can be cleanly presented within the Record Activity form itself. I was originally thinking that the updateActivityCategories() function would automatically update a section of this document, but I'm no longer sure that's best.
Frequently asked questions and their answers.
Links to resources that support the design and development of the system such as this GitHub issues database.
A description of the incentives currently in place for participation (e.g., specific prizes for reaching point thresholds, winning competitions, etc.)
Procedures for opting into (or out of) the Competition Pool.
Timely guidance, such as admonitions to avoid heavy exercise when the air quality is poor.
Expectations for honesty in form submissions.

We'll have to decide what content belongs here vs. the Leader Board, since the latter will probably have to include at least a brief summary of each competition (with reference links back to this Google document for more detail?) It's also possible to embed a Google document within a Google spreadsheet (or vice-versa), so that's also an option.

updateActivityCategories doesn't support hours in Unit column

The points calculation logic in onFormSubmit() handles duration selections measured in hours, so this feature needs to do so as well. In the meantime, I changed the Unit for the Questionable throws category from 1 hour to 30 minutes (leaving Points per Unit set to 1).

Email monthly/weekly Teamwork summary to each player

We could run a script every month/week that sent an email to each player summarizing the Teamwork he/she recorded, the total points earned and % increase/decrease in each category, awards, etc. The content could be formatted better by employing a Google doc, then exporting that to PDF and attaching the PDF to the email.