Learn how to embed a Google Sheet table with formatting in an email using Google Apps Script
In the context of email automation using Google Script, there are instances where we wish to transmit a table from a Google Sheet via email. The preference is not to send it as a PDF but rather to embed it within the email body. From my professional background, I’ve encountered instances where clients sought to incorporate tables, be it an invoice breakdown, a report, or a pricing structure, directly into their email correspondence.
This task, while seemingly straightforward, presents a challenge: preserving the formatting of the Google Sheet table in its entirety when transmitted via email. Unfortunately, Google Sheets lacks a built-in function tailored for this specific purpose. However, to accomplish this, you can utilize and modify the script below.
The script encompasses formatting elements like background color, font color, font weight (bold or normal), and cell merging. However, the script can be adjusted to add other formatting elements if needed.
Create a GS file and paste this script
You need to provide the table’s address name, the sheet name, the receiver’s email address, and the email subject.
/**
* SEND A TABLE WITH FORMATTING VIA EMAIL
*
* Written by Joseph Asinyo (MIT License)
*
**/
// Configure the script
const TABLE_RANGE_ADDRESS = "table range address" // Eg: M1:O10
const SHEET_NAME = "sheet name"
const RECEIVER_EMAIL = "receiver email"
const SUBJECT = "email subject"
function sendEmailWithTable() {
var data = {
table: getTableWithFormatting()
}
var template = HtmlService.createTemplateFromFile("Email");
template.data = data;
var htmlBody = template.evaluate().getContent();
MailApp.sendEmail({
to: RECEIVER_EMAIL,
subject: SUBJECT,
htmlBody: htmlBody,
});
Logger.log("Email sent!")
}
function getTableWithFormatting() {
var ss = SpreadsheetApp.getActive()
var sh = ss.getSheetByName(SHEET_NAME)
var table_range = sh.getRange(TABLE_RANGE_ADDRESS);
var values = table_range.getDisplayValues();
var font_weights = table_range.getFontWeights();
var background_colors = table_range.getBackgrounds();
var font_colors = table_range.getFontColors();
// Initialize a 2D array to keep track of covered cells
var covered_cells = Array(values.length).fill().map(() => Array(values[0].length).fill(false));
// Handle merged cells
var merged_ranges = table_range.getMergedRanges();
var merged_cells = [];
merged_ranges.forEach(function(range) {
var top_left_value = range.getCell(1, 1).getDisplayValue();
var top_left_font_weight = range.getCell(1, 1).getFontWeight();
var top_left_background_color = range.getCell(1, 1).getBackground();
var top_left_font_color = range.getCell(1, 1).getFontColor();
var row_index = range.getRow() - table_range.getRow();
var col_index = range.getColumn() - table_range.getColumn();
merged_cells.push({
row: row_index,
col: col_index,
rowspan: range.getNumRows(),
colspan: range.getNumColumns(),
value: top_left_value,
font_weight: top_left_font_weight,
background_color: top_left_background_color,
font_color: top_left_font_color
});
// Mark the covered cells
for (var i = 0; i < range.getNumRows(); i++) {
for (var j = 0; j < range.getNumColumns(); j++) {
covered_cells[row_index + i][col_index + j] = true;
}
}
});
var table_object = {
values: values,
font_weights: font_weights,
background_colors: background_colors,
font_colors: font_colors,
merged_cells: merged_cells,
covered_cells: covered_cells
}
return table_object;
}
Create an HTML file and paste this code
You should name the HTML file “Email” because it is used in the function sendEmailWithTable of the Google Script code to obtain the email template. You may change it if you want; in such a case, ensure that you make the corresponding modification within the function as well.
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<style>
span {
display: block;
}
table {
text-align: left;
border-collapse: collapse;
}
td {
padding-top: 3px;
padding-bottom: 3px;
padding-left: 6px;
padding-right: 6px;
border: 1px solid #dddddd;
}
.full-image {
width: 75px;
height: auto;
}
</style>
</head>
<body>
<p>Hi,</p>
<p>Please, find below the pricing of the products you requested.</p>
<? if (data.table) { ?>
<br><br>
<div id="table">
<? for (var i = 0; i < data.table.values.length; i++) { ?>
<tr>
<? for (var j = 0; j < data.table.values[i].length; j++) { ?>
<? var cell = data.table.merged_cells.find(function(cell) { return cell.row === i && cell.col === j; }); ?>
<? if (cell) { ?>
<td
class="<?= j < 2 ? 'left' : '' ?>"
rowspan="<?= cell.rowspan ?>"
colspan="<?= cell.colspan ?>"
style='font-weight: <?= cell.font_weight ?>; background-color: <?= cell.background_color ?>; color: <?= cell.font_color ?>;'
>
<?= cell.value ?>
</td>
<? } else if (!data.table.covered_cells[i][j]) { ?>
<td
class="<?= j < 2 ? 'left' : '' ?>"
style='font-weight: <?= data.table.font_weights[i][j] ?>; background-color: <?= data.table.background_colors[i][j] ?>; color: <?= data.table.font_colors[i][j] ?>;'
>
<?= data.table.values[i][j] ?>
</td>
<? } ?>
<? } ?>
</tr>
<? } ?>
</div>
<br><br>
<? } ?>
<p>Best,</p>
<p>Joseph</p>
</body>
</html>
If you run the sendEmailWithTable function, the receiver will receive the table in an email.
Joseph Asinyo
Google Workspace Developer
I’m Joseph. I love building applications and writing Google scripts to automate Google Workspace for greater productivity. Learn more about me.
