CODEWITHSUNDEEP
javascriptgoogle-apps-script
bciepiela10gmailcom
bciepiela10gmailcom

Reputation: 33

How do I add a HTML Table to my data and retain Google Sheets formatting

My email script works and sends the Google Sheet range. The problem is the columns vary in width depending on the data in the cells. So I need the code to format the data to a table and keep the formatting for the phone number.

I do not know how to fix

function SendEmail() {
  var s = SpreadsheetApp.getActive().getSheetByName('LOOKUP');
  var to = s.getRange('G4').getValue();
  var data = s.getRange('A36:E91').getValues();
  var body = '';
  for (var row in data) {
    for (var col in data[row]) {
      body += data[row][col] + '\t';
    }
    body += '\n';
  }
  MailApp.sendEmail(to, 'This Is Your Current Bus Roster', body);
}

The email body data columns need to be a fixed width so they all line up from top to bottom.

Upvotes: 1

Views: 281

Answers (1)

dwmorrin
dwmorrin

Reputation: 2734

You can send an email with a HTML body by adding a fourth parameter to your MailApp.sendMail. Adding <head> and <style> tags will let you format the table. (documentation)

function SendEmail() {
  var s = SpreadsheetApp.getActive().getSheetByName('LOOKUP');
  var to = s.getRange('G4').getValue();
  var data = s.getRange('A36:E91').getValues();
  var body = '<head><style>' /* + your css here */ + '</style></head><table>';
  for (var row in data) {
    body += '<tr>';
    for (var col in data[row]) {
      body += '<td>' + data[row][col] + '</td>';
    }
    body += '</tr>';
  }
  body += '</table>';
  MailApp.sendEmail(to, 'This Is Your Current Bus Roster', '', {htmlBody: body});
}

However, I would recommend going one step further and taking advantage of the HTMLService class and a template. In the template you can add <head> and <style> tags so you can format your table exactly how you want.

function SendEmail() {
  var s = SpreadsheetApp.getActive().getSheetByName('LOOKUP');
  var to = s.getRange('G4').getValue();
  var data = s.getRange('A36:E91').getValues();
  var body = HtmlService.createTemplateFromFile('email');
  body.data = data;
  body = body.evaluate().getContent();
  MailApp.sendEmail(to, 'This Is Your Current Bus Roster', '', {htmlBody: body});
}

file "email.html": (Templated HTML documentation)

<head>
  <style>
    /* style your table here */
  </style>
</head>
<table>
<? for (var row in data) { ?>
  <tr>
<?   for (var col in data[row]) { ?>
  <td> <?= data[row][col] ?> </td>
<?   } ?>
  </tr>
<? } ?>
</table>

Upvotes: 2

Related Questions