Send Daily Email Updates

Advanced

About this Tutorial

extending Google Sheets in a really fun way. We're gonna be sending emails. I have had literally two people in the past week and a half ask me for this and I answered their question very specifically, but I wanna generally reach out to everyone here, give everyone the opportunity to learn this.

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Video Transcript

<div> Hey, sheet checkers. It's Andrew, the big sheeter here. Really fun video we're gonna do right now because it is extending Google Sheets in a really fun way. We're gonna be sending emails. I have had literally two people in the past week and a half ask me for this and I answered their question very specifically, but I wanna generally reach out to everyone here, give everyone the opportunity to learn this. It's probably one of the more fun things I've ever done in Google Sheets and really expands Google Sheets and. Really interesting ways.

So we're gonna send an email from Google Sheets. We're gonna actually send an email every day. It's gonna get a little tricky. So what I've done is this might be a longer video than normal because I'm gonna walk through exactly what you would walk through when you're trying to apply this. You are more than welcome to grab this. Grab any code that I write here, you're gonna definitely have to change it or you're gonna be sending me emails. Don't do that. So you will wanna change it. Send yourself an email and test it out from your own Gmail. There are email limits.

I won't get into them here. I want to get you going. Started on this. Right away. So here's what you might have a use case for this. If you have other people set up, maybe some data that they enter every day, and then you have to go and check that Google sheet, but you just have to check like one cell or one couple of columns. And you would much rather have that emailed to you. Here's how you do it. So I've set this up. Literally, I have just created a sheet new a new sheet here with sheet new. I have not done any other steps, so this is exactly what you're gonna go through.

In order to send an email, we need to write a script. How we find the script is we go to tools script editor . And what we're gonna be doing is we're gonna be sending that that sales number and also the revenue number to ourselves. So we would get a usually untitled project. It says Code Es, it says my function here. We won't need this right now. We will, it will ask you later to name it if we wanna save it. So let's just name it right now cause. I'll send an email daily. Let's call it, it'll take a moment to save. It always does. Don't worry.

Anytime you have write something new in the code, it'll give you this little red dot that you need to save. Don't worry about that. That doesn't mean you made an error . Just means you need to say it. Okay? So I've already written the code. You can obviously get it once it's in the email, but let's see if it works I'll explain it once I copy and paste it in here. Okay, so this function , I have literally just named it basic email. You can name, this name can be anything you want. I recommend naming it something that actually means something that you immediately know.

It's not like some code word or this is a fun function . It will help you later to actually name your functions by what they do or what they do. So I named this basic email cause we're sending a basic email. First thing is, let's go back to this document I have named this tab. With this sheet. Okay. The name of the sheet is sales. Why I say that is because the first thing we do is this spreadsheet app dot get active spreadsheet , dot get sheet by name. And we say sales. It is capitalize S because it is capitalize S here sales. And so we're going to that sheet and we're gonna.

Then we're gonna go variable sales, equal sheet dot, get range , C3 get value, which means that it'll go to the sales page, it'll go to c. Three and it'll grab whatever the value is here, which is 45. How you get that number there? It doesn't matter If this is a function , it'll grab 45. If it's a function or an import range or anything where it's, maybe it's summing five or six different or 12 different sheets, it doesn't matter. It's gonna get the value of this. So right now it's 45. You could combine a bunch of sheets and then get like one number. Bam. You got it.

Then we're gonna grab also the revenue. So that's just c4. Same thing. We're gonna however you get this here, if it's some average, whatever it is, how you get it there, it doesn't matter. It's gonna grab the value, which is gonna be 5 6 8 3 0 3 0 8. We will see Based on formatting what it sends in a hot second. We're gonna do this real quick. And then, so once we have, we know where to go with the sales page, we know what values to get. C3, c4. Now literally all we do is do mail app dot, send email, and then parenthesis. And it's gonna get three things.

We need three things in order to send an email from Google Sheets. You need one. Who are you gonna send it to? Cuz it's actually the account you have, it's who you're sending it. Two, you need the subject. What is the subject? The second thing here, this comma says daily email. Third, you're gonna write, what are you sending? So what is in this third thing is probably the trickiest. Right now you could literally send just any text message you want as an email, but in this case, what I've done is put these variables, the sales and the revenue. This is literally called sales.

This is literally called revenue, and I've put them here. So I says sales, colon, space, sales, whatever is in that. Val that value. Then revenue, whatever is in the range of the, whatever the value is in the range of c4 it's gonna put here. Maybe let's see what happens. Okay, so we're gonna first do command S to save and see that red.is gone. And now we're gonna click this play button, which is literally if you hover over, it's called Run. So we'll run. This is going to happen every time. The first. So it won't happen. Sorry. It won't happen every time.

It'll only happen the first time. It needs authorization to review permissions. And what does it do? First, we need to make sure we're, we are choosing the account you want to use. So this is the account I want to use. Let's move my face. This may happen, so it's still, it says this app isn't verified. We know we wrote the script. We go advanced and then go to un go to Untitled project. Click that and now it'll say Untitled project once to access your Google account. That's it's you are accessing your Google account.

