By @mhawksey

A bulk email/mail merge with Gmail and Google Sheets solution evolution using V8

Last year I had a ‘mail merge using Gmail and Google Sheets’ contribution accepted on the G Suite Developers Solution Gallery. Just over 6 months on there has been lots of useful feedback and requests for solutions to some of the limitations of the original published script. In the meantime Google has also made the new V8 runtime for Google Apps Script generally available. Given this it seemed appropriate to revisit the original solution updating it for V8 as well as covering the most commonly asked for changes. In this post I’ll highlight some of the coding changes as well as some easy modifications. 

To get started if not already you can follow these setup instructions:

Setup

  1. Create a copy of the sample Gmail/Sheets Mail Merge spreadsheet.
  2. Update the Recipients column with email addresses you would like to use in the mail merge
  3. Create a draft message in your Gmail account using markers like {{First name}}, which correspond to column names, to indicate text you’d like to be replaced with data from the copied spreadsheet.
  4. In the copied spreadsheet, click on custom menu item Mail Merge > Send Emails.
  5. A dialog box will appear and tell you that the script requires authorization. Read the authorization notice and continue.
  6. When prompted enter or copy/paste the subject line used in your draft Gmail message and click OK
  7. The Email Sent column will update with the message status.

Next steps and easy modifications

Additional columns can be added to the spreadsheet with other data you would like to use. Using the {{}} annotation and including your column name as part of your Gmail draft will allow you to include other data from your spreadsheet. If you change the name of the Recipient or Email Sent columns this will need to be updated by opening Tools > Script Editor.

Advanced Send Parameters (cc, bcc, sender name/from, replyTo)

As part of the GmailApp.sendMail() method there are advanced options for sending emails with cc, bcc and more. These parameters have been included in the shared script but commented out. For example, to change the name the email is from you would uncomment* the name parameter and add a value: 

*To uncomment remove the // at the start of the line

// @see https://developers.google.com/apps-script/reference/gmail/gmail-app#sendEmail(String,String,String,Object)
// if you need to send emails with unicode/emoji characters change GmailApp for MailApp
// Uncomment advanced parameters as needed (see docs for limitations)
GmailApp.sendEmail(row[RECIPIENT_COL], msgObj.subject, msgObj.text, {
    htmlBody: msgObj.html,
    // bcc: 'a.bbc@email.com',
    // cc: 'a.cc@email.com',
    // from: 'an.alias@email.com',
    // name: 'name of the sender',
    // replyTo: 'a.reply@email.com',
    // noReply: true, // if the email should be sent from a generic no-reply email address (not available to gmail.com users)
    attachments: emailTemplate.attachments
});

More details on the relevant advanced parameters, limitations and usage from the official documentation is copied below:  

NameTypeDescription
bccStringa comma-separated list of email addresses to BCC
ccStringa comma-separated list of email addresses to CC
fromStringthe address that the email should be sent from, which must be one of sender’s account aliases.
nameStringthe name of the sender of the email (default: the user’s name)
noReplyBooleantrue if the email should be sent from a generic no-reply email address to discourage recipients from responding to emails; this option is only possible for G Suite accounts, not gmail.com users
replyToStringan email address to use as the default reply-to address (default: the user’s email address)

Sending emails with emoji/unicode

If you like to include a little bit of 🤪 or 📈 in your emails a feature (or bug) of GmailApp is that it can’t handle most of the modern unicode/emoji characters (details in this SO thread). Fortunately, there is an easy solution by switching GmailApp to MailApp by opening the script editor in your copy of the solution and changing line 90 from:

