4 ways to do Mail Merge using Google Apps Script

Thursday, October 27, 2011 | 5:49 PM

Labels:

Editor’s Note: This blog post is co-authored by James, Steve and Romain who are Google Apps Script top contributors. -- Ryan Boyd

The Google Apps Script team is on a roll and has implemented a ton of new features in the last few months. Some of us “Top Contributors” thought it will be a useful exercise to revisit the Mail Merge use case and discuss various ways in which we can do Mail Merge using Apps Script. Below are several techniques that tap into the power of Google Apps Script by utilizing Gmail, Documents and Sites to give your mailings some zing. Mail Merge is easy and here is how it can be done.

1. Simple Mail Merge using a Spreadsheet

The Simple Mail Merge tutorial shows an easy way to collect information from people in a Spreadsheet using Google Forms then generate and distribute personalized emails. In this tutorial we learn about using “keys,” like ${"First Name"}, in a template text document that is replaced by values from the spreadsheet. This Mail Merge uses HTML saved in the “template” cell of the spreadsheet as the content source.

2. Mail Merge using Gmail and Spreadsheet Services

The Gmail Service is now available in Google Apps Script, allowing you to create your template in Gmail where it is saved as a draft. This gives us the advantage of making Mail Merge more friendly to the typical user who may not know or care much about learning to write HTML for their template. The mail merge script will replace the draft and template keys with names and other information from the spreadsheet and automatically send the email.

To use this mail merge, create a new spreadsheet, and click on Tools > Script Gallery. Search for “Yet another Mail Merge” and you will be able to locate the script. Then, click Install. You’ll get two authorization dialogs, click OK through them. Add your contact list to the spreadsheet, with a header for each column. Then compose a new mail in Gmail. Follow this syntax for the “keys” in your template: $%column header% (see above). Click Save now to save your draft. Go back to your spreadsheet and click on the menu Mail Merge. A dialog pops up. Select your draft to start sending your emails.

You can add CCs, include attachments and format your text just as you would any email. People enjoy “Inserting” images in the body of their emails, so we made sure to keep this feature in our updated mail merge. To automate this process we will use a new advanced parameter of the method sendEmail, inlineImages. When the script runs it looks in the email template for images and make sure they appear as inline images and not as attachments. Now your emails will look just as you intended and the whole process of mail merge got a whole lot simpler.


3. Mail Merge using Document Forms

The next Mail Merge will use a template that is written in a Google Document and sent as an attachment. Monthly reports, vacation requests and other business forms can use this technique. Even very complex documents like a newsletter or brochure can utilize the automation of Google Apps Script to add the personal touch of having your patron’s name appear as a salutation.

Like in the Mail Merge for Gmail, the Google Docs template will use “keys” as placeholders for names, addresses or any other information that needs to be merged. Google Apps Script can add dynamic elements as well. For example you may want to include a current stock quote using the Financial Service, a chart from the Charts Service, or a meeting agenda automatically fetched for you by the Calendar Service.

As the code sample below demonstrates, the Google Apps Script gets the document template, copies it in a new temporary document, opens the temp document, replaces the key placeholders with the form values, converts it to PDF format, composes the email, sends the email with the attached PDF and deletes the temp document.

Here is a code snippet example to get you started. To use this mail merge, create a new spreadsheet, and click on Tools > Script Gallery. Search for “Employee of the Week Award” and you will be able to locate the script.

// Global variables 
docTemplate = “enter document ID here”;
docName = “enter document name here”;

function sendDocument() {
// Full name and email address values come from the spreadsheet form
   var full_name = from-spreadsheet-form
   var email_address = from-spreadsheet-form
// Get document template, copy it as a new temp doc, and save the Doc’s id
   var copyId   = DocsList.getFileById(docTemplate)
                  .makeCopy(docName+' for '+full_name)
                  .getId();
   var copyDoc  = DocumentApp.openById(copyId);
   var copyBody = copyDoc.getActiveSection();
// Replace place holder keys,  
   copyBody.replaceText('keyFullName', full_name);
   var todaysDate =  Utilities.formatDate(new Date(), "GMT", "MM/dd/yyyy"); 
   copyBody.replaceText('keyTodaysDate', todaysDate);
// Save and close the temporary document
   copyDoc.saveAndClose();
// Convert temporary document to PDF by using the getAs blob conversion
   var pdf = DocsList.getFileById(copyId).getAs("application/pdf"); 
// Attach PDF and send the email
   MailApp.sendEmail(email_address, subject, body, {htmlBody: body, attachments: pdf}); 
// Delete temp file
   DocsList.getFileById(copyId).setTrashed(true);
}

