Posted October 16, 2023

A more advanced way to automate your spreadsheets is to integrate Google Scripts into your workflow. For professionals using Google Suite, the hidden gem of Google Scripts can transform tedious tasks into automated processes. While this does require some basic knowledge of coding (e.g. javascript), there are free resources to help you quickly overcome this (check out our blog post on How to Generate Code with ChatGPT: No Experience Needed!). In today’s article we'll explore how you can set up a simple script to track and send reminders for upcoming deadlines.

The Problem:

Imagine you're managing a project with various deliverables, each with its own deadline. Keeping track and reminding yourself or your team about these deadlines can become cumbersome, especially when juggling multiple projects.

The Solution:

A Google Script can automate this! Here's a straightforward script that, when applied to a Google Sheet, will scan for upcoming deadlines and send email reminders. This script will send an email reminder to all task owners whose deadline is within 3 days.

Step-by-Step Guide:

1. Setting up Your Google Sheet

Create a Google Sheet with the following columns:

• Task Name
• Deadline Date
• Email Address
• Reminder Sent?

2. Writing the Google Script

• Within your Google Sheet open Google Scripts by clicking on Extensions > Apps Script.
• Replace any code in the script editor with the following:

function sendDeadlineReminder() {

var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

var range = sheet.getDataRange().getValues();

for (var i = 1; i < range.length; i++) {

var row = range[i];

var taskName = row[0];

var deadline = new Date(row[1]);

var emailAddress = row[2];

var reminderSent = row[3];

var today = new Date();

var timeDiff = Math.abs(deadline.getTime() - today.getTime());

var diffDays = Math.ceil(timeDiff / (1000 * 3600 * 24));

if(diffDays <= 3 && reminderSent === 'No') {

var subject = 'Upcoming Deadline Reminder';

var message = 'Hi there, just a reminder that the deadline for ' + taskName + ' is in ' + diffDays + ' days!';

MailApp.sendEmail(emailAddress, subject, message);

sheet.getRange(i + 1, 4).setValue('Yes');

} } }

3. Scheduling the Script:

• One of the great features of Google Scripts is that it allows you to set triggers for when it can execute your code for you! Follow the below instructions to allow the script to run daily:
• Click on the clock icon on the left sidebar.
• Add a new trigger, selecting sendDeadlineReminder for the function to run, setting the event source to Time-driven, and choosing the time of day you want it to run.

4. Testing Your Result:

• Once you have implemented the script and set up your trigger, it's time to test your result. To do this:
• Change the sample email address within the table on your spreadsheet
• Change the ‘Deadline Date’ to two days from the date you are running the script
• Click the ‘Play’ button on the top of the Google Script screen
• The address you provided should have received an email and the ‘Reminder Sent’ field in your table should now be updated to “Yes”

For those interested in a step-by-step walkthrough, be sure to check out the video above!

And There You Go!

With just a few lines of code, you've transformed your Google Sheet into an automated deadline reminder tool! This example showcases the power and potential of Google Scripts, opening doors for countless other automation opportunities. Looking to dive deeper into automation? Stay tuned in with Excelerated Consulting; we're dropping content like this twice a week!