Gmail (template) from Spreadsheet

 https://katydecorah.com/code/google-sheets-to-gmail-template/

code.gs a l'script del Spreadsheet

// 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
	}

Skip to content

July 30, 2019

Create emails from a Google spreadsheet and Google doc template

Create emails from a Google spreadsheet and Google doc template

Last year I wrote about how to create emails from a Google spreadsheet. I’m going to show you how to simplify the process by creating your email template in a Google doc.

Find your variables

Look at your spreadsheet and find the variables that you will use in your template. These variables are the first row of your table. In the example below, my variables are Name, Email, Type, and Recipe.

💡 Make sure your spreadsheet header has unique titles.

Write your email template

Write your email template in a Google doc. Replace the words that you want to personalize with data from your spreadsheet with their variable name wrapped in curly brackets. For example, “Hi Katy” becomes “Hi {Name}”.

Get your Google doc ID

Look at the URL for your Google doc and copy the sequence of letters and numbers after /d/ and until the next /. See the highlighted string in the URL bar in the image below. This is your document’s ID, you’ll need it later.

Open the Script Editor in Google Sheets

Open up the tab of a Google spreadsheet that has your data.

1. Click Tools > Script editor... from the toolbar.
2. Replace the code in the Code.gs tab with the following script.
3. Edit the first few lines of the script:
   • googleDocId - Paste in your Google doc’s ID number that you found earlier.
   • emailField - Enter the column header name that holds the email addresses, for me, it’s Email.
   • emailSubject - Enter the subject line you want to send. You can use variables from your spreadsheet or keep it static.
   • emailStatus - Enter the column header name that will serve as a status indicator that your email was drafted, for me, it’s Date drafted. This will prevent duplicates if you run the script again.
4. Click the save icon or click File > Save from the toolbar.
5. Enter a project name, such as “Email drafter”.

Run the script

When you’re ready to draft your emails:

1. Click the Select Function dropdown and select draftMyEmails.
2. Click the play button and authenticate your account.
   • A window should appear, click “Review Permissions.” Select the account you want to authenticate.
   • You may get a warning since this isn’t an official script. Review the code to get an idea of how it works and how it will interact with your account. Once you feel comfortable, from the warning screen, click “Advanced” then “Go to Email drafter (unsafe)” (or whatever you named your script). This will get you through to the next screen. Click “Allow” to grant the script access to the stated parts of your account.
3. Check your spreadsheet, the column you indicated as your emailStatus should have today’s date.
4. Open Gmail and check your drafts folder. You should see emails that are ready for you to send.

If something didn’t send properly, double check that your spreadsheet column rows match the variables in your Google doc exactly.

---

Did you enjoy this post? Support The Human Utility. The Human Utility provides assistance to families and makes sure they always have running water at home.

Katy DeCorah is a frontend engineer living in upstate New York. She works on the documentation team at Mapbox and tinkers at doublegreat.dev.