Creating automatic email reminders in Google Sheets
We have a problem — since we DIY a lot of things, we have to RIY those things as well. (RIY stands for repair it yourself) But like all mortals, we forget and often ignore the problem until it’s too late, or most of the time, we forget the last time we did maintenance and hence we don’t know when we have to do it again.
Wait hold on, didn’t you do the QR thing to solve your problem?
Yes, it was supposed to be the solution, where we note down when we did maintenance on our tools and we would check it every so often and take action accordingly.
But, like all of our problems: we ignore them until it costs us money. Hence, the only time we ever interact with the QR sheet is when we need to buy gas and note down how expensive it has got. ( Which is around once in 4 months ).
So, it only solved half of the problem — the human aspect of laziness and making mistakes remain. So, what if that google sheet could alert us when we are past the deadline?
That’s cool, but how will you do it?
This is where a little bit of math, excel tricks, and a little bit of software knowledge come in handy. Mind you, this article gets a little technical, but nothing that should overwhelm you. If you can type, you can code — don’t worry about it.
The operational costs
First off, I wasn’t aware of this at all — but google lets you run up to 50 scripts a day on any of your google docs suites for free!
While there is a lot of information on this page, the only one relevant to us is the last one — Apps Script Project, but keep in mind — this is a one-time cost for you — not an operational cost. The actual operational cost is here
Now all the parameters in this table are relevant to us — but it doesn’t matter if you are sending out an email to 4 people ( i.e., your family members ). There is a lot there, but I'll skip the boring details — if you follow along, you will not reach any of the limits. But if you are curious, you can always contact me about it. Or if you are a pro-developer and think that this is too little for you — you can use Google Cloud AppEngine.
Now that we know that it’s affordable…,
Let’s get to building stuff!
The first thing that we need is the last day we maintained anything. This means that we have to assume that you would have a table similar to this:
The only relevant column is the Date(AD) and it has to be sorted in ascending order, i.e., the last date has to come at the bottom of the table. The rest of the fields can be anything else and is for your own usage.
To get a reminder, we would always need the last date of maintenance, and since we assumed the table is sorted — it would imply that the date we need would be at the bottom of the table.
But wait, if I just pick a cell (in the diagram above it is C12) — it will be outdated when I make a new entry, and I will have to change the formula every time.
And you are right — that’s pretty annoying. So here is an excel trick:
Create a cell with this formula:
=FILTER( C11:C , ROW(C11:C) =MAX( FILTER( ROW(C11:C) , NOT(ISBLANK(C11:C)))))A more generalized form of the above formula would be:
=FILTER( <start>:<end> , ROW(<start>:<end>) =MAX( FILTER( ROW(<start>:<end>) , NOT(ISBLANK(<start>:<end>)))))where <start> is the cell where you want to start the formula, and <end> is the cell where you want to end the formula. If you wish to calculate till the end of the page, simply mention the whole column instead of the cell value as done in the example above.
This formula will always get the last value of the selection range that is shown by the google sheets’ auto-highlights.
As a reference, this is what it should look like, assuming you have done everything correctly:
Once, the formula is applied, you should see the last value of the column, show up in the cell. Like this:
As a test, add another entry and see if it gets auto-updated:
That gives us the last date when we did maintenance on our batteries — but we need the number of days since the last day to warn us. So, let’s use the next formula:
= TODAY()-D1again, generalizing:
= TODAY() - <cell of the last date of maintenance>Your result should look something like this:
Again, let’s add a test value:
And everything has been updated! Awesome, test success! 😁
Now, we also need a deadline, i.e., that is when to send the mail that something needs your attention. This is simple, just create an additional cell, that contains the deadline. Something like this:
This is added here so that we can tune the value of the deadline from the sheet itself, instead of requiring you to go and change the code every time.
Pfft, that was easy, is this what you do at your job?
Maybe you should consider being a computer engineer. 🤭
But no, I do more complicated stuff than this. 😝 However, if you can keep up with the next sections, then you will love the world of software engineering.
Note that the above section will be used for daily operational needs — i.e., you will try not to tamper with the code as much as possible, and just make the entries in this table. The section below this is a one-time setup if you are satisfied with the default settings.
Let’s send an email automatically
This article was about being reminded automatically via email, so let’s write some code to do it. There is a section in google sheets called Extensions → Google Apps Script, click on it.
A new tab should open up, with a pretty fancy place to write code.
Brace yourself, time for some coding
I won’t bore you with all the syntax and boring stuff. So delete everything there and slap this on there.
function houseReminders() {
const fullSpreadSheet = SpreadsheetApp.getActiveSpreadsheet();
const spreadSheet = fullSpreadSheet.getSheetByName("Battery")
const daysSinceMaintainenace = getValueFromACell(spreadSheet, 2,4)
const threshold = getValueFromACell(spreadSheet, 2, 7)
let emailMessage = ""
if(daysSinceMaintainenace > threshold){
emailMessage += `
<br>
Warning! The your equipment has not been maintained for the last ${daysSinceMaintainenace} days.
It is ${daysSinceMaintainenace - threshold} days past the deadline.
Please do scheduled maintainenace and update it on the sheet.
<br>`
const subject = "Automated Reminder from google sheets"
MailApp.sendEmail({ to: "your.mail@gmail.com", "subject": subject, htmlBody : finalMessage})
}
}/**
* Gets value from a spreadsheet
* @param {SpreadsheetApp.Sheet} singleSpreadSheet the sheet to extract value from
* @param {number} row the x-axis value
* @param {number} col the y-axis value
* @return {number} the value present in the cell as an integer
*/
function getValueFromACell(singleSpreadSheet, row, col) {
return parseInt(singleSpreadSheet.getRange(row,col).getValue())
}
Here is a quick summary of what it does:
- Finds your sheet that is named “Battery”.
- Plucks out the value of the number of days that it has been since the last time it was maintained.
- Plucks out the value of the number of days that you considered as a deadline.
- Compares those two values:
- If it’s under the deadline or the threshold, it does nothing.
- If it’s over the deadline or threshold, it will send a mail with a body to the email mentioned.
Note that you may have to change the values in line 4 and 5, which has:
const daysSinceMaintainenace = getValueFromACell(spreadSheet, 2,4)
const threshold = getValueFromACell(spreadSheet, 2, 7)In a general form, it means :
getValueFromACell(batterySpreadSheet,<x-axis value>,<y-axis value>)So, depending on where the values lie on your spreadsheet, you will need to modify these values to match the (x,y) of your appropriate fields.
Change the message that's there by default in the emailMessage section, if you know HTML, it will work here. Finally, replace your.mail@gmail.com with your email id.
The rest of the things that you see is just software engineering mumbo-jumbo, that you don’t need to worry about.
Minor configurations
Since we are going to use Gmail, the code should be told to go and talk to my Gmail. To do this, observe on the left-most part of your screen.
You will see a section called Services, if Gmail is not already automatically added below it, click on the + the icon on its right. A popup will come up on the screen, search and find Gmail API and click Add button below.
If it asks for permission now, go ahead and click Accept and continue.
Alright, let’s see if the code works
Change the highlighted drop down to houseReminders
That dropdown is asking: which section of code should it run when you press run? The answer would be houseReminders as getValueFromACell is already being used inside the previous section to do something else and doesn’t contain the core logic.
Now, let’s press Run!
It may throw a bunch of more permission issues, but that’s fine. Just go ahead, press ok/accept, and continue.
If nothing happened, then check your spreadsheet again. Is everything under the deadline? If yes, then intentionally put a value that is over the deadline and try again. You should get a mail that is something like this.
Of course, a multitude of things can go wrong, like basic syntax issues, but you can contact me if you have any problems 😉
Wait, this is stupid, I have to press run here every time?
Of course, it would be stupid if you had to press run every time, but instead, we are going to set up a scheduler that will run this once every day automatically.
Go to the left section of the screen, you should see a clock icon. Click on it and the right-hand side section of your screen should change.
Press on the Add Trigger Icon that you will see on the bottom right. Another popup should show up on the screen. Change your settings to match mine:
*Don’t worry if you can’t see all the menu options yet. The options change as your start to select different values. Change the values starting from the top and the rest of the fields will show up eventually.
Of course, you can easily change the Time of day to when you want to run the script. Note in the above example, that the script will run once, every day between 8 am to 9 am. Although the code will only take around 2 seconds to execute, it will be executed at a seemingly random* time between 8 am and 9 am.
*The time might seem random, but google will run the code in a slot whenever it has resources available. Set this up and keep it running for a few days, when you go to the Execution section, you will see that it has been executed in the requested one-hour slot.
This will not show up on the first day, as the code runs once a day only — and it’s very likely we’ve missed the time slot. I suggest that you intentionally put the wrong values in the spreadsheet so that it crosses the deadline and see if you get mails for around a week. Then you can check the executions here, and once you are satisfied, keep the correct values in the spreadsheet.
But we aren’t done yet.
Finishing touches
Finally, we need to tell the software that we are done, and this is the version that they should run automatically. Press the Deploy button that you see on the top right, and select New Deployment
On the left of the new popup — you should see Select type and a gear icon beside it. Click on the gear icon and then select the Web app option.
After selecting that option, the right-hand side section should be filled up with additional text boxes, like so:
The only value you will need to fill up is the 1st value, i.e., the “Description”. This is about what the code does — it doesn’t impact the logic at all, it’s only for your reference in the future, so you can fill it up with any message you like. Leave the rest of the options at their default and click Deploy.
You will get a screen like the above picture, which shows that it was successful. Click on Done. The values here are useful if you are going to reference this code elsewhere — but since we are not going to do that, you can ignore everything on the screen here and go ahead.
It’s all about waiting now. You can now close google sheets and your computer and all of this should happen automatically. As I suggested before, for the first few days, you can keep incorrect values in the spreadsheet to get the mails on purpose, tweak things as you see fit, and redeploy the code again.
Once you are satisfied with the tweaking, then you will no longer need to open the code anymore, you will only need to update the spreadsheet as events happen.
Congratulations, you just did what I would have typically done at my job. 😝
Closing thoughts
This is one of the most technical articles which is a borderline tutorial that I’ve written. Please let me know what you think about this. Let me know if you face any problems, I’ll edit out any sections where there were mistakes if you contact me about it.
And yeah, this project is combined with the QR code I created in the previous article. I avoided speaking about it in this article, as this can be applied without the knowledge of the previous one.
The sharp-eyed amongst you might have noticed that I’ve kept a lot of fields empty and my screenshots look partial. This is because I’ve modified the code into a more expanded version so that I can get multiple reminders at the same time. However, I didn’t want to add that here to complicate things for first-timers.
But, if you are interested, then my contacts are right here, in case you need me.
