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
}
@derekaug
Copy link

derekaug commented Nov 5, 2019

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

@kmb11
Copy link

kmb11 commented Jun 30, 2020

@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.

@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