Automate todo list reminder email To specific user on every day with Google Sheets in 5 minutes.
Google sheets provide a low cost (free), easy to set up, with a low entry barrier for operating staff to update the data, and less error solution in this case as compared to commercial available CRM which have a learning curve and integration requirement.
Prepare your google sheet as below, make sure you had formatted the column Due Date to Date format using:
Format → Number → DateThis will tell google sheet that this field is a date field, you can choose the format you prefer such as “dd/MM/yyyy” or “MM/dd/yyyy” according to your preferences. In this case, my sheet name will be “todo”.
| no | Task | DueDate | |
|---|---|---|---|
| 1 | Do to Task 1 | 02/13/2023 | example@gmail.com |
| 2 | Do to Task 2 | 02/12/2023 | example@gmail.com |
| 3 | Do to Task 3 | 02/11/2023 | example@gmail.com |
| 4 | Do to Task 4 | 02/10/2023 | example@gmail.com |
| 5 | Do to Task 5 | 02/09/2023 | example@gmail.com |
| 6 | Do to Task 6 | 02/08/2023 | example@gmail.com |
| 7 | Do to Task 7 | 02/07/2023 | example@gmail.com |
| 8 | Do to Task 8 | 02/06/2023 | example@gmail.com |
| 9 | Do to Task 9 | 02/05/2023 | example@gmail.com |
| 10 | Do to Task 10 | 02/04/2023 | example@gmail.com |
Go to Extensions -> Apps Script to open up and Authorized Google Apps Script, you will notice a file named Code.gs on the left of your screen and the code area on the right of your screen.
Paste the following code inside:
function sendReminderEmails() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("todo");
var now = Utilities.formatDate(new Date(), "GMT+8", "dd-MM");
var data = sheet.getDataRange().getValues();
data.forEach(function (row) {
if (Utilities.formatDate(new Date(row[2]), "GMT+8", "dd-MM") === now) {
Logger.log("Send Email")
MailApp.sendEmail({
to: row[3],
subject: "Task Name - " + row[1],
htmlBody: "Hi Rajkumar," + "<br>" + "<br>" +
"Your task has been expired today. Find the below-mentioned task info." + "<br>" + "<br>" +
"Task name : "+ row[1] +"<br>"+
"<img width='500' src='https://img.freepik.com/free-vector/businessman-holding-pencil-big-complete-checklist-with-tick-marks_1150-35019.jpg'>" +
"<br>" +
"Regards," + "<br>" + "lionrajkumar."
});
}
else {
Logger.log("Birthday Not Match")
}
});
}The code will run through each row of the database, and the row[0], row[1], row[2], row[3] in the code is corresponding to the column A, B, C, D in the datasheet.
Whenever the code found matches between today date and the DOB (exclude the year):
Utilities.formatDate(new Date(row[2]), "GMT+8", "dd-MM") === now It will fire a function “sendEmail”.
Test the code by saving and click the Run button on top. You will get an email as below:
Hi Rajkumar,
Your task has been expired today. Find the below-mentioned task info.
Task name : Do to Task 1
Regards,
lionrajkumar.
The idea of this function is to schedule the sendReminderEmails() to run every day to check the birthday in the database. It is easily done as Google App Script offers a few types of triggers according to the user's needs.
- Goto Triggers => Click Add Trigger
- Select "sendReminderEmails" in Choose Which Function to run dropdown
- Select "Time-driven " in select event source
- Select "Day timer" in Select type of time based trigger
- Select "10am to 11am" in Select time of day
- Click Save button
In this case, I would like the function to be triggered every day 10 am — 11 am as it is a perfect period of time to open a task reminder Email. Lastly, save the trigger, and you have will never miss any reminder email to your collegue anymore!
This simple setup only took 5 minutes and it will improve your relationship with your customers, try it out!
References: Automate Birthday Email To Customer Every Year with Google Sheets in 5 minutes