← Back to Case Study
Apps Script · Mail Merge

The Code

The current version of the script from the Eid Card Mail Automation case study, mail merge from a Gmail draft, variables filled in from a sheet row, an attachment pulled straight from a Drive link, and the CC support added when a later project needed it. Runs from a custom "Send Mail" menu inside the spreadsheet itself.

MailMerge.gs
const RECIPIENT_COL = "Recipient";
const CC_COL = "CC";
const EMAIL_SENT_COL = "Email Sent";
const ATTACHMENT_COL = "Attachment";

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('Send Mail')
    .addItem('Send Emails', 'sendEmails')
    .addToUi();
}

function sendEmails(subjectLine, sheet = SpreadsheetApp.getActiveSheet()) {
  if (!subjectLine) {
    subjectLine = Browser.inputBox("Mail Merge",
      "Type or copy/paste the subject line of the Gmail " +
      "draft message you would like to mail merge with:",
      Browser.Buttons.OK_CANCEL);
    if (subjectLine === "cancel" || subjectLine == "") {
      return;
    }
  }

  const emailTemplate = getGmailTemplateFromDrafts_(subjectLine);
  const dataRange = sheet.getDataRange();
  const data = dataRange.getDisplayValues();
  const heads = data.shift();
  const emailSentColIdx = heads.indexOf(EMAIL_SENT_COL);
  const recipientColIdx = heads.indexOf(RECIPIENT_COL);
  const ccColIdx = heads.indexOf(CC_COL); // new: CC column index

  const obj = data.map(r => (heads.reduce((o, k, i) => (o[k] = r[i] || '', o), {})));
  const out = [];

  for (let i = 0; i < obj.length; i++) {
    let row = obj[i];

    if (row[EMAIL_SENT_COL]) {
      out.push([row[EMAIL_SENT_COL]]);
      continue;
    }

    if (!row[RECIPIENT_COL]) {
      out.push([""]);
      continue;
    }

    try {
      let attachments = [];
      let driveUrl = row[ATTACHMENT_COL];
      if (driveUrl) {
        const matches = driveUrl.match(/[-\w]{25,}/);
        if (matches && matches.length > 0) {
          const fileId = matches[0];
          const file = DriveApp.getFileById(fileId);
          const blob = file.getBlob().setName(file.getName());
          attachments.push(blob);
        }
      }

      const msgObj = fillInTemplateFromObject_(emailTemplate.message, row);

      const recipientList = row[RECIPIENT_COL].split(",").map(email => email.trim()).filter(email => email);
      const ccList = row[CC_COL] ? row[CC_COL].split(",").map(email => email.trim()).filter(email => email) : [];

      GmailApp.sendEmail(recipientList.join(","), msgObj.subject, msgObj.text, {
        htmlBody: msgObj.html,
        attachments: attachments,
        cc: ccList.join(","),
        inlineImages: emailTemplate.inlineImages
      });

      out.push([new Date()]);
    } catch (e) {
      out.push([e.message]);
    }
  }

  sheet.getRange(2, emailSentColIdx + 1, out.length).setValues(out);
}

function getGmailTemplateFromDrafts_(subject_line) {
  try {
    const drafts = GmailApp.getDrafts();
    const draft = drafts.filter(subjectFilter_(subject_line))[0];
    const msg = draft.getMessage();

    const allInlineImages = msg.getAttachments({ includeInlineImages: true, includeAttachments: false });
    const attachments = msg.getAttachments({ includeInlineImages: false });
    const htmlBody = msg.getBody();

    const img_obj = allInlineImages.reduce((obj, i) => (obj[i.getName()] = i, obj), {});
    const imgexp = RegExp('<img.*?src="cid:(.*?)".*?alt="(.*?)"[^\>]+>', 'g');
    const matches = [...htmlBody.matchAll(imgexp)];

    const inlineImagesObj = {};
    matches.forEach(match => inlineImagesObj[match[1]] = img_obj[match[2]]);

    return {
      message: { subject: subject_line, text: msg.getPlainBody(), html: htmlBody },
      attachments: attachments,
      inlineImages: inlineImagesObj
    };
  } catch (e) {
    throw new Error("Oops - can't find Gmail draft");
  }

  function subjectFilter_(subject_line) {
    return function (element) {
      return element.getMessage().getSubject() === subject_line;
    }
  }
}

function fillInTemplateFromObject_(template, data) {
  let template_string = JSON.stringify(template);
  template_string = template_string.replace(/{{[^{}]+}}/g, key => {
    return escapeData_(data[key.replace(/[{}]+/g, "")] || "");
  });
  return JSON.parse(template_string);
}

function escapeData_(str) {
  return str
    .replace(/[\\]/g, '\\\\')
    .replace(/[\"]/g, '\\"')
    .replace(/[\/]/g, '\\/')
    .replace(/[\b]/g, '\\b')
    .replace(/[\f]/g, '\\f')
    .replace(/[\n]/g, '\\n')
    .replace(/[\r]/g, '\\r')
    .replace(/[\t]/g, '\\t');
}

How it's used, a sheet holds one row per recipient, with columns for Recipient, CC, Attachment (a Drive file link) and Email Sent. A Gmail draft, matched by its subject line, supplies the template, including any {{Name}}-style tokens, which get filled in per row from the matching column. Running Send Mail → Send Emails from the sheet's custom menu sends every unsent row and stamps a timestamp back into Email Sent, so re-running it is always safe.