How to Send Messages to Slack using a Google Sheet

[@BootstrappingTools]

🎉Are you interested in a Bootstrapping Tools Community? Let me know! forms.gle/4tzvffHiRqS7pVZY6 🎉

[@satomisd6195]

Thank you so much Joe for your tutorial! It's clear enough

[@rajyagami]

amazing video, i learned on fly and sent update. but can you share any how to on similar way to send slack post when certain date is matched in excel sheet ? it would be really helpful...

[@BootstrappingTools]

You mean like only sending something if the row is assigned a certain date? You'd basically just want an IF statement --> if (row[#] == date_value) { /*then send it*/ }

[@AndrewMathers63]

Hey Joe, great tutorial. I followed your example all the way through, however I couldn't get the highlight to work. What does the text need to be enclosed with to make it red or bold? Is that feature browser specific? Will it work on Sack mobile app?

[@satomisd6195]

To make it red like in the video, you need to put the variable or text in back quotes. Here is an example: "Status changed to `" + status "`"

[@tribalistgg]

Will this trigger if the sheet was updated using importrange?

[@ilhamgeekhunteritsupport1495]

Hi, thank you for your tutorial. Do you have a tutorial on how to send Slack messages to Slack user(using slack ID) from google script?

[@BootstrappingTools]

Glad you liked the tutorial! I don't have a tutorial for how to do that yet, but the way you would create a bot that sends messages directly to a user is by hitting two endpoints in succession. 1. Make a request to the "conversations.open" endpoint with the user id as a url param for users 2. In response, there's a "channel" object, grab that id and then hit the "chat.postMessage" endpoint with that channel id as the channel param and your message in the text param. Happy to do a video walk-through on this subject as well. Let me know if you run into any trouble doing this.

[@muhammadilhamsiddiqqulhaki5930]

@@BootstrappingTools thanks for your explanation, helpful

[@ilhamgeekhunteritsupport1495]

@@BootstrappingTools thank you for your explanation

[@tomasgic5857]

Thanks for this tutorial, it was super helpful! I will share you channel with my network, you deserve way more subscribers and views! I have one question: Is it possible to send a notification to Slack when a certain script (function) in google sheets has finished running? If so, could you briefly explain how? I can't seem to find this specific case anywhere...

[@BootstrappingTools]

Thanks so much Tomas, your support means a lot to me! Glad you're enjoying the content :) To send a notification to slack when a script/function has finished, you can attach the slack chat.postMessage api request at the end of the script so that the last thing it does is send a slack message to notify you of its completion.

[@tomasgic5857]

@@BootstrappingTools Thanks a lot for the reply: it worked for me! I was also wondering how you would go about setting up slack notifications if a google spreadsheet has not been updated. For example if a weekly report has not been added.

[@BootstrappingTools]

Glad it worked! There's a few of ways you could do that 1. If you want to see if anyone has touched it at all, you can leverage the DriveApp library to get the file and then it's last updated date. e.g. DriveApp.getFileById(FILE_ID_HERE).getLastUpdated() developers.google.com/apps-script/reference/drive/file#getlastupdated 2. If you have a very specific sheet/tab in the you want to monitor, you may need something more sophisticated like a listener that records the last time an edit was made to that specific sheet. You can use an onEdit(e) trigger to listen for changes and only log a date using ScriptProperties.setProperty({lastUpdated: new Date()}) if the change was made on that sheet/tab. This method would even let you narrow it down to a specific cell or range In either method, you'd create an if statement to see if the last updated date is x days ago and then send a message out if the comparison is true.

[@juanpablomartinez9211]

Hey Joe, thanks for this tutorial, could ask you. If I want to send a notification to bob i.e mention him he received the task, how would I do that?

[@BootstrappingTools]

If bob's slack username is one word (e.g. no spaces), then you can just do @bob If it has a space, you'll need to first call slack's api endpoint for getting the user id using the person's email address --> users.lookupByEmail (or something like that). Alternatively, if you'll only need to mention Bob and no one else, then you can just copy down their user_id from their slack profile (it's somewhere in the UI, might be a right-click option when you're using the slack app).

