Email Google Sheets table and preserve link formatting

Question

I use the following function to send a table over email. This works well except for the fact that links are not preserved.

function sendMail(){
 var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Email");
 var data = sh.getRange("A:C").getValues();

var TABLEFORMAT = 'cellspacing="2" cellpadding="2" dir="ltr" border="1" style="width:100%;table-layout:fixed;font-size:10pt;font-family:arial,sans,sans-serif;border-collapse:collapse;border:1px solid #ccc;font-weight:normal;color:black;background-color:white;text-align:center;text-decoration:none;font-style:normal;'
var htmltable = '<table ' + TABLEFORMAT +' ">';

for (row = 0; row<data.length; row++){

htmltable += '<tr>';

for (col = 0 ;col<data[row].length; col++){
  if (data[row][col] === "" || 0) {htmltable += '<td>' + 'None' + '</td>';} 
  else
    if (row === 0)  {
      htmltable += '<th>' + data[row][col] + '</th>';
    }

  else {htmltable += '<td>' + data[row][col] + '</td>';}
}

     htmltable += '</tr>';
}

    htmltable += '</table>';
    Logger.log(data);
    Logger.log(htmltable);
    MailApp.sendEmail(Session.getActiveUser().getEmail(), 'Your latest vocabulary','' ,{htmlBody: htmltable})
}

Here is a screenshot of how the table looks that I am sending over email. The original data is on another tab and brought over using the filter formula.

Links on the original source tab are created using the hyperlink formula:

And here finally a screenshot of how the table looks as an email. The links are missing.

Answer 1

SUGGESTION

You can use the Apps Script method called getRichTextValues to get the label and the actual URL from a cell that contains a hyperlink. You can refer to the following sample tweaked script:

Sample Tweaked Script

function sendMail() {
  var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Email");
  var data = sh.getRange("A:C").getValues();

  //Implementation of getRichTextValues
  const urlRawData = sh.getRange('B:B').getRichTextValues(); //Get The raw data of all of the URL column data
  const urls = urlRawData.map(d => d[0].getLinkUrl()); //Retrieve only the URL via map manipulation

  //Manipulate the data variable and replace the existing column 2 text with an <a> tag.
  const newData = data.map((d, index) => d.map((v, col) => {
    return col == 1 && urls[index] != null ? `<a href="${urls[index]}">${v}</a>` : v;
  }));

  console.log(newData)

  var TABLEFORMAT = 'cellspacing="2" cellpadding="2" dir="ltr" border="1" style="width:100%;table-layout:fixed;font-size:10pt;font-family:arial,sans,sans-serif;border-collapse:collapse;border:1px solid #ccc;font-weight:normal;color:black;background-color:white;text-align:center;text-decoration:none;font-style:normal;'
  var htmltable = '<table ' + TABLEFORMAT + ' ">';

  for (row = 0; row < newData.length; row++) {

    htmltable += '<tr>';

    for (col = 0; col < newData[row].length; col++) {
      if (newData[row][col] === "" || 0) { htmltable += '<td>' + 'None' + '</td>'; }
      else
        if (row === 0) {
          htmltable += '<th>' + newData[row][col] + '</th>';
        }

        else { htmltable += '<td>' + newData[row][col] + '</td>'; }
    }

    htmltable += '</tr>';
  }

  htmltable += '</table>';
  Logger.log(newData);
  Logger.log(htmltable);
  MailApp.sendEmail(Session.getActiveUser().getEmail(), 'Your latest vocabulary test', '', { htmlBody: htmltable })
}

Sample Demo

Sample sheet:

Sample email result:

Additional reference

• JavaScript Array map()