Are you tired of checking that one Google Sheet every single day? You've got a stat you wish you had in your inbox, but it's stuck inside of a Google Sheet. Someone else puts updates into a sheet, but you have to check that sheet every day. Let's get it into your inbox.
I'll show you how to email yourself one single cell data. That's one sheet, one cell, one email every single day.
Google Sheets: Email yourself a cell every day
Let’s go to sheet.new and create a brand new sheet. For this tutorial, here’s the data we’ll be using:
• Sheet1 is the sheet
• A1 is the cell
• 55 is the value for cell A1
First Step: Go to Extensions > Apps Script
Go to Extensions > Apps Script.
In there, we’re going create a function called emailMeEveryDay().
Literally write that out.
Use MailApp to send emails from Google Apps Script
And in that function, in the curly brackets, we want to do one thing: MailApp. This is a built-in function inside of Google Sheets and Google Script. (Incredible!)
Place a dot after the mail app function.
We have auto complete here, so let’s click on that. Click on sendEmail.
Three Important parts of the email, in order to send.
Now we need three parts to send the email:
This is who the email will be sent to, what the email subject line will say, and the what will be inside the email.
For our program and email to work, we need to put the variables for the recipient, subject, and body above the MailApp.
Who are we sending an email to?
For the recipient variable, you can put your own email address, which is what I’m going to do. Type in rec for that variable.
Var rec = “email@example.com”
What's the Email subject?
Now let’s enter the data for the subject variable. Our subject is going to be just Today's Data.
Note: One important thing about getting email in Gmail, is that anytime you get an email from the same user that has the same exact subject, it will thread it.
But I don't want these threaded, I want a new email in my inbox every single time. To avoid this email getting threaded, let’s add +. This is going to concatenate the subject. It’s going to push together these two things.
Add a Timestamp to stop Threading
And the other thing I'm going to type in is new Date(). This is a timestamp.
This makes sure that every single time this email is sent, it has in the subject line a completely different subject.
I’ll add a delineator here in the subject Today’s Data, instead of a colon. This will make the subject a lot easier to understand at a glance.
Let's write the body of the email
Okay! The last thing we need is the actual data. We’re going to type in the variable body because that’s the variable right here:
What we need here is the data. So what we're going to do is start on the entire spreadsheet file. How do we get that? It’s with SpreadsheetApp, with a Capital S.
Just like MailApp is a built-in function for emailing from Google Sheets, SpreadsheetApp is a built-in function to access spreadsheets, which is what we're doing in Google Sheets right now.
Now what we want to do is .getActiveSpreadsheet. We want to put parentheses after ".getActiveSpreadsheet".
Google Apps Script helps you with Autofill
And notice there that you can autofill it and auto complete it. It's trying to help you out.
Then we’re going to type in .getSheetByName() and what we want is Sheet1. So put that there. With quotes around the name of the sheet.
Keep going down the spreadsheet hierarchy from sheet to range.
Next step is to the getRange, because inside the sheet we have all these rows and cells. We need to indicate A1 here.
We're using A1 notation, for now.
Get the Value of the Cell inside the Sheet
Now we’re accessing the cell, but we need the actual data inside the cell. In Google Apps Script, this is called a “value.” So let’s type in .getValue();
And that is it! We’re going to save it now.
You can save by either typing Command +S or click the save disk icon up top to save our project.
Here’s what we’ve done so far:
• one line of code MailApp.sendEmail
• a recipient who we’re sending it to
• a subject of what the email is about
• a body for the content of the email
Remember: We just want the value of whatever's in that cell.
And again, important to save! Let’s hit Command + S or hit save project.
How do I run the script?
Select the script, and click "Run"
Now, once I run this, it’s going to ask me for permissions. That’s because I use Spreadsheet App and Mail App, which are built-in functions. What we need to do next is go up top and click on the dropdown with the label emailMeEveryDay. It’s the same function found on our first line of code.
We can run this from here and I will do that right now. Hit Run from up top.
It’s going to ask for authorization. Go ahead and click "Review permissions." These permissions are very basic. We just have to select our own Google account that we are using.
If you have multiple Google accounts already logged in, you have to select a correct one that you want to run it from.
You’ll see here the two things that we added: MailApp and SpreadsheetApp.
“See, edit, create, and delete all your Google Sheets spreadsheets.” – That's what we want to give permission.
“Send email as you” – That’s the MailApp.
So we want to allow these. This might take a moment…
Did we email it?
We need to hit run again and then I'm going to go check my inbox...
Here we go! We have a sent email.
It is today's data with the whole timestamp and the number 55, which is found in our Sheet1, cell A1.
How do we send this every single day from Google Sheets?
We don't want to come in here and hit run every single day. We could be checking the spreadsheet that way, but let’s do things better.
Add a Trigger to run our script every day
Let’s head over to Google Apps Script and click on the first icon (called "Overview") found on the left side. You'll want the 3rd one "Triggers".
Five options will be displayed.
Click on Triggers and on the Triggers page, you’ll see the blue Add Trigger button at the bottom right. We’re going to click that.
We now have a modal with a bunch of options. We're going to choose which function to run (which is emailMeEveryDay), which deployment should run, and our event source.
In Event Source, you must change it from spreadsheet to “Time-driven.” Once you do that, you have some options.
We’re going to choose “Day timer.” This allows us to pick which hour of the day we want to send.
Let’s say you want to send the data an hour before you check your email, like 6:00 to 7:00 AM.
Triggers won't send (the first time) at an exact time you set.
Now what happens is this: When we select this hour, we cannot send it at specific time each day just like cron job in programming. We can't do that. What we have to do is select an hour.
Now what's going to happen is Google servers will send it at one point – randomly within that hour – the first time it runs. But then every single time it runs after that, it will be 24 hours later.
So it will send between 6:00 AM and 7:00 AM the same time whenever it does choose.
Okay, got that? Now we’re going to hit save. And once we've done that, it will show up here as a trigger.
We can always delete this. And if you want to change the time, you can edit it. You can also send it at different intervals. If you want to send it twice a day, then you have to have two time driven ones.
You can send it at 6:00 AM and 6:00 PM right? Two different triggers doing the exact same script.
So that's pretty powerful way to email yourself a cell of data every single day.
Watch the video for this tutorial:
Learn how to automate more types of emails from Google Sheets:
Get more from Better Sheets
I hope you enjoyed this tutorial! If you want to do more with your Google Sheets, I have other tutorials, like how to create a timer with Apps Script and learning to code with Google Sheets. Beginner? Intermediate? There’s a lot of tutorials for everybody! Check them out at Bettersheets.co.
Join other members. Pay once and own it forever. You get instant access to everything: All the tutorials and templates. All the tools you’ll need. When you’re a member, you get lifetime access to 200+ videos, mini—courses, and Twitter templates. For starters. Find out more here.
Don’t make any sheets. Make Better Sheets.