Tutorial: A Slack Reading Group Bot
Tutorial for using Google Spreadsheets and Slack to create a collaborative reading group
Tutorial to create a collaborative, living reading group using Google spreadsheets and Slack.
I built this as a sample for a particular group in 2016.
The challenge with spreadsheets is that they aren’t always easy to keep up with. Apart from getting email updates every time someone adds something or manually opening and scanning through the spreadsheet to find changes, spreadsheets are sometimes hard to engage with over the long term.
In order to incorporate the reading list into the space where people are already having discussions — Slack — I built a bot that helps increase visibility of the list and lets people add directly to it without ever leaving Slack. It’s now open sourced, and this tutorial is a walk-through of how to set it up for your own Slack reading group.
The basic structure of this project was built on top of this tutorial by JD Maresco on how to use Google Scripts and Slack to automate standups. We’ll be using a Javascript-based programming environment called Google Apps Script, built into all Google Apps, which you can use to build helpful tools and improved workflows.
So, let’s get started.
The Goal
Allow users to add items to a shared reading list, as well as check for list topics and see list activity via a Slack channel.
What you need
- A Google Account
- Access to a Slack team’s integrations
- Basic Javascript knowledge
Set up your spreadsheet
For this reading group, we knew we wanted to list reading materials and group them by broad categories. After looking at some of the book suggestions from our Slack group, we identified a number of topics and split these up into pages on a spreadsheet.
Each individual topic has the same order of information in columns A through E: Title, Author, Description, Link, and Added By. This consistent ordering will be important for how we set up the bot.
Brainstorm a good user experience
It’s always good to know what you’re trying to build before you actually start writing code. With this bot, I wanted to make sure it felt intuitive for users to add items from Slack, and that feedback loops helped increase visibility in the group and assure the user that they had successfully posted to the spreadsheet.
We needed a way to organize the Slack message so that it was easy for humans to write, but unambiguous for the bot to parse. I decided that using a semi-colon to separate items like title, author, description, and link was the clearest format. A semi-colon is not likely to be used in the main content, so it’s unique, but it’s not too abstract or intimidating.
The goal was for each Slack message to be added to the correct Google sheet, with each piece of information being added to the correct column. In order for Slack to know which messages to send, we can take advantage of a Slack feature called “trigger words”, which we’ll talk about in the tutorial. The format for telling the bot what to send via the trigger word add: would look like this: add: category; title; author; description; link .
For example:
add: Black History; Between the World and Me; Ta-Nehisi Coates; In a profound work that pivots from the biggest questions about American history and ideals to the most intimate concerns of a father for his son, Ta-Nehisi Coates offers a powerful new framework for understanding our nation’s history and current crisis; https://www.amazon.com/Between-World-Me-Ta-Nehisi-Coates/dp/0812993543/ref=sr_1_1?s=books&The order of title, author, description, and link follows the order of columns in our spreadsheet.
Since users will have to input the correct topic name in order for their item to be entered correctly into the spreadsheet, it’s useful to be able to check the topics without actually having to open the spreadsheet. The bot could return a list of topics based on sheet names after a user types the trigger words list topics.
After a user adds an item via Slack, it would make sense for some sort of confirmation to be published in Slack to reassure the user and help everyone track what’s being added in one place. More on this later.
This seems like a pretty good setup. Users can view topic names, post new content, and see a confirmation, all within Slack. Now that we know what we’re building, we just have to build it. To do that, we can use the spreadsheet’s built-in Google Apps Script.
Create a Google Apps Script
From the Tools menu of the spreadsheet, select Script Editor and click Blank Project.
In your new Google Scripts file, erase everything and add this:
In order for Slack to be able to post data to the spreadsheet, we have to listen for Slack’s outgoing webhook and connect it to the spreadsheet. We can do this by publishing a Google Scripts endpoint that listens for POST requests. Google has a magical convention where any script that contains a method named doPost that accepts a request object as its argument will accept POST requests and will be wired up appropriately.
The SpreadsheetApp API has identifiers for the spreadsheet as a whole and individual sheets within that. Since we’re allowing users to add items to multiple different sheets, we’ll have to specify a way to identify sheet names at some point. For now, we want to start by referencing the spreadsheet by its ID, which is a string of characters included in the URL. We’re giving it the variable sheets so we can reference it later.
request.parameters contains all of the data we need from the Slack message. We’re giving it the variable name params so we can reference it later.
From the Publish menu, select Deploy as Web App. It will prompt you to save and name your project, which you can do and then click OK. Then deploy with the following settings:
Project version: NewExecute the app as: MeWho has access to the app: Anyone, even anonymous
Then click Deploy. Copy the Current web app URL, which you’ll be using later.
Create a Slack Webhook
To get information out of Slack and into the spreadsheet, we need to create an outgoing webhook.
Go to https://[your Slack group].slack.com/apps/build/custom-integration, and select Outgoing WebHooks. On the next screen, select Add Outgoing WebHooks Integration to get started.
In the section called Integration Settings, choose these settings:
Channel: Any (if you want people to be able to post from any Slack channel)Trigger Word(s): add:,Add;List Topics,list topics,List topicsURL: Paste the URL from the Google Apps Script that you already copiedToken: Copy this value into your clipboard to use laterDescriptive label: Auto-add to reading list bot (or however you’d like to describe this)
Remember, we’re going to allow people to add to the spreadsheet from Slack starting with the trigger word add:. I wanted to include both a lowercase and uppercase trigger so people wouldn’t get tripped up by capitalization preferences.
We’re also going to allow people to type list topics to receive a list of available spreadsheet topics. Variations of this are included in the trigger word input as well, so we can access that trigger in our script.
You can also choose a custom icon or emoji to appear whenever people utilize this bot. After you finish, click save settings.
Write message data to spreadsheet
Go back to your script and add your webhook token. We’ll be setting up two separate functions for adding to the spreadsheet and listing topics, depending on the trigger word used. Here’s what it should look like:
The two functions we’re using are called addNewItemToSheet and listSheetNames. Now, we need to write them:
We’re doing a few things here.
First, we remove the trigger word by removing add: from the message text.
Then we sort the text into columns by splitting on the ; character. That split creates a list of items, which we can assign to columns, with a fallback to empty strings.
To identify the which sheet to add the item to, we match the topic name specified in the message with a sheet name. This is done through the Google API command sheets.getSheetByName(sheetName), which we assign to the variable sheet.
We also want to add entries in the next available row, so we set up the function getNextRow to look through the titles column and return the first empty row.
Since I wanted to record the username of the person entering items into the spreadsheet in column E, I used sheet.GetRange('E:E').getCell(nR,1) to find the correct cell and .setValue(params.user_name) with the username supplied by the Slack webhook.
We also needed to connect columns A through D with the information that users are trying to add. This is done by getting the column range, finding the next available empty cell within the new row, and setting the value to the different attributes like title , author , description , and link . Each one will look like this:
sheet.getRange(‘A:A’).getCell(nR,1).setValue(title);Now the spreadsheet knows where to put new rows of information, and writes the information in the appropriate columns.
Listing the sheet names in Slack is much simpler. All we have to do is get all the available sheet names by calling getSheets() and map across them calling the getName() function to end up with a list of sheet names.
Set up a Slack confirmation
We also want to provide some sort of feedback loop to be posted in Slack. It seemed like a good idea to specify a channel in our Slack group purely for these confirmations so we could see a running list of entries. We called this channel #reading-list.
To post something in Slack from an external source, we have to set up an incoming webhook. It’s in the same place as the outgoing webhook, and you can set up the Integration Settings as follows:
Channel: #reading-list (this is the channel where all the entries will get added)Webhook: Copy this value into your clipboard to use laterDescriptive label: post updates back to Slack (or however you’d like to describe this)
Copy the webhook URL and save your settings. Now, we have to tell Slack to post a confirmation and format it appropriately.
Back in the Google Scripts file, we’ll write a new function called postResponse. To understand how to structure the confirmation post, we can reference Slack’s Webhook API documentation, and more importantly, the documentation on message attachments.
We’re aiming to set up an attachment in #reading-list that will look something like this:
Messages have attachments, defined as arrays. Using the documentation as a guide, set up your message as you see below. You can also use this handy message builder to preview your attachment.
We set up our payload using the documentation above.
After setting the incoming webhook URL, we can use Google’s UrlFetchApp.fetch API to trigger the Slack incoming webhook.
We call this function at the end of addNewItemToSheet().
Similarly, we can post our topic names by slightly altering the payload as follows:
Make sure you deploy as a web app again, and keep your project version set to new. Your final bot will work like this:
And you’ll be able to see the item added to your spreadsheet like this:
Ready to set this up for yourself? Here’s the full Google Apps Script code.
