How to create an automated email reminder using google sheets

We’ve all been there, working too much on one thing and forgetting the deadlines/timelines on some other project, assignment or even errand. I used to face this problem a lot(still do), so I thought of creating an email reminder on google sheets. So far it has worked amazingly for me, so I thought to share the tool with the rest of the world.

Every morning (or night, depending on the time slot you choose) you’ll get a mail, from yourself, which would look something like this-

What you need-

1. Gmail Account(to create a google sheet)
2. …..Thats it!

Yep! It’s that simple, although we would be using a little bit of app scripts (the language used to program our google sheets with a script), you don’t actually need to know the language since you could just the copy the code from here and paste it with some minor tweaks to personalize it. Although if you have a bit of experience in the same, that would be much better.

So lets get started.

Creating the google sheet and designing the page according to your needs

All your important work can be categorized into certain buckets. For the sake of explaining, I would be using the following buckets-

1. Personal
2. Office
3. Gym
4. Online Course

As you saw in the above screenshot, your tasks would be split and shown accordingly.

Open your google sheets, rename Sheet 1 to Reminder and delete all the columns after column D(Not necessary, but looks a bit cleaner) and put the following headers-

Column A : Reminder-The actual task

Column B : Date-The date on which that particular reminder must come

Column C : Keep it blank

Column D : Type-The bucket in which that task would go in

Data Validation

This is a tool in google sheet used to fix the type of input a cell can take. We need to do data validation on Column B and Column D.

Go to Data>Data Validation>Add Rule

Select B2:B1000 under “Apply to Range”, ‘is valid date’ under ‘Criteria’, “Reject the input”and then click on done.

Similarly for column D, Select the range D2:D1000 and keep the criteria as “Dropdown” and enter the categories you would need. As decided earlier, we would keep the following-

Now lets go ahead and populate with a few entries(Note : for date, just double click on the cell to get the calendar)

The article was written on March 5, and I’ve deliberately kept all office work on a different date so we can handle the situation of not having any work in a particular category as well.

Alright, last thing in the google sheet before we move towards. Click on C1 and type the following code —

=ArrayFormula({"";IF(A2:A="","","*"&A2:A)})

Now right click on Column C and hide it, why we did this would be understood once we get the actual mail.

So the spreadsheet portion is done, lets move to the app script part.

Go to Extensions>App Scripts and you’ll get the following opened in a new tab-

This is the app script page, it is used to enhance the functionalities on google sheets using scripts. It can help to automate tasks, create data entry forms,connect with API’s and much more. We would be using it to send us emails daily(from ourselves 😅).

Now copy and paste the following code —

function sendTaskReminder() {
  var sheet = SpreadsheetApp.getActive().getSheetByName('Reminder');//If you have named sheet differently, use that name
  var dataRange = sheet.getDataRange();
  var values = dataRange.getValues();
  var today = new Date();
  var emailAddress = "your@email.com" // Replace with your email address
  
  //Here are the categories I chose forthe example, you can rename them according to your needs
  //Remember to rename every mention of it, use ctrl+f to do it
  //like personal_array, there is a personal_message too(same for all buckets). Rename that too.
  var personal_array=[]
  var gym_array = []
  var onlineCourse_array = []
  var office_array = []

  for (var i = 1; i < values.length; i++) {
    var row = values[i];
    var typeTask = row[3]
    //Logger.log(typeTask)
    var task = row[2]; // Assuming task name is in column A
    var date = new Date(row[1]); // Assuming date is in column B
    var actualDate=Utilities.formatDate(date,'IST',"MM/dd")
    let today= new Date();
    today=Utilities.formatDate(today,'IST',"MM/dd")
    
    // Check if the date matches today's date
    if (typeTask=='Personal' && actualDate==today) {
      personal_array.push(task)
    }
    if (typeTask=='Gym' && actualDate==today) {
      gym_array.push(task)
    }
    if (typeTask=='Office' && actualDate==today) {
     office_array.push(task)
    }
    if (typeTask=='Online Course' && actualDate==today) {
      onlineCourse_array.push(task)
    }
  }
  //onlineCourse
  if(onlineCourse_array.length==0){
    var onlineCourse_message = "Nothing to be done for this"
  }
  else{
    var onlineCourse_message = onlineCourse_array.join("\n")
  }

  if (office_array.length==0){
    var office_message = "Nothing to be done for this"
  }
  else{
    var office_message = office_array.join("\n")
  }
  //Personal
  if(personal_array.length==0){
    var personal_message = "Nothing to be done for this"
  }
  else{
    var personal_message = personal_array.join("\n")
  }
  //gym
  if(gym_array.length==0){
    var gym_message = "Nothing to be done for this"
  }
  else{
    var gym_message = gym_array.join("\n")
  }
  today=Utilities.formatDate(today,'IST',"dd MMM")

  // //Send email if there are tasks due today
  MailApp.sendEmail(emailAddress, "Tasks of the day : "+today, "Hey, here are your task reminders for today : \n\nGym : \n"+gym_message+"\n\nOffice:\n" +office_message+"\n\nOnline Course:\n"+onlineCourse_message+"\n\nPersonal:\n"+personal_message+"\n\nPlease Don't forget to delete them if done. If not done, then reassign them to a further date.")

}

In Line 6, please add your email ID under the double quotes

To personalize it, rename the office_message and office_array(and all other categories) according to what you need. For example, freelancingClient1_array and freelancingClient1_message.

Make sure to make the same changes in the mail message at the end.

(Note : the “\n” is used to skip a line, this makes the output a bit cleaner)

Once your personalization is done, type ctrl+s or click on the floppy icon at the top.

After that is done, click on run. You will have to authorize the script to be used by the sheet.

So do the following-

Review Permissions>(Choose the email ID with which you created the sheet)>Advanced>Go to Untitled Project(Unsafe)>Allow

Once that is done, go to your mail ID and you would get the following mail-

Now you see why we added the formula on column C, it was just to make the presentation a bit neater without any extra effort in typing 😃(Also, since no office work was assigned today, just tells us the same)

So now that we have done the annoying programming and data validation work, we move to the daily reminder portion.

So on your app script page, click on trigger(the alarm clock image)> “+Add Trigger” and make the following changes-

Select the time according to your personal needs and click on save.

How do you use the sheet?

Just delete the rows of the tasks that you have done to ensure that your spreadsheet remains clean and easy to use.

Bonus

If you would like your spreadsheet to be a bit more organized, you could use conditional formatting to color code the tasks of today.

Select all the rows then Format>Conditional Formatting and select “Custom Formula is” under ‘Format cells if’ and add the following formula-

=$B2=today()

This is how it should look like, so you could clearly keep track of all your today’s tasks.

So, there you have it! A personal email reminder built only using google sheets. So make sure the future you doesn’t need to plan his day because the present you already did!

If you have any questions, you could connect with me on linkedin or comment below.