Members-only tutorial

Watch the video and get the practice sheet with membership.

I Created an Automatic Emailer

Advanced Intermediate

About this Tutorial

While creating SheetOps, a new product, I came up with this idea of an automatic emailer that I can just enter an email and select a series of emails to send them. Needs an unsubscribe link. And so I made it in less than 45 minutes. Here it is. You can start using this right now for free if you're a member.

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Video Transcript

<div>00:00 So, I was building CheatOps and I realized that there is a little bit of a product sort of already inside and I wanted to explore this. 00:10 So, this is the video where I'm exploring a new product or a new template or a new sheet that does something. 00:16 Let's see how far I get. And the idea is, you enter an email address. So, enter an email address. You select , uh, email series. 00:29 And it autosends emails until it stops when they unsubscribe. or the email series ends so That's it. 00:49 But, that, in that it, that's it, uh, there's some interesting ideas here.

So let's call this Roadmap. And, we need to set up a list. 01:00 Uhm, let's call this Emails. Uhm, and then we're gonna have a new sheet called Series. Like, email series, email list, but, maybe we'll run it. 01:12 Rename these, but for now, Emails. This is going to be, literally, email address. And then, I think we're gonna need to keep some kind of database here. 01:26 So, we're gonna select the series, and maybe we have a number .

So, I'm wondering if we should start this out compost complicated and figure it out, or start it out very simple and complicate it later. 01:42 So, the idea could be, there's only one series. So, we have, uhm, subject, and we have body, and maybe we have number as well, or some identifier here, maybe name or something. 01:57 Let's delete all the other columns, and, expand these a little bit, make the body a little bit bigger. We don't need to make this very beautiful, or, or, like, user interface or anything yet. 02:11 Uhm, but, I actually don't want a number , I want a name.

Yeah, again, like, do we create, I'm wondering if we have a series. 02:24 settings tab already, settings, and we have series name, let's call them, call this, uh, simple long and something else. 02:43 So, we're going to have this, this is like a, what do you want to call this? This is like, uh, settings, these are the names, and if we have some names here, we want to select these in series. 03:00 This should be a drop down menu, drop down from our range , we're going to select this settings here. Done.

So now if we add any more, one more, automatically we have this series. 03:21 So we can say, which series is it in? Let's add a new column as well, and we'll call it number , or this is like the order, right? 03:32 So we'll have a simple one. We will end. We'll actually delete all those, or sorry, delete all them. Simple. There. Now, in the simple series, we have number one, two, three. 03:48 We have subject, hi, hello, what's up? And the body is going to be hi there hello wanted to follow up on the last email. 04:03 What's going on in there?

So if you have a series of emails you want to send, perhaps we also need to know how far in between they go. 04:18 So yeah, this is where it gets sort of complicated, right? This roadmap is for a user. We want to enter an email, select the series, auto-send that series, but it's now we're going to be adding more features. 04:26 We want to write, um, create a series of emails, uh, set cadence or frequency. Now it's starting to get a little complicated, right? 04:42 So we add a, let's say, last cent It would be easier. So here we have series.

We will select actually the same exact dropdown here we will have here. 05:04 So we'll select this, enter an email address, select a series, and it should automatically go into happening, right? Whatever the first one is, it will say, send it in some unspecified date later. 05:17 Perhaps we have a start date , which will be if we enter an email address, series, and not last sent, but start. 05:29 If we add email and series, we want, uhm, the start date to be, tomorrow or today?

If we send today, maybe we didn't, uhm, we'll have, like, a trigger , right? 05:48 A series of time triggers that say every day, start the ones that were started, go to the ones that have been sent, send the next one. 05:58 Can we can sort of say, uh, frequency? And here, let's select , create a drop-down menu. 06:17 We're gonna I say either daily or weekly. That's for now. We might not. End up with this. We might do this a different way, but I just want to have the option. 06:31 Let's select daily for now. How do we want to do this?

Let's go write some app script and start setting this up. 06:40 So the user enters an email address. Last sent. And if we, oh, this is the email Interesting part, right? So we're writing these series, but if we add a new one, let's say we've already sent a certain amount, we should add that next one, send it as well. 07:05 The next time these emails can be sent, that would be interesting.

Yeah, this is a very little sort of, uhm, use case, right? 07:19 So, or like, not little, but very specific, because ConvertKit, I use ConvertKit, and I signed up for ConvertKit because their workflows were supposed to be easier, sort of. 07:30 I want to send a series of emails when somebody buys something, uhm, or does something.

Maybe they get a freebie, maybe they sign up for the list, anything. 07:39 Like, I want to send a certain number of emails over a certain number of time, over a certain frequency that I set, uhm, and in the case of, like, a new client, I have a very specific series of emails I would love to send. For each of my products, I will have a very specific series of emails I want to 08:00 send. Some of those products are going to be the same series. So, I want to just send those emails.

If somebody unsubscribes from them, I don't want them to unsubscribe from everything. 08:12 I want, I don't want them to have to be like, oh, I want to not get these updates from this one, but I want to know everything else, right? 08:18 That's a little complicated for me to set up in ConvertKit because when you say unsubscribe, you take them off the whole list, right? 08:25 In this particular case, I want them to unsubscribe from the email series they're getting right then and there.

So, we will set that up later. 08:34 So, actually, we need to have a place, let's duplicate this, and say unsubscribed. And we want to save all of the information that we had. 08:49 All right. But just move them to unsubscribed. But we're checking emails every day. So, let's put in a dummy email. 08:56 Andrew plus emails at bettasheets.co. Let's send the simple series. Let's delete all There's other rows, columns. 09:12 And now. This is set up, so that we're, we're gonna send that simple email.

We want to create an unopen. 09:23 I have bettasheets.co slash snippets for an unopen menu. That is a custom menu we have. Let's call this emails. We want set up. 09:38 And this is gonna create a trigger for us. Set up. Daily. This is how I want to do it here. 09:49 Not the set up, uhm, before. Sorry, not the settings. Sorry, I'm talking through this as we're trying to figure this out, right? 09:59 So we have this, this is a user can just set up daily. Thank you. But what are they going to set up? 10:03 They're going to do a function set up daily.

We're going to create a trigger . Here, let's create a trigger . Function . Send. Daily. What are we going to do? 10:24 We need to check. Check emails list. For each one, check which series they have selected. Say which is the last sent. 10:37 Here we have a longer one. Let's call this for more. Do you want to get more? Four, five, even, even, longer. This is so long. 10:55 And this is all in the long series. So, but we're sending the simple series. We want to check which series, which is the last sent. 11:07 Add one, find it here.

If an error or we can't find it, don't send anything, right? So, let's write this out in what's called, like, uhm, not fake code, but, like, we're just writing this out. 11:20 Get emails on tab emails. What is that? Yeah, tab emails. Uhm, get emails and series. 11:37 So, selected on tab emails. Column A and B. Uh, series. Email addresses . 11:51 For each email address and series, get the series, oh, sorry, get the, and last sent, and see.

Last sent. 12:03 For each email address and series, get the last sent, add one, and then search for that number on. 12:24 Tab series, column B, where the column A is equal to the series name. 12:47 If no number for that series found, do not send any email. 13:05 If number is found, then send that email. Subject That's series, tab series, column C is subject And body is tab series, column D And then, uh, if number found, then write new number in tab, email, column, which column, emails, column C There.

So, what's interesting is, we can also just use this code 14:08 , uhm, this sort of fake code to get Chachipitito to write this, and get us something we can edit later. So, let's create a trigger , time-based, uh, daily, uhm, called sendDaily. 14:26 So, let's do this. Let's take this, put it in Chachipiti, and see what it does. WriteAppScript. This is nice. 14:42 It's gonna delete any, uh, thing that, so we don't add more and more and more. Let's do this.

