Question

Sending scheduled Looks to Slack

  • 28 December 2015
  • 16 replies
  • 365 views


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.


16 replies

This is great!! Thanks for sharing.


@Dave_Suchmann check this out

Did you try using the “Email to Slack” feature?

Nice work, @Michael_Erasmus!!! We’re heavy Slack users as well, so it’s nice to see Looker and Slack together. Given the new tools that Slack just made available, I’d love to see the Looker team provide a tighter integration with Slack.

how do you get a three part zapier sequence? I have never used zapier and it seems like they only offer a two part sequence.


@jamiew We did, however we really didn’t like the way that data is presented in Slack. It looks like an email, and you have to expand the message to see the attachment. We really wanted to have control over the formatting and making the data easily digestable at a glance, which is why I used Zapier.

@taramaclean I think that you can initially only set up a Zap with a single trigger and 1 action (2 part sequence) but adding an action or filter after that is pretty easy by just clicking the + icon when editing



We have a business plan with Zapier, so this might be a business only feature, although I don’t think it is.

Thanks so much! it turns out it was a beta feature.


I do actually have a second question. When I use your code snippet I get an error saying forEach is referencing a null value and as I looked through I couldn’t figure out what it was referencing.


could you possibly explain what item is referencing here?


Also, thank you so much for your help! I am really excited about implementing this.

Hmm @taramaclean, that’s a good one. Can you perhaps send me screenshot of the error and on which line it is?


Looking through the code there are a couple of .forEach loops, but the one that doesn’t check for a null value (which it probably should), is this one:


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];
}
});

Basically what this does is look for all <a> tags in the email body to find the one which has a open-in-looker attribute. Normally I would expect any scheduled Look email to have this link.



It sounds like there might be something funky with the email body you’re testing on, could you double check if looks correct? Zapier let’s you see the email body on the ‘Test this Step’ page. Look for something like this:






Hope that might help! Excited to see if we can get this working for you 😃

that was exactly the problem! the email I was testing against wasn’t formatted exactly how it needed to be. thanks again so much for the help!

Thanks wilg! Good question, I actually realised I left out a crucial step in my original write up!


In Zapier, the ‘Send Slack Channel Message’ action lets you attach an image. We use the image url we grab from the email trigger:


https://www.dropbox.com/s/9izlf5hr8n89xfh/Screenshot%202016-01-14%2015.25.39.png?dl=0


I’m a little fuzzy on the details of what happens (Zapier just takes care of it), but I think the image gets uploaded to Zapier’s servers, and the Attachment field points to that URL. Looking at the image detail in Slack seems to confirm it:


https://www.dropbox.com/s/xx3d6e214d25cgu/Screenshot%202016-01-14%2015.30.01.png?dl=0

FYI it seems that since 3.46, URL of looks that contains a “#” resolve to an error page (“Could not find either the model or view requested”).


So basically we can still receive them on Slack But you can’t access the look either from the URL “company.looker.com/looks/XXX” or from the space where it’s located.

Oh wow Cyril, here I’ve been scratching my head trying to figure out why my scheduled Looks are broken and could get to the bottom of it. Did you find a workaround for this?


Perhaps the channel parsing code in the Zap could be tweaked to use a different syntax, something like SLACK_CHANNEL=xxx?

Yes just like you said.

Just need to change the JS in Zappier to take into account a new variable like SLACK_CHANNEL=xxx

Hi! So I had this set up and it was working great! Then a couple months ago something happened and the url stopped showing the end part of the URL that had been parsed. So it just showed the part that we write in. Not sure what happened and when I play around with the code it seems like it is skipping the part where we reassign the url variable. Any ideas what is going on here?


// 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(‘plex.looker.com’) == 0) {

url = ‘https://plex.looker.com’ + url;

}


var cleanSubject = input.subject;

var ccString = ‘’;

Here is a solution to the aforementioned problem.


// Get url

var url = ‘’;

if (input.bodyHTML) {

url = ‘https://plex.looker.com’+input.bodyHTML.split(‘plex.looker.com’)[2].split(’"’)[0];

}


var cleanSubject = input.subject;

var ccString = ‘’;

Just want to confirm that we’ve switched over to lookerbot and it I would definitely recommend that over my original hacky version!

Reply