From Google Sheets, how to send email based on date

Suppose you want to send reminder emails automatically from your Google Sheets. The email needs to be sent when the invoice is overdue. So it requires to check the current date with the invoice due date. Let us see how to do that in Google Sheets using Google Apps Script.

We have a Google Sheet with the list of clients and the invoice due date like this:
Invoice due Google Sheets

Mark Overdue rows

First, let us create a feature to mark the overdue rows. The script will iterate through the rows and add an “overdue” column.

Here is the code:

Note about Date Fields

The date fields in the Google sheet will return Javascript Date() object when you call getValue() on that cell.

This makes the rest of the check easy. Note that the dates (today and invoice date) are normalised to 0 hours first (so that only the date part is compared ).

After entering this code, do: File → Save and then close and re-open the Google Sheet. It should show the new menu item. Try running ‘Mark Overdue’.

Extract the overdue Record

Now that we have the overdue records, we need to extract the information from the row so that we can customize the email.

Here is the code:

The code above extracts the client details from the row and computes some of the values like the number of days since the invoice is due. We will later use this overdue information to compose the email to the client.

Compose the email

You can create an HTML template in the script editor. In script editor fo File → New then select HTML.
Here is a sample HTML email template.

Notice how the overdue record is used in the template (like overdue.name and overdue.date_str)

Here is the code to compose the email using the template:

Sending emails to all overdue clients

Now that we have the function to compose and send the email, the next step is to iterate through the rows, find overdue clients and send the email.

The sequence in this function is not much different from the function that marks the overdue clients. The difference is that this function calls sendEmail() .

There are two ways to invoke this function and send the emails. The first one is to add a menu item to trigger the sendOverdueEmails() function.

You will have to visit this Sheet every few weeks and then invoke “Send Emails” manually.
The second way is to completely automate the process.

Automating emails from your Google Sheet

In order to automate, you have to install a trigger from the Google Sheet.

From the Script Editor, select the menu item Edit → Current Project’s triggers

In the “project triggers” page, add a new trigger.

for “Choose the function to run” select sendOverdueMails function

for “event source” choose “Time-driven”

Then choose the timer type. For example, “Week timer”, “Every Monday”

Press save.

automating google sheet emails

You can get the complete script here

See also

Leave a Reply 0 comments

Leave a Reply: