---
# System prepended metadata

title: "Committee Award Bot \U0001F947\U0001F948\U0001F949"

---

<div style="text-align: justify"> 

# Committee Award Bot 🥇🥈🥉 

The Committee Award Bot keeps track of feedback and points awarded to candidates and members in a UPE committee.

Officers use the `/award [awardee_name] | [points] | [feedback]` slack command to assign feedback and points to a specific awardee. The respective data is then recorded in a spreadsheet. 

The spreadsheet automatically tallies the results for each committee on the `overall tally` sheet. At the end of the semester, officers are able to view feedback relating on their respective committee sheets in order to finalize award recipients.

## Motivation behind the Award Bot 

The Committee Award Bot essentially awards committee members and candidates who display exceptional dedication and commitment to their committee. This is similar to the AI-award system that is currently in place for CS 61A, which has boosted AI-performance. Hopefully, a similar system in UPE would help boost candidate and member performance in committees, and thus ease officer workload.

Candidates and members in committees that spend their semester working hard to improve their committee (and thus UPE as a whole) would be publicly recognized for their hard work. At the end of the semester, they will be presented with a small trophy and certificate right after initiation. The number of committee members who recieve the award would be determined by committee officers, taking into account the total number of people in the committee. The award system would serve as positive-reinforcement and ADDITIONAL motivation to keep candidates/members actively engaged in and contributing to a committee.


## Choice of Award System Software

**Slackbot:** We have decided to implement a slackbot to enable officers to input in feedback as easily as possible - without having to open up a spreadsheet each time. Even without the award system in place, officers are generally very active on slack, as it serves as UPE's main medium of communication. Thus, using a slackbot seems to be the most efficient way to integrate this novel system into our club, from a usability standpoint.

**Google Scripts / Spreadsheets:** The sheets API is easy to use, well documented, and provides reliable and free data storage accessible to all officers. Google Scripts is also a very reliable language with very little boilerplate code - making it easy to learn and use.

## Quick Links

* _Committee Awards_ spreadsheet: https://bit.ly/awardBot

## Usage - Slack Commands

* `/award [awardee_name] | [points] | [feedback]` (case insensitive) is the general command used to assign points to a valid candidate/member. Note, only point values between 1 and 10 (inclusive) wil be accepted. The `parseInt()` function will be applied to the points prior to storage in order to round decimal values to their integer eqivalents.
* `/award showAll` (case insensitive) outputs a list of all candidates and members in committees.
* `/award Hi Bot` OR `/award Hello Bot` (case insensitive) - a friendly command to help people in your channel meet the bot.
* `/award why is bot` (case insensitive) - a twist on the iconic phrase "why is Gamora?" - contains a link to the master spreadsheet.
* `/award readme` OR `/award transition` - (case insensitive) - Has a view only link to the bot's readme.

## Spreadsheet Internals

### Committee Awards Spreadsheet

This spreadsheet contains the google script which runs processes the post request recieved from Slack. This code is present in the _Code.gs_ file in the scripts editor.

#### Overall Tally

This sheet (part of the Committee Awards Workbook) contains a set of tables corresponding to each committee. These tables comprise of a set of queries which list the total points obtained by each awardee, for each committee.

#### * Table

These sheets (part of the Committee Awards Workbook) contains a table for the corresponding committee. The entries in the table consist of the date of the award, the name of the officer that assigned the points, the points, and the corresponding feedback. Officers will be able to easily skim through the table and view explicit feedback.

#### Candidate Committee

This sheet contains the names of the candidates on the committee track and members in a committee. The information for committee candidates is automatically queried from the internal candidate tracking sheet into the award system sheet using an excel formula / SQL query.

```javascript
= query(IMPORTRANGE( /* link to candidate tracking spreadsheet */ ,"Candidate Tracker!A1:D108"), "where Col3 contains 'Committee'", 0)
``` 

_Col3_ (_Column C_) on the candidate tracking spreadsheet (and on the _Committee awards sheet_) contains the string *"Committee"* if the corresponding candidate belongs to the committee track. 

_Column B_ **must** contain the candidate name and _Column D_ **must** contain their corresponding committee: '*Professional Development*', '*Industrial Relations*', '*Social*', '*Outreach*', and '*Publicity*'.

In addition to candidate information, this sheet must also contain the same information for an active member in a committee. 


#### Officer Emails

The _Officer emails_ spreadsheet contains the list of officer slack usernames (which we believe are derived from their emails).

* _Column C_ contains the officer's slack username.
* _Column D_ contains the entire name of the officer.
* _Column E_ contains the officer's committee (optional).


## Set up for transition

1. Change officer usernames on the _Officer emails_ sheet.
2. Clear the information stored on the _* Table_ sheets, other than the table headers.
3. In _cell A1_ of the _Candidate Committee_ sheet, change the 0th parameter to be the link to the **NEW** Candidate tracking spreadsheet. Ensure that the correponding columns (as mentioned above in the _Candidate Committee_ section) remain the same. Specifically:
> _Col3_ (_Column C_) on the candidate tracking spreadsheet (and on the _Committee awards sheet_) contains the string *"Committee"* if the corresponding candidate belongs to the committee track. 

> _Column B_ **must** contain the candidate name and _Column D_ **must** contain their corresponding committee: '*Professional Development*', '*Industrial Relations*', '*Social*', '*Outreach*', and '*Publicity*'.

> In addition to candidate information, this sheet must also contain the same information for an active member in a committee. 

4. Right below this candidate information on the _Candidate Committee_ spreadsheet, input the same information for any active members in a committee (excluding officers). To account for any potential additions to the aforementioned candidate information, add a space of a few rows between the two.
5. Next, we have to update the google scripts. On the spreadsheet, go to _Tools_ > Script Editor. This script processes the post requests sent from slack, updates the sheet accordingly, and sends back a post request with a custom response. During a semester transition, **NONE** of the script needs to be changed. However, if you do decide to add in a new committee / change sheet names, these changes would need to be accounted for in the script. If such a change is being made, update the corresponding column number / string in the code. The code is well documented with comments, indicating which numbers correspond to which columns as well as the general program flow, making it easy to ammend on account of deviation from the current format.
6. Finally, move the slackbot to the new slack. The *Award Point Bot* is the corresponding slack bot dedicated to this system. The owner of the bot should had over ownership to another UPE member if the former owner decided to no longer be an active officer of UPE. The owner of the bot should then visit https://api.slack.com/apps?new_app=1, click on the _Committee Award Points_ App, then click on _Manage Distribution_, and finally export the bot to the new Slack Channel, by clicking _Distribute App_.


## Contributions
Contributions are welcome. For major changes, please meet with the SoftDev team and Exec first to discuss what you would like to change.

For any additional information / major concerns, please contact the creator of this system: Michelle Fae D'Souza (michelled@berkley.edu) (Executive Vice President, Fall 2020).

## Licenses

Bot is too young to have a license :sob:. Bot will get license when bot is older.
</div>
