Skip to content

Instantly share code, notes, and snippets.

@katydecorah
Last active July 19, 2023 22:06
Show Gist options
  • Save katydecorah/34054b8d241265d18c068fbf413056e3 to your computer and use it in GitHub Desktop.
Save katydecorah/34054b8d241265d18c068fbf413056e3 to your computer and use it in GitHub Desktop.
Draft emails in Gmail from a Google spreadsheet and a Google doc template: https://katydecorah.com/code/google-sheets-to-gmail-template/
// 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
}
@priceotm
Copy link

priceotm commented Oct 2, 2020

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?

@elena122112
Copy link

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

@kellyctex
Copy link

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment