How to Send Messages to Slack using a Google Sheet

[Music] hey everyone thank you for joining me i'm a guy called joe this is bootstrappingtools let's build where we help scrappy bootstrappers just like yourself figure out how to automate their work and overcome some of the hurdles that they're facing now before we dive into today's video make sure to check out our youtube channel we got lots of videos up there that go through all all sorts of low code and no code solutions including retool shopify api airtable data studio as well as many others if you don't see what you're looking for up here feel free to send a email over to feedback at bootstrapping.tools we'll be happy to take a look at that app and possibly make a video just for you now in today's video we're going to be going over how you can send a slack message using a triggered event through google sheets which is also going to be using google apps script so that's right we're going to be using google sheets so if you're using task management in google sheets you're just doing something in there to manage your operations what we're going to do is we're we're going to use google apps scripts the triggered events in there to take a look at a specific field and when you update that it's going to trigger a message that we send over to slack so that you can notify any members of your team that a change has occurred and this makes a lot of sense right slack where everybody's basically on slack nowadays i mean you know they're definitely people who aren't on slide but there's a large majority of people that aren't slack so this should be really helpful uh for you and everything that you're doing in your company so uh let's go ahead and dive into it so up on the screen we got a uh template uh just some sample data that i put in here we're gonna focus on task management in this case i'm gonna make this a little bigger for everybody uh we have a task column and a signing column and a status column what we're going to do is we're going to actually trigger everything off of the status column here now what you're going to need before we get started over here is you're going to have to have your slack account open and just make sure you go over to the api.slack.com apps and go to your apps you can build a new one you can create a new app over here i already have one created uh g sheet notifier but once you do create your app and you dive into your settings for it there's an option called incoming web hooks over on the side this is the one that we're going to use because it's really easy to set up just click on that and then what this allows you to do you're going to activate this you're going to create a new web hook where with that webpack is going to be tied to a specific channel uh the channel that we have here is demo g sheet slack notification uh and what's gonna happen is whenever you hit that specific web po it's gonna send messages directly into that specific channel so if you have a project that's going on or if you just have a general tab that's that's for notifications like this you can just use this web hook to send messages to it based on whatever is going on in your google spreadsheet so once you have this set up uh you're going to have to copy this web url and we're going to use that in our code before we do that let's uh let's hop over to our spreadsheet over here we're going to be using google apps script so click on the tools option in the menu bar and the select script editor what that's going to do is that's going to bring you over to the google sheet so in the google sheet it's going to default to my function and that's basically we're going to start so we're going to change this let's uh let's call this send slack message all right we're going to test this out first so that url that we had before let's go back let's grab that go back to our code we're going to say declare url by saying bar url i'm going to paste that url in there once we have that now we're going to create a message or payload option for and that payload is pretty simple when we look into this it's really just text as the variable and then you're just going to say whatever you want within there so it's going to look just like that let's go said our payload equals we're going to set this to an object text and we'll keep it simple just like in the words hello world it's probably going to be the cleanest uh code that we write all day today um so we're going to set that to payload now we're going to create headers and the headers here we just want to pass through content type content type and then that's going to be application json and the reason we're doing this is because in the documentation over here it says sample request is content type application json and where that's the way that we want to pass it through and we're going to post it um we're going to make that request as a post request method so right below here let's go to end of our options equals headers is equal to headers you don't have to do you can do this all in just one shot but i prefer to do it separately just to keep it nice and clean now we have method which is going to be set to post and then we're going to have payload which is going to be set to the json stringified version of our payload all right so now that we have that we're going to use the url fetch app uh which is a library that google apps scripts uh gives provides to us makes it really easy for us to use we're gonna pass through the url which is the webhook url that we created specifically for that channel and we're going to pass through our options all right so when we run this what we expect to happen is that within our slack uh environment demo g slack notification is going to just go ahead and send the message here this just says hello world let's go here let's run the code and then let's go ahead and check our google uh our slack app and the channel says hello world perfect g sheet notifier that's the slack bot that we created and it's saying hello world it's working as expected so now the next step that we're going to be doing here is to make it trigger off of our spreadsheet so to do that we're going to use something called the on edit function but more specifically we're going to use the installable version of that instead of the simple trigger version of that so we're going to add in a trigger that is going to use the on edit as the trigger for it so we're going to say the send slack message is the function we want to run it's going to be the event source is informed spreadsheet and then we're going to select the event type as on edit and we're going to save that so now every single time that someone edits this spreadsheet it's going to trigger uh the send slack message function and then it's up to us within this function to determine do we even want to make that change so first thing we're going to do is we're going to do an if statement here and we're going to say e dot range uh yeah we're going to do e.range so dot get column and if that is equal to it should be three one two three for status three then we're gonna do all this otherwise we're not gonna do any of this so let's grab this paste it into our if statement can make the formatting a little bit better and then we're going to do an else statement over here and we'll just do a console log that says status was not updated the column updated was and so we'll do the same thing there where e dot get column so let's save this and before we actually start sending ourselves a message let's go ahead and comment this out post it in here status was column updated was and then that column so let's go ahead and change one value in here let's say bob's actually going to do the project plan instead of carla so we change that and go into our executions log we should see that something failed actually what failed e is not defined e is not defined because i did not put it in the parameter make sure you put in the parameter and that parameter is right up over here you need to add that in there save again and let's try that one more time so change our minds card is actually going to do the project plan so let's put that in there go to our executions and we can see that it's completed let's see what logs status was not updated the column updated was two so we go back here and then say this is no longer done this is actually in progress go back here wait for the next log which appears just here complete it and refresh it until we get the logs there column updated was three so perfect so now our triggered event is targeted to just column number three which is our status column our column for status and uh we're going to use this as the basis for triggering off the slack notification so going back to our code here now we have this we don't need the console log anymore what we're going to do is we're going to uncomment out all this code and instead of doing this console we're just going to say turn and then up here we're going to start getting the values out of the trigger row so a couple of things we're going to determine here we're going to say var source equals to e.source okay and then within there we're going to get the assignee name as well as the row so the row is pretty important because we need to know which one was edited so the row is going to be equal to e.range dot get row with the open and close parenthesis because that is a function that is made available to us and now we're going to say task is equal to source.getactivesheet and now what we're going to do is we're going to get the range based on the row which is a numeric value and then we're going to do the first column for the task or in the first column and we're only going to get one row and one column long but make sure you don't forget to get value at the end of that so you actually get the value and not just an object now we're going to do the same thing with assignee and the assignee is going to basically be equal to the entire thing that we just wrote the only difference is that instead of the first column we're going to do the second column and then status uh we're actually going to do something slightly different with the status we're going to say e dot value because that's um that's something that comes through the triggered event in our text we're going to say assignee i'm going to string together a couple of words here that a task to and then we're going to say status and then in there let's also add in the tasks so we have that context so now the payload whenever someone changes the status it's going to say an assignee name so maybe that's bob so bob said a task two and then done and then it's gonna out i'll put the task over there so going back over to our slack notification let's say this is actually done so when we hit the done it's going to fire off the event and what we expected for the text to say in slack is to say bob has set a task to done hyphen hyphen import data set click on that let's go check the slack notifications we see that it has says bob set a test done import data set let's try this one more time this is no longer blocked let's say it's done and go back over to her slack message steve said it tested done analyze data set and let's say carla is now creating a proposal for short-term solutions she's going to say i'm going to get that started so i'm in progress and then recruiting team members bob is going to say i'm no longer blocked i'm ready to start so we go back over the slack we got two new messages here probably set a task to in progress create a proposal for a short term solution and bob said it has been ready to start recruit team members so to make this a little bit prettier though you can actually add in markup into the code here so task um we can add in quotation marks for and status as you put the little tick marks for it you can make it look like code so we're going to do that and then we're going to say single quote over here and do the same thing at the end of task and when we save this go back to our spreadsheet i'm going to say maybe the long term solution is also in progress then we go to our slack our slack channel cartilage set task 2 in progress and that's got the little tick marks in it to make it look like code so it's easy to read and then you have little quotations next to the task name or title so you can clearly def um differentiate that versus the rest of the message but once um that's basically it for this um and all it took was one function over here we used an if function we made a trigger out of it and basically if the trigger is um is made by that specific column change and we're going to do all this code otherwise reason hit return we're going to ignore it and not do anything else but if you did run into any issues you drop a comment in the section below we're always here to help so don't be shy if you had a question or you ran into an issue about it most likely somebody else out there does as well and of course if you did like the video make sure to hit that like button it's the best way to help support this channel and keep us continuing to make content for you and all those crappy bootstrappers out there of course we have lots of videos coming up so make sure to hit that subscribe button and also the bell so that you get notified when we release the next video but i'm a guy called joe this is boot wrapping tools let's build it's been a pleasure and we're out you

Channel: Bootstrapping Tools

Views: 223

Keywords: Bootstrapping, Business Intelligence, automation, google apps script, google apps script slack, google sheet automation, google sheet notification, gsheets automation, how to build a slack app, how to build a slack bot, slack, slack api, slack api tutorial, slack app, slack apps, slack automation, slack bot, slack bot tutorial, slack integration, slack integrations, slack notification, slack tutorial, slackbot, slackbot tutorial, triggered message

Id: i19EjYZ7qbk

Length: 15min 25sec (925 seconds)

Published: Thu Oct 28 2021