How to pick merged rows and column in mail merge?

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

JavaScript
​x
 
  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

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

JavaScript
 
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] + ';">' + '&nbsp;' + '</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
}

JavaScript
 
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] + ';">' + '&nbsp;' + '</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
}
​

Advertisement

Answer