How to Build a Slack Bot with only Google Sheets
In this article, I discuss how to develop a serverless Slack app that reads information from a Google Sheet and returns it to a user.
I am fortunate enough to work for a company that cares so much about the welfare of its employees. One of the benefits, that the company provides its employees, is a fully-serviced accommodation facility. When new employees apply for a space in the accommodation facility, they are usually added to a waiting list. This waiting list is updated manually behind-the-scenes when space becomes available.
Employees that want to get updates on their status on the waiting list usually have to send someone on the facilities team a message and then wait for a response. The facilities team does a good job of keeping everyone updated when they request for updates. However, it was not a very efficient process. I came up with an idea to automate this process with a Slack bot on a weekend, when I needed to get updates on my waiting status, and the colleague I was supposed to send a message, was not available. The idea sounded exciting since we all used Slack for company-wide communication.
The following week, I had a chat with a member of the facilities team who was in charge of space allocation. She was excited about the idea and how it would help her save valuable time for other tasks. We were able to reach consensus on having a bot automate the process. The bot will take the email of the user as input, read data from the Google sheet containing the allocation information and then inform the user about their current number on the list.
The rest of this article details how I created this bot.
Designing a solution
To get started with the solution design, here are the functional and non-functional requirements that define the scope of the problem and guide the solution.
- The system (Slack app and dependencies) has a command that can be accessed anywhere in the Slack workspace
- The system accepts the employee’s email as input
- The system checks the employee’s email against their number on the list and returns information about the number
- The system returns an appropriate response if input is invalid or is not found on the list.
- The system has near 100% uptime (so it can be used at anytime, even weekends)
- The system responds fast to user requests
- The system is able to execute multiple requests simultaneously without blocking
- The system has zero maintenance cost.
Researching the available options
In this part, we explore available options for meeting our requirements.
Functional Requirement #1
Our first requirement is to have a specific command that is available everywhere in the workspace. A quick look through the Slack API documentation shows that there are two easy ways to achieve this:
- Outgoing webhooks
- Slash commands
To help us choose between these two alternatives, we note that outgoing webhooks are going to be deprecated in future releases of the Slack API. They are also not available across a Slack workspace as they are generally listen on a specific channel. The latter behaviour violates our first functional requirement which says the command should be available throughout the workspace. It is also important to note that outgoing webhooks automatically convert email addresses to
mailto links which makes it harder to parse the email addresses sent to the server. Slash commands on the other hand do not have any of these limitations and are the obvious solution to the first requirement. I initially got to know about the webhook approach from this article that discusses a similar topic.
Functional Requirement #2
Our second requirement is to have the system accept the employee’s email as input. This requirement means we need to define/design an interface that allows the employee to specify their email address. Given that we are using slash commands, we are stuck with a command interface. Our best shot is to design a command interface that is intuitive and reasonable. Looking through the Slack API docs once again, a typical slash command takes the form:
/todo ask @crushermd to bake a birthday cake for @worf in #d-social
In the above command line,
todo is the command and the rest of the sentence is the text. A reasonable design for our slash command following this structure will be:
/listbot check firstname.lastname@example.org
listbot triggers our app and passes the text along which contains a
check action and an argument
email@example.com. I chose this structure because it has similarities to how we use commands on traditional CLIs. For example,
git add myfile.txt.
Functional Requirement #3
With a Google App script, we can read data from a Google sheet. This solves the first part of our third functional requirement. Interestingly too, we can deploy the script as a web app, which effectively solves for the second part of our functional requirement. Voila! Our third functional requirement is met. The biggest pro of using Google App script here, is that it also meets all our non-functional requirements.
Note: One alternative would have been to setup a Node.js server on a free Heroku dyno instance and use the Google Sheets API to read data from our Google sheet. But Heroku free dynos can like to sleep a lot, which does not meet our non-functional requirements for quick responses and near 100% uptime. Of course, Heroku is not the only PaaS provider around. But free tiers of most other providers generally have similar limitations and are relatively an overkill for such a simple bot.
Functional Requirement #4
Our fourth functional requirement is nothing but logic that will run in our Google App script. So that is met too.
To get started with the implementation of our solution, we will need the following environment setup:
- A Slack workspace that you have admin access to (Visit slack.com to create one).
- A Google Sheet with the column structure below (this is a simplified version of the real sheet)
Slack App Setup
After setting up the environment, we need to create our Slack app and configure it. The process for doing this is quite easy and is fully explained in the Slack API docs. Here’s an overview of the steps involved:
- Click the “Start Building” CTA button on the API homepage. This will popup a modal where you specify the App Name and the workspace you want to install it to.
- In the page that comes up next, select “Slash Commands” in the “Add features and functionality” section. This will navigate to a page that allows you to configure the Slash command. Configure as shown below:
Note: Do not check the “Escape channels, users, and links sent to your app” checkbox. This will allow us to get the raw text of emails. Also, leave the Request URL field blank. We will fill the field when we deploy our Google app script.
Handling requests from Slack with Google Apps Script
To get started with Google Apps Script, open the Google sheet that you created earlier. On the Toolbar, click on
Tools > Script Editor and your browser should open a new page that contains the script editor. This is where we will create our Google App script.
Note: You can also create a standalone Google App script. This method is however, not in line with the title of this article.
In order to handle requests from Slack, we need to:
- Get the request body
- Parse the request body
- Route user request to the right handler
- Make the handler process the user request and return result of operation
- Send response to user
Quick note for the reader: Google Apps script does not support ES6+ for development.
To get request body, we capture the posted data in the
doPost() function as follows:
After retrieving the request body and converting it to a plain object, our
req object will have the following shape:
We can then go on to the next step of parsing the
text property of the
req object to extract the action and arguments specified by the user. To do this, we first create an
actions object which contains each action as a key. The value of each action key in turn will encapsulate all the information related to that action. This design ensures that we can extend our app with more actions if there’s need to. The
actions object is implemented as follows:
We parse the text property of each request object as follows:
Now that we are able to extract the action intended by the user and the action arguments supplied, we can invoke the right handler and pass the supplied arguments to the handler function in the specified order.
After setting up the skeleton of our action handler, it’s time to implement it. For the action we are working on (
check), we need to:
- Retrieve an instance of the Google sheet we are working with
- Get the range of data values
- Search the email column on the range for the user’s email
- Return the corresponding number for the email if found; else return null
Here’s the snippet of code that helps us to achieve this:
Now that we are able to get the user’s number on the list, we can finally return a response to our expectant user. The interesting part is that all of the code you see above will have executed in less than 2 seconds. We can divide the job of sending a response into two parts which are composing and actually sending the message. Here’s an implementation of this separation of concerns:
Whoops! Our solution is ready. It is time to deploy. Save the script and on the Toolbar, click on
Publish > Deploy as web app . A modal will pop up. Fill the fields in the modal as shown below:
Click “Deploy” and you will get a URL endpoint that you can post messages to. Copy this URL endpoint and go back to paste it in the Request URL field of your Slack app’s configuration. Trigger your app by typing
/listbot from anywhere in the workspace and watch the system come to life. This is what it looks like for me:
That’s all there is to creating a Slack bot using only Google Sheets. If you need the full version of the code snippets presented in this article, check out this Github repo.
Thanks a lot for taking time to read such a long piece. If you liked the content of this article and would like more people to discover good stuff like this, please feel free to clap up the article up to 50 times. It will go a long way! You can also hit me up on Twitter @olusola_dev if you have any question.