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.
Mail merge steps

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: '[email protected]',
    // cc: '[email protected]',
    // from: '[email protected]',
    // name: 'name of the sender',
    // replyTo: '[email protected]',
    // 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.

35 Comments


  1. Thank you, this is simple & great, just what I was looking for. And came just at the right time 🙂

    Reply

  2. Thanks Martin. You are a lifesaver, this has solved a massive problem for me, great work!!

    Reply

  3. Very nice. I’m sure that is going to come in very handy.

    Re. “Using formatted cell values for currencies, dates and more”. You can use SpreadsheetApp.getDisplayValues() to avoid having to go into the Advanced service.

    I spent many an hour formatting the values before I discovered that function – although I didn’t know you could do it with the Advanced service too.

    Another thing I’ve come up against was missed case-differences in the placeholders, so you can use (i) to make them case-insensitive (https://stackoverflow.com/questions/3939715/case-insensitive-regex-in-javascript).

    This is my take on the popular mail merge everyone should have in their store cupboard: http://www.andrewroberts.net/2014/10/google-apps-script-create-pdf/.

    Reply

    1. … not sure how I missed .getDisplayValues() and this change will make this a lot cleaner. With this I think it makes sense to switch to just using SpreadsheetApp to limit the scopes.

      Thanks also for highlighting how to make placeholders case-insensitive. Personally, I prefer having them case sensitive but other might want to make this mod.

      Merging for doc is also a common use case I see so thanks for sharing the link to your post :)

      Reply

      1. If we merge your mail merge and my doc merge we could have the best open source “merge” script out there!

        Reply

  4. Really useful tool, but inline images get converted to attachments – any way to avoid this?

    Reply

    1. Hi Chris – I think this might be how images are added to the draft email. When copy/paste images from another source they should be included inline for each email. If not let me know and I can investigate further

      Reply

      1. I tried several times, yet everytime the images gets converted into attachments

        1. Tried Drag and drop keeping inline
        2. Tried uploading
        3. Tried Copy paste

        Reply

        1. I am experiencing the same behaviour – inline images aren’t show in the email either – only the file name is shown. Any news on a fix for this?

          Reply

  5. Data Privacy when using this mail merge solution

    I’ve had a couple of people ask about privacy/security with this published solution and here is an explaination:

    This solution uses Google Apps Script which is a scripting language that runs on Google servers https://www.google.com/script/start/

    Apps Script can be used by developers to build 3rd party Add-ons for Docs, Sheets, Slides and Forms. There are add-ons for Google Sheets which can be used for mail merges https://gsuite.google.com/marketplace/search/mail%20merge

    With these add-ons users can’t see the Apps Script code that runs them and potentially they can pass the data you use via their own servers. As a layer of security Google has a verification process which requires 3rd party developers to publish a privacy policy.

    The mail merge in the solutions gallery is different in that it’s not shared as an add-on. Instead all the Apps Script code used for this application is attached to the copy of the Google Sheet you make in the setup instructions. The Apps Script code is attached to the copy of the Google Sheet and I as the original sheet owner don’t have access to either the copied Google Sheet or the copy of the Apps Script code attached to it.

    In the copy of the solution you can see all the code by opening Tools > Script editor… I appreciate that not being a developer it might not make much sense hopefully it gives you some comfort to see the code that is being executed. A test you can do is make another copy (copy B) of the original copied solution (copy A). If you edit the code in the Script editor for copy A and save it, when you open copy B the script is unchanged (this means when you run this solution there is no way for me as a the original developer to change the code and do something malicious).

    In terms of this solution and where the data goes, as Google Apps Script runs on Google servers it remains in your Google account, the script essential runs as if you were at the keyboard (see Script Authorisation docs https://developers.google.com/apps-script/guides/services/authorization). In terms of Apps Script additional terms these are also available from Google’s website https://developers.google.com/apps-script/terms.

    The last couple of sentences are useful guidance “Using or importing any Script or Add-ons is at your own risk. You should only run the script if you trust the developer of the Script or Add-on, as you are solely responsible for any compromise or loss of data that may result from using this Script or Add-on.”

    In terms of trustworthiness I would highlight the code is visible to you and with the experiment noted above hopefully it reassures you that once you make a copy of the solution only people with edit access to your copy of the sheet can change the code. This solution has also been published in the G Suite Solutions Gallery https://developers.google.com/gsuite/solutions/mail-merge. With this solution all the code is run on Google servers. Ultimately it is however up to you to decide whether you are happy to use this solution.

    Reply

  6. Can you give an example of how to use a 2nd recipient column to send emails by cc?

    How would the line below from the script be changed if the 2nd recipients for each email were in a column named Recipient2 ?

    // cc: ‘[email protected]’,

    Reply

    1. Hi Jeremy – if the column is called Recipient2 you should be able to change that line to:

      cc: row['Recipient2'],

      Reply

  7. Hi Martin. I’m trying to include a link in my mail merge.

    The easy solution is to put the URL in Google Sheet and include this as a parameter in the draft email, but this results in the email consisting of a long ugly URL.

    It would be better and less spammy, to have a parameter which is text with the URL embedded in the text. Is there are way to do that? Perhaps with fomatted cells?

    Reply

  8. Awesome thanks Martin, I’ll give that a try.

    Reply

  9. Hey Martin, this has been so useful. Thank you very much.

    Two things.

    1. Could you please share the script for an onEdit/onChange trigger of send emails? i.e. when Recipient column becomes populated

    2. Is it possible to set the trigger for multiple sheets?

    Really appreciate your help with this.

    Louis

    Reply

    1. Hi Louis – someone asked a similar question. There is a variant you can copy here which has a function to install an onEdit trigger. This variant uses ‘Yes’ in column G to trigger the emails (this version also requires you to specify the Gmail draft template to use in column H – you could hardcode this in the script editor with a minor change). This example also works with multiple sheets in the Google Sheet as long as columns with the headings `Recipient`, `Send Email`, `Email Template` and `Email Sent` are included (doesn’t matter on where they are or what order they are in.

      Reply

  10. I thought I had it all set, but when trying to send the emails, I get this returned in the “email sent” column:
    Cannot read property ‘replace’ of undefined
    What am I doing wrong?

    Reply

    1. I am seeing the same error. Any suggestions?

      Reply

      1. This error would appear if a column was referenced in the email draft that didn’t exisit. I’ve modified the code in the solution. If you take a new copy do you still get this error?

        Reply

        1. Thank you so much! I took the new copy and it worked great. I truly appreciate this script. Hopefully, my students will turn in their overdue work now! :)

          Reply

        2. I have tried and works perfectly ! Thank you very much!

          Reply

          1. I commente too soon, I just put in the draft {{First Name}}, and when test went out it just left a blank,

            is there something I am missing?


          2. When mine went out I had {{First name}} {{Last name}} and my emails only included the First name. So, I’m not sure what I did wrong there… In your case, is it the capitalization of Name in {{First Name}} maybe? I also used a field called {{grade}} and it did not populate in the email either, but I realized that it may have been because in the spreadsheet it was a capital letter: Grade.


        3. The new sheet works perfectly! THANK YOU!

          Reply

  11. Hi Martin,

    thanks for sharing this!

    Can I also make the subject line of the mails variable?

    Reply

    1. Hi Philipp – yes merge values work in the subject line e.g. Your personal discount code - {{Discount code}}. When you are prompted for the subject of the draft message use the subject line of your draft message as normal.

      Reply

  12. Hi! Thank you so much for this!

    I’m trying to attach files from Google Drive to these emails. These files have unique file names.

    Is it possible to have a column for their filenames, what should the content of that column be (ex. Folder/Filename.JPG), and do I have to modify the script to make it run?

    Thank you again for this!

    Reply

    1. Hi Francis,

      The way I would approach this is two columns one with the name you want to appear as the link text and the other column with the Google Drive filename links. To setup the email draft see the note here further up this thread https://mashe.hawksey.info/2020/04/a-bulk-email-mail-merge-with-gmail-and-google-sheets-solution-evolution-using-v8/#comment-187502

      In terms of getting Drive links and file names you’ll have to find another script solution or add-on to help you do that.

      Hope that helps
      Martin

      Reply

  13. Hi – this solution works great! I was wondering if there is an easy way to add in some sort of tracking support or to request read receipts?

    Reply

  14. this is great!!! it’s worked wonderfully for me so far!! thanks!!! and i have been sharing it with my friends also :-)

    though i need a lttle help – i am trying to add a different attachment to each email (i.e. each email has a unique attachment, not all emails having the same attachment)

    but i can’t seem to figure out the code for this…and where do i tag + place this attachment.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *