Building a Slack serverless bot with Google Apps Script and Spreadsheets

Sometimes when you work in remote environments with multiple staging servers it’s hard to keep track of which servers are free to use. Last place I worked we had 3 of those environments. When someone needed to deploy a branch to test something we picked one randomly, then we asked in the tech team channel if that server was already taken by someone else. As you can imagine people is always busy with their daily work. It’s very frequent that the person who already took a staging server take his time until he answers.

Given this problem we needed to figure out a better workflow for this purpose. Something that only requires the interaction of the person who wants to take or leave a staging server. Waiting for answers of the rest of the team wasn’t efficient.

The perfect solution for this particular problem was a bot.

The Bot

The idea was to build a bot that connects to the tech team channel and listen for these commands:

  • help: shows the list of available commands
  • list: shows the list of available and busy servers
  • take <server_name> <reason>: marks the server as taken by you
  • leave <server_name>: marks the server as available
  • create <server_name>: adds a new server
  • remove <server_name>: removes an existing server
  • rename <old_server_name> <new_server_name>: renames a server

All the commands must begin by the !staging prefix.

Alternative #1: Self-hosted bot. Nope

At the beginning my first intention was to create a nodejs or elixir project. I would build a traditional bot that connects to the Slack channel and listens for the specific commands we want.

There is a problem with this kind of simple ad hoc stuff. I feel it’s too much provisioning a virtual server and a database for it. It’s not cool having to set up something in a remote environment when requirements are dead simple.

To ease these pains we considered Heroku which makes the setup and deploy straight forward, however given the 30-min inactivity policy of free dynos we dismissed the idea.

Alternative #2: A serverless bot? YES!

After explaining the bot idea to my friend Alvivi he hinted me about Google Apps Script. It turned out he used it before in combination with Google Docs acting as the persistence layer.

With Google Apps Script you can write a bunch of javascript functions and expose them as an endpoint, without worrying about setting up anything.

It sounds hacky but it works really well!

The Recipe

Google Apps Scripts and Spreadsheet is not enough for setting up the whole solution so here is the full recipe of what we need:

  • 1x Slack incoming webhook
  • 1x Slack outgoing webhook
  • 1x Google Spreadsheet
  • 1x Google Apps Script

Implementation

Spreadsheet

First of all we need to create a new spreadsheet in our Google account. Why would we need something like this? As we are not going to have a regular database to store the statuses of our staging servers we need something to persist this information. A spreadsheet is good enough!

We can give the spreadsheet the name we want. It doesn’t matter for next steps. The content of the spreadsheet could be something like this:

Once we’ve our spreedsheet with this content we can save it and copy the ID of it. The ID can be found in the url.

Blurry area corresponds to the ID of the spreadsheet that you should copy.

Set up Slack incoming webhook

Setting up a Slack incoming webhook is really easy and it’s probably something you have already done before. You just have to go to Slack Apps directory and search for “incoming webhook”, create a new one and copy the Webhook URL. You can leave the rest of the settings as default since we’ll override some of them from our script.

Script

Visit script.google.com, click on New script and it’ll bring you to the Google Apps Script editor. There you just copy the content of the script located in this github repository. The script basically contains the logic to communicate with Slack and alter the content of the spreadsheet where we persist the statuses of all our staging environments.

The script itself isn’t something really sophisticated. I won’t explain it in detail but I think it’s worth to mention how Google Apps Script runs it. Given our script is gonna be considered a web application accessed by a specific url it can receive GET and POST requests. In our case, as we’ll use it in combination with Slack Outgoing Webhook which makes POST requests, it’ll need to implement the doPost function.

function doPost(e){
var commandReceived = e.parameter["text"];

if (commandReceived.match(/help/)) showHelp();
if (commandReceived.match(/list/)) listStaging();
if (commandReceived.match(/take/)) take(e);
if (commandReceived.match(/leave/)) leave(e);
if (commandReceived.match(/create/)) create(e);
if (commandReceived.match(/remove/)) remove(e);
if (commandReceived.match(/rename/)) rename(e);
}

As you can see doPost function basically acts as a router of commands. It identifies which command we are using and then it executes the proper function to handle it.

Our script keeps record of all the events we send by logging them into a separate page of our spreadsheet. To facilitate this job we use Peter Herrmann’s library BetterLog. Adding it to our project is quite straight forward following instructions in the readme.

Now we are going to expose a few properties we’ll need inside our script. We can do it by going to Project Properties > Script Properties tab. We’ll add the following ones:

  • SPREADSHEET_ID
  • SLACK_CHANNEL_NAME
  • SLACK_INCOMING_WEBHOOK

Once we have done all the above we save the project and click on Publish > Deploy as web app. Then a popup will show up. There we have to change the access to the app to anyone, even anonymous. If it’s the first time it’ll ask you to review some permissions to give the script the ability to alter the spreadsheet. After giving permissions the popup will generate our script url which we’ll save for using it later.

That should be all regarding Google Apps Script!

Set up Slack outgoing webhook

The last step is making possible for our Google Apps Script to listen slack channel commands. In fact our script won’t be actively listening. It’ll be responsability of Slack to keep it posted when someone sends a command starting by !staging.

That’s done by creating a Slack outgoing webhook. We have to go to the Slack Apps directory and search for “outgoing webhooks”. Then we create a new one with following settings:

  • Channel: The channel we’ll send commands from.
  • Trigger Word(s): !staging
  • URL(s): The URL generated Google Apps Scripts in the last step.

Click on Save settings and we should be ready to go.

Demo

Working example of Google Apps Script and Slack integration