Email When Over 10 Days Since Last Update
Learn how to automate email notifications for clients that haven't been contacted in over 10 days using Google Sheets and Apps Script. This tutorial walks you through creating a helper column and setting up a function to send alerts based on your CRM data.
We have a fairly straightforward CRM, client company names, deal value, statuses assigned to. We have multiple people working on these things at a time. We want to keep updated of what's the last time we've touched them, what's their touch point? Have we emailed them, followed up? Well, we have the last date in the column F, and then in column H is when they were added. So we know how long have they been in our pipeline. But really what's important for us right now is we wanna get an email that says, which of these clients have we not reached out to for 10 days? Meaning over nine days has been since we've done something, followed up, done something with them, right? Now, the closed ones, those might actually move to another tab, so we won't care about them. But we care about this 11 here. move to Now, we, of course, need to have a helper column, I think, and I did that already.
So we have in the G column, DATEDIF is F2, looking at F2, that, that date and saying, today, what is the date between that date and today? What is the difference? And in quotes, we put D to be the unit or days. So this gives us a real integer number without having to do some funky math in Apps Script. We have a number here of what is actually the amount of days since we've reached out to them. So in our Apps Script, we're gonna go Extensions, Apps Script. And here we're gonna write a function Email intent. This is just an internal name for it. function
We can name it whatever we want. We're gonna get our variable CRM sheet, which is spreadsheetApp.getActiveSpreadsheet.getSheetByName CRM And we're gonna get the ver- uh, the data, the actual information we're gonna look at, which is crm.getDataRange, getValues. This getValues means we're getting all of the values, everything we see here, all of it. And we are only really caring about this G column, but we wanna get all of the data here so that we can access our client name, company name, and any other information we wanna include in sort of an email each day. We'll start with a email body that is empty.
This is important because when we start to grab the data, and if there is no data to grab, we will look at this email body and say, "Hey, if there's nothing, don't do anything." So what… We're gonna go down here at the end and say, if emailBody is still equal to this, then just return, meaning skip the rest of this, just move on with our lives. So what do we actually do here? We go for variable I equals one. I is less than data.length And I++. What this is doing, it's a four loop. It's just iterating through each one. The I is sort of an iterator starting with one, then two, then three, until we go beyond the length of how much there is, how many things are in here.
First, we need a client. We're gonna grab this data now just 'cause it is easy to grab equals data. With this data, square brackets I Zero. That's the first item. And the reason is that because it's the A column, client name. Company Data in square brackets I, square brackets one. It's the second column can also get assigned at this moment in time And that is in the E column, so zero, one, two, three, four. So in square brackets four, and the real data, which is dates since equals data I, and it is gonna be the G column, which zero, one, two, three, four, five, six. It's gonna be number six.
And that's column G. And we can add a little note here, column G. If Date since is equal to, sorry, is greater than nine, meaning it's 10 or above. And here we can also do greater than or equal to 10. Doesn't matter, it's the same. Greater than nine Let's bring this up to the top a little bit. And so if these dates are greater than nine, we're gonna go email body plus equals. Plus equals is saying, "Take whatever we're gonna give you now and add it to this email body." So before this, it's blank. If there's nothing here, it'll remain blank. But if there's something, we'll do client plus, and in this case, our plus is just combining this string.
So the plus here, plus equals is saying, "Add it." The string is just saying, "Combine these two," or this plus is a string saying, "Combine these two." So client company And you can put a delimiter here, whatever you want. It could be pipes or hyphens or nothing at all I'll start putting this like this so we can see what we're doing This assigned we can say assigned to And then at the end, we will add a / n, which is actually new line. So as we add it to the body, we're adding a new line at the end And again, as we go through this, if it's blank, it will return nothing.
So let's add the actual email. So after this, we'll send an email. It'll be mail app.sendemail, and we're gonna send it to someone So it's sending to someone, have a subject, and we have a body. The body is going to be some text that we wanna have at the beginning Over nine days since last update. Colon slash n for a new line, slash n for a new line Plus email body. Our subject Over nine days. Get a little alert here, maybe even add a little emoji That should show up.
But who is this someone? Well, we can go to the spreadsheet, get active spreadsheet, which is the entire file, get the owner, and get their email. And that's me in this particular case. Yes, I'm sending it to myself, but sometimes you might be creating this for someone else who is the owner of the sheet. All right. We have everything we need. And when we run this, we will have to authorize it the very first time Yep, let's select all. Let's continue. See if we have any errors.
If we don't have any errors, we'll go check our email Here's our email. We haven't gotten it. Hmm, interesting. Let's go check what is going on. So we are in the G column. This is 11. I see an 11 right here, so it should have this in there Let's see what we did wrong So it looks like our if here is returning poorly. We need actually two equal signs. So let's save that and run it again.
We have a new email here. It says Bob Smith tech start assigned to Mike. If we go back, yes, it is Bob Smith tech s- mar- start, and it is assigned to Mike, and it is 11 days since we've updated. That's perfect. Let's change this to 12. So we'll include Evan and that one, and let's run it again and see if we get the same, or not the same, more in our email And yes, we did. We got more in our email. Fantastic. Oh, Mike, you've been behind. You, you haven't been working hard. Hopefully this was fun for you, but we need to do one more thing. We can't come in here and just run this every single time. We need to actually automate this. This is the whole point of the video, right? You wanna automate this email.
All right. We have done the hardest part, which is writing this function and making sure it works. Now we just have to automate it. Go over to the left side, Triggers. It'll be just a few clicks. Click Add Trigger on the bottom right. Choose which function, Email in 10. Event source is going to change to time-driven. Our timer is gonna change to a day timer. Our s- time of day is going to be whatever time of day we wanna do this, which I think this might be better if it's in the morning.
However, you may only wanna do emails at a certain time of day when you know people are looking at their email and they're gonna take action on it. Or, you know, you wanna do it at the end of the day, give them that last few hours to be able to process things, and then you wanna alert them, "Hey, at 5:00 to 6:00 PM, let's get that report." In my book, I really like more reports than not, so I would rather do it on the morning of and be like, "Okay, hey, it's, it's time to, to figure this out right now."
Give them the alert. It's not like a bad smirch on them. It's not a bad email. It's just saying, "Hey, these items haven't been… These people, these customers haven't been talked to within, you know, the last nine days." And if you wanna delete this or edit this and change it, this trigger, just go over to the pencil icon on the right. You can edit it if you want to change the time, or the three dots on the right, Delete Trigger, and delete forever.