Allow, you might get security messages on in your email or whatever that might happen. Now let's go and see here I got an email and there I got daily email. It says Sales 45, revenue 5 6 0 3 0 8. So it didn't format the value. It just says 5 6 3. Cool. We got an email sent. That's it. Okay. One more thing. Actually, no, two more things. Sorry. One, we might want to only send an email maybe in case of like maybe we wanna send two different emails.

One, if it's like too low and one if, like you've hit your mark right. So we have some kind of if statement, and then second, the third or the third thing we're gonna do in this video is I'm gonna show you how to send this email every single day without fail, and you never have to remember it again until you get the email. Okay. So I wrote the other one here. I have an if, and let's see if this is gonna work. And again, you can totally go into this sheet, go into this document and copy it your.

We're gonna go command s save, and now I have a function that says if email, that, that's just me naming it. Now, I said if the only thing it added is this, if function , if the sales are less than 20, it's gonna send this top one. And it says here, it just changes the subject to low sales. Then it, whatever else happens if it's not under. It'll send this other email which says, daily email over 30 sales. Actually should be over 20 sales. 20 plus sales. Let's say that. Okay, so we'll save. Let's run.

Oh, it's gonna run the top one and the second one. Let's see what we get in our email and everything. Okay, so it did send just the top one. So let's do this. I am going to show you how to we are gonna copy that and we're gonna make a copy here. We're only gonna have the IF email. We're gonna rename this to if email. We're gonna go here, we're gonna delete the if email. And we're gonna rename this to basic email. This allows you to run two separate codes, same document, two separate codes. Now I can run, so it says typewriter get range , null get range , null line five.

Ah, I just found my problem. It's a get range null because it's looking for the sheet by name summary here. We need to rename that. So I do command S to save. Let's try it again. See what problems we get run into now. Oh, so it should have run. And so if it's correct let's see what it should do. So if it's sales 45, it's above 20, so it should have sent daily email, 20 plus subs, and then sales and revenue. Let's see what it sent. Daily email. 20 plus sales. Sales 45 revenue 560,000. Great. Okay, so now let's test under 20. So if it is, let's say it is 15. Okay.

Now, when it runs, let's see what it does. Sent us an email there, low sales, and then it tells us Sales 15, revenue five 60. So now we have an email, a function that can send us an email without fail, right? No matter what the number is in that val, in that one box, it will send us an email. So how, now how do we run this without having to come here, come to this script and run it? This is how we do it. We create a trigger and we trigger it every. So we're gonna go to edit. This is very hard to find. It is almost always impossible to find edit current projects triggers. Okay?

And you're gonna load this up. It's gonna look like this on the very bottom you're gonna click add trigger . And now choose which function to run. Obviously it'll be up to you to decide which one you want. You can add number , a number of different triggers. Maybe you want to add, a basic one to start off with and then you wanna switch it out, or you want to add, maybe both of them at different hours in the day. But let's do the if emails and that every day deployment is just head, the select event source we want to do at time. And we don't wanna do it every hour.

We wanna do it once a day and then you can choose your time of day. I, this is a completely, what will happen is it will take up to a day to run and then at some point within this hour it will run. Usually whenever I've done this before and set this up, usually happens within the first five minutes. What's really cool is that you can get a failure notification. So say you You wanna make sure you get an email.

Every, you wanna make sure you get an email every day, but you wanna be notified any weekly. It doesn't really like super matter if you get it unless you don't get it for a week. So you might be able to change this failure notification. I usually leave it at notify me daily. Click save, and then you take a moment and now this will send that email every single day. Or if it doesn't, it'll send me a. A failure notification and and that's how you send emails from Google Sheets. Feel free to ask me any questions. This is a really cool function of Google Sheets.

It makes life freely easy sometimes for things that you check Google Sheets for constantly, but you don't have to edit them. Once you set it up. Now you can live in your email. More you can, it ends up being email help, but it's a really cool function in the script that you can do. And it's a pretty simple script. More than welcome. If you have any difficulties with this maybe you have a di difficulty with who you send it to or subject or how to do these variables, please feel free to ask questions about it. How to do change this to different things.

This if function sometimes could be very it depends on what you wanna do. Sometimes it's a date, like one of the people, one of the people I helped on in the last couple weeks, they asked for a certain day, like one. And so what you have to do is essentially understand when the, if function is not within that like range that you wanna send of dates. Oh, I wanna send this within two days of X. You need to just delete this else function and you. If it's not within this range , then it does nothing. And so you don't get an email until you do.

But literally this will script will run once a day, every day, just do, just end with nothing. But you can test it out by sending yourself an email being like, this is an email to tell you nothing happened. So that's a really fun thing you can do to test it out. And yeah, you might run into some problems if you don't give it the permissions you need. And feel free again to ask me any questions. Bye.</div>