Mail merge

How to keep worksheet cell formatting in mail merge

You can format source data in Google Spreadsheet in different colors, fonts and sizes and all your cell formatting will be retained in email messages.

The Direct mail The app merges data from a Google Spreadsheet and sends it as personalized emails. You can format your sheet data in multiple colors, choose different font families, vary your text size, include hyperlinks, line breaks, and more.

Rich text in spreadsheet cells is internally translated into HTML tags with inline CSS and thus cell formatting is preserved in outgoing Gmail messages. Here is an example :

If you want to enable this feature, go to the Add-ons menu in Google Sheets > Mail merge with attachments > Configure the mail merge and check the “Keep cell formatting” option.

You can even format your spreadsheet cells with conditional formatting and the text styles will be preserved in the mail merge. For example, you can dynamically color the invoice amount column in red and make it bold if the due date has passed and this value will also appear in bold red in the email message.

Send Rich Text HTML Emails with Google Sheet

This fragment handles transforming spreadsheet data into rich text in HTML. The functions read data from a cell, specified in A1 notation, split the rich text into blocks that have the same text styles, and translate the individual blocks into HTML tags.

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

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

  
  const getRunAsHtml = (richTextRun) => {
    const richText = richTextRun.getText();

    
    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 = `${richText}`;
    return url ? `${styledText}` : styledText;
  };

  
  const runs = richTextValue.getRuns();

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

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

Known limitations

You can format your Google Spreadsheet cells in any font family – from Cursive Caution to Heavy Impact – but if the recipient doesn’t have these fonts installed on their computer, the rendered text in the email would revert to the default Font.

Font colors, font size, and text styles (bold, italic, underline) are perfectly transformed in HTML, but other properties such as background fill colors, borders, and alignment text in the cell are ignored.

Also, if your spreadsheet cells are formatted as dates, rich text functions may not work.