google-email-template-from-draft.js

function sendEmails(sheet = SpreadsheetApp.getActiveSheet()) {
  const emailTemplate = getGmailTemplateFromDrafts_("test email");
  const RECIPIENT_COL = "Adresse e-mail";
  const dataRange = sheet.getDataRange();

  const data = dataRange.getDisplayValues();
  const rowNumber = dataRange.getLastRow();
  const heads = data.shift();
  const obj = data.map(r => (heads.reduce((o, k, i) => (o[k] = r[i] || '', o), {})));
  const row = obj[rowNumber - 2];

  const msgObj = fillInTemplateFromObject_(emailTemplate.message, row);
  
  GmailApp.sendEmail(row[RECIPIENT_COL], msgObj.subject, msgObj.text, {
    htmlBody: msgObj.html,
    // bcc: 'a.bbc@email.com',
    // cc: 'a.cc@email.com',
    // from: 'an.alias@email.com',
    // name: 'name of the sender',
    attachments: emailTemplate.attachments,
    inlineImages: emailTemplate.inlineImages
  });
}



function getGmailTemplateFromDrafts_(subject_line) {
  try {
    // Récupérer les brouillons
    const drafts = GmailApp.getDrafts();
    // Filtrer les brouillons qui correspondent
    const draft = drafts.filter(subjectFilter_(subject_line))[0];
    // Récupérer le message du bourillon
    const msg = draft.getMessage();

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


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




function fillInTemplateFromObject_(template, data) {
  // Convertir en chaîne JSON
  let template_string = JSON.stringify(template);

  // Remplacement des tokens
  template_string = template_string.replace(/{{[^{}]+}}/g, key => {
    return data[key.replace(/[{}]+/g, "")] || "";
  });
  return JSON.parse(template_string);
}