How to preserve formatting of google spreadsheet in mail merge?-CodePudding

I want to send an email with google spreadsheet content as a body with formatting. I took the reference from

CodePudding user response：

You need to use getRichTextValues to get all the rich text values of each cell in the range given and then iterate each of them. Then compile them and format into a table.

Also, since the script didn't include the background, I have added it as well. See working script, sample data and the output below.

Script Modification:

const sendRichEmail = () => {
  // update cellAddress if needed, or use getDataRange below instead.
  const cellAddress = 'A1:B2';
  const sheetName = 'Mail Merge';
  const recipient = '[email protected]';

  const richTextValue = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName(sheetName)
    .getRange(cellAddress)
    .getRichTextValues();

  // Adding background color
  const backgroundColors = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName(sheetName)
    .getRange(cellAddress)
    .getBackgrounds();

  /* Run is a stylized text string used to represent cell text.
     This function transforms the run into HTML with CSS
   */
  const getRunAsHtml = (richTextRun) => {
    const richText = richTextRun.getText();

    // Returns the rendered style of text in a cell.
    const style = richTextRun.getTextStyle();

    // Returns the link URL, or null if there is no link
    // or if there are multiple different links.
    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' : '',
    };

    // Gets whether or not the cell has strike-through.
    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;
  };

  // Format the data that will work on multiple cells. 
  // Edit table properties if needed
  var finalBody = `<html><body><table border='1px'>`;
  /* Returns the Rich Text string split into an array of runs,
  wherein each run is the longest possible
  substring having a consistent text style. */
  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]}'>`;
      finalBody  = richTextValue[i][j].getRuns().map((run) => getRunAsHtml(run)).join('');
      finalBody  = '</td>';
    }
    finalBody  = '</tr>';
  }
  finalBody  = '</table></body></html>';

  MailApp.sendEmail({to: recipient, subject: 'Rich HTML Email', htmlBody: finalBody});
};

Sample Data:

Output:

Note:

I have also formatted it to have the data sent as table instead. Feel free to modify the properties of the table if needed.
To reduce the spacing between cells, use this:

Cellspacing 0:

var finalBody = `<html><body><table border='1' cellspacing='0'>`;

Output:

Reference:

Modified Script:

const sendRichEmail = () => {
  const sheetName = 'Sheet1';
  const recipient = '[email protected]';

  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 });
};