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:
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:
function onOpen()
{
var ui = SpreadsheetApp.getUi();
ui.createMenu('Invoice')
.addItem('Mark Overdue', 'doOverdueCheck')
.addToUi();
}
function doOverdueCheck()
{
var sheet = SpreadsheetApp.getActiveSheet();
var data_range = sheet.getDataRange();
var last_row = data_range.getLastRow();
var today= new Date();
today.setHours(0,0,0,0);
sheet.getRange('E:E').clearContent();
for(var r=2;r<=last_row;r++)
{
var inv_date = data_range.getCell(r,4).getValue();
inv_date.setHours(0,0,0,0);
if(today > inv_date)
{
sheet.getRange(r, 5).setValue("overdue");
}
}
}
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:
function getOverDueInfo(row)
{
var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');
var values = sheet.getRange(row,1,row,4).getValues();
var rec = values[0];
var overdue =
{
first_name:rec[0],
last_name:rec[1],
email: rec[2],
due_date:rec[3]
};
overdue.name = overdue.first_name +' '+ overdue.last_name;
overdue.date_str = sheet.getRange(row,4).getDisplayValue();
var due_date = new Date(overdue.due_date);
due_date.setHours(0,0,0,0);
var today = new Date();
today.setHours(0,0,0,0);
var difference_ms = Math.abs(today.getTime() - due_date.getTime() );
overdue.num_days = Math.round(difference_ms/(24*60*60*1000) );
return overdue;
}
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.
<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
<h2>Your Invoice is Overdue</h2>
<p>Hello <?= overdue.name ?> </p>
<p>
Your invoice is due since <?= overdue.date_str ?> . It has been <?= overdue.num_days ?> days now.
</p>
<p>Please complete the payment within next 1 day. </p>
<p>Let us know if you need any help completing the payment</p>
<p>Expecting more continued support from you, as always</p>
<p>Thanks<br/></p>
Company.com
</body>
</html>
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:
function sendEmail(row)
{
var overdue = getOverDueInfo(row);
var templ = HtmlService
.createTemplateFromFile('client-email');
templ.overdue = overdue;
var message = templ.evaluate().getContent();
MailApp.sendEmail({
to: overdue.email,
subject: "Your invoice is due.",
htmlBody: message
});
}
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.
function sendOverdueEmails()
{
var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');
var data_range = sheet.getDataRange();
var last_row = data_range.getLastRow();
var today= new Date();
today.setHours(0,0,0,0);
for(var r=2;r<=last_row;r++)
{
var inv_date = data_range.getCell(r,4).getValue();
inv_date.setHours(0,0,0,0);
if(today > inv_date)
{
sendEmail(r);
}
}
}
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.
function onOpen()
{
var ui = SpreadsheetApp.getUi();
ui.createMenu('Invoice')
.addItem('mark Overdue', 'doOverdueCheck')
.addItem('send Emails', 'sendOverdueEmails')
.addToUi();
}
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.
You can get the complete script here