It's gonna delete the existing trigger called SunDaily and make a new one if you select it. 14:57 This hour is going to be one we want to select , uhm, by the user. So let's get that. Spreadsheet , uh, uh, variable at hour equals spreadsheetApp .getActiveSpreadsheet, getSheetByName. 15:12 Settings, getRange , we'll enter the range later, getValue , but this atHour is going to be right there. 15:26 Whoop, atHour. Now let's go make one in settings, frequency, hour. we need a drop-down menu of just numbers.

This is one. 15:39 This is going to be 2, 3, 4, 5, 6, 7, 8, 9, 11, 12, 13, this is so boring, right? 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, uh, I think only 20, I think it's actually 0 to 23, but let's put in 23. Done. 16:08 Let's make F3 not this drop pill, but an arrow. Let's select it, what time of day do we, let's say 10, that's between 10 and 11 a.m. 16:21 So it's F3 for now f3 there.

So we've edited this so that we create a time trigger to run at the time we want, to run daily at the, uh, hour we want. 16:39 And if we ever want to update this, this hour, like, we can just set up again. So we'd set it up with 10 and just changing this number won't change a trigger . 16:51 We have to update the trigger as well, or set up the trigger again. We can even use the exact same function . 17:00 For users, they might not know to set it up again. So let's just say, uh, this is called update daily. 17:12 And let's give them another item here. Uhm, update daily send.

Set up daily send. 17:26 And this is update daily. Just in case we have to use that code in some other way. Update is going to be the same as set up. 17:35 We can even use the same name actually. But that's neither here nor there. Uhm, that might actually make it much less complicated. 17:43 Let's do that, because it's the exact same code for now. To update. But this send daily, we need to write this. 17:50 So let's use Apps Script to do that. Let's use Chetchi Petit, again, to do this. 18:01 write Apps Script , please.

So we have this choice between these two responses. 18:14 I'm going to select this one response only because it's using Gmail app. This mail app is totally useful and fine. 18:23 I prefer it for internal emails, like to myself, because, When I just found this out, like, within the past year, that sending from mail app will have, will almost always send, send it as spam, like, it'll end up in the spam folder. 18:38 Sending from Gmail will not. So let's select this one. Let's copy this and prefer that response.

Let's go back and, And let's not delete these comments yet, just double-check that it is doing everything it needs to. 19:03 We'll take that function out, and we have an extra there. Okay, let's read through this and make sure it's doing what we want. 19:10 So we have active spreadsheet , we have the email sheet, we have a series sheet, let's double-check those names, nope, it's all caps, series, that's good to change. 19:21 Email data is going to be everything on email list. Hmm, do we want every single row?

Uhm, I think we want just the data range , let's just delete these for now. 19:39 Series data is everything, oh, it's data range , so it's actually not going to, it doesn't matter, it's not going to get those, don't think, uhm, skip the header , that's good, So, email data , series name, last sent. 20:02 This is, a for loop, and this is iterating through the first item, second item, and third item, uhm, columns, email, series name, and last sent, that is correct. 20:15 We may want to add that start date later, but, let's see, Uh, if, and now this will, not continue, maybe I think this should be returned, but, let's see if it works. 20:27 If neither of those are available, last number , it's asking for a number , uhm, and the next number is last number plus one, series data, finding series name, if no match, perfect, don't continue, continue, subject and body is matched, two and three, which is, yeah, zero, one, two and three, perfect,  21:03 sort of, uhm, explain this is like, this two is not the second item, zero is the first, one is the second, two is the third, and then send the email. 21:13 So, In this body, we need to, be adding an unsubscribe link.

That's probably the most interesting part of this whole build, maybe, for you. 21:22 How do we give someone an unsubscribe link? So, let's save this for now. Let's run it and see if this works. 21:30 Send daily. Let's run. We're gonna, it's gonna ask us permissions first, the very first time we do it. Let's see if we get an error or get anything out of it. It's very exciting, right? 21:46 It completed. Let me check my email. I got the first one. Hi. Hi there. Let's see. Look at, look there. 21:58 We got hi. Hi there. Awesome. Let's see. That is hi. Hi there. Awesome. Simple emails.

