Untitled SNIPPET

function sendMailMerge() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("Test");
  const data = sheet.getDataRange().getValues();
  
  const headers = data.shift(); 
  const nameIndex = headers.indexOf("Họ tên");
  const emailIndex = headers.indexOf("Email");
  
  if (nameIndex === -1 || emailIndex === -1) {
    throw new Error("Could not find 'Họ tên' or 'Email' in the header row.");
  }
  
  const docId = '1tTD00wA1qOqACEQdFhl1N9Glh57igpemM9Vczt2mEMA';
  
  // 1. Get the Doc content as HTML instead of plain text
  const docHtmlTemplate = convertDocToHtml(docId);

  data.forEach(row => {
    const recipientName = row[nameIndex];
    const recipientEmail = row[emailIndex];

    if (recipientEmail) {
      // 2. Replace placeholders within the HTML string
      let emailHtml = docHtmlTemplate;
      emailHtml = emailHtml.replace(/{{Họ tên}}/g, recipientName);
      emailHtml = emailHtml.replace(/{{Email}}/g, recipientEmail);

      MailApp.sendEmail({
        name: "SOFT Việt Nam",
        to: recipientEmail,
        subject: "Giải pháp chăm sóc khách hàng đa kênh giúp tăng năng suất vượt trội",
        htmlBody: emailHtml
      });
    }
  });
}

/**
 * Helper function to convert Google Doc body to basic HTML
 */
function convertDocToHtml(docId) {
  const doc = DocumentApp.openById(docId);
  const body = doc.getBody();
  let html = "";

  const numChildren = body.getNumChildren();

  for (let i = 0; i < numChildren; i++) {
    const child = body.getChild(i);
    const type = child.getType();

    if (type === DocumentApp.ElementType.PARAGRAPH) {
      html += "<p style='margin: 15px 0; padding: 0;'>";
      html += processTextElement(child.asParagraph());
      html += "</p>";
    } else if (type === DocumentApp.ElementType.LIST_ITEM) {
      html += "<li>" + processTextElement(child.asListItem()) + "</li>";
    }
    // Note: You can add logic here for Tables if your Doc uses them.
  }
  return html;
}

/**
 * Extracts text and applies styling (Bold, Italic, Link, Color)
 */
function processTextElement(container) {
  let textHtml = "";
  const textObj = container.editAsText();
  const textString = textObj.getText();
  
  if (!textString) return "<br>";

  const indices = textObj.getTextAttributeIndices();

  for (let i = 0; i < indices.length; i++) {
    const start = indices[i];
    const end = (i + 1 < indices.length) ? indices[i + 1] : textString.length;
    let part = textString.substring(start, end);

    // Escape basic HTML characters to prevent breaking the layout
    part = part.replace(/&/g, "&amp;").replace(/</g, "&lt;").replace(/>/g, "&gt;");

    const bold = textObj.isBold(start);
    const italic = textObj.isItalic(start);
    const underline = textObj.isUnderline(start);
    const url = textObj.getLinkUrl(start);
    const color = textObj.getForegroundColor(start);

    let style = "";
    if (bold) part = "<strong>" + part + "</strong>";
    if (italic) part = "<em>" + part + "</em>";
    if (underline) part = "<u>" + part + "</u>";
    if (color) style += `color: ${color};`;
    
    if (style) part = `<span style="${style}">${part}</span>`;
    if (url) part = `<a href="${url}">${part}</a>`;

    textHtml += part;
  }

  return textHtml;
}