A Google docs Slackbot tracker thing

Apparently, bots are the latest thing. Or are they the next thing? Or maybe they’re passe now.. I’ve lost track. All I know is, I’d never built a bot before, and I wanted to give it a go, so I did. And this is the story all about how…

I built a Slackbot to chat to a Google spreadsheet in order to provide information on the whereabouts of colleagues

Slackbot Person Tracker

It’s impossible for me to go into detail without it being a copy/paste from this tutorial, which is effectively 90% of what I did. It’s so good, and makes the whole thing very easy. So I wont do that – you should read that tutorial for a great overview of the whole process.

David McCreath’s walk-through takes you through setting up a slash command > posting to a php script > using cURL to open and then handle a JSON response from a url > handle the response > post a message back to Slack. It really is a very nicely put together guide. From here on out, I’ll only refer to the bits I altered, and it probably wont be as well written.

The url used in the example is https://isitup.org/, and while it can useful to check the status of websites directly in Slack, replacing it with something else is very straightforward. Pretty much anything that returns JSON will slot nicely into this script with only a little tweaking, so the only thing I needed to do was think of something and get my JSON on.

I had all sorts of ideas, some of which I’ve subsequently implemented – <spoiler>future blog post on how I built a self contained Slackbot FIFA league table</spoiler> – but to start with I wanted something semi useful and easily editable, with minimal set up required.

What’s semi useful?, I thought. Something that tells you where your work chums are at any given moment, I answered. What’s easily editable?, I continued. Google spreadsheets, I said. And does it provide data in an easy to work with JSON format?, I challenged. Apparently so, said the internet.

As you can tell, it was a great conversation.

Google spreadsheet of locations

I started with a Google doc, not dissimilar from this one. It’s editable by anyone that has the link, so people can add themselves and update their location as and when. As per the link shared above, there’s a parameter you can use to return the spreadsheet info in JSON, and if you use that URL in the PHP script, you end up with lots of data ready to search through.

That was the hardest part. Actually traversing through the response to find what I wanted and only grabbing the bit I needed.

The script takes what is posted from slack after the /slash command and puts it into a variable called $text, ready to use. So in this case, once I’d configured a slash command of /whereis on Slack, when I type “/whereis matt“, the word ‘matt’ is what I’d get through in my PHP script.. then all I needed to do was match that value with wherever ‘matt’ appeared in the spreadsheet data. I wrote a small function to search the ‘Name’ column for matching names and return the corresponding ‘Whereabouts’ value (or return ‘nope’ if there was no match).

function lookup($people, $value)
   foreach($people as $location => $person)
      if ( $person['gsx$name']['$t'] === $value ) 
         return $location;
   return 'nope';
$text = $_POST['text'] // what's posted from slack
$location = lookup($response_array, $text);

The next thing to do was create the responses which would get sent back to Slack. Mostly these consist of simply passing the location info as it appears in the spreadsheet, but there’s a couple of fallbacks too for when there’s no match, or if the call itself fails. You can put in emoji’s too, so for the few people in our channel that have their own emoji faces, it’s a nice touch to include them.

if ($location != "nope") {
	if ($response_array[$location]['gsx$whereabouts']['$t'] != "") {
		$reply = "*".$text . "* is " . $response_array[$location]['gsx$whereabouts']['$t'] ." :simple_smile:";
	} else {
		$reply = "*".$text . "* is hiding something :zipper_mouth_face:";
} else {
	$reply = "I don't have information on *".$text."*, I'm afraid :no_mouth:";

Finally, you echo the $reply, and all being well, you end up with a personal response to your Slack commands. That should be noted by the way, this particular tutorial results in a bot that is visible only to the person who posted the request message. To make something that posts a public response to the channel, you need to configure an Incoming WebHook, and drop a few more lines of code into your script.. I’ll cover it in a follow up to this when I write up the previously mentioned FIFAbot thing. It’ll be worth the wait.

Final note: this was merely a proof of concept. No-one’s activities are being tracked on a Google spreadsheet ready for Slack consumption. It’d take a bit of persuading to get people to commit to updating their location constantly, and there’s all sorts of considerations around privacy and whatnot that some people might take issue with. That said, I think it’s got legs. It wouldn’t be too much of a stretch to make something like this which is fully automated, actually tracking locations of people so that they don’t need to manually update a spreadsheet. Someone go and make it, then let me know how it goes down in the workplace :)

Tags: · · ·