How To Email Reminders Before a Deadline in Google Sheets
Learn how to set up email reminders in Google Sheets to notify yourself before deadlines using Google Apps Script. This tutorial covers creating a function to send emails based on due dates and setting up triggers for daily reminders.
We have a bunch of tasks and some due dates, but we want to email ourselves not on the due date, but before the due date. So we need to have another piece of data here, which is how many days until that due date. We can do date Diff and Date diff is going to need a start date . Which is today and an end date . And then it needs some amount of units, which is D days. Now we'll autofill this and we'll see that if something is due today, it's zero. And there's also one day before the deadline, two days before the deadline. So we need to go through. Each of these, find out what is either one day away from being due or two days whenever you wanna send that email. Let's go and create an email in app Script . Let's create a function called email reminder.
And we're gonna need a few things. We're gonna need the sheet spreadsheet app. Do get active spreadsheet , get sheet by name. And in our case it's sheet one. Maybe in your case it's a different name. Sheet one. And then we're gonna need the data , which is gonna be sheet do get range . And it's gonna be a two C. And then we can also do, plus we need the number of the last row, so it's sheet do GI last row, and then outside of this GI range we're gonna do get value. So we're gonna get all of the values in the A column to the C column. And then we're gonna do data dot four each, and we're gonna create a row and index a function here,
which we're gonna get the task by doing equals row in, not curly brackets, but square brackets. Zero we're gonna get days until due equals row, and it'll be two, which is the third column, and if days until due is equal to one. We'll send an email if days until due is equal to two. We're gonna send a different email. In the first case, we're gonna do mail app 'cause we're gonna just gonna send to ourselves, send email and we're gonna get spreadsheet dot get spreadsheet . Active spreadsheet , get owner , get email. That is our email so we can actually separate this.
Let's go here and create a constant owner . Email equals, and we're gonna send that to ourselves. Owner email a subject one day till, and then we need the task. So we'll put that in the subject plus task, and then in the body, we'll say it's only one day until, and then add plus task. There we go. So in our email, we're sending someone, which is us, a subject and a body. Very simple email. We're gonna copy this email and say two days till. It's only two days until so. Let's run this and see what we can, we're gonna have to authorize, obviously, the very first time we run it. Select all . Hit continue. And we have an email review client contract one day.
Let's see if that is correct. Review client contract is one day until it's due. Perfect. So how do we run this each and every day? Go over to triggers on the left side. Then on the bottom right click add trigger , then choose which function to run. If you have multiple functions , you'll have to choose it here. Email reminder, and we're gonna change event source to time driven. And we're gonna change this to day and we're gonna change this time to, let's say we'll do it in the morning, 5:00 AM to 6:00 AM Hit save. And now we have a. Trigger it. We'll send every single day. If we wanna delete this, go to the three dots here and click delete trigger . Now, if you're looking for this app script , you want to copy it down below here on YouTube is this exact app script . Go get it absolutely free. Just copy it to your drive and copy this script and use it in your sheets.
are watching better sheets here on YouTube. Make sure you check out this video or this video and subscribe right now to get more tips, tricks, how tos, get more out of your Google sheets than you ever have before. I'm excited to be making a ton more videos here. Ask me questions down in the comments and I will answer them in future videos. But for right now, right here, one of these videos is gonna be your next Google sheet.