Mail merge using draft emails

In this post, we’re going to look at creating a simple mail merge program, which will use a draft email as the basis of the final email that will be sent out. The idea is to have a program which is flexible enough to send different messages out and that can be used by admin staff without any need to touch the underlying code. All they need to do is create a draft email and to add the columns they need to the data sheet.

A lot of the email sending programs I’ve written in the past, have had the email written into the program in HTML and CSS, which is fine to send one specific type of email but if there are any changes needed, the users needed to ask me to update the code, which isn’t the most efficient way to work.

The program will run from a Google Sheet and from a sheet of data. It uses the active sheet, so multiple data sheets can be made if sending out different emails.

On the sheet, cell A2 is for the subject line of the draft email. This is how the program will use the correct draft. It’s possible to add a placeholder here too, for example the student’s name. To help avoid emails going to spam, I’d recommend adding a personalized subject line.

The data starts from row 3. There is a column for the students’ names, the emails and a status column. Note, the program is flexible enough to add other columns of data, which you may or may not want to include in the emails.

Note, the status column always needs to be the last column on the sheet.

You will need to write and save a draft email. The placeholders need to be in the form of text in between two curly brackets, i.e. {{placeholder}}. You could change this if you wanted, but you’ll need to change the format it looks for in the code.

It’s essential that the placeholders you add to the draft match with the column headers on the data sheet. For example, the name column is called {{NAME}} and in the draft email it’s also called {{NAME}}.

All we need now is a bit of code to send the personalized emails.

Code

The main function is called getDataAndSendEmails and within it, it will call the getDraft and sendEmail functions.

1.	function getDataAndSendEmails() {
2.	  //Get data and subject placeholder
3.	  const ss = SpreadsheetApp.getActiveSpreadsheet();
4.	  const sh = ss.getActiveSheet();
5.	  let subject = sh.getRange(2, 1).getValue();
6.	  let subjectPH = "";
7.	  let rSubject;
8.	  let body;
9.	  const lastColumn = sh.getLastColumn();
10.	  const [header, ...data] = sh.getRange(3, 1, sh.getLastRow() - 2, lastColumn)
11.	                              .getValues();

L1: Open the function.

L3: Get the active spreadsheet.

L4: Get the active sheet

L5: Get the subject line from sheet

L6-8: Set up the variables subjectPH (to store the placeholder in the email subject line, if there is one), rSubject (to store the subject line with the placeholder replaced), body (email body).

L9: Get the last column.

L10-11: Get the data from the sheet from row 3 and split it into the header (first row) and the rest of the data by deconstructing the array and using the rest parameter (…).

13.	//Extract subject placeholder if there is one
14.	const subjectPH1 = subject.match(/\{\{\w+\}\}/);
15.	if (subjectPH1 !== null) {
16.	  subjectPH = subjectPH1[0];
17.	}
18.	else {
19.	  rSubject = subject;
20.	}

L14: Extract the subject line placeholder (if there is one) using a bit of RegEx. This looks for a word inside double curly brackets.

L15: If the above line doesn’t find a placeholder it will return null. So, we need to check for that before we try to extract the placeholder further.

L16: If it isn’t null, we get the first element in the subjectPH1 variable.

L17: Close the if statement.

L18-20: If it is null, then we just set the subject line to the variable rSubject.

22.	//Loop thru data, replace body placeholders
23.	data.forEach((row, r) => {
24.	  body = getDraft(body, subject);
25.	
26.	  //Replace body placeholders
27.	  header.forEach((hCol, h) => {
28.	
29.	  if (hCol.includes("{{") && hCol !== "{{EMAIL}}" && hCol !== "{{email}}") {
30.	
31.	  var rg = new RegExp(hCol, "g");
32.	  body = body.replace(rg, row[h]);

L23: Now, loop through the data you want to send.

L24: Call the getDraft function to get the draft we want to use in our Gmail. Pass the two arguments. Then store the body of that email that is returned by the function.

L27: Now, loop through the headers to replace any placeholders that are in the draft email.

L29: Look for headers with a double curly bracket and ignore any called {{EMAIL}} (or {{email}}).

L31: I want the script to be able to replace all instances of the placeholder, in case the same placeholder is used more than once in the draft email. We do that by creating a new regular expression with the current cell value and combining it with the global expression g.

L32: Replace the regular expression with the current cell value in the loop.

34.	  if (hCol === subjectPH) {
35.	    rSubject = subject.replace(subjectPH, row[h]);
36.	    };
37.	  };
38.	
39.	  if (hCol === "{{EMAIL}}") {
40.	    email = row[h];
41.	  };
42.	});
43.
44.	//Send email
45.	sendEmail(body, email, rSubject);
46.

L34:  Check to see if the header equals the subject line placeholder.

L35: If it does, replace the placeholder with the current cell value.

L36-37: Close the if statements.

L39: Check to see if the header is {{EMAIL}}.

L40: If it is, sent the email address in the cell to the email variable.

L41-42: Close the if and header forLoop.

L45: Call the sendEmail function to send the personalised email. Pass the three arguments.

47.       //Update status on EMAILS sheet
48.	  let rw = r + 4;
49.	  sh.getRange(rw, sh.getLastColumn())
50.	    .setValue("SENT").setBackground('#93c47d');
51.	  SpreadsheetApp.flush();
52.	  if (r % 5) {
53.	    Utilities.sleep(1000);
54.	  };
55.	 });
56.	}
57.	

