Prerequisite: You have put your credential JSON file into your Python project directory, and you must install nest-asyncio, discord, keyring, gspread and oauth2client in your Python environment. You may install these libraries by
pip install nest-asyncio
pip install discord
pip install oauth2client
pip install gspread
pip install keyring
Step 1: The channel id is the last part of the Discord link on your server. It will be defined as commands_channel in this article.
The client id of the generated URL from the previous article will be used as bot_id.
Step 2: Initialize the program by
import nest_asyncio
nest_asyncio.apply()
import discord
import gspread
from oauth2client.service_account import ServiceAccountCredentials
import keyring
intents = discord.Intents.default()
intents.messages = True
intents.message_content = True
intents.reactions = True
client = discord.Client(intents = intents)
guild = discord.Guild
messages = []
result_txt_location = 'whitelisted_user.tsv'
scope = ["https://www.googleapis.com/auth/spreadsheets", "https://www.googleapis.com/auth/drive"] #https://spreadsheets.google.com/feeds']
credentials = ServiceAccountCredentials.from_json_keyfile_name('genuine-amulet-386508-e126616283cf.json', scope)
gs_client = gspread.authorize(credentials)
docid = "1BVg-WgsDEjembne02KQ7F7U3EQLq33BG7uuybmFAZXs"
commands_channel = 1106832839615451208
bot_id = 1106830556429012345
print("Bot started")
@client.event
async def on_message(message):
#do sth here
client.run(keyring.get_password("api", "discord"))
An event handler on the above Python script will be triggered whenever someone sends a message to your Discord group.
Step 3: Add an if loop under #do sth here. It will ensure the message is from the command channel.
if message.channel.id == commands_channel:
Step 4: Inside the if loop, add another if loop to ignore the message sent by your bot. Otherwise, the bot will keep echoing messages on your channel. Print the author id and message for debugging.
if message.content != "" and (message.author.id != bot_id):
print(message.author.id)
messages.append(message.content)
print("New message: " + message.content)
Step 5: Download the latest Google Spreadsheet authentication file from the cloud for synchronization.
########################################
########################################
########################################
#
# sync whitelisted_user from Google Cloud
#
########################################
########################################
########################################
spreadsheet = gs_client.open_by_key(docid)
export_file = spreadsheet.export(format=gspread.utils.ExportFormat.TSV)
f = open(result_txt_location, 'wb')
f.write(export_file)
f.close()
Step 6: Read the saved user database and store it in a uid list.
########################################
########################################
########################################
#
# read data
#
########################################
########################################
########################################
uid=[]
with open(result_txt_location, 'r', encoding='utf8' ) as f:
for line in f:
filtered_data = line.replace("\n","").split('\t')
uid.append(filtered_data[0])
Step 7: Verify the input. If the input command is not add or remove. Send a warning to the Discord command channel/
########################################
########################################
########################################
#
# verify input
#
########################################
########################################
########################################
try:
command = message.content.split(',')[0]
if(command == 'add' or command == 'remove'):
insert_data = message.content.split(',')[1]
print(insert_data)
if(not insert_data.isnumeric()):
channel = client.get_channel(commands_channel)
await channel.send("Invalid format! User id Should be an integer.")
#else execute the command
else:
channel = client.get_channel(commands_channel)
await channel.send("Invalid format! Should be add, uid or remove, uid")
except:
channel = client.get_channel(commands_channel)
await channel.send("Invalid format! Should be add, uid or remove, uid")
Step 8: If the input command is add or remove, execute it. Replace #else execute the command with
########################################
########################################
########################################
#
# execute command
#
########################################
########################################
########################################
if(command == "add"):
uid.append(insert_data)
elif(command == "remove"):
for count, user_id in enumerate(uid):
if(insert_data == user_id):
del uid[count]
Step 9: Write the file to local storage first after executing the command.
########################################
########################################
########################################
#
# write to file (local)
#
########################################
########################################
########################################
count = 0
with open(result_txt_location, 'w', encoding="utf8") as f:
while count < len(uid):
data = "".join([uid[count],'\n'])
f.write(data)
count+=1
Step 10: Update it to the Google Cloud by
########################################
########################################
########################################
#
# write to cloud avatar_full_list
#
########################################
########################################
########################################
content = open(result_txt_location, 'r', encoding="utf8").read()
gs_client.import_csv(docid, content)
channel = client.get_channel(commands_channel)
await channel.send("Updated user whitelist!")
Bonus: Add another event handler, it will take action to your ❌ reaction on the command channel. It will delete the message whenever you send a ❌ (You have to start the bot first, it won’t handle previous messages).
@client.event
async def on_reaction_add(reaction, user):
print(reaction.emoji)
message = reaction.message # our embed
if message.channel.id == commands_channel: # checking if it's the same channel
if(reaction.emoji == "❌"):
#Logs
channel = client.get_channel(commands_channel)
await channel.send('Deleted message: '+reaction.message.content)
await reaction.message.delete() #Remove original message
Add a user id to the Google Spreadsheet (database) by leaving a command on your command channel.
Check your Google Spreadsheet. It will update if everything functions correctly.
Remove a user id to the Google Spreadsheet (database) by leaving a command on your command channel.
Check your Google Spreadsheet. It will update if everything works properly.
Try to type a wrong command. An error message will be displayed, and your Google Spreadsheet won’t be changed.
Try to remove a message by adding a ❌ reaction to it.
Congratulation! You can now update your Telegram Bot user database with a Discord bot! You may modify the code below to create your own bot for automation! The complete Python script
import nest_asyncio
nest_asyncio.apply()
import discord
import gspread
from oauth2client.service_account import ServiceAccountCredentials
import keyring
intents = discord.Intents.default()
intents.messages = True
intents.message_content = True
intents.reactions = True
client = discord.Client(intents = intents)
guild = discord.Guild
messages = []
result_txt_location = 'whitelisted_user.tsv'
scope = ["https://www.googleapis.com/auth/spreadsheets", "https://www.googleapis.com/auth/drive"] #https://spreadsheets.google.com/feeds']
credentials = ServiceAccountCredentials.from_json_keyfile_name('genuine-amulet-386508-e126616283cf.json', scope)
gs_client = gspread.authorize(credentials)
docid = "1BVg-WgsDEjembne02KQ7F7U3EQLq33BG7uuybmFAZXs"
commands_channel = 1106832839615451208
bot_id = 1106830556429012345
print("Bot started")
@client.event
async def on_message(message):
if message.channel.id == commands_channel:
if message.content != "" and (message.author.id != bot_id):
print(message.author.id)
messages.append(message.content)
print("New message: " + message.content)
########################################
########################################
########################################
#
# sync whitelisted_user from Google Cloud
#
########################################
########################################
########################################
spreadsheet = gs_client.open_by_key(docid)
export_file = spreadsheet.export(format=gspread.utils.ExportFormat.TSV)
f = open(result_txt_location, 'wb')
f.write(export_file)
f.close()
########################################
########################################
########################################
#
# read data
#
########################################
########################################
########################################
uid=[]
with open(result_txt_location, 'r', encoding='utf8' ) as f:
for line in f:
filtered_data = line.replace("\n","").split('\t')
uid.append(filtered_data[0])
########################################
########################################
########################################
#
# verify input
#
########################################
########################################
########################################
try:
command = message.content.split(',')[0]
if(command == 'add' or command == 'remove'):
insert_data = message.content.split(',')[1]
print(insert_data)
if(not insert_data.isnumeric()):
channel = client.get_channel(commands_channel)
await channel.send("Invalid format! User id Should be an integer.")
else:
########################################
########################################
########################################
#
# execute command
#
########################################
########################################
########################################
if(command == "add"):
uid.append(insert_data)
elif(command == "remove"):
for count, user_id in enumerate(uid):
if(insert_data == user_id):
del uid[count]
########################################
########################################
########################################
#
# write to file (local)
#
########################################
########################################
########################################
count = 0
with open(result_txt_location, 'w', encoding="utf8") as f:
while count < len(uid):
data = "".join([uid[count],'\n'])
f.write(data)
count+=1
########################################
########################################
########################################
#
# write to cloud avatar_full_list
#
########################################
########################################
########################################
content = open(result_txt_location, 'r', encoding="utf8").read()
gs_client.import_csv(docid, content)
channel = client.get_channel(commands_channel)
await channel.send("Updated user whitelist!")
else:
channel = client.get_channel(commands_channel)
await channel.send("Invalid format! Should be add, uid or remove, uid")
except:
channel = client.get_channel(commands_channel)
await channel.send("Invalid format! Should be add, uid or remove, uid")
@client.event
async def on_reaction_add(reaction, user):
print(reaction.emoji)
message = reaction.message # our embed
if message.channel.id == commands_channel: # checking if it's the same channel
if(reaction.emoji == "❌"):
#Logs
channel = client.get_channel(commands_channel)
await channel.send('Deleted message: '+reaction.message.content)
await reaction.message.delete() #Remove original message
client.run(keyring.get_password("api", "discord"))