[@AnyPossibleRandomThing]

What if I want to return/send a hyperlink? along with the message? Also, what if I want to mention a group? or a specific person?

[@BootstrappingTools]

To mention a group or person, you just need to do @____ for them. However, if there's a space in the name, slack won't recognize it. I suggest using the group id or user id if you're planning to @mention someone in the message. For the hyperlinks, I think slack will just recognize it as a link if you include it in the message.

[@nami1540]

Hey Joe, how do I trigger a change in a field which is determined automatically? I want to point out new highscores of certain team members

[@BootstrappingTools]

You'd need to add in your criteria into the IF statement block. e.g. - if (playerRole === 'higherscore_notifications') { //send them the highscores )

[@nami1540]

@@BootstrappingTools Thanks, I will try it. Previously it would return not the value but the formula of a field. That obviously makes no sense. What documentation for such coding would you recommend? I would like to use this as a motivational element for our rowing team. Each time someone breaks his or the team record, I want to applaud him/her.

[@BootstrappingTools]

@@nami1540 Hmm... So when you pull the value of the field using range.getValue() - it returns the formula? That is definitely weird. Send me a message on [e-mail] with your code snippet and I'll take a look.

[@user-wo8ih4xn4w]

Amazing tutorial Sir! I have a question. I have a report that I run, that updates another sheet via importrange. Because the changes are like a copy paste, the event doesn't trigger. Is there a workaround for this? Also I can't figure out how to get it to return a set of data instead of a single cell. For example, on edit or on change, it returns cells A1:B20. Any help is greatly appreciated!

[@keyd2356]

Thanks for the tutorial ! Do you know if it's possible to make the bot add an emoji reaction to their own message almost immediately after posting ? keep up the good work !

[@BootstrappingTools]

Oh yes, I've seen those before. Haven't made one myself.... BUT the Slack API has an endpoint called "reactions.add" which lets your bot add a emoji reaction to a message. That's probably what you'll want to use.

[@rheybustos689]

Hi Sir, that was impressive tutorial May I ask if How do you call the google account that triggers the google sheet and put as well in Slack Notification? thanks sir

[@MelodyFrancisco-dm7it]

Can we send the report to Slack in a way that - for every row that's been added, it will send the report to Slack? Like this format: Header & Data Header & Data So on... Not a message but the newly input data will be sent to Slack. How can I do it?

[@BootstrappingTools]

Well.. slack doesn't do formatted tables - so if you're okay with it just being text with the usual slack text formatting... that's totally doable. Though.. it sounds like you're not trying to send a message? Not sure what you're trying to accomplish if it's not a message.

[@sKeithZ]

Hey Joe, amazing tutorial. Using your existing script over here, what changes should I make to the script if I only want the script to only be applied to one specific sheet tab out of the many in my google sheet file?

[@ArmanDavoodian-jo5zg]

Hey Joe, Thank you for the tutorial. I have an IF formula for my checkbox to automatically change the Value to TRUE or FALSE, it, but the code you have doesn't send the message to Slack whenever the checkbox automatically changes.

[@Marvin-oy8iw]

Hey. Is there a way to let say three users are working on the google sheet? How should I do it on the scripts? Thanks in advance. More power. Thank you.

[@ujjwalsharma1752]

1. Is it possible to send a separate message to the particular assignee? There will be a dropdown menu with the assignee name. Whenever the admin will assign a particular task to any assignee, the assignee will receive a notification in his slack. 2. Can you please guide me in detail on how to receive a message when someone submits a google form?

[@BootstrappingTools]

