Make A Slackbot From a Google Spreadsheet

In this episode of "probably useless", I'll quickly write about how to make a SlackBot that runs in the cloud using data from a Google Sheet. The example I'll be using was made by me and my friends to handle commuting to and from school, but I've generalized it to many carpooling situations. Because I don't have a lot of time to work on projects right now, I'll be explaining briefly how this works and how you can apply it to your own Slack (or otherwise) bot and maybe pick up some neat tricks for code like this along the way.

Let's start with the design of the spreadsheet:

First, the only thing that needs to be entered here is your own data. The dates never have to be changed and the highlight is always correct. Let's look at how that is:

The real magic is in the formula for the date headers. I'll add indentation to make it readable. Here's for Monday's column header:

=SWITCH(
  WEEKDAY(NOW()),

    1,TODAY()+1,

    2,TODAY()+0,

    3,TODAY()+6,

    4,TODAY()+5,

    5,TODAY()+4,

    6,TODAY()+3,

    7,TODAY()+2

)

This basically switches on the weekday and then returns the date of the next Monday. The numbers you add by are shifted based on the day of the week you want to calculate (for example, Tuesday uses 2, 1, 0, 6, 5, 4, 3 and so on).  This makes the day of the week correct. The format of the cell dictates how it's written out (Weekday, M/DD).

The next step is to do the shading on when today is. This is done with conditional formatting. Basically, we're highlighting green if the day that cell is linked to is equal to its date header. So, for Monday, is today equal to A3? If so, make those cells green. Everytime the spreadsheet is recalculated, boom. It's updated correctly. If you want to keep this sheet up and running for long periods of time, you can make it update every minute or hour by going to the Spreadsheet Settings under the File menu.

Now for the Slack bot. Unfortunately, Google Apps Scripts can't, as far as I can tell, deal with multiple formats in a single cell. If it does, it's not clear in the documentation. So I enforced a strict data policy: different cars are separated by semicolons, the passengers in each are separated by commas, and the first passenger is always the driver. So now we can make human readable strings based on this.

In order to make a Slack bot from Google Apps Script, you have to talk to the Slack API. For simple bots like this one, the easiest way to do that is to make a webhook you can post data to. You set the channel you want the hook to post to, and it'll give you a secret URL you can post to in order to get your messages in. At that point, it's just a matter of asking Apps Script to post to it with the data payload. This is the last thing that gets called.

So, how does this work? I set up a time-based trigger in the App Script to run the checking loop. It will get the nearest date and time range as cells on the spreadsheet and parse those so that it can make strings about who's coming and who's going and then it'll send the message. I added Moment.JS to the project just to make dealing with dates and times easier.

So that's about it, you can look at the code via the Spreadsheet or, if for some reason that doesn't work, you can go to this Gist that has the code too. I hope this yields some insight into how this could work and how you could make your own "serverless" Slack bot with a Google Sheets as a datastore.