L48: Let’s get the current row number by adding two to the loop number.

L49-50: Get the last column of the current row and set the text and background colour.

L51: Flush the spreadsheet to update the changes on the sheet. This slows the program down a little, and is optional, but I’ve left it in as one of my colleagues likes to see the progress of the program and see which emails have been sent out.

L52-54: I’ve added an optional delay here, as this can help with emails not being identified as spam. It checks to see if the loop number is divisible by 5, and if so, will pause for 1 second. This effectively sends the emails out in mini-batches of 5. This obviously slows the program down, and you may want to remove this part.

L55-56: Close the data forLoop and the function.

58.	function getDraft(body, subject) {
59.	  const drafts = GmailApp.getDrafts();
60.	
61.	  drafts.forEach((draft) => {
62.	  let draftId = draft.getId();
63.	  let draftById = GmailApp.getDraft(draftId);
64.	  let msg = draftById.getMessage();
65.	  let draftSubject = msg.getSubject();
66.	
67.	  if (draftSubject === subject) {
68.	    body = msg.getBody();
69.	  };
70.	 });
71.	 return body;
72.	}
73.	

L58: Open the getDraft function.

L59: Get all the draft emails in your Gmail.

L61: Loop through the drafts to look for the one you need to send.

L62: Get the draft ID.

L63: Get the draft email by its ID.

L64: Get the message in that draft.

L65: Get the subject line of that message.

L67: We can now see if this is the draft we need by checking if the subject line of the draft is the same as the subject line we entered on the SUBJECT sheet.

L68: If it is, we get the body of the message.

L69-70: Close the if and forLoop.

L71: Return the body back to the main function on line 16.

L72: Close the function.

74.	function sendEmail(body, email, rSubject) {
75.	  GmailApp.sendEmail(email,
76.	  rSubject, "",
77.	  { htmlBody: body });
78.	}

L74: Open the sendEmail function.

L75-76: Use the GMailApp to send the email and pass the email address, the subject line with the placeholder replaced, and third parameter (the body), leave blank, as we’ll add it as an HTML email.

L77: Add the htmlBody option and pass the body.

L78: Close the function.

Now we need a menu to run the program from.

1.	function onOpen() {
2.	  const ui = SpreadsheetApp.getUi();
3.	  ui.createMenu('ADMIN') 
4.	    .addItem('Send emails', 'getDataAndsendEmails')
5.	    .addToUi();
6.	}

L1: Open the onOpen function.

L2: Get the spreadsheet UI.

L3: Create a menu called ADMIN.

L4: Add a menu item and call the getDataAndSendEmails function from it.

L5-6: Add it to the UI and close the function.

Add the data you want on the data sheet. Then click ADMIN and click “Send emails”. The first time you will need to authorize the script.

As you will see it sends the emails and updates the status on the sheet.

Click HERE to make a copy of the document that contains a copy of the code.

Taken from my book Google Apps Script Projects 2, available on Amazon.

a