Monday, May 3, 2021
HomeDevelopmentHow to Preserve Formatting of Spreadsheet Cells in Mail Merge

How to Preserve Formatting of Spreadsheet Cells in Mail Merge

- Advertisement -
- Advertisement -

The Mail Merge application combines information from a Google Spreadsheet and sends them as customized messages. You can organize your sheet information in various tones, pick diverse textual style families, differ the size of your content, incorporate hyperlinks, line breaks and then some.

The rich content of accounting page cells is inside converted into HTML labels with inline CSS and along these lines the cell arranging is saved in the active Gmail messages. Here’s a model:

How to Preserve Formatting of Spreadsheet Cells in Mail Merge

In the event that you might want to empower this element, go to the Add-ons menu in Google Sheets > Mail Merge with Attachments > Configure Mail Merge and check the “Safeguard Cell Formatting” choice.

You can even arrange your accounting page cells with restrictive organizing and the content styles will be held in mail blend. For example, you can progressively shading the receipt sum segment in red and make it strong if the due date has passed and this worth would appear in intense red in the email message also.

Send Rich Text HTML Emails with Google Sheet

This piece handles the change of rich-text Spreadsheet information to HTML. The capacities peruses the information from a cell, indicated in A1 documentation, breaks the rich content into blocks that have a similar book styles and make an interpretation of individual squares into HTML labels.

const sendRichEmail = () => {
  const cellAddress = 'A1';
  const sheetName = 'Mail Merge';
  const recipient = 'amit@labnol.org';

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

Known Limitations

You can arrange the cells of your Google Spreadsheet in any textual style family – from the cursive Caveat to the hefty Impact typeface – however in the event that the beneficiary doesn’t have these text styles introduced on their PC, the delivered text in the email would fallback to the default text style.

The textual style tones, text dimension and text styles (striking, italics, underline) get totally changed into HTML yet different properties like foundation fill tones, boundaries and text-arrangement of the cell are overlooked.

Additionally, if your bookkeeping page cells are arranged as dates, the rich content capacities may not work.

- Advertisement -
namanmahajanhttp://artistogram.in
I am a Multimedia artist, I am born and based on Dewas (M.P), I am working as a freelancer with various brands and different country. My expertise are Graphics designing, motion graphics, 2d/3d animation, advertising videos, cartoons designing, all types of packaging and branding designing, digital marketing, SEO, design website and app Developments etc...

Leave a Reply

- Advertisment -
- Advertisment -

Most Popular

- Advertisment -