Utopian spreadsheet management + bot

Repositories

https://github.com/amosbastian/utopian-spreadsheet
https://github.com/utopian-io/utopian-bot

When Utopian's front end was taken down we needed a way for moderators to keep reviewing contributions and contributors to be able to get rewarded for their hard work. To keep this process running smoothly I created a spreadsheet and a few Python programs that manage the retrieving of contributions, the updating of the worksheets and the upvoting or unvoting of the contributions.

Managing the spreadsheet

The programs for managing the spreadsheet can be found in the first repository. This contains three scripts that are used in conjunction with a crontab to manage everything

• spreadsheet.py

The main program. This uses beem to iterate over recent contributions, checks if they are already somewhere in the spreadsheet, and if they are not it, it adds them to it. While doing this it performs some checks, like if the category is valid and/or the user is banned or not. Once everything is confirmed it adds a row containing the URL of the post, the linked repository and its category to the worksheet. It is also used to calculate each moderator's points for the week and saves them to a file, allowing it to be displayed on e.g. https://utopian.rocks/json/2018-05-17.

• reviewed_sheet.py

This checks if contributions in the main worksheet have been reviewed or not. It determines this by checking the moderator, date and score column. If they are all filled the row is deleted and moved to the current week's "reviewed" sheet, so the bot can vote on it for example. While doing this it also calculates the voting percentage the bot should use, and sets the status of the contribution to pending, so the bot also knows it needs to be voted on.

• new_sheet.py

This is used to create a new worksheet and update the name of the main worksheet where unreviewed contributions come in automatically. Moderators get upvoted every Thursday at midnight, hence why each date starts with a Thursday.

Upvoting and unvoting

Once contributions have been reviewed they might need to be upvoted depending on the score. To do this I created two additional programs, one for upvoting and one for unvoting (unfortunately this is sometimes needed), which can be found in the second repository. These scripts are also meant to be used in conjunction with crontab, which is what we do to run them every five minutes.

• upvote_bot.py

This basically checks if the bot's voting power is above a certain amount, then sorts the pending contributions by score and picks the one first in the list. It also checks if a contribution has been staff picked, because then the score is disregarded and the contribution automatically receives the highest upvote possible for its category. Once it has selected a contribution it performs some checks, like if the bot has already voted on the contribution or not, then upvotes the contribution, comments on it and updates the spreadsheet.

• unvote_bot.py

As I mentioned before sometimes contributions need to be unvoted. To implement this I make it check if any of the contribution's scores have been changed to 0 from something higher than 0 since the last time it was run. If it finds such a contribution, then it is upvoted with a voting % of 0 (which is the same as unvoting), updates the comment under the contribution to reflect this, and updates the sheet so we know it has actually been unvoted.

It was actually pretty nerve-wracking implementing this, because of how huge the consequences could be if something went wrong because of me, haha...

Technology stack

The most important Python packages used are listed below

• https://github.com/holgern/beem - used to retrieve contributions, upvote, unvote and more
• https://github.com/burnash/gspread - used to interact with the spreadsheet

Roadmap & how to contribute

I don't really have a roadmap for this, as I just keep adding stuff whenever someone asks me to. Hopefully in the future when we have a proper database instead of a spreadsheet (which is actually pretty nice), I can refactor the code to use classes instead of having everything in a separate file (everything was pretty rushed).

Some people have already helped out with little things here and there (shoutout to @crokkon and @mwfiae), so if you want to help out you can simply create a PR/issue like they did or contact me on Discord at Amos#4622.

GitHub Account

https://github.com/amosbastian