Hi Ujjwal! 1. You're in luck because I recently made a video around sending slack messages directly to specific individuals. You'll need to tweak it a bit for your specific use-case of selecting an assignee and triggering the message to send when the task is assigned, but it's pretty straight forward. Here's the link to that video: kzbin.info/www/bejne/aYLWqK2XbdyEhbc Let me know if you have any trouble with it - I would be happy to make a video that walks through your exact use-case if that helps. 2. Ah yes, you can achieve this by creating an installable trigger that fires based on a form submission. This video on auto-reply emails includes a part about doing just that: kzbin.info/www/bejne/Z3S0qqCqnbeWr9E It's not your exact use-case, but I'll add this topic to my queue of videos. Good luck! Feel free to reach out if you run into any trouble.

[@liviaverdolinbernardes7433]

Hi Joe, thanks for the tutorial. I have a sheet that creates a new line everytime I have a new response in my Google Forms, and I wanted to send a message to slack everytime I have a new line created in this sheet with the main information of this line (3-4 columns). So basically, it would have to get everytime the last line of the sheet, and recognize when this line is created. Do you have any recommendations on how to do this?

[@BootstrappingTools]

Hi Livia! OH BOY DO I HAVE A SUGGESTION FOR YOU! (was all caps too much?) Anyhowww, you can use an onFormSubmit() trigger for what you're trying to do. It'll always grab the row/line of data that was submitted by the Google form so you don't have to worry about pulling the last row of the sheet. This way, it'll also ignore anyone writing directly into the sheet since you're really aiming for the form submissions and not just any new row in the sheet. Here's a more in-depth video event triggers in general. Example number 1 is actually exactly what you're looking for - using onFormSubmit with slack. What a coincidence, right? kzbin.info/www/bejne/nKS4YqSFnbKhd6s

[@gustavodaniel7570]

Hi! I saw a couple of comments regarding files with multiple sheets, but for me is not clear how can I apply this logic to a specific sheet in a file with multiple sheets. I love what you have done, it will be awesome if you could give me some advice on how to apply it to a specific sheet in a file.

[@BootstrappingTools]

