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