Sending scheduled Looks to Slack

Please note that the Looker Slack Bot now directly supports scheduling for a more streamlined experience. Learn more here.

As a fully distributed company at Buffer we use Slack extensively to communicate as a team. I’ve been curious for a while if we could somehow integrate the two. One idea I had was to use the ability to send scheduled Look emails, but to have the emails go to a Slack channel instead. However, it was hard to get the slack messages to be formatted nicely.

With the 3.36 Early Access release I noticed that a new feature, still currently in Labs, lets you send scheduled emails with an Inline Image attachment.

I could use this new feature to get rudimentary Slack integration with our Looker data and was able to post graphs to a specified channel in Slack. The messages look something like this.

##How does it work?

This uses Looker’s email scheduling feature under the hood, but with some fun Zapier magic I was able to send these emails to a Zap that will craft a Slack message and post it to a specific room.

To start out with, I created a Zap in Zapier that will receive a scheduled email from Looker, do some processing and formatting and then send it to a Slack room.

Here is how the Zap looks in Zapier:

This is the email address we can use to send scheduled emails from Looker to (blurred out here)

The next action will run some Javascript, using the email body to extract the ‘Explore this data in Looker’ link, which channel to send the message too, as well as a CC list of people to @mention, if needed.

Here is the full code snippet, which is pretty hacky and could definitely be improved 😄

// Get url

var reGlobal = /<a[^>]* href="([^"]*)"/g;

var reGroup = /<a[^>]* href="([^"]*)"/;

var url = '';

if (input.bodyHTML) {

	var matches = input.bodyHTML.match(reGlobal);

  

	matches.forEach(function (item) {

  

  	var match = reGroup.exec(item);

    if(match && item.indexOf('open-in-looker') > 0)  {

    	url = match[1];

    }

	});

}

if (url.indexOf('looker.buffer.com') == 0) {

  url = 'https://looker.buffer.com' + url;

}



var cleanSubject = input.subject;

var ccString = '';



//check for !here



if (cleanSubject.match(/\@here/g)) {

  cleanSubject = cleanSubject.replace(item, '@here');

  ccString = ccString + '  <!here>';

}



//Get @handles

var reAt = /@\w+/g;

if (input.subject) {

	var matches = input.subject.match(reAt);



  	if(matches) {

      	ccString = ccString + ' cc';

		matches.forEach(function (item) {

    		cleanSubject = cleanSubject.replace(item, '');

      		ccString = ccString + ' ' + item;

  		});

    }

}





//Get #room



var reHash =/#(\w|-)+/i;

var room = 'data-alerts';



if (input.subject) {

	var match = input.subject.match(reHash);

	

  	if(match) {

      	cleanSubject = cleanSubject.replace(match, '');

      	room = match[0].replace('#', '');

    }

}



output = [{

  'url' : url, 

  'cleanSubject': cleanSubject, 

  'ccString': ccString, 'toRoom' : room

}];

The final step will send the message to Slack. In the message template, we can use a combination of the values that the our Zapier email and Javascript actions have collected to send a nicely formatted message:

##In Looker

With the Zap up and running, it’s pretty easy to set up scheduled messages in Looker, by just scheduling a Look and using the Zapier email address. Just don’t forget to send it using an ‘Inline Visualization’.

Looker gives you fine grained control on how often to send the messages, as well as conditions like if there are results, if there are no results or if the results have changed since the last run.

And that’s it! Pretty easy. This can be done on any Look, but you might want to experiment with the Looks visualization a bit and see how it ends up looking in Slack.

I tried this out and this even works on custom visualizations, here is an example of a heatmap:

##Controlling the channel and @mention’ing people.

Since Looker doesn’t really let you send any other data in the scheduled email beyond the attached visualization, I came up with a hack to have the Zap to extract that data from the Look title, which gets attached to the message.

By default, any scheduled email will go to a room called #data-alerts. If you want to change that, just add the name of the channel to Look name. You can also optionally add a list of @mentions or @here in the Look name, which will be included in the message and ping those people on Slack (obviously this should be used with care!)

This results in odd looking Look names, and I wish there was a better way of sending that data along, but it works for us.

Summary

Our hope with using Looker with Slack is that we could use this as a way to surface key metrics and also alert us to important or interesting events. Ultimately the idea is to ‘Bring data to the Team’, instead of needing people to have to go and find what they look for. Using Slack also makes data more visible and transparent to everyone and makes discussion and collaboration much smoother.

I’m hoping that ultimately Looker will make this kind of thing easier, by perhaps providing a public API for scheduled looks or built in Slack integration.

If you wanted to try this out yourself and run into any snags, just let me know, I’ll be happy to help if I can.