You can send HTML email from Google Apps Script. For example, suppose you want to send emails from your Google Sheet when certain events occur. You can send email using MailApp.sendEmail() function like this:
MailApp.sendEmail({
to: email,
subject: "This is a test email",
htmlBody: message
});
where the message is a string with HTML content.
An Example
Suppose you have a Google sheet with a list of candidates.
Make a copy of this Google Sheet. You can see the whole code in your copy.
You want to streamline the approval process. The example below will show you how to approve a candidate when a menu button is pressed. Then the script will automatically send an email to the candidate welcoming them.
Create a new menu item in the Google sheet
Create a new blank Google Sheet. First, let us add a menu item to the Google Sheet.
function onOpen()
{
var ui = SpreadsheetApp.getUi();
ui.createMenu('Process')
.addItem('Approve', 'doApprove')
.addToUi();
}
function doApprove()
{
SpreadsheetApp.getUi().alert("Approval process");
}
The process is that you will select on the candidate and then, select the menu item Process → Approve.
After entering this code to the script editor (In google sheet → Script editor) do File → Save and then close and re-open the Google sheet so that the new menu item (Process) appears.
Select the menu item Process → Approve. Google Sheet will ask the permissions and approvals to run the script. Complete the process.
Get selected candidate
Now we need to get the details of the currently selected candidate. Here is the code for that:
function doApprove()
{
var cell = SpreadsheetApp.getActiveSheet().getActiveCell();
var row = cell.getRow();
var candidate = getCandidateFromRow(row);
var ui = SpreadsheetApp.getUi();
var response = ui.alert('Approve '+candidate.name+'?', ui.ButtonSet.YES_NO);
}
function getCandidateFromRow(row)
{
var values = SpreadsheetApp.getActiveSheet().getRange(row, 1,row,3).getValues();
var rec = values[0];
var candidate =
{
first_name: rec[0],
last_name : rec[1],
email: rec[2]
};
candidate.name = candidate.first_name+' '+candidate.last_name;
return candidate;
}
Create an HTML Email Page
Create a new file in the script editor. Do File → New. Then select HTML file. Do File → Save and then give it a name candidate-email.html
Here are the contents of candidate-email.html
<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
<h2>Your application is approved!</h2>
<p>
Hello <?= candidate.first_name ?>
</p>
<p><strong>Congrats! Your application is approved</strong></p>
<p>
Welcome aboard, <?= candidate.name ?>.
</p>
<p>Hope you will enjoy the time with us!</p>
<p>Best Regards,</p>
<p>Paul,<br>Company.com</p>
</body>
</html>
Note that this is an HTML template. It uses variables like candidate.first_name and candidate.name.
Send Email
The code:
function handleApproval(row, candidate)
{
var templ = HtmlService
.createTemplateFromFile('candidate-email');
templ.candidate = candidate;
var message = templ.evaluate().getContent();
MailApp.sendEmail({
to: candidate.email,
subject: "Your application is approved!",
htmlBody: message
});
SpreadsheetApp.getActiveSheet().getRange(row, 4).setValue('approved');
}
First, we load the HTML template for the email. Then assign the candidate object to a variable in the template. When you call evaluate(), the variables in the template are used to compose the HTML email. (See candidate.first_name in the HTML template)
Then send the email using MailApp.sendEmail() function.
We then update the spreadsheet’s status column to “approved”.
If you run Process → approve now, it will send email to the candidate and update the status to “approved”.
