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.

Google Sheets Sort By Color And Google Sheets Filter By Color, With Apps Script

Google Sheets added a native Sort By Color And Filter By Color feature on 11th March 2020.

Read more here in the G Suite update blog.

Below is my original Apps Script solution, left here for general interest.

Sort By Color And Filter By Color, Original Apps Script Solution

Excel has a handy feature that lets you sort and filter datasets by the cell color.

With a few simple lines of Apps Script, we can implement our own version.

Filter By Color in Google Sheets

This article will show you how to implement that same feature in Google Sheets.

It’s a pretty basic idea.

We need to know the background color of the cell we want to sort or filter with (user input 1). Then we need to know which column to use to do the sorting or filtering (user input 2). Finally we need to do the sort or filter.

So step one is to to prompt the user to input the cell and columns.

I’ve implemented this Google Sheets sort by color using a modeless dialog box, which allows the user to click on cells in the Google Sheet independent of the prompt box. When the user has selected the cell or column, we store this using the Properties Service for retrieval when we come to sort or filter the data.

Google Sheets Sort By Color

At a high level, our program has the following components:

  1. Custom menu to run the Google Sheets sort by color program
  2. Prompt to ask user for the color cell
  3. Save the color cell using the Properties Service
  4. Second prompt to ask the user for the sort/filter column
  5. Save the sort/filter column using the Properties Service
  6. Show the color and column choices and confirm
  7. Retrieve the background colors of the sort/filter column
  8. Add helper column to data in Sheet with these background colors
  9. Sort/Filter this helper column, based on the color cell
  10. Clear out the values in the document Properties store

Let’s look at each of these sections in turn.

Add A Custom Menu (1)

This is simply boilerplate Apps Script code to add a custom menu to your Google Sheet:

/**
 * Create custom menu
 */
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Color Tool')
        .addItem('Sort by color...', 'sortByColorSetupUi')
        .addItem('Clear Ranges','clearProperties')
        .addToUi();
}

Prompt The User For Cell And Column Choices (2, 4 and 6 above)

I use modeless dialog boxes for the prompts, which allows the user to still interact with the Sheet and click directly on the cells they want to select.

/**
 * Sort By Color Setup Program Flow
 * Check whether color cell and sort columnn have been selected
 * If both selected, move to sort the data by color
 */
function sortByColorSetupUi() {
  
  var colorProperties = PropertiesService.getDocumentProperties();
  var colorCellRange = colorProperties.getProperty('colorCellRange');
  var sortColumnLetter = colorProperties.getProperty('sortColumnLetter');
  var title='No Title';
  var msg = 'No Text';
  
  //if !colorCellRange
  if(!colorCellRange)  {
    title = 'Select Color Cell';
    msg = '<p>Please click on cell with the background color you want to sort on and then click OK</p>';
    msg += '<input type="button" value="OK" onclick="google.script.run.sortByColorHelper(1); google.script.host.close();" />';
    dispStatus(title, msg);
  }
  
  //if colorCellRange and !sortColumnLetter
  if (colorCellRange && !sortColumnLetter) {
      
      title = 'Select Sort Column';
      msg = '<p>Please highlight the column you want to sort on, or click on a cell in that column. Click OK when you are ready.</p>';
      msg += '<input type="button" value="OK" onclick="google.script.run.sortByColorHelper(2); google.script.host.close();" />';
      dispStatus(title, msg);
  }
  
  // both color cell and sort column selected
  if(colorCellRange && sortColumnLetter) {
    
    title= 'Displaying Color Cell and Sort Column Ranges';
    msg = '<p>Confirm ranges before sorting:</p>';
    msg += 'Color Cell Range: ' + colorCellRange + '<br />Sort Column: ' + sortColumnLetter + '<br />';
    msg += '<br /><input type="button" value="Sort By Color" onclick="google.script.run.sortData(); google.script.host.close();" />';
    msg += '<br /><br /><input type="button" value="Clear Choices and Exit" onclick="google.script.run.clearProperties(); google.script.host.close();" />';
    dispStatus(title,msg);
    
  }
}

/**
 * display the modeless dialog box
 */
function dispStatus(title,html) {
  
  var title = typeof(title) !== 'undefined' ? title : 'No Title Provided';
  var html = typeof(html) !== 'undefined' ? html : '<p>No html provided.</p>';
  var htmlOutput = HtmlService
     .createHtmlOutput(html)
     .setWidth(350)
     .setHeight(200);
 
  SpreadsheetApp.getUi().showModelessDialog(htmlOutput, title);

}

/**
 * helper function to switch between dialog box 1 (to select color cell) and 2 (to select sort column)
 */
function sortByColorHelper(mode) {
  
  var mode = (typeof(mode) !== 'undefined')? mode : 0;
  switch(mode)
  {
    case 1:
      setColorCell();
      sortByColorSetupUi();
      break;
    case 2:
      setSortColumn();
      sortByColorSetupUi();
      break;
    default:
      clearProperties();
  }
}

The buttons on the dialog boxes use the client-side google.script.run API to call server-side Apps Script functions.

Following this, the google.script.host.close() is also a client-side JavaScript API that closes the current dialog box.

Save The Cell And Column Choices In The Property Store (3 and 5)

These two functions save the cell and column ranges that the user highlights into the Sheet’s property store:

/** 
 * saves the color cell range to properties
 */
function setColorCell() {
  
  var sheet = SpreadsheetApp.getActiveSheet();
  var colorCell = SpreadsheetApp.getActiveRange().getA1Notation();
  var colorProperties = PropertiesService.getDocumentProperties();
  colorProperties.setProperty('colorCellRange', colorCell);

}

