June 12, 2018

Write a Slack bot that handles Excel files (xlsx)

So, as the title says, I will teach you to build a simple Slack bot that will use an Excel sheet as it's database in the back.

set up

First things first, if you have been reading my tutorials you know what this is. Yes, we first create a virtual environment.

$ virtualenv venv -p python3.6
$ source venv/bin/activate
(venv) $

Dependencies

Installing the dependencies from PyPI, using pip

(venv) $ pip install slackclient xlrd

slackclient - official Slack library for Slack API by the Slack team itself

more details at https://github.com/slackapi/python-slackclient
xlrd - to perform Excel related operations

And the data I am using, the Excel file, is in this format:

A1      A2      A3
Quote   Author  Topic

Get Slack token

Go to https://slack.com/create and create a Slack workspace, or have one where you have rights to add a bot. Preferably create a new one to test the bot.
Go to https://api.slack.com/apps and click on "Create New App". Give it a name and select a workspace where it will be initiated at first.

In my case, I am calling my bot "quoter".

On the left panel, you'll see "Bot Users" under "Features". Click it and add a bot user to your application.

Head over to "Install App" under "Settings" in left-side panel. Install your app in the workspace and you'll be then given two Access token. Save the "Bot User OAuth Access Token" somewhere safe, you're going to need them.

Writing the bot

This time I am not gonna write it all down in one single file. So that it's easier to maintain.

Let's first write the module to handle the Excel sheet

Filename: xlsx_utilities.py

from random import randint
import xlrd
class XlsxUtilities:
    def __init__(self, workbook_file_path, sheet_name):
        self.workbook = xlrd.open_workbook(workbook_file_path, on_demand=True)
        self.sheet = self.workbook.sheet_by_name(sheet_name)
        self.total_columns = len(self.sheet.col_values(1))      # including title row
    def random_quotes(self):
        # excluding the title row
        random_row = randint(1, self.total_columns)
        return self.sheet.row_values(random_row)

We are reading our Excel file (a.k.a. workbook) and it's Sheet. We are then calculating the number of all the rows present, this is the number of all the quotes. Next, we select a number randomly between 1 and the length we got earlier. We take that row and return it.

Next, we will write the file to initiate the bot and listen to messages.

Filename: bot.py

import time
from slackclient import SlackClient
import event
class Bot(object):
    def __init__(self):
        self.slack_client = SlackClient("YOUR_SLACK_API_KEY_HERE")
        self.bot_name = "quoter" # NAME OF YOUR BOT HERE
        self.bot_id = self.get_bot_id()
        if self.bot_id is None:
            print("Error, could not fine " + self.bot_name)
        self.event = event.Event(self)
        self.listen()
    def get_bot_id(self):
        api_call = self.slack_client.api_call("users.list")
        if api_call.get("ok"):
            # retrieving all users so that we can find out bot
            users = api_call.get("members")
            for user in users:
                if "name" in user and user.get("name") == self.bot_name:
                    return "<@" + user.get("id") + ">"
        return None
    def listen(self):
        if self.slack_client.rtm_connect(with_team_state=False):
            print("Successfully connected, listening for commands")
            while True:
                # to keep it running always
                self.event.wait_for_event()
                time.sleep(1)
        else:
            print("Error, Connection failed!")

Here we're initiating the bot with our API key. Then we get out bot's id, this helps us make sure that our bot got mentioned. Then in the listen function, we are listening to all the events happening in the channel, and for everything that happens we are passing it on to the even.waiting_for_event function. And, now it's time to finally handle these events.

Filename: events.py

import sys
import xlsx_utilities
class Event:
    def __init__(self, bot):
        self.bot = bot
        self.xlsx = xlsx_utilities.XlsxUtilities(workbook_file_path="Quotes.xlsx", sheet_name="Quotes")
    def wait_for_event(self):
        events = self.bot.slack_client.rtm_read()
        if events:
            for event in events:
                # print(evnet)
                self.parse_event(event)
    def parse_event(self, event):
        # if our bot get's mentioned, in a text message
        if event and "text" in event and self.bot.bot_id in event["text"]:
            try:
                response = self.xlsx.random_quotes()
                response = """{} — {}""".format(response[0], response[1])
                channel = event["channel"]
                self.send_message(channel, response)
            except:
                print("Unexpected error:", sys.exc_info()[0])
                raise
    def send_message(self, channel, response):
        self.bot.slack_client.api_call("chat.postMessage", channel=channel, text=response, as_user=True)

First, in the constructor, we initialize out xlsx file. Then in parse_event, we check if out bot got mentioned, then we get the random quote from the file. And using the helper function send_message send it to the channel where it was requested.

And now to actually get the bot started, we have our most important file.

Filename: app.py

import bot
bot.Bot()

Test the bot

Conclusion

This was a very bare bones application to get you started. Rest is up to you and your curiosity. Some features that you can add here are, give quotes related to a particular topic, after all, we can't let the topic column go to waste, right? Another feature you can add is a search feature.

So, what are you waiting for? Get started.

Contacte me on twitter.com/anshul and GitHub.