4. Mail Merge using Sites and Spreadsheet Services

For the last example let’s assume you have a great Google Site where you create new letters for your followers. However, you have had some feedback suggest that while many users don’t mind visiting your site, some would prefer to have the newsletter emailed to them. Normally this would require copying and pasting into an email or doc. Why not simply automate this with Google Apps Script?

The body section of a site, the part you edit, can be captured as HTML by the Sites Service and placed in the body of an email. Because the return value is HTML, the pictures and text formatting come through in the email.

Here is a simple example for you to try out:

function emailSiteBody() {  
 var site = SitesApp.getPageByUrl('YourPageURL');
 var body = site.getHtmlContent();
  
 MailApp.sendEmail('you@example.com', 'Site Template', 'no html :( ', {htmlBody: body}); 
}

It really is that simple. Add a for loop with email values from a spreadsheet and this project is done.

Happy merging!

Updated 10/28: fixed instructions for accessing the complete script source for solution 3.




James Ferreira   profile

Author, Scripter, and developer of free apps for non-profits and schools, James has written software to help more than half a million people by extending Google Apps.


Steve Webster   profile

Google Sites and Scripts expert from Dito specializing in training and application development. When not busy finding solutions to enhance customer capability in Google Apps, Steve shares examples of his work in the Google Apps Developer Blog.


Romain Vialard   profile | YouTube

Google Apps Change Management consultant at Revevol, Romain writes scripts to automate everyday tasks, add functionality and facilitate rapid adoption of cutting edge web infrastructures.

28 comments:

jogo said...

To better understand, how the last option works, you can check this video.

Fully automated life-cycle of email newsletter created within Google Apps is shown there.

http://www.youtube.com/watch?v=ynDRr0i8yr0

michaellynton said...

This is a great post. Re: #3 Mail Merge using Document Forms - any tips on how to save the PDF to a Document collection instead of emailing it?

Anonymous said...

"Get the source code" link is not working. Could you please fix it. Thanks.

Steve Webster said...

We are resolving the source code link in #3 now. The work-around is to sign-in to a Gmail account, instead of a Google Apps account. - Steve Webster, Dito

Steve Webster said...

@michaellynton - Visit http://code.google.com/googleapps/appsscript/class_docslist.html#createFile and use the DocsList.createFile. Next, add the PDF file to the desired collection by using DocsList.addToFolder. You can post questions like this in the Apps Script forum here: http://www.google.com/support/forum/p/apps-script/

Anonymous said...

This is excellent! It definitely provides the ability to be creative, as well as create very diverse communications!

My only question would be to ask if there was a way to ensure that the recipient does not receive the message "This message may not have been sent by:" (I got it when I tested it and sent it to myself). Thanks!

Anonymous said...

In regards to the "Yet Another Mail Merge" option (excellent by the way!!), is there a way to ensure that the recipient does not receive the message, "This message may not have been sent by..." (I received it when I sent it to myself". Thanks!

michaellynton said...

Thank you Steve! I really appreciate it.

Patrick Oliviero said...

Hi,

I wrote, some months ago, a script based on the #4 option, with interesting improvements, especially merge variables and merge fields, triggers to send emails...
Take a look at http://scripts.revolution-informatique.fr/ .

James said...

Hello,

I have tried your option 2 a dozen times and when I run the final version of the script I get the below error:

TypeError: Cannot read property "1" from null. (line 62)

I have restarted, cleared scripts, created test drafts and tests at every stage and at the point at second 55 in the youtube video it just gives me that error.

Any ideas?

Thanks,
James

Karen Huffman said...

Getting the same thing with option 2 as James. Any resolution to this error:

TypeError: Cannot read property "1" from null. (line 62)

Thanks,
Karen

Lalita Manjari said...

Is it possible to send plain text e-mails using #2? When I attempted this, friends only viewing plain text e-mails got a
at the end of each line. Can that be avoided? Otherwise it's perfect.

Saurabh Gupta said...

This can be avoided. You can easily send text emails. Can you please post this on Google Apps Script with the issue and we can help you there.

Lalita Manjari said...

Thanks Saurabh. I've posted on Google Apps Script here:

https://groups.google.com/a/googleproductforums.com/forum/#!msg/apps-script/Jy5B-z7-9kg/R8hjyqcqWv4J

Any ideas?

MailMergeR said...

Hello Romain, can you pls help me with my question posted here: https://groups.google.com/a/googleproductforums.com/forum/#!topic/apps-script/QfOvNdlmeuU

thank you!

Miss Elaine said...

Yet another Mail Merge does not appear under script gallery for me. Any ideas?

Thanks.

Saravanan Anna said...

Thanks, It is working fine.

arjun said...

Is there a way i can pick custom documents to be attached to each individual. Say Path to the document can be a field in the spread sheet.

vbignacio said...

thanks for this. in option 3, can i send the attachment as a .doc file instead of it being converted to pdf?

James said...

Does BCC work with Step #2?

Nik said...

Thanks for the great tip - Mail Merge using Gmail and Spreadsheet Services is just what I have been looking for.

The first time I tried it, the bcc didn't work for me (I just put an address in the bcc field in the draft email). After looking at the code I realized that they had actually worked that in to the script.

Adding a bcc column in my spreadsheet actually worked!

One weird thing happens for me though. It won't work the first time I load the script. After I load it a second time it executes without an error though.

Harp Er said...

I am having troubles with option #3. I have added the script to my form and changed the values to match my form. I am receiving the following error message when debugging:

TypeError: Cannot read property "values" from undefined. (line 7)

I have watched the tutorial several times but can't figure out what I am supposed to do. Can someone please direct me on what to do next? Links to the script and the form are listed below.

Thanks in advance for your assistance.

Script: https://docs.google.com/document/d/13ucoER1N8NDAYaK0wqbyBOh1cDxfn5ZKx49jh-vZHUc/pub


Form: https://docs.google.com/document/d/1hvYFABFrwqnPqm9PfHOFiQuDpsiNneI3ap9X2CvaLCQ/edit?usp=sharing

Steve Webster said...

+Harp Er
Since form values are only available after submitting a form, the "values" are "undefined" most likely because you executed the script within the editor.

In other news. Since the "new refresh forms" were introduced, you may want to follow this issue tracker that I submitted:
http://code.google.com/p/google-apps-script-issues/issues/detail?id=2391

Admin said...

For option #2 (which is SWEET, by the way), it is importing my date column from the spreadsheet field as the full day, date, time info. For example: the date is coming in like:

Tue Jan 01 2013 00:00:00 GMT-0800 (PST)

I just want to list: Jan 1, 2013.

The script seems to disregard the column format in the spreadsheet. Any idea about how to retain that?

aram harrow said...

Google apps has a daily limit for number of emails sent. This is a big big problem with these mail merge apps. :(

Also "Yet Another Mail Merge" has an annoying bug where it can confuse a draft template with an already sent email.

So I found myself having sent out 100 wrong emails, but unexpectedly running out of quota before being able to send the correction. It's very frustrating!

Randy Edwards said...

I'm using the "Yet Another Mail Merge" script and it's working beautifully.

I have it set to populate the email address column with the address based off of whatever name I enter into another cell. Then of course I have to hit mail merge, standard merge, and go through the steps.

Is there anyway for this to function on edit or time delay? So once I enter the name, the address is populated and the script runs.

Thanks in advance!

James ODonnell said...

There is great information in this post, and although I don't want to detract from the great contribution made here it does some like a glaring omission that none of these solutions addresses how to create an unsubscribe link

David Vitale said...

I've grasped how to merge now using the yet another mail merge script. But, I have 500 odd people I need to contact an I know some emails will bounce becasue their email adresses are old or incorrect. I want to know which ones there are so I can contact the people to update their address. How do i do this?

Many thanks,

David.