Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.