How to Embed a Google Sheet Table with Formatting in an Email Using Google Apps Script

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.

 * 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"); = data;
  var htmlBody = template.evaluate().getContent();

    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();
      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>
    <base target="_top">

      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;


    <p>Please, find below the pricing of the products you requested.</p>
    <? if (data.table) { ?>

    <div id="table">
      <? for (var i = 0; i < data.table.values.length; i++) { ?>
          <? 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) { ?>
                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 ?>
            <? } else if (!data.table.covered_cells[i][j]) { ?>
                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] ?>
            <? } ?>
          <? } ?>
      <? } ?>

    <? } ?>

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.

Scroll to Top