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.

61 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

    2. This is spectacular. Thank you Martin, while(true){{We’re not worthy}}

      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

    1. Hi CZ!

      This was the problem that I faced a few days ago. I was able to get around it by modifying this solution to fit Martin’s script:

      https://stackoverflow.com/questions/48831263/how-to-attach-google-drive-file-with-sendmail-function-in-apps-script

      On top of adding this, you’ll have to add a column named “Attachment” and to place all the filenames there. Make sure that all your filenames are unique though or else it’ll retrieve the same file for multiple emails.

      Good Luck!

      Reply

      1. Thanks Francis. Let me have a shot at this.

        Possible to share a snippet of your script pls? So i could refer to the “modifications” you made? I’m pretty noob at this so need a bit more handholding.

        Thanks!!

        Reply

        1. Sure no problem. I didn’t even know what Google Apps Script was a week ago (and I’m never touching it again!).

          https://script.google.com/d/1_QBwhTexF6BL6L0rq0AHjNezj5-K8IHslMCOgIASP44n6IWw58G6z-1Y/edit?usp=sharing

          We’ve isolated the important part that makes the sending of attachments work. As I said earlier, you’ll have to create a column called “Attachment” for the unique filenames of your attachments.

          Further, we added an if statement so that if you want to use this code to send emails without attachments, it’ll still work.

          Let me know if you have any other questions, or if you manage to make a more user friendly interface for Scheduling Emails. (Typing the date and time is prone to errors! I wish there was a way to click on a calendar)

          Best Wishes!

          Reply

          1. Thanks Francis and Martin!! This works like magic.

            No I haven’t figured a more user friendly interface for scheduling emails :-(

            I made a little change to the script to remove the .pdf part…so that I can send all sorts of attachments. This means though that within the gsheet “Attachment” column I have to add the file type….but small effort.

            Thanks a million guys!!


          2. Hi Martin and Francis,

            I tried the original script together with the modification to send individual PDFs, but it did not work.

            I got “Invalid argument: attachments” in the column Email Sent.

            Can you kindly advise why I might have got this error or can you provide the whole script?

            Thanks.


  15. Hi,
    I have the email draft structured like this (one field per line):
    {{name1}}
    {{name1_info}}

    {{name2}}
    {{name2_info}}

    (and so on, for 18 more fields)

    Whenever one of this fields/cells is empty in the sheet, I get a blank line in my email. If I have a lot of empty cells, I get a lot of empty lines in my email. Is there a way avoid this from happening?
    Thanks in advance

    Is there a way to avoid blank lines in the email when the cell

    Reply

  16. Dear Martin,

    Could you help merging the schedule send code with full code. I tried making few changes in script as per above suggestion but could to achieve schedule working, all emails were sent immediately.

    What i am trying to achieve.
    The script should schedule mails based on date and time mentioned in a column , In case its blank send emails immediately. any suggestions?

    Reply

    1. Hi Mohit – there is a copy of the Sheet example with the scheduling code setup here https://docs.google.com/spreadsheets/d/1oCUxAkKP088J9hiNoLA8NvzUG6FzJh9yG33A0mfTP-o/copy

      It doesn’t do exactly what you are looking for in terms of looking for a date column but you could setup the `sendScheduledEmails()` function to automatically keep running with a timed trigger and then modify the `if (row[EMAIL_SENT_COL] == ”){` condition to do a date based calculation e.g.

      if (row[EMAIL_SENT_COL] == ” || (row[EMAIL_SENT_COL] !== ” && new Date() >= new Date(row[EMAIL_SENT_COL]))){

      Reply

  17. Hi Martin,

    Thanks a lot. Great code that you’re sharing here, while studying your previous code, I was trying to achieve the same goal than this post, which ultimately I’ve found as I visit very often your sites.

    For the sake of learning, how can we push the emails so that multiple rows are sent for a single email (i.e. repeated emails receive all rows together instead of receiving one email for each row). Ideally it would be within a table and I don’t know if this table should be ready or not within the draft?

    Thank you for any response that you may provide, or perhaps you could include this suggestion in your code, which I believe is very handy in some scenarios.

    Reply

    1. I’m wondering if this is possible as well. I have a list of classes for students and would like to send one e-mail per student w/ a list of their classes.
      This would be most useful.

      Reply

  18. Tried many different approaches for the images to show up inline including the solution referenced above (and the updated version of the solution) for the YAMM tool. Also tried inserting the images directly, via incognito-vetted URLs, from Photos, from my computer, etc. None of these worked for me. In every case, the emails showed up with broken links for the images in the email body but were included as attachments. Image size is not an issue AFAIK since both images were around 100Kb each.

    Would greatly appreciate some assistance.

    Reply

  19. Is there a daily limit?

    After around 100, it won’t send anymore. Message is: Service invoked too many times for one day: email.

    Reply

  20. Thank you so much for this solution! Saved me from having to buy something from the Marketplace. I have two questions:

    1) how do i write the code (// name: ‘name of the sender’,) so that the sender name defaults to the user name instead of hard coding a particular name in there? i want other non-tech people in my org to use the script and i want to do that by sending them a “ready to use” file. If they have to go in and change code, that could be an issue.

    2) is there a way to adjust the code or my email so it is not perceived as bulk mail by some email servers? I tested the script to go to a gmail account and an outlook account. The email that went to Outlook ended up in Spam folder.

    Thanks for your help.

    Reply

  21. Hi Martin, thanks for your work with the MailMerge script, it’s really helpful and works a treat. I’ve tried the hidden rows variant and this is throwing an error (Sheets not defined). I’m sure this is simple to fix, but I’m not familiar enough with the scripting environment to do this easily. Hope you can have a look.
    Thanks, Andrew

    Reply

    1. Hi Andrew – you might have better luck replacing

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

      with

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

      Reply

  22. Thanks for this! This was exactly what I needed. I am a super beginner!! I had to add a menu item and function that allows me to clear the Emails Sent column so that I don’t have to manually delete the data in the column. Is it supposed to delete automatically? I couldn’t figure out if it was supposed to do it on it’s own because mine wasn’t.

    Reply

  23. How can you send the draft email (after mail merge) to Gmail without automatically sending it out? Send through Gmail after review.

    Reply

    1. Hi Carrie – to only create draft messages you should be able to open the Tools > Script Editor and change GmailApp.sendEmail to GmailApp.createDraft

      Reply

  24. Hi Martin – thanks for the well documented code and useful examples. Really helpful and a great solution.

    Question: Many of our column names contain a space (ie Monthly Usage / Access, Gas Used) etc. The merge works as designed on all fields where the column name is one-word. Is there an escape character I can use in the merge document for these other columns? I’ve tried single quotes such as {{‘Monthly Usage / Access’}}, double quotes, back slash etc. to no avail.

    Rick

    Reply

    1. As a follow-up and sanity check … the merge does work on multiple word column headings.

      Our issue is that we’ve used line breaks (ALT+Enter) within the Column Name cell for formatting (such as the below), and that is why its not being recognized as a merge value.

      Monthly
      Usage / Access

      Reply

  25. the script works beautifully – EXCEPT when there are emoji’s in the email
    title – and they are changed to question marks. . .

    Reply

    1. Still an issue of you follow the tip in the section of the post titled ‘Sending emails with emoji/unicode’?

      Reply

Leave a Reply

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