Hi Daniel! So you would basically want to add in a check that compares the name of the sheet which triggered the event against the sheet you want to fire off the script. So in the if statement in this video's code: if (e.range.getColumn() == 3) { // stuff } You'd want to update that to something like: if (e.range.getColumn() == 3 && e.range.getSheet().getName() == 'your_target_sheet_name') { // stuff }

[@gustavodaniel7570]

@@BootstrappingTools Hi, Thanks for your answer, you guys are awesome. However let me explain what I am trying to do. I have a sheet (sheet1) in which a user puts some info and runs a Macro that makes some calculations but at the end it leaves some info in another sheet (sheet2). I need to send a message to slack everytime a user hits runs the macro with the results that the macro puts in sheet 2. Let me know if I am being clear (english is not my first language 😊)

[@BootstrappingTools]

@@gustavodaniel7570 Not your first language? I thought it was very well written. I totally understood what you're trying to do! My suggestion here would be to put your slack api call right after the code that puts in the data into 'sheet2' - so that it's basically the last thing that happens and ensures that you have the info you need to curate your slack message. So kinda like... function yourMacroLogic(e) { let ss = e.source let sheet = e.range.getSheet() if (sheet.getName() == 'sheet1') { // do the calculations that your macro is doing ss.getSheetByName('sheet2') // write data into sheet2 let slackMessage = `A message with the same ${data} you put into sheet2` // your slack api call } }

[@user-tt6uw6rn9p]

Hey Joe ! thanks for the tutorial ! I'd like to know if it is possible to send messages with a column date ? for example employee birthdays ? Thanks a lot

[@raswin8058]

Did you finad anything related to this?

[@Eperezmosqueda]

Hi Joe, I really liked your video. I am developing a case similar to yours but I can't complete it because I don't know how to do it. Please, could you help me with the GAS? I have column G complete with checkbox that is automatically updated by an IF. I want my column (G) to be my trigger, that is, if any cell automatically changes to TRUE, the following message is sent: text: assignee + "data is: " + status + "data is" + task + assignee = column A status = column B task = Column E

[@BootstrappingTools]

Hi Eliel, thanks for reaching out! It sounds like you'll want to create an "installable trigger" for a script that checks to see if the triggered range is for Column G and the value is set to "true". From there, you'll just need to use the getRange() function against that row to grab your assignee, status, and task data. For the triggered event, you'll need to reference the triggered data - assuming you're using "e" as your function's parameter, you can use e.range to determine which cell was updated. From there, use an IF statement to see if it's column G and if the value is true (should be e.value) If that evaluates to true, that's where you can do getRow() on the range to retrieve the row index, and then use that in your getRange() against the sheet to get all of the data you want to pass through the Slack API as a message. Best of luck with this - if you run into any issues/errors, feel free to reply back with the error message and a snippet of the code.

[@kristoffersteen5898]

Hi, thanks for great tutorial! i have one question: is it possible to add so you also can tag a person in the slack message?

[@BootstrappingTools]

Hi Kristoffer! Yup, you would need the slack user id in order to do that (at least, I've found it more accurate/stable to use the slack user id instead of just their slack handle) For example, the message you write would look sorta like this: "Hey , I'm pinging you on slack" If you need to get the person's id, you can use the "users.lookupByEmail" endpoint on the slack api and pass through the email of the user you want to tag in your message.

[@krsteen8448]

@@BootstrappingTools Thank alot:) keep up with making great videos!

[@BootstrappingTools]

@Kr Steen Glad you're enjoying the content. ❤️

[@user-vs1tj6ct2p]

hi joe, is this doable if we already have existing workflow that's created from zapier?

[@barrettjakeman3419]

Hey Joe, thanks for this tutorial. I've managed to get it setup the way I want, however I was wondering if there is a way to limit the amount of POSTS to Slack per minute? When updating multiple cells in a row on the sheet it will send through a succession of notifications, which isn't required. Ideally I'd like one notification sent per row edited, rather than per cell edited. Hoping you can help!

[@BootstrappingTools]

That's a great question! To do something like that... you'd have to alter the code quite a bit so that you're keeping track of which row was edited and also the last time updates for it were sent to slack. Off the top of my head, there's two methods that could be used: 1) You set up a notification job that runs every like 5 minutes or something, then set up an onEdit trigger to capture the timestamp of when someone updates the row. In the notification job, you'd want to check for a row's last updated timestamp and compare it with the last time it notified to slack - making sure to update that notification timestamp once it successfully posts the slack message. - The potential downfall here is if someone is editing a row during the same time that the job runs to send the notification. In this case, there may be 2 notifications - each with a partial update in them. 2) Put a delay on the slack message so that it gives the user some time to update any other cells in the row before it sends the message. - The potential downfall here is that apps script limits the number of concurrent onEdit triggers than can run at a time - so you might end up having a bottle neck if many updates are going on at the same time. There's a more sophisticated way of doing this... but that would require a bunch of designing to make sure any of the downfalls I mentioned above are handled. At a high-level, it'd be creating a pseudo pub/sub system that logs the changes into a separate place and processes them to send the notifications out. Might be a fun topic to cover in a longer video... :)

[@raphaelpereiragomes8398]

Hey Joe, good job on this. I need a tip about a code that i'm doing... What if I wanted to change the Status of the task via Slack? Like, I type a slash command on a channel and the Slack changes the value "Status" on Sheet?

[@BootstrappingTools]

Hmm, that's possible with the Sheets API - but that's a much more complex build that would require you to build out a fuller slack app to handle that. One that accepts POST requests to it so that it can pick up the data that your slack app is sending out whenever someone makes a slash command. I know that you can turn your apps script project into an api executable, but I'm not sure if Slack will allow you to provide customized parameters in the payload when someone does the slash command... If slack doesn't, then you'll need something in-between to manipulate the payload from slack into something that the apps script api executable can parse. Depending on how many slash commands you expect, I would suggest pipedream to serve as that medium. The other option would be to build an app outside of apps script that handles webhook responses from slack. Hope this helps! p.s. - this is a pretty interesting topic, so I might cover it in one of my future videos

