Send Email In HTML Table Format via App Script App
Spreadsheets have emerged as a powerful tool to automate various business functions while minimizing human efforts. With the help of Google Sheets send emails to the recipients. You can create and save entire code associated with processing an order, creating an invoice or emailing it. This article will help those who are seeking to send email in HTML table format from a spreadsheet for hassle-free management of tasks and better accountability.
Guide on how to send email in HTML Table Format
In this guide, you will learn how to convert data from spreadsheet(s) into HTML table format and *send email using gmail app and scheduling email using trigger* create trigger(s) for scheduling emails to the recipient(s).
Step 1: Convert spreadsheet data into HTML table format.
The above screenshot resembles spreadsheet data. In this article, you will learn how to convert this data into a HTML table format and send to multiple recipients via Google Script App.
Step 2: Read spreadsheet data & convert into HTML table format.
To read spreadsheet data and convert it into HTML table format, use the same code that is provided in the screenshot below-
<!DOCTYPE html>
<html>
<body>
<div><b>See below for 1) customers that are above 90 days overdue on their renewal and 2) top 20 ACV customers who are up to 60 days overdue.
<br>Please provide your manager with an update on renewal status (or payment status for expected autorenewals) for all customers on either list
in the next 2 days.
</b>
<br>
<br>
</div>
1. <strong>Customers that are above 90 days overdue on their renewal = <?=total?> </strong> <br>
<div>
<table width=”100%”>
<thead>
<tr style=”background-color:gray;”>
<th><?=AccountName?></th> <th><?=AccountTeam?></th> <th><?=AccountOwner?></th> <th><?=EffectiveDate?></th><th><?=DaysAged?></th> <th><?=Days30?></th> <th><?=Days60?></th>
<th><?=Days90?></th><th><?=Days120?></th><th><?=Days150?></th><th><?=Over150?></th>
</tr>
</thead>
<tbody>
// TableRangeValues to send in HTML Template using FOR EACH loop.
<? TableRangeValues.forEach(r => { ?>
<tr>
<td><?= r[0] ?></td> <td><?= r[1] ?></td> <td><?= r[2] ?></td> <td><?= r[3] ?></td> <td><?= r[4] ?></td> <td><?= r[5] ?></td>
<td><?= r[6] ?></td> <td><?= r[7] ?></td> <td><?= r[8] ?></td> <td><?= r[9] ?></td> <td><?= r[10] ?></td>
</tr>
<? }) ?>
</tbody>
</table>
</div>
</body>
</html>
This code will help you in reading and conversion of the data available in the spreadsheet. Once done, we will read the HTML file to retrieve the data that is required to send in email.
Step 3: Create a function to read HTML files.
The following code will help you to create a function to allow you to read the HTML file and send email via gmail app.
function outstandingDataEmail()
{
var ss = SpreadsheetApp.getActiveSpreadsheet();
var ActiveSheet = ss.getSheetByName(“Days aged> 90”);
var headers = ActiveSheet.getRange(“A1:K1”).getValues();
var total = ActiveSheet.getRange(“L2”).getDisplayValue();
var Recipients =”John@gmail.com, Marry@gmail.com”
var AccountName = headers[0][0];
var AccountTeam = headers[0][1];
var AccountOwner = headers[0][2];
var EffectiveDate = headers[0][3];
var DaysAged = headers[0][4];
var Days30 = headers[0][5];
var Days60 = headers[0][6];
var Days90 = headers[0][7];
var Days120 = headers[0][8];
var Days150 = headers[0][9];
var Over150 = headers[0][10];
var lr= ActiveSheet.getLastRow();
var TableRangeValues = ActiveSheet.getRange(2, 1, lr-1, 11).getDisplayValues();
var htmltemplate =HtmlService.createTemplateFromFile(“Email”);
htmltemplate.total=total;
htmltemplate.AccountName=AccountName;
htmltemplate.AccountTeam=AccountTeam;
htmltemplate.AccountOwner=AccountOwner;
htmltemplate.EffectiveDate=EffectiveDate;
htmltemplate.DaysAged=DaysAged;
htmltemplate.Days30=Days30;
htmltemplate.Days60=Days60;
htmltemplate.Days90=Days90;
htmltemplate.Days120=Days120;
htmltemplate.Days150=Days150;
htmltemplate.Over150=Over150;
htmltemplate.TableRangeValues=TableRangeValues;
var htmlforemail = htmltemplate.evaluate().getContent();
// This is the function for sending email
GmailApp.sendEmail(
Recipients,
“[ACTION REQUIRED] Outstanding Renewals”,
“See below for 1) customers that are above 90 days overdue on their renewal and 2) top 20 ACV customers who are up to 60 days overdue”, {htmlBody: htmlforemail});
}
Step 4: To create a trigger to schedule email(s).
After using the code to create a function, you must now create a trigger to facilitate emails via the App Script App. There are two ways to create a trigger, i.e. either by UI or using manual code.
To create a trigger using UI, follow these steps-
- Click on Tools
- Select ScriptEditor
- Go to Triggers
- AddTriggers
Or, use the following code to create a trigger-
function createBiWeeklyTrigger() {
// Trigger every other TUESDAY at 09:00 AM.
ScriptApp.newTrigger(‘outstandingDataEmail’)
.timeBased()
.everyWeeks(2)
.onWeekDay(ScriptApp.WeekDay.TUESDAY)
.atHour(9)
.create();
}
(In this example, we have created a trigger to facilitate email on alternate tuesday in every month; i.e. second and fourth tuesday at 9:00 am.)
Wrapping up
Now when we have done the coding job easy for you, scheduling emails to multiple recipients must have never been much easier for you. Ensure that you do not miss out on any step for a hassle free mailing experience using spreadsheet on App Script App. Approach a company like NLINEAXIS for professional assistance on software solutions and services.
