Last active
March 27, 2021 19:28
-
-
Save katydecorah/e956c783965e65f1e53b2b2b1f3a22e0 to your computer and use it in GitHub Desktop.
Draft emails in Gmail from a Google spreadsheet https://katydecorah.com/code/google-sheets-to-gmail/
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
var EMAIL_DRAFTED = "EMAIL DRAFTED"; | |
function draftMyEmails() { | |
var sheet = SpreadsheetApp.getActiveSheet(); // Use data from the active sheet | |
var startRow = 2; // First row of data to process | |
var numRows = sheet.getLastRow() - 1; // Number of rows to process | |
var lastColumn = sheet.getLastColumn(); // Last column | |
var dataRange = sheet.getRange(startRow, 1, numRows, lastColumn) // Fetch the data range of the active sheet | |
var data = dataRange.getValues(); // Fetch values for each row in the range | |
// Work through each row in the spreadsheet | |
for (var i = 0; i < data.length; ++i) { | |
var row = data[i]; | |
// Assign each row a variable | |
var clientName = row[0]; // Col A: Client name | |
var clientEmail = row[1]; // Col B: Client email | |
var veg = row[2]; // Col C: Vegetable name | |
var vegDesc = row[3]; // Col D: Vegetable description | |
var emailStatus = row[lastColumn - 1]; // Col E: Email Status | |
// Prevent from drafing duplicates and from drafting emails without a recipient | |
if (emailStatus !== EMAIL_DRAFTED && clientEmail) { | |
// Build the email message | |
var emailBody = '<p>Hi ' + clientName + ',<p>'; | |
emailBody += '<p>We are pleased to match you with your vegetable: <strong>' + veg + '</strong><p>'; | |
emailBody += '<h2>About ' + veg + '</h2>'; | |
emailBody += '<p>' + vegDesc + '</p>'; | |
emailBody += '<p>' + clientName + ', we hope that you and ' + veg + ' have a wonderful relationship.<p>'; | |
// Create the email draft | |
GmailApp.createDraft( | |
clientEmail, // Recipient | |
'Meet your vegetable', // Subject | |
'', // Body (plain text) | |
{ | |
htmlBody: emailBody // Options: Body (HTML) | |
} | |
); | |
sheet.getRange(startRow + i, lastColumn).setValue(EMAIL_DRAFTED); // Update the last column with "EMAIL_DRAFTED" | |
SpreadsheetApp.flush(); // Make sure the last cell is updated right away | |
} | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
How would you go about adding a signature with an image to the bottom of an email? Image part is what i'm struggling with. I've tried and few different things and can't seem to get it nailed down. Love this by the way. So useful! Thank you!