[@BootstrappingTools]

Thinking about this some more... you might be able to deploy your apps script project as a web app instead since that has an execution command which allows you to leverage the doPost(e) command... I'd have to test this out when I have some time, but I hope this helps point you in a good direction!

[@francarti3053]

Hi Joe, thank you for this video. I've been using a version of this script for months now and it's really helped my team and I save a lot of time. I was wondering if there was a way to edit this particular script to send messages to different channels depending on the assignee that's working on the task. You're help with this would be much appreciated. Thanks!

[@BootstrappingTools]

Wowow, I'm glad it's been helping y'all for months! That's literally why I make these videos :) For channel specific messages based on the assignee, you'll want to do something like a switch statement and then switch out the channelId in the payload. Sorta like... switch (assignee) { case 'bob': channelId = 'somechannel' break; case 'jane': channelId = 'someotherchannel' break; case 'steve': channelId = 'anotherchannel' break; default: channelId = 'defaultchannel' break; } let payload = { text: 'your message for slack', channel: channelId } ^^ wrote this off the top of my head, so it might need tweaking before it works. This is roughly what you need though.

[@francarti3053]

It's working now! Thank you so much. @@BootstrappingTools

[@BootstrappingTools]

@@francarti3053 Glad it's working!

[@insanity9954]

@@francarti3053 Hello, I have the same problem but I can't change chanel with the url which is unique to a chanel. How did you manage to do it?

[@ewtwetrwerwteet]

Does everyone need to install the App/Plugin or can you deploy it to the workspace once at admin

[@ewtwetrwerwteet]

Oh, and thank you for the tutorial Very helpful :)

[@BootstrappingTools]

Glad you're finding the content helpful! After you've created the app, you should just need to install it once into the workspace. From there, you're really just calling the webhook url to trigger the messages. Keep in mind that this is specifically for the webhook url feature. If you're building an app bot, then I believe you'll need to add it to the channel for it to interact with your users.

[@apu3501]

Is there a way to do this by Sheet name, I have multiple sheets in the active spreadsheet and if I make edits in any sheet, it triggers messages

[@BootstrappingTools]

