-
-
Save katydecorah/34054b8d241265d18c068fbf413056e3 to your computer and use it in GitHub Desktop.
| // 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 | |
| } |
@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
This just saved the non-profit I'm a part of a ton of time / money. Thank you for this!