Creating automatic email reminders in Google Sheets

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.

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!

Free Quota for Google Sheet “programming”
Your actual operational limits

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:

Your standard maintenance table
=FILTER( C11:C , ROW(C11:C) =MAX( FILTER( ROW(C11:C) , NOT(ISBLANK(C11:C)))))
=FILTER( <start>:<end> , ROW(<start>:<end>) =MAX( FILTER( ROW(<start>:<end>) , NOT(ISBLANK(<start>:<end>)))))
Use the auto-highlight of the google sheet to guide yourself with the value of <start> and <end>
24 Aug 2021 is shown in the cell correctly.
test success 😁
= TODAY()-D1
= TODAY() - <cell of the last date of maintenance>
I have removed the test value here. Check if the number of days match-up.
test value has been re-added
Just add a simple number — nothing complicated

Pfft, that was easy, is this what you do at your job?

Maybe you should consider being a computer engineer. 🤭

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.

It’s right here.
Something like this

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())
}
  1. Finds your sheet that is named “Battery”.
  2. Plucks out the value of the number of days that it has been since the last time it was maintained.
  3. Plucks out the value of the number of days that you considered as a deadline.
  4. Compares those two values:
  5. If it’s under the deadline or the threshold, it does nothing.
  6. If it’s over the deadline or threshold, it will send a mail with a body to the email mentioned.
const daysSinceMaintainenace = getValueFromACell(spreadSheet, 2,4)
const threshold = getValueFromACell(spreadSheet, 2, 7)
getValueFromACell(batterySpreadSheet,<x-axis value>,<y-axis value>)

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.

Make sure that Gmail is there
It just needs to be highlighted and then you can press add.

Alright, let’s see if the code works

Change the highlighted drop down to houseReminders

The highlighted section should be selected as houseReminders, not getValueFromACell

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.

It seems that Google has resources free around 8.43 am every day to allow my code to execute

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

This one to be more precise.
The popup should show that the deployment was successful
You are now a developar.

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.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Samridh Tuladhar

Samridh Tuladhar

13 Followers

A computer engineer, with a passion for cheap, affordable & environmentally friendly automation and utter disdain for paperwork and waiting.