Hi App! (I'm guessing it's not your name, but it's all that I have 😅 ) Thanks for reaching out. Yeah, you can totally do it by sheet name. The trigger will still fire, but you can stop the message from getting sent by adding a conditional to your code. At a high-level, you'll want to grab the active sheet (i.e. getActiveSheet() ) and then get it's name. From there, add in an If statement that compares the active sheet's name against the sheet name you want the messages to fire off from. Let me know if you have any difficulty with this. I'll also add this to my queue of video topics to help walk ya through it.

[@apu3501]

@@BootstrappingTools hey 👋 thanks for getting back to me, I'm thinking of using on form submit trigger in script as the sheet is connected to a Google form

[@BootstrappingTools]

Hi Apu! Yeah, that's totally possible. So for the case of trigger off of a form submit - you won't have a source to work from, but you will have a range. So what you'll to do is get the range and then chain a .getSheet() off of it. -- For example: "let sheet = e.range.getSheet()" (assuming you're using "e" as your param for the triggered function. Once you have that set up, you can use it for whatever comparisons you need.

[@apu3501]

@@BootstrappingTools wow! That went way over my head 😂 I have activated notification for all your videos, would love to see one on 'How to Send Messages on Slack upon Google Form Submissions'

[@BootstrappingTools]

Haha, thanks for subbing! I'll add this to the topic queue - please stay tuned!

[@Snorlax-94]

great challenge (I couldnt manage to make it work): make the trigger send a GIF to slack. Let me know if anyone could do it

[@ButterflySongs2714]

Hey Joe, I really liked your video and content. Thank you for such valuable information. I have 1 doubt, I pasted your code exactly same, still getting an error as below - TypeError: Cannot read property 'range' of undefined sendSlackMessage @ Code.gs:2 Can you please help with the same to resolve ? function sendSlackMessage(e) { if (e.range.getColumn() == 3) { error in the second line.

[@BootstrappingTools]

OMG, youtube held your comment for review and I didn't notice until now 🥲 So the error you're seeing is b/c "e.range" can only be obtained when the function is triggered by an event. To do that, you'll have to set up the trigger to run off of an onEdit event - and then edit something in the associated spreadsheet. I go over that around 6:27 in this video.

[@BootstrappingTools]

What's the error you're seeing? How are you executing the script? I might need to take a look at your code to really figure out what's going on with it.

[@anderson-andii]

Congratulations on the work! On my machine it's not working even adding the trigger. The error is: --- Registro de execução 01:36:08 Notificação Execução iniciada 01:36:09 Erro TypeError: Cannot read property 'range' of undefined sendSlackMessage @ Código.gs:2 --- What could be the problem? :/ function sendSlackMessage(e) { var range = e.range; if (e.range.getColumn() == 3) { var activateSheet = SpreadsheetApp.setActiveSheet(' Precher Folga ✏️'); var source = e.source; var row = e.range.getRow(); var task = source.getActiveSheet().getRange(row, 1, 1, 1).getValue(); var assignee = spurce.getActiveSheet().getRange(row, 2, 1, 1).getValue(); var status = e.value; var url = 'webhook' var payload = { text: assignee + " set a task to " + status + " -- " + task } var headers = { 'Content-type': 'application/json' } var options = { headers: headers, method: 'POST', payload: JSON.stringify(payload) } UrlFetchApp.fetch(url, options); } else { return; } } Thank you

[@BootstrappingTools]

Soooooo the issue you're running into is that you're trying to execute the script through the script editor, but your function requires an event object which you can only get when the function is triggered by an event. To get around the error, make an update to the spreadsheet instead of running the code through the script editor. - Also make sure that when you edit the spreadsheet, you're making the change to Col C since that's what you've set as the column to check in your IF statement. Let me know if you're still running into the error.

[@anderson-andii]

@@BootstrappingTools Now it worked, it was a simple mistake. Thank you so much for this class.

[@anderson-andii]

@@BootstrappingTools Here on my machine the trigger doesn't work if the cell has a formula changing the value, only if I change it manually. Do you know how I can make the trigger that contains formula in the cell work? As in your example, when I put in a formula to change the status automatically, the trigger doesn't work. thanks :)

[@BootstrappingTools]

Oh, that's an interesting use case. If your status changes automatically, what are the conditions for it to change? Does it depend on if another column in that row is changed? If so, you can use that column as the trigger but update the IF statement to check for the value in the status column instead. Off the top of my head, maybe something like: if (triggeredColumn == `your_triggering_column` && statusColumn.getValue() == `some_status`) { // ...do stuff }

[@daru946]

Hi! Thank you for this video! I copied the code and adjusted the names of the columns so it can reflect my sheet, however, the error that keeps popping up is : TypeError: Cannot read property 'range' of undefinied. SendSlackMessage @test.gsL2 Any insight would be appreciated. :S

[@BootstrappingTools]

Hi Daruko! Thanks for reaching out. That sounds like you might not have the trigger set up for onEdit() OR that you're not executing the script via the trigger. To execute the script via the trigger, just edit something in the spreadsheet and then check your execution logs through the apps script editor.

[@daru946]

@@BootstrappingTools , thank you, it works now!! Thank you for creating awesome videos!! ❤

[@daru946]

Hi, if I can bug you a bit more, how would this be set, if I want only one status to send a message? For example Status Completed would send a message but not other statuses

[@BootstrappingTools]

haha no worries, I love the engagement. To have it only send based on one status, you'll need to add in an IF statement. For example if(status == "Completed") { // then do stuff } else { // don't do stuff }

[@CeciliaMontessoro]

for getActiveSheet().getValue() i get undefined and not the value

[@BootstrappingTools]

"getValue()" only works for a range, so you'll need to do a "getRange(INSERT_RANGE)" on the sheet object before you attempt to pull the value.