Weekly Email Sheet as Lead Magnet

<div>00:00 When we're selling spreadsheets, we may want to give away a sheet as a lead magnet, or even when we create a sheet for someone to buy, they might not want to consume the entire sheet, or all of the rows, all at once. 00:14 We may want to set it up that they get one a week, and that they could check the sheet on their own. 00:20 So, for this example, I created 52 data items.

So, you would send one of these every week, and this is a really fun idea, either to sell as a sheet, which is that you have 52 of something, some deep ideas, prompts, writing prompts, creative prompts, uh, but you could give this away as a lead magnet,  00:44 and how do you get in their inbox every week, which is by the email, and I'm going to show you in this video. 00:50 We're going to set up, all we have so far is a list of ideas, but we're going to set up the sheet to have a menu that they can set it up, that they can, uh, the user can consume this, but also I'll show you where you can add your own brand and your own interesting ideas. 01:08 So, we're going to go up to extensions, Apps Script first, and write a little bit of script.

We don't have to write this from memory, uh, we can go to bettorsheets.co slash snippets, and I'm going to first grab the on open menu. 01:21 So, this is a menu, custom menu , that will open for the user when we open the sheet. So, I'm going to delete a few items here, and I'm going to call this set up email menu because we're only going to have one item in here. 01:37 Actually, yeah, one item, which is set up weekly email. And we'll call this set up.

We'll delete this second item. 01:45 And now if I save, Command S, save, we'll call this weekly, and we close Apps Script and we refresh the sheet. 01:59 Let's see if it shows up right away. Right next to Help, there is a menu there. We can see it there. 02:07 Right next to Help is set up weekly menu. So, we can rename this and use this as we wish.

But now the user has a very cool thing. 02:17 They have access to our Apps Scripts and we can give them exactly the kind of access we want, which is just press a button, set So, run this function , set up. 02:27 And what set up is going to do is essentially create a weekly trigger , weekly email trigger . So, we need Script App dot, Script App dot, Script new trigger . 02:50 And we're going to call this email, we'll create that later. And it's going to be time based on weekday at hour, we'll say 8 to 9 a.m., and dot create. 03:11 so this all is creating this.

We need to know now which weekday, so we'll do script app dot weekday dot, uh, and we can select any one of these days. 03:25 Let's say it's Monday. So what's this going to do is create a trigger that will trigger the function email. We haven't written that yet. 03:36 So let's go. Create that function email, and now we have a function email that we can run every week. What is that going to be? 03:47 Well, we want an event because when the actual time-based trigger happens, we get some very interesting data here.

Variable week equals event week. 04:01 And in square brackets, in quotes, week hyphen of hyphen year. 04:19 This function event will give us. The number of the week. That's a number from 1 to 52. What's really cool is we have 52 rows, starting at number 2. So, we need to know variable row equals week plus 1. And we're going to mail app dot send email. 04:45 And we're going to send it to someone. So, we're going to send it to the owner of the sheet. We're going to send a subject, date, idea, this week, comma. 04:55 And now, we need to actually put in the information.

So, we will create a body here, a body variable. Let's create the owner variable first. 05:04 Owner equals spreadsheet app dot get active spreadsheet . Get owner , get email. So, that's whoever you are. Whoever owns this sheet, that's their email. 05:16 That's really awesome to be able to say, hey, just set this up and it'll be sent from your email. Done and done. 05:21 We don't need to actually have them input it. We don't need to add them anywhere, text, write in anything. We don't have to check email formatting . 05:32 Just get the email of the owner of the spreadsheet .

Now, the body, variable body, equals. Here, we want to write a little email. 05:41 Hey, or hello, or good day. Let's say, good day. And we're gonna do slash n slash n, which is new lines. 05:54 this week's idea is colon And then I'm gonna add a plus, and I'm gonna say, date idea as a variable. 06:07 That date idea as a variable, date idea, equals, spreadsheet app, dot get active, spreadsheet , get sheet by name And we're gonna go back over here for a second, and it's called ideas in all caps. 06:24 Ideas?

Dot, get range , and now we put in the row, which is whatever we get is, plus one. And then we say, well, we're in the first column, here. 06:37 And we're gonna do got, uhm, dot, get values . Sorry, value, because only one value of one cell. And that's our date idea. 06:49 And that's it. It's pretty much it, except we want to add here some branding. So we're gonna add a plus sign. 06:56 Another quote. I'm gonna put this on a new line. I'm gonna add slash n slash n, so there's two new lines. 07:04 This date idea is brought, brought to you by, BetterSheets.com. There you go.

So, you can put anything you want here. 07:17 Any text, any information, links, uh, we could make this HTML. I like to do plain text . Just to be quite honest, we can do HTTPS, so that it comes in as a link. 07:28 Let's see if this, this might not run because we don't have this event yet, maybe. But let's just see if it'll run. 07:37 Do you have to authorize it? Ah, the E here. Line 20. Ah, I know why. 08:05 This is just event week of the year. That's why. So let's try running that again.

Ah, this is undefined because it's not a time-based trigger . 08:31 So what we could do is just test this out. Let's go create trigger . Add trigger , email, and we're gonna do time-driven, which we will do later. 08:44 Let's do minutes, every minute, save. And that'll be done within a minute. Here's the example email. It says, good day. 08:54 This week's idea is take turns planning a surprise hour of the evening. Let's go see which one that is. That is 22. It is May 20th, and May 20th right now is week 21.

So that's perfect that it is row 22 because it's one more hour. 09:13 Then this header . Awesome. So it's working. So when we create, we can go here and delete this trigger . So when we create a time driven thing that's every week, it will work. 09:28 So we just tested that out and all the user has to do is go to this function setup and run it and it'll send it 8 a.m. 09:37 every day. Every Monday, that date idea. Pretty cool. And you can add your URL here. You can see here it is a URL, HTTPS, BetterSheets.co.

This date idea brought to you by, so we edited a little bit, brought to you, there you go. 09:56 So if you're watching this on BetterSheets.co, down below is a link to this date exact spreadsheet . You can get the code and the ideas if you want, uh, and get it all there. 10:08 If you're not yet a member of BetterSheets, become one today.</div>