Task Reminder System with Google Sheets and Apps Script

Have you ever missed an important deadline because you simply forgot about it? In this tutorial, I'll show you how to build a simple but effective task reminder system using Google Sheets and Apps Script. This app will automatically send you an email alert one day before any task is due, helping you stay on top of your responsibilities without having to manually check your task list every day.

Prerequisites

This tutorial assumes the following prerequisites:

• Basic familiarity with Google Sheets

• Basic understanding of what Apps Script is

• How to create your first Apps Script and authorize it

SUBSCRIBER EXCLUSIVE

Automated Task Reminders Template

Never miss a deadline! This Google Sheets template sends email reminders for tasks due tomorrow.

FEATURES

• ✓
  Automated Email Reminders: Get timely notifications directly in your inbox.
• ✓
  Beautiful HTML Emails: Receive clear, well-formatted email reminders that are a pleasure to read and act upon.
• ✓
  Smart Task Batching: Get a single, organized summary if multiple tasks are due on the same day.

Subscribe to Access ▶

Already a subscriber? Log in

5 steps to implement a task reminder system

• Step 1 — Create a spreadsheet to track tasks

• Step 2 — Write the Apps Script code

• Step 3 — Test your reminder system

• Step 4 — Set up automatic daily triggers

• Step 5 — Add finishing touches

Step 1 — Create a spreadsheet to track tasks

First, let's create a Google Spreadsheet that will store our tasks and their due dates:

• Create a new Google Spreadsheet by going to sheets.new in your browser

• Rename the default sheet from "Sheet1" to "Tasks" by right-clicking on the sheet tab at the bottom and selecting "Rename"

• In cell A1, enter "Task" as the column header

• In cell B1, enter "Target due date" as the column header

• Format row 1 to make the headers stand out (optional) - you can make them bold or add a background color

• Enter a few sample tasks in column A starting from row 2

• Enter the corresponding due dates in column B (make sure to use the date format)

Step 2 — Write the Apps Script code

Now that we have our spreadsheet set up, let's create the Apps Script that will check for tasks due tomorrow and send email reminders:

• In your spreadsheet, click on "Extensions" in the top menu, then select "Apps Script"

• This will open the Apps Script editor in a new tab

• Delete any code in the editor and replace it with our task reminder script

• Click the Save button (disk icon) to save your script

• Give your project a name, such as "Task Reminder System"

Step 3 — Test your reminder system

Before setting up the automatic trigger, let's test our script to make sure it works correctly:

• Make sure you have at least one task in your spreadsheet with tomorrow's date as the due date

• In the Apps Script editor, select the main function from the dropdown menu next to the Debug button.

• Click the Run button to execute the script

When you run the script for the first time, you'll need to authorize it.

If everything works correctly and you have a task due tomorrow in your spreadsheet, you should receive an email reminder. Check your inbox to confirm!

Step 4 — Set up automatic daily triggers

Now that we've confirmed our script works, let's set up a trigger to run it automatically every day:

• Return to your spreadsheet

• Refresh the page if necessary (this ensures the onOpen function runs to create our custom menu)

• You should see a new "Admin" menu item in the top menu bar

• Click on "Admin" and then select "Activate Daily Reminders"

• You'll see a confirmation dialog letting you know the trigger has been set up

• Click "OK" to close the dialog

That's it! The script will now run automatically at 7 AM every day, checking your task list and sending you email reminders for tasks due the next day.

To verify that your trigger was created:

• In the Apps Script editor, click on the "Triggers" icon in the left sidebar (it looks like a clock)

• You should see your trigger listed, showing that the main function is set to run daily at 7 AM

Step 5 — Add finishing touches

Let's add a few finishing touches to make our task reminder system more user-friendly:

• Consider formatting your spreadsheet to make it more visually appealing:

• Add color-coding for past due, upcoming, and completed tasks

• Adjust column widths to fit your content

• Add a description at the top of the spreadsheet explaining how to use it

• You might want to add a third column called "Status" where you can mark tasks as "Pending" or "Completed"

• If you want to customize when reminders are sent, you can modify the trigger time in the createTriggers() function by changing the atHour(7) parameter to a different hour (based on a 24-hour clock)

Full code

Here's the full code for our task reminder system:

//@OnlyCurrentDoc

function main() {
 var sheet = SpreadsheetApp.getActive().getSheetByName("Tasks");
 var numRows = sheet.getLastRow();
  // If there's only the header row or no data at all, exit
 if (numRows <= 1) {
   Logger.log("No tasks found.");
   return;
 }
  var range = sheet.getRange(2, 1, numRows - 1, 2);
 var values = range.getValues();
  for(var i = 0; i < values.length; i++) {
   var task = values[i][0];
   var dueDate = values[i][1];
  
   // Skip empty rows
   if (!task || !dueDate) {
     continue;
   }
  
   if(isTaskDueTomorrow(dueDate)) {
     // Task is due tomorrow, send an email reminder
     sendEmailReminder(task);
   }
 }
}

function isTaskDueTomorrow(taskDueDate) {
 var today = new Date();
 var tomorrow = new Date();
 tomorrow.setDate(today.getDate() + 1);
  // Compare dates by stripping time information
 var tomorrowStr = Utilities.formatDate(tomorrow, Session.getScriptTimeZone(), "yyyy-MM-dd");
 var dueDateStr = Utilities.formatDate(taskDueDate, Session.getScriptTimeZone(), "yyyy-MM-dd");
  return tomorrowStr === dueDateStr;
}

function sendEmailReminder(task) {
 var subject = "DUE TOMORROW: " + task;
 var recipient = Session.getActiveUser().getEmail();
 var body = "Your task '" + task + "' is due tomorrow.";
 MailApp.sendEmail(recipient, subject, body);
}

function onOpen(e) {
 var ui = SpreadsheetApp.getUi();
 ui.createMenu("Admin")
   .addItem("Activate Daily Reminders", "createTriggers")
   .addToUi();
}

function createTriggers() {
 var triggers = ScriptApp.getProjectTriggers();
 if(triggers.length == 0) {
   // Trigger every day at 7am
   ScriptApp.newTrigger('main')
       .timeBased()
       .everyDays(1)
       .atHour(7)
       .create();
  
   SpreadsheetApp.getUi().alert("Daily reminder trigger has been activated! You'll now receive email reminders at 7am for tasks due the next day.");
 } else {
   SpreadsheetApp.getUi().alert("Trigger is already set up.");
 }
}

How the code works

Let's break down how our task reminder system works:

Main Function

The main() function is the core of our script:

function main() {
 var sheet = SpreadsheetApp.getActive().getSheetByName("Tasks");
 var numRows = sheet.getLastRow();
  // If there's only the header row or no data at all, exit
 if (numRows <= 1) {
   Logger.log("No tasks found.");
   return;
 }
  var range = sheet.getRange(2, 1, numRows - 1, 2);
 var values = range.getValues();
  for(var i = 0; i < values.length; i++) {
   var task = values[i][0];
   var dueDate = values[i][1];
  
   // Skip empty rows
   if (!task || !dueDate) {
     continue;
   }
  
   if(isTaskDueTomorrow(dueDate)) {
     // Task is due tomorrow, send an email reminder
     sendEmailReminder(task);
   }
 }
}

First, it gets a reference to our "Tasks" sheet using SpreadsheetApp.getActive().getSheetByName("Tasks"). Then, it finds out how many rows the sheet has using sheet.getLastRow().

If there's only the header row or no data at all, it logs a message and exits. Otherwise, it retrieves all task data starting from row 2 (skipping the header row) using sheet.getRange(2, 1, numRows - 1, 2) and then gets the values as a 2D array with range.getValues().

It then loops through each row of data. For each row, it:

• Extracts the task name (column A) and due date (column B)

• Skips any empty rows

• Checks if the task is due tomorrow using the isTaskDueTomorrow() function

• If the task is due tomorrow, it sends an email reminder using the sendEmailReminder() function

Date Comparison Function

The isTaskDueTomorrow() function handles date comparison:

function isTaskDueTomorrow(taskDueDate) {
 var today = new Date();
 var tomorrow = new Date();
 tomorrow.setDate(today.getDate() + 1);
  // Compare dates by stripping time information
 var tomorrowStr = Utilities.formatDate(tomorrow, Session.getScriptTimeZone(), "yyyy-MM-dd");
 var dueDateStr = Utilities.formatDate(taskDueDate, Session.getScriptTimeZone(), "yyyy-MM-dd");
  return tomorrowStr === dueDateStr;
}