Last one was sent. Perfect. 22:07 One. Let's see if it'll send the next one. Let's run it again. If this was the next day, do we get the next one? 22:15 Yep. Here it is in our inbox. Hello. Great. And it's being sent to the correct Andrew plus emails at better sheets. 22:25 Co. Hello. Want to follow up on the last email. And now it's sending a new email. That is one interesting thing about this, right? 22:31 That it's not following up. It's not in the thread. Um, I think that might be something we do later.

If we want to thread it in some weird way, we can probably save the thread in some way. 22:47 But for now, this is super simple. just sending a new email each time and it's working. It's sending the correct series, the correct one. 22:56 Awesome. Now, the next and probably most important step and final step, maybe in this video, because this video is going pretty long, is we're going to create an unsubscribe link. 23:08 So we want that someone who has this link clicks it.

And when they click it, we will know that move their email from here to unsubscribed. 23:22 So for now, let's write status. We just want to write an E2 delete or unsubscribe for this email address and this series as well. 23:37 So we need to get the name of the series, we need to know, so let's do const unsubscribe link equals URL plus series, What was the series name? 24:04 Plus, we also need email. Right? Email. But we're gonna need some more data . This is not gonna be what we send right away. 24:18 We are going to create function do post do get with e.

This This is very, interesting function where we're going to deploy a web app, and it's gonna allow us to accept URL, HTTP requests, essentially, URL sentence. 24:40 This do get, we're gonna get that information. Basically, someone else is clicking a button, and it's getting information from us. 24:48 That's what the do get means. We can also do a do post here, but the way that the URL click is, it's gonna do a do get. 24:55 So we want do get, even though we're sending information to us. This E is gonna be filled with information.

So we're gonna say, uhm, we wanna know, we have some parameters that we're going to create. 25:09 Basically, we're gonna create a URL that looks like HTTPS, Then, uh, a bunch of stuff here, and then at the end, it's gonna have a question mark, and we're gonna add a parameter , which is gonna be series equals, and then it's gonna to have some name here. 25:33 Name, ampersand, email, equals, email address, right, whatever their email address is. So we need to get those parameters from this. That's the URL we're gonna create, craft. 25:52 This will make sense soon enough.

Uhm, let's actually write, uhm, web app. 26:04 And the user will update this. But for now, it'll be blank. It'll be this, sort of, thing here. 26:20 And then we're gonna add this information to it. So we need parameters , so get parameters , uhm, email, uh, series, and email from URL. 26:34 We want to search in emails for the email address we get from the email parameter . We want to then, for now, let's add, in the E column, and next time. in E column, the word delete. 26:59 No, sorry, unsubscribe. Let's save this, because we also want the series.

If we're sending the same email address, different series, and B column, get parameter or search in emails for the email we get from the email. 27:16 And make sure the series column, column B is the series from the parameter , parameters from the, there. 27:32 So let's put this into chat GPT and ask it to write this. Let's write this, dude. Get in app script , e. parameter .email, e. parameter .series. 27:51 This is looking good so far. And here's our URL example. So let's copy this. 28:04 Function do get actually just the, this part inside part. Perfect. And paste it here.

We can format document, make it look nicer. 28:17 Save. This looks good for now, but let's deploy new deployment and let's test it out. We're going to create a web app, new description, new, we want a web app, uh, execute as me, it's anyone, anyone with this link, deploy, it's going to give us a URL, copy that, and we're going to have to show the user 28:44 at the end how to do this, or you if you want to use this kind of thing. We're going to have to show them how to use this probably in a video or, or post, uhm, written post. 28:53 post.

