Hey there stranger!

Sign up to get access.

Creating a Live Message in Google Sheets

About this Tutorial

Sit back and enjoy this comprehensive tutorial on how to create a live message in Google Sheets. In this video, we dive deep into the process of live message creation, providing step-by-step instructions and practical examples. Whether you're a beginner or an experienced user, this tutorial will empower you to unleash the full potential of Google Sheets.

Video Transcript

0:00 Hey, so we're gonna create a live message in Google Sheets, meaning it'll pop up. This is also called a toast message or toast notification, but I want to show it to you real quick.
0:09 But right now, I want to change the background of this to make it a little bit nicer. Alright, so we use Sheets styles to do that.
0:15 Now, I'm gonna insert a checkbox here and do some, like just a few check boxes maybe we have like a checklist of task one task two and we have these tasks and we want to check them off and we want to check them off when we check them off we want a little like confetti but a little notification this 
0:33 is yay so we're gonna use this checkbox as this trigger to say okay we're triggering this we want this message to come up I'm actually going to move my face because the message is going to come up here.
0:44 We're going to go over to extensions, app script, open our app script. We have to do this in app script.
0:49 We're going to use the on edit trigger here and on a simple trigger on edit. When we create the on edit or when the on edit event happens, it's going to be one of these checkbox.
1:00 So right now we're going to if we need, what do we need? We need the source E.variable row equals E.range.get row.
1:14 Variable column equals E.range.get column. And if we need, what do we need? If E, no, row is equal to, we're gonna do it in row five to 11 is not equal to, is greater than or equal to five.
1:37 Double ampersand, row is less than or equal to, what was the, the other one, 11. And then we also want and column is equal to, two equal signs, D, which is four.
1:53 We don't care right now about the sheet or anything like that, we just want to get this done. Now what are we gonna do?
1:58 We're gonna do spreadsheet app dot get app. Active.Toast. And we're going to write here a message. We can write any message we want.
2:07 We want to put a variable here. Variable message equals. We want spreadsheetApp.getActive sheet. Dot get range. We're going to use the range of the actual event.
2:26 Row column. We're going to get value, but we don't want the value of the checkbox. We want the value of the e column.
2:36 So we're going to take the column and we're going to add plus one here. We're going to save. That. Let's see if this starts right away.
2:45 We're going to check that box. Check this box. We're editing here. And let's see if we have some errors. We have completed.
2:57 We can also add some. There it is. We didn't need to do anything. We just had to wait a second.
3:04 So now, as we do this, we're getting the name of the task that we completed. And we want to say, you know, variable message equals, let's see, let's change that to task.
3:15 And then we want to say variable message equals task plus plus complete. And maybe we want to say, I'm going to add yay.
3:31 Oh, we need a plus sign there, and an exclamation point. We always want exclamation points, right? So now as we click these, yay complete.
3:41 Issue is right now we're going to get this message anytime even if we uncheck it we're going to get it so that might be something we want to fix.
3:51 What we could do is get the value is equal to, let's say, true, right? The new value. There's also old value here, but we only want to do it when marking true.
4:11 So let's do Command-S. Let's see. If we uncheck this, is it going to happen? Nope, no toast. But now if we check task one, it's not.
4:22 Working we can also go over to here. We can also log this if we not want. E.value. We can do something like that.
4:35 Nope. So because this isn't working with this value what I did is I had to log this if we not want.
4:42 Added a logger here to edit value called e.value. And let's see if this shows up in the logs for us to see.
4:53 Give it another couple of events to log. There's the new one. And we'll refresh. There we go, we're logging, it's true.
5:06 So perhaps what we need to do is actually call this all caps true. Let's try that. Nope, it's not all caps true.
5:30 That's problematic. It should just be true. Let's go through a couple more. Let's add these two. We're still not getting a message, right?
5:44 Let's see what our execution looks like. Getting true. So what we had to do is we had to change edit.
6:03 Is equal to text true. So this e.value is not a Boolean true or false. As we were doing when it was true like this, we needed to put it in quotes and capital T-R-U-E for true.
6:17 And now it works only when the, now this toast will only happen when the There is a turn to true.
6:25 So if you check off the box to true, we now get our toast. And thus, we have now a nice little message, a little live message based on some action we take.
6:35 You can base this action on any kind of editing. You can access the row, the column of the edit. You can access even the value or even the old value.
6:43 Value of an edit and then say if based on these parameters or criteria, we're going to give this toast message and then we can even grab some values in the sheet to show our message to ourselves.
6:59 Create a really cool live message in Google Sheets.