This function takes a date and checks if it's tomorrow. It:

• Creates a Date object for today

• Creates a Date object for tomorrow by adding 1 to today's date

• Formats both the calculated tomorrow date and the task's due date as strings in "yyyy-MM-dd" format (removing any time information)

• Compares these strings to see if they match

This approach ensures we're comparing just the dates, ignoring any time components that might cause issues.

Email Function

The sendEmailReminder() function handles sending the email:

function sendEmailReminder(task) {
 var subject = "DUE TOMORROW: " + task;
 var recipient = Session.getActiveUser().getEmail();
 var body = "Your task '" + task + "' is due tomorrow.";
 MailApp.sendEmail(recipient, subject, body);
}

It takes the task name, creates a subject line with "DUE TOMORROW" prepended to the task name, gets the active user's email address, creates a simple message body, and sends the email using MailApp.sendEmail().

Custom Menu and Trigger Creation

The onOpen() function creates our custom menu:

function onOpen(e) {
 var ui = SpreadsheetApp.getUi();
 ui.createMenu("Admin")
   .addItem("Activate Daily Reminders", "createTriggers")
   .addToUi();
}

This function runs automatically when the spreadsheet is opened. It creates a custom menu called "Admin" with one option: "Activate Daily Reminders". When this option is selected, it calls the createTriggers() function.

The createTriggers() function sets up our automatic daily trigger:

function createTriggers() {
 var triggers = ScriptApp.getProjectTriggers();
 if(triggers.length == 0) {
   // Trigger every day at 7am
   ScriptApp.newTrigger('main')
       .timeBased()
       .everyDays(1)
       .atHour(7)
       .create();
  
   SpreadsheetApp.getUi().alert("Daily reminder trigger has been activated! You'll now receive email reminders at 7am for tasks due the next day.");
 } else {
   SpreadsheetApp.getUi().alert("Trigger is already set up.");
 }
}

It first checks if any triggers already exist. If not, it creates a new time-based trigger that runs our main() function every day at 7 AM. It then displays an alert to confirm the trigger has been created. If triggers already exist, it shows a message saying they're already set up.

Conclusion

In this tutorial, you've learned how to create a simple task reminder system using Google Sheets and Apps Script. This system automatically checks your task list every day and sends you email reminders for tasks due the next day, helping you stay on top of your deadlines without having to remember to check your task list manually.

We've covered:

• Setting up a Google Sheets spreadsheet to track tasks and due dates

• Writing Apps Script code to check for tasks due tomorrow

• Sending email reminders

• Setting up a time-based trigger to automate the process

• Adding user-friendly elements like a custom menu

This project demonstrates how powerful Apps Script can be for automating simple workflows, and it only scratches the surface of what's possible!

SUBSCRIBER EXCLUSIVE

Automated Task Reminders Template

Never miss a deadline! This Google Sheets template sends email reminders for tasks due tomorrow.

FEATURES

• ✓
  Automated Email Reminders: Get timely notifications directly in your inbox.
• ✓
  Beautiful HTML Emails: Receive clear, well-formatted email reminders that are a pleasure to read and act upon.
• ✓
  Smart Task Batching: Get a single, organized summary if multiple tasks are due on the same day.

Subscribe to Access ▶

Already a subscriber? Log in

Future work

Here are some ways you could enhance your task reminder system:

• Multiple reminder intervals: Modify the script to send reminders at different intervals, such as a week before, three days before, and one day before the due date. You could add a column to your spreadsheet to specify these reminder intervals for each task.

• Consolidated daily digest: Instead of sending one email per task, modify the script to collect all tasks due tomorrow and send them in a single, nicely formatted digest email. This would be less intrusive if you have multiple tasks due on the same day. You could use HTML email for better formatting.

• Task completion tracking: Add a status column to your spreadsheet where you can mark tasks as "Completed" or "In Progress", and modify the script to only send reminders for tasks that aren't marked as completed.

• Priority-based reminders: Add a priority column (High, Medium, Low) to your spreadsheet and modify your email reminders to highlight high-priority tasks that are coming due.

• Recurring tasks: Implement support for recurring tasks (daily, weekly, monthly) by adding logic to automatically create new task entries when recurring tasks are completed.

Feel free to adapt this system to your specific needs and workflow. The beauty of building your own tools is that you can customize them exactly as you want!

Thanks for reading!