00:00 In this video we're going to create something we might not necessarily want, which is a micromanager, sort of a manager who is going to stick on us like a glue to a post-it note and ask us, are we done yet?
00:14 Have we done this task? We have task lists and we might keep notes and tasks and projects in Google sheets, but I wanted to show you how I might go about doing this and you might not necessarily like it, but you're going to learn an important lesson of how to send an email reminder in this video and
00:32 how to set it for an hourly trigger. So what we're going to end up doing is we have eight things we want to do here.
00:40 We have of tasks one, two, three, four, five, six, seven, eight, and we want to get those done in a day.
00:46 So we want to put in eight hours of work and get each one of these done in one hour of work, ideally, right?
00:52 And then this is sort of just this creative situation here. We're going to create, but we don't want to have to like come back to this each and every moment.
01:01 Be like, click on this. What we're going to do is we're going to send ourselves as an email reminder, because some of us have our inbox open and our email open all the time.
01:10 And we can sort of I don't know if you've done this as well, but I sort of sometimes live in my inbox.
01:16 I live in my email. And the top thing, the most recent email is the one that I'm. I'm going to be working on.
01:22 So this task list, this task list, we might create, and then we want to make sure, have we done that first one and get an email, an email reminder and says, Hey, do it.
01:32 And if it is checked, if it's checked the next one. So there's going to be a few things here that we have to do.
01:40 We have to send an email hour. We want to do it if there are blank or unchecked things. So if we have all of these check boxes checked, we don't want to get an email.
01:53 We've done our work for that day. We want to only get one item at a time. In our inbox. So that email should be based on this task.
02:06 So we're going to grab the information that's here, this task, whichever the first one is that's not checked, we're going to send that hourly and be like, have you done this yet?
02:15 Have you done this yet? Have you done this yet? That is our micromanager. Are you done with it? Are you done with it?
02:20 Are you done with it? And this is sort of our roadmap, right? I'm going to double check this off camera, but we're going to send an email.
02:29 We're going to create an hourly trigger to send that email. We're going to create an if winner. There are only blank ones.
02:37 We're going to get the one item at a time. Oh, and we're going to get only the. First unchecked item.
02:46 And we want in our, in, in our email, the task itself and perhaps a link to the sheet. Let's do that as well, because if for some reason we close this, you know, if we don't keep this checklist in our sort of as a tab, maybe we want to close it, work on that project and really focus.
03:06 But we're going to leave our inbox open all the time and we get that reminder, right? Oh yeah, I got to go back to that sheet.
03:11 We don't want to have to remember this sheet. We want to have a link to the sheet in our email.
03:17 So this is everything we're going to do. We're going to walk through this all the way. So first things first is we're going to go over to apps.
03:22 Start up app script and just start up app script. The email is actually one of the easiest parts of this.
03:29 We're going to use mail app in our function. So. I'm going to just called send for example. You can label this anything you want.
03:39 Actually, let's call it manage. All right. Maybe that's a weird title, but the simplest thing we're going to do is this mail app.send email.
03:49 We have some options here. We are going to probably use not the options here. One of these, they're all send email.
03:59 You can send just one message you can send. Let's see down. Let's click this with some we have someone who it's sending to someone who is the sub text.
04:13 That is the subject and text. That is the body. We'll probably do that one. So we're going to, who's the recipient?
04:18 We're going to call this variable R, R E C. We're going to have a subject. And we're going to have a body and that's a pretty simple thing, right?
04:27 Now we have to get our variables. Rec is going to be something variable. Subject is probably going to be micromanager and we're going to think of something to put here, some variable.
04:43 Text, but we'll get to that in a second and the body will be, Hey, are you working on, on this?
04:52 And we'll add plus some variable we'll get there. We'll say current task and maybe we'll say. Task subject here. We might change this.
05:07 I don't know. One second. So I think we can get our own email. So we don't have to type in our email here.
05:13 I think we're going to use session dot get active user dot get email. And what this will do, and we can also log it if you really want to see it.
05:22 But this will just get whoever is the. The user of the trigger that should work. We'll save this right now and we still need to get the task subject.
05:31 But we also want to, before we go too far, we are sending an email now so we can check that off the list.
05:38 But we want to create an hourly trigger that this thing is going to send every hour. But I think. What we want to do is do the if before that just so that we send the correct information.
05:49 So we'll do the trigger later. All right, let's see. What do we have? We have our checkboxes in B3 to B10 for right now.
05:58 So we'll save variable tasks equals. Spreadsheet app dot get active spreadsheet. And we're going to rename this sheet one to tasks.
06:15 You can rename it to anything you want. But we are doing this because we want to get sheet by name.
06:22 We're going to write tasks. Let's double check the capitalization, no capitalization, all lowercase get range. And our range is going to be three colon B 10.
06:33 I do believe that was correct. B three to be 10. Yep. And the, we don't need just the range. We also need to get values, which actually gets us the actual values we need the, the actual tasks.
06:47 Actually that's wrong. This is the task, the check boxes checks. And let's get the tasks. Oh, actually we don't need them.
06:58 We can, we can deal with this together. So we do want to rename, to name this tasks and we just want we want B3 and C to C10.
07:08 This will get us this two column and all of these rows. B3 until C10. And I'll show you what that looks like in a second.
07:20 So let's log that actually. Logger.log so I can show you what that might look like. It'll be. Little different than just getting the values of one row.
07:30 Let's comment this out mail app and we're just going to log manage. Let's save it and just hit run. Let's save, save, save.
07:42 We can probably comment out all of this. Other stuff too. Let's see if that saves. Hmm. Hmm. Oh, I think we can save it.
08:02 Now it should save there. Let's run it. We should see the log here. We need to review permissions. Get these permissions allowed.
08:13 And there we go. So now we see these falses here. We have our next two, these tasks. And if we check off a few and run this again, first few will be true, true, true.
08:25 So the checkbox. The box is just true or false. And then it'll have some text for the task. Great. Now let's just double check this session dot get active user.
08:35 I'm not super familiar with it, but we can always log it and see what happens. There we go. It's correct.
08:39 We email address. We want just some text here, whatever the task subject is. Do want the body and we're not going to send the email just yet.
08:51 We still have some work to do. So we need to get these values. We need to know, do we actually want to run the email?
08:58 When we set this as a trigger, it's going to run, but we don't want it to run all the time.
09:02 We want it to run. If eh, let's see. One second. All right. So what we're doing now is we're going to create a checks array and we're just going to create it from the first column of tasks.
09:18 So we have B three to C 10. We're going to get everything here, but we're just going to get the checks and then we're going to check, does it include false?
09:26 And if it. It includes false. If there is one of those check boxes, that is false. We want to actually send the email.
09:34 So what we did is here we did variable checks equals is this blank array. We created a for loop here.
09:40 I equals zero. I is less than task dot length and I inter rates one plus one each time we we take the checks and we push to it, whatever's in tasks, I zero bracket notation.
09:53 So the very first thing, all the true and falses. Then we're going to check variable check equals just, is there a false?
10:00 So this check is going to be either true or false. If there's a false, it is true. All right. Now we're going to use if check.
10:09 S.includes or actually we can do F check and that's going to be true or false. So it is going to be false if there is no falses.
10:21 If there is a false, it will be true. And so we will actually run this if. And now we can put all of this email.
10:26 We only want to put this email. In here. We only want to send the email if there is a false, if there is a blank thing.
10:35 So we'll now we'll be able to keep checking this off and the email will stop triggering. The trigger will trigger, but the email will not send if this is all checked off.
10:46 Cool. So now we have that check. Great. We're gonna, if blank or unchecked, it's done. We only want to get one item at a time.
10:58 So we want to know which is the top unchecked task. Cool. So now we are going to use index of, so we're using the checks.
11:10 Array we just built of all of the falses and trues. And we're going to just find the first false because true we don't care about, right?
11:18 We only, where, where we know if check, I mean, there is a false somewhere. We need to find the first false.
11:25 So we're going to use index of right now. It is the first one. And this, this index, this first one, this first false is going to be the one that we want to, the, what we want to get the thing in the C column and use that.
11:39 So let's use it. We're going to do variable first task or top task. Actually, we should call this like top task.
11:49 What is the current task? Oh, wait, I think we have it here. Current task is equal to tasks and then, ah, first and one in the bracket notation here.
12:05 And so this current task should be the, a string in there. So let's log this, logger.log, current task. And we're going to delete this log of first and see, ah, we don't need the tasks anymore.
12:22 We don't need to know the recipient. So the only log we should get is that task and we should get the string.
12:27 Then do this and that is this one. You can check this off. Now let's run this again and it should be the next one.
12:34 Then after that, do this. So we're getting the exact text that is the first true. Okay. So if it was like this, we're going to hopefully get, then do this, then do this perfect.
12:45 So it's the first thing. It's a top of our list. Even if we have some other things checked off, still reminding ourselves our micromanaging.
12:52 Hey, did you get this done? Well, it's let's go here. We have our current task and we can say micromanager or actually we can edit this text a little bit better instead of micromanager.
13:05 Hey, did you do this yet? And then we have the task. Subject, which actually is probably just going to be current task.
13:14 It'll be in our it'll be in the subject so we can see it right away. And in the body where you can do exactly, almost exactly the same thing, except a little different text.
13:24 Hey, are you working on this current task? What do you want to do? And one thing that's going to happen if we do not.
13:31 Change it yet is that it will thread if we want to use this text and it's the same task in another hour, it's the same exact text in that subject.
13:41 Google or Gmail will usually thread that text which might not be that great. We want to see like in our inbox a couple of them, hey, we missed this.
13:50 Oh, shit, we've been. So far behind or something, right? So let's add in the subject, a timestamp. The timestamp is going to be variable timestamp equals new date.
14:06 But we want to format this because this is going to be very long and it's going to be very specific to our.
14:11 Time zone. Let's just go do a little bit of a sort of milliseconds or something here. We'll format this one.
14:17 And so here we've just added this utility dot format new date. I'm not exactly sure about this format because I do want to include the hours and minutes here.
14:25 But let's double check that this is working correctly. We're going to get hopefully in our inbox and email now because we.
14:31 We have tasks to do and we're going to get this neck then do this task. So one second. So let's send this.
14:38 Let's run it before we end up creating that trigger. Let's just see if this actually runs and is in here.
14:43 Perfect. Hey, did you do this? Then do this. And it's sort of a little obscure because of this date and time.
14:50 So let's fix that up. A little bit. We want to add at least a space here. So we're going to add another plus sign and we're going to add at least a space.
15:01 We might do more. Let's maybe do two spaces and a pipe or a space and a pipe. Let's add one more space in front of after, Hey, did you do this yet?
15:13 And let's change this. Hey, did you do you do this yet? I mean, make it a little more managerial art.
15:19 You do this yet. Make it a little less nice. Right. Let's run this again. And again, we should have a different text here.
15:32 There we go. Hey, you do this yet or maybe even more rooter done yet then do this. And we have that time is really still looks a little much, right?
15:47 But it'll still be here. Oh, and we can also add a new line. Add a new line in here. Then we can do shift n is it?
16:02 Nope. Oh, I think, you know, it's actually in there. That should work. Let's see. Yep. Now we have it on a new line.
16:18 Hey, are you working on this? Great. Now, last thing we need to do is our trigger. That's it. So we have we're only getting one item at a time.
16:30 We're only getting the first unchecked item. Let's actually double check that. Before we move on much. But if we run it again, before lunge brunch, let's delete that before lunge brunch.
16:42 There we go. That is the next item on our list. We get the last, oh, we do need a link to the sheet.
16:49 Let's make sure we have that in our text. Let's do a new line. We want actually two new lines and then the link to the sheet.
17:02 We can get the URL, spreadsheet app dot get active spreadsheet, get, I think it's just URL. Let's just see if that's.
17:15 It let's delete this. And there we go. We have a link to our spreadsheet, our micro manager right there. And it has shows there.
17:24 Awesome. That was easy, right? But let's do the trigger. I think it's going to be really easy to do the trigger because now let's double check.
17:33 We got all of our tasks in order. Now we need to do our hourly trigger. Okay. Go over to triggers on the left side, hit triggers, wait for it to load underneath my face over here, click add trigger.
17:48 We're going to choose the function where we only have one function right now. Choose which deployment don't have to choose any.
17:55 We are not going to select the event source as from spreadsheet. We are going to change it to time driven and it is already selected as an hour timer.
18:03 So let's keep it at that and select our interval. It is every hour. We want our micro manager to be on our butts.
18:11 And hit save. And that will start the trigger. Now every hour until we are done with our tasks, we are going to get our micro manager emailing us and it will be at the top of our inbox this next task now.
18:26 Maybe you might want to create this trigger and uncreate the trigger or delete the trigger if in some other way you can do that programmatically.
18:34 I'm not going to do that. Go for that in this video. If you want to learn more about triggers, take spreadsheets, automation 101.
18:40 And if you want to see this kind of stuff done much more, take spreadsheets, automation 101. I go from the ground zero, little as tiny details.
18:49 And in three hours, you get to send emails and then actually within three hours, you get to send emails and then you get.
18:55 To access an API and you, so you can get information from outside spreadsheets into Google sheets. And that goes far beyond this, but this, I thought was a fun thing.
19:04 Get a mic, me create a micromanager spreadsheet task list that'll just keep on our butts and in our inboxes. It'll keep our, this item, this thing we want to do right at the top of our inbox every.
19:15 Hour on the hour and still remember which task we're working on or what we're working on next. And we can also change this.
19:22 If we actually come in here, click the check off box. It will automatically then go to the next one and start emailing us every hour this.
19:29 So hopefully this is fun for you. And you got to see this app script. If you are a better, she's remembering what.
19:36 This is on better sheets. Go then you have access to this spreadsheet and you have access to this app script.
19:40 Just copy it to your Google drive and you can have it. It's all yours. You'll be able to use it.
19:46 Your email will be used here and your spreadsheet, your, wherever you add this spreadsheet, the app script to your spreadsheet.
19:54 It'll get that URL and. Feel free to ask any questions. Bye.