Draft emails in Gmail from a Google spreadsheet and a Google doc template: https://katydecorah.com/code/google-sheets-to-gmail-template/

email-drafter.js

// What is the Google Document ID for your email template?
var googleDocId = "abcd0000abcd0000abcd0000abcd0000";
// Which column has the email address? Enter the column row header exactly.
var emailField = 'Email';
// What is the subject line?
var emailSubject = 'You\'re bringing {Type}!';
// Which column is the indicator for email drafted? Enter the column row header exactly.
var emailStatus = 'Date drafted';

/* ----------------------------------- */
// Be careful editing beyond this line //
/* ----------------------------------- */

var sheet = SpreadsheetApp.getActiveSheet(); // Use data from the active sheet

function draftMyEmails() {
  var emailTemplate = DocumentApp.openById(googleDocId).getText(); // Get your email template from Google Docs
  var data = getCols(2, sheet.getLastRow() - 1);
  var myVars = getCols(1, 1)[0];
  var draftedRow = myVars.indexOf(emailStatus) + 1;

  
  // Work through each data row in the spreadsheet
  data.forEach(function(row, index){
    // Build a configuration for each row
    var config = createConfig(myVars, row);    
    
    
    // Prevent from drafing duplicates and from drafting emails without a recipient
    if (config[emailStatus] === '' && config[emailField]) {  
      // Replace template variables with the receipient's data
      var emailBody = replaceTemplateVars(emailTemplate, config);
      // Replace template variables in subject line
      var emailSubjectUpdated = replaceTemplateVars(emailSubject, config); 
      
      // Create the email draft
      GmailApp.createDraft(
        config[emailField],   // Recipient
        emailSubjectUpdated,  // Subject
        emailBody             // Body
      );
      
      sheet.getRange(2 + index, draftedRow).setValue(new Date()); // Update the last column
      SpreadsheetApp.flush(); // Make sure the last cell is updated right away
    }
  });
}

function replaceTemplateVars(string, config) {
  return string.replace(/{[^{}]+}/g, function(key){
    return config[key.replace(/[{}]+/g, "")] || "";
  });
}

function createConfig(myVars, row) {
  return myVars.reduce(function(obj, myVar, index) {
    obj[myVar] = row[index];
    return obj;
  }, {});
}

function getCols(startRow, numRows) {
  var lastColumn = sheet.getLastColumn();      // Last column
  var dataRange = sheet.getRange(startRow, 1, numRows, lastColumn) // Fetch the data range of the active sheet
  return dataRange.getValues();            // Fetch values for each row in the range
}

This just saved the non-profit I'm a part of a ton of time / money. Thank you for this!

@katydecorah Thank you for posting - I referenced this to build a solution for our team and it works great!

Quick question. Is it possible to define who receives the draft itself? Not the email recipient or the cc/bcc values - but defining whose inbox actually gets a draft email. Now it just goes to my drafts, but I'm wondering if I can get it to go to someone else's. *** SEE EDIT BELOW

Thanks for any guidance you might have.

EDIT: It appears that whichever user executes the script is the one who receives the draft in their Gmail.

katydecorah - Thanks for this script... Veeeery handy 👍 I tried to use the DOC as a html template (with url and image), but I had issue to use getBody() on the Document.. do you have any idae how to do it properly?

Hei! Same like priceotm. How to keep text formating. i have a template whith tables and pictures.

Is it possible to keep the images and original auto filled document instead of generating only text? Thank you for producing this! I'm missing something when i try to change that part. @katydecorah