Send notifications with Slack

#1

Based on @Torben’s Send emails with SendGrid post I’ve been looking for a similar notification system using Slack. It’s cheaper than SMS and faster than e-mail. :slight_smile:

Slack has a lot of functionalities regarding notifications.
I’ll just describe two of the existing methods to send notifications:

For both, I will show you a small example of how to send notifications to a specific channel or user.


Incoming Webhooks

This is the simplest way to post messages from apps into Slack as there is no need for authentication. It’s just a simple POST request to a specific URL.

Example:

  • Range A1:B3 - mapping between channels/users and Webhook URL
  • Range A6:B8 - notification text and the recipient
  • Range D6:D8 - POST request and response

Steps:
1- After creating the Slack App you need to create a webhook for each channel and each user that you want to send notifications. In my case, I’ve created a webhook for #general and another one for myself:

2- Fill all manual information as shown on the image above
3- Add the formula that looks up for the correct webhook and does all the magic

D7: =POST("https://hooks.slack.com/services/"&VLOOKUP(B7,A$1:B$3,2,FALSE);;"{'text':'"&A7&"'}")
D8: (copy-paste formula in D7)


Slack Web API

For authenticating with the Web API you need to use a Bearer Token (more on that below)
If you want to learn more about this: API Authentication Types and How to Use them

Example:

  • B1 - Bearer token used for authentication
  • E1 - calculated header used in GET and POST request (just for simplicity)
  • E2 - GET request to retrieve the list of users. Needed to send notifications to a specific user.
  • Range A5:C7 - notification text and the recipient
  • Range D5:E7 - used to retrieve the user_id by parsing E2 and filtering it with C7
  • Range G5:G7 - calculated body used in GET and POST request (just for simplicity)
  • Range H5:I7 - POST request and response status

Steps:
1- After creating the Slack App you will need to go to: Basic Information > Add features and functionality > Permissions
2- In the Scope sub-section, add scope: chat:write:bot
3- Add scope: users:read
3- Save changes
4- Go to the top of the page and click on Install App to Workspace
5- In the new page, proceed with Authorize
6- Copy OAuth Access Token and paste in on cell B1
7- Fill all manual information as shown on the image above
8- Add the following:

A1: Authorization
E1: =PAIR2JSON($A$1,'Bearer '&$B$1)
E2: =GET("https://slack.com/api/users.list",$E$1)
D6: =IF(C6<>"";PARSE(E$2,"$..members[?(@.name=='"&C6&"')].id");"")
E6: =PARSE(D6,"[0]")
G6: =PAIR2JSON(A$5,A6,B$5,IF(B6<>"";B6;E6))
H6: =POST("https://slack.com/api/chat.postMessage",$E$1,G6)
I6: =PARSE(H6,"['ok']")

If everything worked correctly you should have got a message to the #general and also to the slackbot.


Hope this How-to has been helpful for your spreadsheets.
This is my first one so please let me know if you have any suggestion!

5 Likes
Send Slack messages out of your spreadsheet!
#2

@pedropalmares, soon we’ll have this in integration form!
cool to see how you built it!

1 Like
#3

@pedropalmares just to let you know that the Slack integration is now live.
Looking forward to hearing your feedback on it!

2 Likes
#4

@henrique it works great!
5 minutes was all I needed to replace my notifications with a simple formula.

2 Likes