mail-merge-sheets-gmail.js

const SUBJECT_LINE_EN = "So, what do you think?"
const SUBJECT_LINE_FR = "Alors, t'en penses quoi ?"
const FROM = "dorian@socializus.app"
const NAME = "Dorian"
const FIRST_NAME_COLUMN = "first_name"
const EMAIL_COLUMN = "email"
const LOCALE_COLUMN = "locale"
const SENT_COLUMN = "sent"

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

function sendEmails(sheet = SpreadsheetApp.getActiveSheet()) {
  const emailTemplateEn = getGmailTemplateFromDrafts_(SUBJECT_LINE_EN)
  const emailTemplateFr = getGmailTemplateFromDrafts_(SUBJECT_LINE_FR)
  const dataRange = sheet.getDataRange()
  const data = dataRange.getDisplayValues()
  const heads = data.shift()
  const sentColumnIndex = heads.indexOf(SENT_COLUMN)
  const obj = data.map((r) =>
    heads.reduce((o, k, i) => ((o[k] = r[i] || ""), o), {})
  )
  const out = []

  obj.forEach(function (row, rowIndex) {
    if (row[SENT_COLUMN] != "") {
      out.push(row[SENT_COLUMN])
    }

    try {
      const emailTemplate =
        row[LOCALE_COLUMN] == "en" ? emailTemplateEn : emailTemplateFr
      const message = fillInTemplateFromObject_(emailTemplate.message, row)

      GmailApp.sendEmail(row[EMAIL_COLUMN], message.subject, message.text, {
        htmlBody: message.html,
        from: FROM,
        name: NAME,
        attachments: emailTemplate.attachments,
        inlineImages: emailTemplate.inlineImages,
      })

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

  sheet.getRange(2, sentColumnIndex + 1, out.length).setValues(out)

  function getGmailTemplateFromDrafts_(subjectLine) {
    try {
      const drafts = GmailApp.getDrafts()
      const draft = drafts.filter(subjectFilter_(subjectLine))[0]
      const message = draft.getMessage()
      const allInlineImages = draft
        .getMessage()
        .getAttachments({
          includeInlineImages: true,
          includeAttachments: false,
        })
      const attachments = draft
        .getMessage()
        .getAttachments({ includeInlineImages: false })
      const htmlBody = message.getBody()
      const imageObject = allInlineImages.reduce(
        (obj, i) => ((obj[i.getName()] = i), obj),
        {}
      )

      const imgexp = RegExp('<img.*?src="cid:(.*?)".*?alt="(.*?)"[^>]+>', "g")
      const matches = [...htmlBody.matchAll(imgexp)]

      const inlineImagesObject = {}
      matches.forEach(
        (match) => (inlineImagesObject[match[1]] = imageObject[match[2]])
      )

      return {
        message: {
          subject: subjectLine,
          text: message.getPlainBody(),
          html: htmlBody,
        },
        attachments: attachments,
        inlineImages: inlineImagesObject,
      }
    } catch (e) {
      throw new Error("Can't find Gmail draft")
    }

    function subjectFilter_(subjectLine) {
      return function (element) {
        if (element.getMessage().getSubject() === subjectLine) {
          return element
        }
      }
    }
  }

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

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