How Can I get the merge cells formatting in email? I have data in google sheet and I want to pick the data as it is in the mail. I took reference from the existing question but not able to pick the merged rows and columns in the email but instead of merging the cells, it returns to blank. See the attached images and sheet for references.
Any help is highly appriciated.
Script:
const sendRichEmail = () => { const sheetName = 'Sheet1'; const recipient = 'test@email.com'; const richTextValue = SpreadsheetApp.getActiveSpreadsheet() .getSheetByName(sheetName) .getDataRange() .getRichTextValues(); // get string equivalent of the data const values = SpreadsheetApp.getActiveSpreadsheet() .getSheetByName(sheetName) .getDataRange() .getDisplayValues(); const backgroundColors = SpreadsheetApp.getActiveSpreadsheet() .getSheetByName(sheetName) .getDataRange() .getBackgrounds(); // pass the current index of row and column const getRunAsHtml = (richTextRun, i, j) => { var richText = richTextRun.getText(); // if richText is empty, assign value from getDisplayValues if (!richText) richText = values[i][j]; const style = richTextRun.getTextStyle(); const url = richTextRun.getLinkUrl(); const styles = { color: style.getForegroundColor(), 'font-family': style.getFontFamily(), 'font-size': `${style.getFontSize()}pt`, 'font-weight': style.isBold() ? 'bold' : '', 'font-style': style.isItalic() ? 'italic' : '', 'text-decoration': style.isUnderline() ? 'underline' : '', }; if (style.isStrikethrough()) { styles['text-decoration'] = `${styles['text-decoration']} line-through`; } const css = Object.keys(styles) .filter((attr) => styles[attr]) .map((attr) => [attr, styles[attr]].join(':')) .join(';'); const styledText = `<span style='${css}'>${richText}</span>`; return url ? `<a href='${url}'>${styledText}</a>` : styledText; }; var finalBody = `<html><body><table border='1px'>`; for (var i = 0; i < richTextValue.length; i++) { finalBody += '<tr>'; for (var j = 0; j < richTextValue[i].length; j++) { finalBody += `<td bgcolor='${backgroundColors[i][j]}'>`; // pass i and j into getRunAsHtml finalBody += richTextValue[i][j].getRuns().map((run) => getRunAsHtml(run, i, j)).join(''); finalBody = finalBody.replace(/n/g, '<br>'); finalBody += '</td>'; } finalBody += '</tr>'; } finalBody += '</table></body></html>'; MailApp.sendEmail({ to: recipient, subject: 'Rich HTML Email', htmlBody: finalBody }); };
Screenshot: Data in Sheet Email Received
Advertisement
Answer
Try with that script
const sendRichEmailWithMergedCells = () => { const recipient = 'test@email.com'; const sheetName = 'Sheet1'; const range = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName).getDataRange() MailApp.sendEmail({ to: recipient, subject: 'Rich HTML Email', htmlBody: tableHTML(range) }); };
with
function tableHTML(range){ var data = range.getDisplayValues() var size = range.getFontSizes() var style = range.getFontStyles() var weight = range.getFontWeights() var line = range.getFontLines() var bg = range.getBackgrounds() var color = range.getFontColors() var font = range.getFontFamilies() var textalign = range.getHorizontalAlignments() var offsetx = range.getRow() var offsety = range.getColumn() var mergedRanges = range.getMergedRanges(); var flag = Array.from({ length: range.getNumRows() }, () => Array.from({ length: range.getNumColumns() }, () => true)); var layout = Array.from({ length: range.getNumRows() }, () => Array.from({ length: range.getNumColumns() }, () => '')); mergedRanges.forEach((rng,i) => { for (x=rng.getRow();x<rng.getRow()+rng.getNumRows();x++){ for (y=rng.getColumn();y<rng.getColumn()+rng.getNumColumns();y++){ try{flag[x-offsetx][y-offsety]=false}catch(e){} } } flag[rng.getRow()-offsetx][rng.getColumn()-offsety]=true layout[rng.getRow()-offsetx][rng.getColumn()-offsety]=' colspan="'+rng.getNumColumns()+'" rowspan="'+rng.getNumRows()+'" ' }); var htmltable = '<table cellspacing=0 cellspadding=2px border="1px">'; for (row = 0; row<data.length; row++){ htmltable += '<tr>'; for (col = 0 ;col<data[row].length; col++){ if (flag[row][col]){ if (data[row][col] === '') { htmltable += '<td '+layout[row][col]+' style="background-color:' + bg[row][col] + ';">' + ' ' + '</td>'; } else htmltable += `<td ${layout[row][col]} style="font-family:${font[row][col]}; background-color:${bg[row][col]}; color:${color[row][col]}; font-style:${style[row][col]}; font-weight:${weight[row][col]}; text-decoration:${line[row][col]}; font-size:${size[row][col]}'px'; text-align:${textalign[row][col]};">${data[row][col].replace(/(rn|n|r)/gm,"<br>")}'</td>`; } } htmltable += '</tr>'; } htmltable += '</table>'; return htmltable }