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.
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:
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 sheet:
Sample email result: