Softwire Morale Event Discord Bot

This Discord bot makes it easier for morale event admins to organise events, and for people to sign up to those events. It links up with Google Sheets to generate an at-a-glance list of all the events happening, and has other features to make this all easier.

The main branch currently publishes to the live bot "ReleaseBot". Once you've joined the Discord Developer team you'll have the ability to add the bot to a server in which you have the "Manage Server" or Admin permissions. ?help will show you the commands the bot currently has and you can do ?help [command] [optional: subcommands] for more information.

Useful links

• DSharpPLus API
• Discord Developer Portal
• Google Sheets API
• Google Sheets .NET Client source (for when the API docs don't explain how to do something using the .NET client)

TODO List

Initial Setup

• Set up Discord Developer Team
• Set up Google Sheets API Account
• Set up Shared Azure Account
• Set up Zoho Vault
• Create Discord bots (for both testing and release)
• Get Azure deployment working
• Set up continuous integration on main branch
• Set up build testing on all branches
• Create basic bot skeleton with DSharpPlus and test it works

Core Features

• Set up interactivity with discord bot
• Event creation conversation with discord bot
• Event creation integrated with Google sheets
• Event removal conversation with discord bot
• Event removal integrated with Google sheets
• Event editing conversation with discord bot
• Event editing integrated with Google sheets
• Listing all events with discord bot conversation
• Showing specific events with discord bot conversation
• Set up Sign up sheet in Google sheets
• Create signup/start event command with discord bot conversation
• Allow people to signup using reactions
• Set up user Direct Messaging on signup
• Group commands to allow bypassing of full conversation with bot
• Remove event signup form on event removal
• Allow people to unsign from events
• Hard code in response sets for events
• Make bot post events in separate channel
• Use a Discord role to control who can issue bot commands
• Notify users that their event is going to start
• Allow admins to forcibly sign people up to events with the discord bot
• Allow creation of recurring events
• Add handling for multiple timezones (eg. Bucharest and London)
• Generate summary message with link to open signup messages that stays as the latest message on the event channel

Complications that need to be addressed

• Migrate from DSharpPLus 3.x to 4.x
• Batch Google sheet API requests to avoid hitting the 100 requests per 100s limit
• Implement some sort of job scheduling for notifications (maybe using Quartz Scheduler)

Stretch Goals

• Allow the creation of custom response sets with the bot
• Notify users if the event they signed up to has changed
• Switch from using Google Sheets to a database with transactions, ACID properties, no API quotas, etc.
• Allow users to disable bot reminder notifications
• Add pagination to event lists (limit 5 per page)

Setup

Creating a bot for local testing

1. Have someone on the team add you to the SoftwireCambridge Discord developer team
2. Go to https://discord.com/developers/applications and click "New Application" in the top right
3. Name your local testing bot, making sure that the team is set to SoftwireCambridge

Getting Google Sheets credentials

1. Add yourself as an editor to the softwire-discord-bot project on Google Cloud Platform, or get someone else who is an editor to follow the subsequent steps.
   1. Login into the "Google Developers Project Manager" account (in the Zoho chamber)
   2. In IAM & Admin -> IAM, add your Google account as an editor
2. In IAM & Admin -> Service accounts, create a new service account
   1. Name the account with some reference to your name ideally
   2. Don't give it any access, roles, or give users special access (so just click continue and done)
3. Click on your newly made service account, and add a key (Add key -> create new key -> JSON -> Create)
4. A JSON credentials file will download. You will need this to run the bot locally.
5. Create a Google Sheet of the right format (just duplicate this one)
6. Add the email address of your service account as an editor to this sheet using the Google Sheets "Share" functionality.
7. You need to find the spreadsheet ID of the sheet you made. At the moment, this is easiest extracted from the URL of the webpage of the Google Sheets editor, but this might be different in the future so Google it. Currently, that URL is of the form: https://docs.google.com/spreadsheets/d/{spreadSheetId}/edit, where {spreadSheetId} is you spreadsheet ID. Note this, you will need it to run the bot locally.
8. Done! You should be able to see the changes made by the bot to the sheet using the Google Sheets website.

Running the bot locally

1. A few things need to be passed in as environment variables when you run locally. These are:

// Found on Discord for developers
RELEASE_BOT_TOKEN

// Found from the URL of your sheet, found when getting Google Sheets credentials
GOOGLE_SHEET_ID

// Found in the JSON file that you downloaded when getting Google Sheets credentials
GOOGLE_CLIENT_EMAIL
GOOGLE_PROJECT_ID
GOOGLE_PRIVATE_KEY_ID
GOOGLE_PRIVATE_KEY

There is a template launchSettings.json.copy file in "DiscordBot/Properties/ServiceDependencies/", all you need to do is duplicate this file and remove ".copy" from it.

2. With your bot selected in Discord for developers, go to "Bot" on the left
3. You will need to give the bot the "Server members intent" under "Privileged Gateway Intents" to give it access to member events.
4. Copy the token on this page and pass it into your launchSettings.json as RELEASE_BOT_TOKEN
5. Pass in the GOOGLE_SHEET_ID and the four relevant fields in the JSON from Zoho
6. On the Discord developer page, select your app and go to "OAuth2", from here select "bot" and any permissions you want the bot to have. Copy the generated link and paste it into your browser to add the bot to your testing server.
7. Run!
8. You will need to create and add a role name "Bot Whisperer" in your discord server and give that role to anyone that needs to use ?event commands.

You should now be able to interact with your bot in the Discord server. Try saying ?event!