/**
 * saves the sort column range in properties
 */
function setSortColumn() {
  
  var sheet = SpreadsheetApp.getActiveSheet();
  var sortColumn = SpreadsheetApp.getActiveRange().getA1Notation();
  var sortColumnLetter = sortColumn.split(':')[0].replace(/\d/g,'').toUpperCase(); // find the column letter
  var colorProperties = PropertiesService.getDocumentProperties();
  colorProperties.setProperty('sortColumnLetter', sortColumnLetter);
  
}

As a result of running these functions, we have the color cell address (in A1 notation) and the sort/filter column letter saved in the Property store for future access.

Sorting The Data (7, 8 and 9 above)

Once we’ve selected both the color cell and sort column, the program flow directs us to actually go ahead and sort the data. This is the button in the third dialog box, which, when clicked, runs this call google.script.run.sortData();.

The sortData function is defined as follows:

/** 
 * sort the data based on color cell and chosen column
 */
function sortData() {
  
  // get the properties
  var colorProperties = PropertiesService.getDocumentProperties();
  var colorCell = colorProperties.getProperty('colorCellRange');
  var sortColumnLetter = colorProperties.getProperty('sortColumnLetter');

  // extracts column letter from whatever range has been highlighted for the sort column
  
  // get the sheet
  var sheet = SpreadsheetApp.getActiveSheet();
  var lastRow = sheet.getLastRow();
  var lastCol = sheet.getLastColumn();
  
  // get an array of background colors from the sort column
  var sortColBackgrounds = sheet.getRange(sortColumnLetter + 2 + ":" + sortColumnLetter + lastRow).getBackgrounds(); // assumes header in row 1
  
  // get the background color of the sort cell
  var sortColor = sheet.getRange(colorCell).getBackground();
  
  // map background colors to 1 if they match the sort cell color, 2 otherwise
  var sortCodes = sortColBackgrounds.map(function(val) {
    return (val[0] === sortColor) ? [1] : [2];
  });
  
  // add a column heading to the array of background colors
  sortCodes.unshift(['Sort Column']);
  
  // paste the background colors array as a helper column on right side of data
  sheet.getRange(1,lastCol+1,lastRow,1).setValues(sortCodes);
  sheet.getRange(1,lastCol+1,1,1).setHorizontalAlignment('center').setFontWeight('bold').setWrap(true);
  
  // sort the data
  var dataRange = sheet.getRange(2,1,lastRow,lastCol+1);  
  dataRange.sort(lastCol+1);
  
  // add new filter across whole data table
  sheet.getDataRange().createFilter();

  // clear out the properties so it's ready to run again
  clearProperties();
}

And finally, we want a way to clear the properties store so we can start over.

Clear The Property Store (10 above)

This simple function will delete all the key/value pairs stored in the Sheet’s property store:

/**
 * clear the properties
 */
function clearProperties() {
  PropertiesService.getDocumentProperties().deleteAllProperties();
}

Run The Google Sheets Sort By Color Script

If you put all these code snippets together in your Code.gs file, you should be able to run onOpen, authorize your script and then run the sort by color tool from the new custom menu.

Here’s the sort by color tool in action in Google Sheets:

Google Sheets sort by color

You can see how all of the green shaded rows are sorted to the top of my dataset.

Note that this sort by color feature is setup to work with datasets that begin in cell A1 (because it relies on the getDataRange() method, which does the same).

Some improvements would be to make it more generalized (or prompt the user to highlight the dataset initially). I also have not included any error handling, intentionally to keep the script as simple as possible to aid understanding. However, this is something you’d want to consider if you want to make this solution more robust.

Google Sheets Sort By Color Template

Here’s the Google Sheet template for you to copy.

(If you’re prompted for permission to open this, it’s because my G Suite domain, benlcollins.com, is not whitelisted with your organization. You can talk to your G Suite administrator about that. Alternatively, if you open this link in incognito mode, you’ll be able to view the Sheet and copy the script direct from the Script Editor.)

If GitHub is your thing, here’s the sort by color code in my Apps Script repo on GitHub.

Google Sheets Filter By Color

The program flow is virtually identical, except that we filter the data rather than sort it. The code is almost exactly the same too, other than variable names being different and implementing a filter instead of a sort.

Rather than sorting the data, we create and add a filter to the dataset to show only the rows shaded with the matching colors:

Filter By Color in Google Sheets

The filter portion of the code looks like this:

// remove existing filter to the data range
if (sheet.getFilter() !== null) {
  sheet.getFilter().remove();
}

// add new filter across whole data table
var newFilter = sheet.getDataRange().createFilter();

// create new filter criteria
var filterCriteria = SpreadsheetApp.newFilterCriteria();
filterCriteria.whenTextEqualTo(filterColor);

// apply the filter color as the filter value
newFilter.setColumnFilterCriteria(lastCol + 1, filterCriteria);

If you want a challenge, see if you can modify the sort code to work with the filter example.

Google Sheets Filter By Color Template

Feel free to copy the Google Sheets filter by color template here.

(If you’re prompted for permission to open this, it’s because my G Suite domain, benlcollins.com, is not whitelisted with your organization. You can talk to your G Suite administrator about that. Alternatively, if you open this link in incognito mode, you’ll be able to view the Sheet and copy the script direct from the Script Editor.)

Or pull the code directly from the GitHub repo here.

Add-On

Lastly, there is an add-on, called Sort Range Plus, which allows you to do a sort by color. I have not used it however.