GmailApp.sendEmail(row[RECIPIENT_COL], msgObj.subject, msgObj.text, {

to 

MailApp.sendEmail(row[RECIPIENT_COL], msgObj.subject, msgObj.text, {

If you are wondering why the script doesn’t just use MailApp while the MailApp advanced parameters are similar there is no from parameter and from personal experience we use from more than emojis.

Sending emails to filtered rows

There are various ways to detect if a row has been hidden by a sheet filter. One method is isRowHiddenByFilter() which uses Google Sheets filter. If you would like to modify this solution so only emails are sent to rows that are visible when filtered you can change line 83 from:

if (row[EMAIL_SENT_COL] == ''){

to (this assumes the header row is only row 1):

if (row[EMAIL_SENT_COL] == '' && !sheet.isRowHiddenByFilter(rowIdx+2)){

As this method is potentially expensive in terms of script run time an alternative approach is using the Advanced Sheets Service to make a batch call to the Sheets.Spreadsheets.get method that gets all the hidden rows, which can then used this to supplement the source data:

/**
 * Add hidden row identifier to sheet data.
 * @see https://sites.google.com/site/scriptsexamples/learn-by-example/google-sheets-api/filters#TOC-Get-filtered-rows
 * @see https://tanaikech.github.io/2019/07/28/retrieving-values-from-filtered-sheet-in-spreadsheet-using-google-apps-script/
 * @param {string} ssId of the spreadsheet
 * @param {string} range of the sheet
 * @param {Object} sourceData of sheet as object
 * @return {Array} of data with hidden row identifier.
 */
function addFilteredRows_(ssId, range, sourceData) {
  // limit what's returned from the API
  const fields = "sheets/data/rowMetadata/hiddenByFilter";
  // make Sheets API call
  const sheet = Sheets.Spreadsheets.get(ssId, {
    fields: fields,
    ranges: [range]
  }).sheets[0];
  // get the row metadata
  const data = sheet.data[0].rowMetadata;
  // update sourceData with hidden row status
  data.map((ar, i) => {
    (ar.hiddenByFilter) ? sourceData[i].hidden = true: sourceData[i].hidden = false;
  });
  return sourceData;
}

A variant of this solution that implements addFilteredRows_() is available in this commit.

Scheduling/triggering bulk emails

If you’d like to send bulk emails at a scheduled time or trigger sending based on another event the following functions can be copied into the code in your copy of the sheet via Tools > Script editor. The sendScheduledEmails_() function allows you to define the sheet and email draft subjectLine you want to use. 

If you’d also like to then schedule the send you can either use the Edit > Current project triggers dialog in the Script Editor or edit and run the setupScheduledSend() function.

/**
 * The sheet and email draft to send
 */
function sendScheduledEmails_() {
 const sheet = SpreadsheetApp.getActive().getSheetByName('Sheet2'); // <-- Insert sheet name to use data from
 const subjectLine = 'Your personal discount code {{Discount amount}}'; // <-- Insert email draft subject line
 sendEmails(subjectLine, sheet);
}
/**
 * Create a script trigger to run sendScheduledEmails_
 */
function setupScheduledSend() {
 const triggerDay = new Date('April 17, 2020 23:20:00'); // <-- date/time to send emails
 ScriptApp.newTrigger("sendScheduledEmails_")
  .timeBased()
  .at(triggerDay)
  .create();
}

Coding changes

As well as some V8 runtime syntax changes when revisiting the original mail merge solution code there were a couple of common requests I thought worth incorporating, plus some areas where I felt the code could be refactored. As some of these code changes might be useful in your own projects I’m highlighting them below: 

Using formatted cell values for currencies, dates and more

Thanks to a comment from Andrew Roberts this solution has been updated to use the SpreadsheetApp service call to .getDisplayValues(), which “takes into account date, time and currency formatting, including formats applied automatically by the spreadsheet’s locale setting”. For reference, the original Sheets.Spreadsheets.Values.get method is included below:

When using the SpreadsheetApp service and calling .getValues() the data is returned as either Number, String or Date. For example, if cells are formatted with currencies these are converted to numbers and dates turned into a Date object. The solution was to switch to the Advanced Sheets Service, which defaults to the formatted cell values when calling Sheets.Spreadsheets.Values.get

// Fetch values for each row in the Range
// @see https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/get
const data = Sheets.Spreadsheets.Values.get(SpreadsheetApp.getActive().getId(), sheet.getName()).values;

Cleaner token replacement

I was never happy with the token replacement used in the original solution and when I came across this efficient JavaScript string replacement snippet on Stack Overflow it was easy to modify for the {{}} notation used in this mail merge solution: 

/**
Fill template string with data object
@see https://stackoverflow.com/a/378000/1027723
@param {string} template string containing {{}} markers which are replaced with data
@param {object} data object used to replace {{}} markers
@return {object} message replaced with data
*/
function fillInTemplateFromObject_(template, data) {
  // we have two templates one for plain text and the html body
  // stringifing the object means we can do a global replace
  let template_string = JSON.stringify(template);
  // token replacement
  template_string = template_string.replace(/{{[^{}]+}}/g, key => {
    return data[key.replace(/[{}]+/g, "")] || "";
  });
  return JSON.parse(template_string);
}

Summary

Hopefully you find this solution useful. Please keep in mind that this script is designed to be a sample solution that demonstrates how you can solve common problems with G Suite. You are welcome to build and modify the shared code as you need.  Feel free to reach out to me if you have any questions.

Exit mobile version