Creating automatic email reminders in Google Sheets

Free Quota for Google Sheet “programming”
Your actual operational limits
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
It’s right here.
Something like this
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())
}
const daysSinceMaintainenace = getValueFromACell(spreadSheet, 2,4)
const threshold = getValueFromACell(spreadSheet, 2, 7)
getValueFromACell(batterySpreadSheet,<x-axis value>,<y-axis value>)
Make sure that Gmail is there
It just needs to be highlighted and then you can press add.
The highlighted section should be selected as houseReminders, not getValueFromACell
It seems that Google has resources free around 8.43 am every day to allow my code to execute
This one to be more precise.
The popup should show that the deployment was successful
You are now a developar.

--

--

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