0:00 So in the last video you ta- well, you- I talked to you and you found out about automations. Uh, simple automations that you can do, uh, to really turn your templates into tools and sell these sheets as tools, not just templates.
0:13 Uh, in this video I'm gonna talk about emails and I'm gonna show you- I'm gonna how to write an email script and you're also gonna get this, uh, script if you go to the sheet, uh, here, um, which you'll have available in this course, um, you can download this Apps Script once it's done as well.
0:28 So we're gonna go to Extensions, Apps Script, and it'll look like this over here. We're just gonna add an email function, but what are we gonna do?
0:34 Why would we do this? I love doing this, say, uh, a weekly counter, if you're doing a CRM, you want some reporting every week, or you want some just notifications of certain things, maybe certain milestones or whatever.
0:46 Um. Insane settings. I'm gonna add one thing, which is going to be email, because we want to, the user to be able to set who the email is sent to.
0:56 In this case, I'm gonna just write Andrew at BetterSheets.co. Again, if you have any questions, you can always email me at BetterSheets.co, Andrew at BetterSheets.co, and also check out BetterSheets.co for any more tutorials and lessons.
1:06 Um, okay, so that's the email we want to send, but what do we want to send in our email? Well, let's just get a count of all the universities, and we'll send this every week.
1:14 Um, as we're gathering all these curated information, maybe we just want an email that says, hey, this is how many universities we have listed.
1:21 Uh, and we have some count. We'll call this equals count all, and we're going to take the value of here, C2 to C, um, count.
1:42 Ah, we have a lot of, that's what's going on there. So we're just going to delete all of this extra stuff down here.
1:48 You can always use it later. Okay, now our count. So if we add more to it, we'll get a count.
1:55 We just need to know there's three universities listed. But how do we get that information into an email? I'm going to show you how right now.
2:01 So function email me. I'm going to create this function here. So the actual function of email. Emailing is super easy.
2:09 It's just mail app dot send email. And we need three things. We need someone to send it to. We need a subject and we need a body or a message.
2:17 Uh, those are going to be all variables that we create. So variable two is going to be the hardest. We're going to do equals there.
2:24 Spreadsheet app dot get active spreadsheet. There it is. Get sheet by name. We're going to get the settings dot get range and we're going to grab settings.
2:39 Just double check it is B3 and in here just we're going to write B3. Then we need to get the value.
2:45 So let me move my face here a little bit. Get value. We need to get the value of that cell.
2:50 So whatever's in that cell, that's the two. Super simple. Variable subject equals. We're going to write some text here. We're going to say university count.
3:03 And then the message variable, message equals. Message equals. And here you could possibly do some html formatting. Right now we're just going to do some plain text.
3:14 Um we're going to get the count. So actually I want to get count equals. We're going to use exactly the same script up here.
3:24 But instead of settings, right, we have count. And I think we have a 1 is the value we want to get here.
3:34 There are, and a space, and a plus sign, count, plus sign, more quotes, universities. And we're going to end. We're also going to add plus URL.
3:49 Just in case we forget where did this, where did this ah come from. Variable URL is equal to spreadsheet app dot get active spreadsheet.
4:01 dot get URL. So we're just going to get the URL of the existing sheet that we're emailing from and we're going to say that.
4:09 If we want to uhm what this message is going to be, it's all going to be on one line but we can create new lines by doing slash n.
4:16 And we're just going to do two of these, slash n, uh because it'll be new line and the new line.
4:21 So there'll be a space between this and the URL. And let's call this sheet uh URL. And put a space there.
4:29 Cool. So save this email. Now we're going to create a trigger. We're going to, we can run it first, and we will run it first just to check.
4:39 Let's do that actually first. Select email me. Select run. We're going to have to authorize it the very first time that we try it.
4:47 Let's go. Go here. Allow. Make sure there's no errors. If there are errors we'll debug them right now. Let's just run it again just to make sure it runs.
5:00 And this is what the email looks like. It is in my inbox already. There are three universal. In the sheet and here's the URL to that sheet.
5:07 Uh, already I can open it. Bam, there are three. Let's add another one and see if that count changes. Uhm.
5:16 University alumni rewards. Let's check this one. So just as we did before, we are on the benefits page. We're gonna click plus uni.
5:33 One second. Let's do that again. Click Click. Plus uni. It brings us to a google sheet. We need NYU there.
5:41 Hit submit. And now we have here another one. Move that to our universities here. Now we're going to select the same thing.
5:53 Email me again. Run. Let's check if we have four. And we now have there are four universities in the sheet.
6:02 Perfect. So this is giving us an accurate count. How many universities are in the sheet? This is a really cool reminder.
6:07 Um. Thanks watching. Thanks watching. Thanks watching. But how do we do this? Like do we really want to set this up for someone to deploy it?
6:14 Like they have to go here. Run this. This is not that great, right? So what we could do is add this.
6:19 We can definitely add this to the OnOpen. So all we have to do is add dot add item. Join us in You to our.alarmtour.com Bye. We're gonna add whatever we want to say send, report, email.
6:32 That's gonna be forward facing and it's gonna be email me function. Let's make sure we have the right text. On Open, there we go.
6:40 You e-mail me and now it's gonna be uh we hit command S, make sure the orange button goes away. If we refresh this page uh the sheet and now in our custom menu, let's look at it.
6:54 Custom functions we have send, report, email and that. We'll work running the script great. But what if we want to set up a trigger?
7:00 Let's create that trigger here now. And we want to create a trigger in this custom functions. Let's do that. So we have this ability to create a script that once run will create a trigger.
7:13 Umm, so we're going to actually copy this from Google Apps Script. So let's go call this function create trigger. And inside we'll do this script trigger new trigger.
7:28 Bigger. We're going to use the email me as the name because that's the function here. We want this every week on Friday and we want everyone every week time based on week create.
7:42 So how do we do this create trigger? Trigger. Well we go to our unopen and we can add uh add separator then add item and in the item we want to say set up weekly email.
8:00 That's going to be the forward facing and what the function is going to be is create trigger. That's it. They only have to do that once.
8:07 So hit ok. Everything's saved. We're going to close this but we're going to refresh our sheet again. And again if you don't want to type this out you can get this sheet here.
8:15 Thank you. Umm copy the sheet and you can have these functions already written out for you. In our custom functions set up weekly email.
8:21 Let's click it. Let's see what happens. We need to authorize it the very first time. I forgot to do it the very first time we run it.
8:26 We have to authorize it. Alright let's allow. Let's go to extensions. Appreciate it. Script. Double check that it's not already done.
8:35 Right so the first time we did it was just to authorize it. So let's do that again. Custom functions set up weekly email.
8:41 Finish script. We can always add some toast messages or anything. Let's see. Did we add it? It is now a trigger based.
8:54 Uh so we can edit this if we want. Um but let's look at it. It is the functions running email me every week, every Friday midnight to 1 a.m.
9:03 Perfect. It is done. Right? We will now have that email every single week. And again we can go to our triggers and we can delete the trigger if we want.
9:09 Um. And you can also create an Apps Script to delete the trigger. But that I think is really cool to be able to give someone the ability to create these notifications with just these.
9:20 And I'll see next week. Uh functions in this custom function. So someone who wants to use your sheet and use these automations doesn't have to type out all this shi- all this uh Apps Script and doesn't have to create the triggers manually add trigger.
9:32 They can just uh you can just create it for them. So hopefully this was super helpful. And umm create some automations, use some automations, get you into the automation world and create turning your templates into tools.
9:45 Bye.