Gmail Mail Merge For A Specific Label With Apps Script

Every Monday I send out a Google Sheets tip email and occasionally I’ll include a formula challenge.

I posted Formula Challenge #3 — to alphabetize a string of words separated by commas using a single formula — in January 2020 and had over 150 replies!

It would have been too time consuming to reply to all 150 responses manually from my inbox.

Since 95% of all my replies would be the same (a thank you and the formula solution) it was a perfect case for automation.

And Apps Script is designed for automation in G Suite.

(The solution was essentially a mash up of this post on extracting email addresses in Gmail and this post on reply to Google Form solutions quickly with Apps Script.

Gmail Mail Merge Script Outline

  1. Make sure all of the emails are labeled correctly in Gmail (you can use a filter to do this).
  2. Then use Apps Script to extract the solution responses into a Sheet with names and emails addresses.
  3. Categorize each row of data (i.e. each email) into 3 or 4 different categories, e.g. “Correct”, “Correct but…” etc.
  4. Next, create a reply template for each of these categories, to say thank you for taking part and also sharing any feedback.
  5. Then use a simple VLOOKUP formula to add a reply to each row, based on the category.
  6. Following that, use Apps Script to create draft emails for everyone in the Sheet (the Gmail Mail Merge part).
  7. The last part is manual: a quick check of original email and response, add any customization and then press SEND.

Part 1: Extract Gmail Emails To Google Sheet With Apps Script

Assuming all your emails are labeled, so that they’re all together in a folder, you can use Apps Script to search for this label and extract the messages into a Google Sheet.

Search for the messages under this label with the search query method from the GmailApp service. This returns an array of Gmail threads matching this query.

Retrieve all the messages with the getMessagesForThreads() method.

From this array of messages, extract the From field and the body text.

The From field takes the form:

Ben Collins <test@example.com>

Parse this with a map function, which creates a new array out of the original array where a function has been applied to each element. In this case, the function parses the From field into a name and email address using regular expression.

Finally, this new array, containing the Name, Email Address and Message Body, is returned to whichever function called the extractEmails() function.

Here’s the code:

function extractEmails() {
  
  // define label
  var label = 'marketing-formula-challenge-#3';
  
  // get all email threads that match label from Sheet
  var threads = GmailApp.search("label:" + label);
  
  // get all the messages for the current batch of threads
  var messages = GmailApp.getMessagesForThreads(threads);
  
  var emailArray = [];
  
  // get array of email addresses
  messages.forEach(function(message) {
    message.forEach(function(d) {
      emailArray.push([d.getFrom(),d.getPlainBody()]);
    });
  });
  
  // parse the From field
  var parsedEmailArray = emailArray.map(function(el) {
    var name = "";
    var email = "";
    var matches = el[0].match(/\s*"?([^"]*)"?\s+<(.+)>/);
    
    if (matches) {
      name = matches[1]; 
      email = matches[2];
    }
    else {
      name = "N/k";
      email = el;
    }
    
    return [name,email,"'"+el[1]];
  });
  return parsedEmailArray;
}

To paste into the Google Sheet, I created this function, which actually calls the extractEmails() function on line 8 to retrieve the email data:

function pasteToSheet() {
  
  // get the spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();  
  
  // get email data
  var emailArray = extractEmails();
  
  // clear any old data
  sheet.getRange(2,1,sheet.getLastRow(),4).clearContent();
  
  // paste in new names and emails and sort by email address A - Z
  sheet.getRange(2,1,emailArray.length,3).setValues(emailArray);
  
}

Running this pasteToSheet() function creates a Google Sheet with the Name, Email Address and Message Body in columns A, B and C:

Gmail Mail Merge Google Sheet

Now review each row and assign a category. You want to have enough categories to catch the main differences in responses but not too many that it becomes manual and tedious (which we’re trying to get away from!).

For example, in this formula challenge, I had these four categories:

Correct, Extra Transpose, Other, N/a

Part 2: Create Reply Templates In Google Sheets

In a different tab (which I called “Reply Templates”), create your reply templates. These are the boilerplate replies for each generic category.

Gmail Mail Merge Reply Templates

Then use a standard VLOOKUP to add one of these reply templates to each row, based on the category:

=VLOOKUP(D2,'Reply Templates'!$A$1:$B$6,2,FALSE)

The Sheet now looks like this (click to enlarge):

Gmail Mail Merge Vlookup

Part 3: Create Draft Replies For Gmail Mail Merge

The final step is to create draft Gmail replies for each email in your Sheet, and then send them after a quick review.

This function retrieves the extracted email data from the Sheet, then searches for them in the label folder. It creates a draft reply for each email with the reply template response from the Sheet data.

function createDraftReplies() {
  
  // grab the email addresses from Google Sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var data = sheet.getRange(2,1,sheet.getLastRow(),7).getValues();
    
  // loop over them, find mnost recent email under that label for that email address
  data.forEach(function(arr) {
    
    if (arr[6] === "") {
      var emailAddress = arr[1];
      var reply = arr[5];
      var threads = GmailApp.search('label:marketing-formula-challenge-#3 from:' + emailAddress)[0];
      var message = threads.getMessages()[0];
      message.createDraftReply(reply);
    }
    
  });
}

When the script has finished running, all of the emails in this label folder will have a draft reply.

Review them, customize them if needed and press Send! 📤

Gmail Mail Merge Notes

1) I could have used the reply method of the GmailApp service to automatically send replies and skip the draft review process. This would be useful if reviewing each draft was too time consuming at scale.

2) I did not include any error handling in this script.

This was deliberate because I was creating a one-use-and-done solution so I wanted to move as quickly as possible. This is one of the strengths of Apps Script. You can use it to create quick and dirty type of solutions to fill little gaps in your workflow. If the problem is specific enough, and not intended to be used elsewhere, you don’t need to worry too much about error handling and edge cases.

3) Lastly, be aware of Apps Script quotas when sending emails automatically with Apps Script. It’s 100 for consumer plans and 1,500 for G Suite (Business and Education).

Formula Challenge #3: Alphabetize Comma-Separated Strings

(This Formula Challenge originally appeared as Tip #85 in my weekly Google Sheets Tips newsletter, on 20 January 2020.

Sign up here so you don’t miss out on future Formula Challenges!

Find all the Formula Challenges archived here.)

The Challenge

Start with a list of words in a single cell, separated by commas and not in alphabetical order, like so:

Epsilon,Alpha,Gamma,Delta,Beta

Formula Challenge 3

Your challenge is to create a single formula (i.e. in a single cell) that reorders this list into alphabetical order.

Step 1

Use the SPLIT function to separate the comma-delimited string into separate cells.

=SPLIT(A1,",")

(Split has two additional arguments and you have to be precise with your delimiter. In this simple example, we can omit the two additional arguments. See here for more info on the nuances of the SPLIT function.)

Step 2

Use the TRANSPOSE function to change from row orientation to a column orientation, so that we can sort in Step 3.

=TRANSPOSE(SPLIT(A1,","))

Step 3

Sort the data with the SORT function!

You don’t need to specify a column or direction, because we only have 1 column and we want ascending order, which is the default order. This keeps our formula brief.

=SORT(TRANSPOSE(SPLIT(A1,",")))

Step 4

Finally, join the column back together with the JOIN function, again using a comma as the delimiter.

There’s no need to use a second transpose because the JOIN function works with a column of data just as easily as a row of data!

=JOIN(",",SORT(TRANSPOSE(SPLIT(A1,","))))

Bingo!

Formula Challenge 3 Solution

Community Solutions

I had over 150 responses to this formula challenge, and most came up with this same formula. It confirmed what I thought that there’s no shorter way to do it.

If you want to see how I used Apps Script to help me reply to these 150 emails, check out this article: Gmail Mail Merge For A Specific Label

Don’t Miss Future Formula Challenge!

There are more formula challenges in the pipeline.

If you want to get involved and up your Google Sheets game whilst you exercise the ‘ole grey matter, consider signing up to my Google Sheets Tips newsletter.