Google Script - Send email via Google Sheets, and keep formatting, line breaks, indentations, etc

0
votes

I have been struggling to find a way to send emails through Google Sheet that keep indentations, formatting, line breaks, etc. I know how to do this using HTML in the code, but I am wanting the script to keep the formatting of a cell so that emails can be easily typed and sent out.

I found this code online reached out to the author and no response) that keeps the formatting, but I can't get it to keep the line breaks. https://www.labnol.org/send-rich-text-emails-200830

const sendRichEmail = () => {
  const cellAddress = 'A1';
  const sheetName = 'Mail Merge';
  const recipient = '[email protected]';

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

  /* 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;
  };

  /* Returns the Rich Text string split into an array of runs,
  wherein each run is the longest possible
  substring having a consistent text style. */
  const runs = richTextValue.getRuns();

  const htmlBody = runs.map((run) => getRunAsHtml(run)).join('');

  MailApp.sendEmail(recipient, 'Rich HTML Email', '', { htmlBody });
};

I set up a test sheet https://docs.google.com/spreadsheets/d/1C5GS4c_HwFTaMwWFbyLKbfhPRpxnK6BKw2bzAZjgR1U/edit#gid=0

Any help is greatly appreciated.

2
google-apps-scriptgoogle-sheets
Try asking the author. He is among the top users here – Cooper
I emailed him last week. I haven't received a response yet – C West
Try @Amit Agarwal, can you answer this person question? – Cooper

2 Answers

0
votes

I figured out how to fix the code. Or at least a way that worked for me.

Change the const line below to a var

const htmlBody = runs.map((run) => getRunAsHtml(run)).join('');

and then add the replace line as shown below.

var htmlBody = runs.map((run) => getRunAsHtml(run)).join('');
    htmlBody = htmlBody.replace(/\n/g, '<br>');
0
votes

You can modify the code like so:

const getRunAsHtml = (richTextRun) => {
  const richText = richTextRun.getText()
    .replace(/([\t<>])/g, c => `&#${c.charCodeAt(0)};`)
    .replace(/\n/g, '<br>')
  
  // [...]
}

This not only makes sure to add line breaks when necessary, it also changes specials characters like < to its HTML entity code. You can make the list of special characters longer by adding it to the first replace's regular expression.