So here we got our URL, web app URL , that's, looks like that. Wow, okay, that's a bunch of stuff, and we now need this web app URL down here, and we're going to add And mark, email equals, and let's get that email, then, plus, ampersand, series, what was the, I think it was just series, in our  29:33 parameter , email, and series, yeah, perfect.

series equals plus series that's going to mean that the name of the series, series name, oh, almost messed that up, and unsubscribe link, we want to add this to the body, so we're going to go to body, plus equals, let's add, uh, in, in and we need this to  30:05 be um, HTML. Actually, for now, let's write unsubscribe, just to test this out, unsubscribe plus unsubscribe link. 30:22 There we go, this is just to test it out, see if this works, we're going to send And another daily, this will be the third. 30:29 Third one, let's run it and see. Assignment to constant variable 95.

Oh, I think this is supposed to be plus plus equals. 30:49 There we go. Let's run it again. I think it was plus equals, not equals plus. What? We should be adding. Oh, we can do something else. We can say variable. 31:05 Body, body, body. I think that's what the issue is, is that it is const variable body. Now that should work. 31:17 We're just adding that to it. Let's save. Let's run again. Execution completed.

We got our new email and now it has an unsubscribe link. 31:29 We want to, so in testing, because we sent this to ourselves, we need to open this in an incognito window. 31:39 Let's see if we get any errors. Email in series not found. So let's test this out. It says email in series not found, but let's log this stuff so that we know logger.log email param This is sort of series, sort of trying to fix this on the fly, right. 32:03 It's not, it didn't say missing. It said email in series not found. Mmm. It would have just returned this if it got here. 32:39 Let's see, data .

Let's log this data as well. Logger dot log data . Yeah, data . Data is all the information in the sheet. 32:53 Email. Emails. It's getting, is it getting the correct emails? Yes, A. Should be A through Okay, let's save all of that. 33:11 Let's deploy manage deployments. We don't need to update the URL. We just need to edit. New version. We're going to say, uhm, log deploy. 33:26 Should be the same URL. Open. In. Incognito. Email in series not found, but if we go here to executions, maybe we'll see. 33:41 See a log. Let's hope we get to see the law.

Oh, we don't get to see the log here. I forgot that it's do get. 33:54 All right. We need another way to log this. So let's do something else. Let's create for now. And instead of logger.log, we will just write to the log. 34:12 Spreadsheet .app.getActiveSpreadsheet. GetRange . Oh, sorry, no. 34:24 Append. Append row. Email. I'll pray. Comma, series, param. And let's also do this, but for data . Just see what happens. 34:45 Data . And let's try this again.

Oh, wait, no, we need to, Deploy again, click edit, new version, log sheet, deploy, done, and opening Cognito again. 35:13 Probably going to get the exact same thing, but let's look at here. we have Ah, I see, it is taking the plus sign, that is why it's not finding it. 35:25 Look, the plus sign is missing, so it's going to not find that plus sign.

So, we need to figure that out in this ha in this part We may have emails with a plus sign, how to get that included in the email parameter from the URL Yeah, it's, it's URL encoded Should appear like that, plus 40 lits Oh, do  36:14 we have to build it? Oh, we don't need to change our doGet code Aha!

That is awesome Down here, this email here, uhm, variable emailEncoded equals email, perfect, emailEncoded down to 2, we're going to delete this email, sending daily again, and now our, uh, email looks correct, open it in Kineto window 37:08 , this is very exciting, if this is gonna work, you have been unsubscribed, let's go to our sheet, it says unsubscribe, fantastic, so, we have two options now. 37:17 Oh, this is getting very exciting, right?

We can continue using this status unsubscribe, or we can move it, so let's do the move to unsubscribed, instead of just putting the status as unsubscribed, and we have a list of subscribed and unsubscribed, we wanna clean, keep this list clean of what are the 37:37 emails that are in our series that have not been unsubscribed, who has not unsubscribed, So, let me, let's move this. 37:43 Now, we need to do, to get, we're adding unsubscribe here, instead of unsubscribe, We do want to move it, but we want to say move row to sheet. 38:06 Unsubscribed.

Edit this to get, please. 38:20 And, we're just looking for this, marking as unsubscribe, we don't want to do that. Here we have move row, if unsubscribe data , unsubscribe row. 38:40 We need to delete that row. Got an idea, but let's just do this for now. Unsubscribed sheet is unsubscribed. 38:58 If unsubscribed sheet exists, append row, data , all of that row. I think that's going to be correct. Yeah, but we need to delete it. 39:11 So, i plus one, we need to do sheet dot delete row i plus one.

And we don't want to set unsubscribed. 39:33 But first, let's do, let's do the move first, or the appending. Let's format , document, save. Now, we need to, again, deploy, manage deployments. 39:49 New version, move, unsubscribed, deploy. Now we have our Our email is still here.

We don't need status anymore, we'll delete that column. 40:06 We will try this again, this link, open incognito, does it, is it going to give us an error , or you've been unsubscribed, emails, it's gone, unsubscribed, it's there, fantastic, now we have a complete unsubscribed system. 40:30 That is pretty cool, alright, so, this is working, completely, like, we have set this up in less than an hour, we have set up a way to email anyone we enter their email address in, we have selected a series, we have a way to write a series of emails, we have, we can actually delete this log now, and, 40:55 delete this log here, uh, but this is really exciting that now with just a bit of code, right, uh, just around a hundred lines of code, we have a full operating system of sending automatic emails, and this is really cool, right? 41:18 We have a way, of course, the biggest part of this and, and probably the most important part of this is this web app URL needs to be updated by the user. 41:27 Whoever copies the sheet, starts using this sheet, needs to come here to web app, uhm, to Apps Script , sorry, click deploy, new deployment, uh, and then whenever they get that, uh, new, uh, web app URL , they put that they save it here. 41:43 And if you're watching this, and you want this sheet, and you're watching this on BetterSheets.co, down below you can copy the sheet for free. 41:50 If you're not watching this on BetterSheets.co, then, uhm, become a member today.

It's like nine bucks a month now, go grab it, or lifetime member, you can become this, uh, become a lifetime member to get this. 42:03 Uh, I'm gonna be probably making this much more, more user-friendly, probably making this a little bit more robust in some way, weird ways, like, uhm, but you can use this right away, this is a pretty cool little, uhm, uh, product, right? 42:20 So I'll be making this a bit better, and, and probably putting this up as a paid template , but of course, if you're watching this, even if you're an, a monthly member, you can get this particular one for free. 42:31 If you're a lifetime member, you'll get this for free, plus every single sheet that I make, every single template that I sell, lifetime members get them for free, uhm, I'm, I'm really, it's, this is super funny that I came up with this, uhm, now, because I've been working on and, and making a product 42:47 called SheetOps, that has over a thousand lines of code, and does a bunch of cool stuff with emails that essentially say, hey, I have this task, you have been assigned this task, uh, and how is it going, right? 43:01 Uh, being accountable.

It's a really cool operational dashboard and command center that's interconnected with Google Tasks and Google, uh, Gmail and Google Calendar, and this one, this idea came from, hey, I just wanna be able to send customers an email, uh, a few e-mails, and if they want to ask unsubscribed 43:19 from that particular series, they can.

Uh, but I wanna, like, if they buy something else and, or they're a new customer, after that, I wanna add them. 43:27 I don't want a, uh, I don't want to pay, be paying, you know, a hundred bucks or two hundred or three hundred bucks a month for a convert kit. 43:40 I wanna just send a few e-mails, right? Uhm, and, and, yeah, this has been pretty cool. I'm happy this came together in, now, less than 45 minutes. 43:48 Hope you enjoyed as well. Please ask any questions whatsoever. Uhm, especially if you're a member. Ask me anything.

I'm more than happy to answer questions from members all the time. 43:59 If you're watching this somewhere else, you cannot get this sheet Uhm, boohoo, go become a member today.</div>