How to send HTML table in the email body using google app script

This article contains a Google App Script code to send auto email alerts for employees’ leave transactions. Let’s learn how to send an HTML table in the email body using the google app script.

 

  • Step 1 – Create a new Google Sheets or open an existing one
  • Step 2 – Create data or table in any spreadsheet to be sent in the email body as HTML Table
  • Step 3 – Open Script Editor – Tools>>Script Editor
  • Step 4 – Copy-paste the below code in the opened script file, change range per your data
send HTML Table
function sendEmail() {
  
  const ss  = SpreadsheetApp.getActiveSpreadsheet();
  const ws = ss.getSheetByName('LeaveCard')//rename sheet name per your file;
  const headers = ws.getRange('B3:H3').getDisplayValues()//get table headers name;
  
  //assign variable to each header value
  const name = headers[0][0]; 
  const leavetype = headers[0][1];
  const fromDate = headers[0][2];
  const toDate = headers[0][3];
  const totaldays = headers[0][4];
  const status = headers[0][5];
  const reportingmanager = headers[0][6];

  const lr = ws.getRange('B1').getValues()//get last low row of active range, forula entered in B1 =countif(B3:B,"*")+2;

  const tablerangeValue = ws.getRange(4,2,lr-3,7).getDisplayValues()//get range value, my data range start from 4th row and has 7 columns, pls chage per your need;

  const htmlTemplate = HtmlService.createTemplateFromFile('emailTable');

  htmlTemplate.name = name;
  htmlTemplate.leavetype = leavetype;
  htmlTemplate.fromDate = fromDate;
  htmlTemplate.toDate = toDate;
  htmlTemplate.totaldays = totaldays;
  htmlTemplate.status = status;
  htmlTemplate.reportingmanager = reportingmanager;
  htmlTemplate.tablerangeValue = tablerangeValue;

  const htmlForEmail = htmlTemplate.evaluate().getContent();
  
  var toEmail = 'myemail@test.com';
  var ccEmail = 'myemail@test.com,myemail@test.com';

  MailApp.sendEmail({
  to: toEmail,
  cc: ccEmail,
  subject: "Subject-------------------------",
  htmlBody:htmlForEmail
    });

}

how to send an HTML table in the email body using the google app script.

  • Step 5 – Create an HTML file, click on ‘+’ sign button and select HTML
  • Step 6 – Copy base below code in the new created HTML file
<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <style>
  table, th, td {
  border: 0px solid black;
  font-size:15px;
}

th, thead {
  background-color: lightblue;
}

</style>  
  </head>
  <body>
    <div>
      <table>
        <thead>
          <th><?= name ?></th><th><?= leavetype ?></th><th><?= fromDate ?></th><th><?= toDate ?></th><th><?= totaldays ?></th><th><?= status ?></th><th><?= reportingmanager ?></th>
        </thead>
        <tbody >
          <? tablerangeValue.forEach(r => {?>
          <tr> 
          <td><?= r[0] ?></td><td><?= r[1] ?></td><td><?= r[2] ?></td><td><?= r[3] ?></td><td style="text-align:center"><?= r[4] ?></td><td><?= r[5] ?></td><td><?= r[6] ?></td>
          </tr>
          <?})?>
         
        </tbody>

      </table>
    </div>
  </body>
</html>
  • Step 7 – Rename HTML file as ’emailTable’
  • Step 8 – Go to your script file and run the code, to check whether the code is working fine!
  • Step 9 – Click on the clock icon on the left sidebar menu to create auto triggers
  • Step 10 – Click on ‘Add Trigger’
  • Step 11- Create triggers per your choice

Read More:

COMMENTS (1)

  • comment-avatar
    Lukasz July 17, 2021

    Very helpful! Thank you!

  • Open chat
    Talentnett Solutions
    Hello,

    How may I help you today!

    Regards,
    Umesh Agarwal
    Contact - +91 - 7017544508
    Email - umesh.agarwal@talentnett.com

    Cool Tips and Tricks Directly Delivered in your Inbox. Subscribe to our Newsletter.

    You have successfully subscribed to the newsletter

    There was an error while trying to send your request. Please try again.

    Talentnett will use the information you provide on this form to be in touch with you and to provide updates and marketing.