Skip to content

Instantly share code, notes, and snippets.

@katydecorah
Last active March 27, 2021 19:28
Show Gist options
  • Star 5 You must be signed in to star a gist
  • Fork 2 You must be signed in to fork a gist
  • Save katydecorah/e956c783965e65f1e53b2b2b1f3a22e0 to your computer and use it in GitHub Desktop.
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/
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
}
}
}
@flanaganmediava
Copy link

flanaganmediava commented Jan 17, 2021

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!

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