Update (August 2014): Try the Yet Another Mail Merge add-on for Google Sheets.
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.
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.
${"First Name"}
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.
$%column header%
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.
sendEmail
inlineImages
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); }
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.
for
Happy merging!
Updated 10/28: fixed instructions for accessing the complete script source for solution 3.
Editor's note: This post by Google Senior Product Manager Justin Smith has been cross-posted from the Google Code blog because we think it'll be of great interest to Google Apps developers. -- Ryan Boyd
In the coming weeks we will be making three changes to the experimental OAuth 2.0 endpoint. We expect the impact to be minimal, and we’re emailing developers who are most likely to be affected.
https://www.example.com/back?error=access_denied.
https://www.example.com/back?error=access_denied
https://www.example.com/back#error=access_denied
approval_prompt=force
access_type=offline
https://accounts.google.com/o/oauth2/auth? client_id=21302922996.apps.googleusercontent.com& redirect_uri=https://www.example.com/back& scope=https://www.google.com/m8/feeds/& response_type=code
https://accounts.google.com/o/oauth2/auth? client_id=21302922996.apps.googleusercontent.com& redirect_uri=https://www.example.com/back& scope=https://www.google.com/m8/feeds/& response_type=code& access_type=offline& approval_prompt=force
Just a few weeks ago, several members of our Google Apps Developer Relations team returned from Buenos Aires, Sao Paulo, Hyderabad and Bangalore where they met with many enthusiastic developers as part of Google Developer Day and DevFest events. We're now headed to the skies again and looking forward to talking with amazing Russian, Polish, Czech and French developers.
Whether you're building integrations with Google Apps into your products to connect users with their data, helping customers integrate Google Apps with other parts of their Enterprise IT systems, or are simply customizing your own Google Apps environment-- we want to meet you. Drop us a line on Google+ or Twitter and let us know where you'll be.
Here's our schedule:
Here's who's visiting:
“@FOOD”
“@WEIGH”
