With Microsoft Excel and Microsoft Word, it is easy to merge rows from a spreadsheet into pages in a Word file. This was traditionally used to make paper mailings. How can I do the same with Google Drive / Google Docs?
There are plenty of templates that offer spreadsheet-to-email mail merge: How do I do a mail merge with Gmail? but that's not what I'm after.
You will need to write a Google Apps Script for that. You could let the first row of the spreadsheet be field names, and create a template document where the fields are referenced like [FIELD].
So if your spreadsheet looks like:
NAME | STREET | ZIP | TOWN --------------------------------------------- Vidar | Karl Johans gate 15 | 0200 | Oslo John | 3021 Arlington Road | 123456 | Memphis, TN ... you could have a template document like
Dear [NAME], living at [STREET], [TOWN] [ZIP] ...
Your script will need to create a new, empty document, and for each row in your spreadsheet, add a new page and search/replace the field placeholders with row values.
I have a somewhat working version, which might need some polishing. It can be invoked here. It will create a new document named Result of mail merge.
You could use it as a starting point for your own script. Let me know if you're into that, or I can spend some more time finishing the script.
Script content:
var selectedTemplateId = null; var selectedSpreadsheetId = null; var spreadsheetDocPicker = null; var templateDocPicker = null; function mailMerge(app) { var app = UiApp.createApplication().setTitle("Mail Merge"); templateDocPicker = createFilePicker(app, "Choose template", UiApp.FileType.DOCUMENTS, "templateSelectionHandler"); templateDocPicker.showDocsPicker(); return app; }; function createFilePicker(app, title, fileType, selectionHandlerName) { Logger.log("Creating file picker for " + fileType); var docPicker = app.createDocsListDialog(); docPicker.setDialogTitle(title); docPicker.setInitialView(fileType); var selectionHandler = app.createServerHandler(selectionHandlerName); docPicker.addSelectionHandler(selectionHandler); return docPicker; } function templateSelectionHandler(e) { var app = UiApp.getActiveApplication(); selectedTemplateId = e.parameter.items[0].id; UserProperties.setProperty("templateId", e.parameter.items[0].id); Logger.log("Selected template: " + selectedTemplateId); var spreadsheetDocPicker = createFilePicker(app, "Choose spreadsheet", UiApp.FileType.SPREADSHEETS, "spreadsheetSelectionHandler"); spreadsheetDocPicker.showDocsPicker(); return app; } function spreadsheetSelectionHandler(e) { var app = UiApp.getActiveApplication(); UserProperties.setProperty("spreadsheetId", e.parameter.items[0].id); selectedSpreadsheetId = e.parameter.items[0].id; Logger.log("Selected spreadsheet: " + selectedSpreadsheetId); doMerge(); return app; } function doMerge() { var selectedSpreadsheetId = UserProperties.getProperty("spreadsheetId"); var selectedTemplateId = UserProperties.getProperty("templateId"); Logger.log("Selected spreadsheet: " + selectedSpreadsheetId); var sheet = SpreadsheetApp.openById(selectedSpreadsheetId); Logger.log("Spreadsheet opened"); Logger.log("Opening template: " + selectedTemplateId); var template = DocumentApp.openById(selectedTemplateId); Logger.log("Template opened"); var templateFile = DocsList.getFileById(selectedTemplateId); var templateDoc = DocumentApp.openById(templateFile.getId()); //var mergedFile = templateFile.makeCopy(); var mergedDoc = DocumentApp.create("Result of mail merge"); var bodyCopy = templateDoc.getActiveSection().copy(); Logger.log("Copy made"); var rows = sheet.getDataRange(); var numRows = rows.getNumRows(); var values = rows.getValues(); var fieldNames = values[0]; for (var i = 1; i < numRows; i++) { var row = values[i]; Logger.log("Processing row " + i + " " + row); var body = bodyCopy.copy(); for (var f = 0; f < fieldNames.length; f++) { Logger.log("Processing field " + f + " " + fieldNames[f]); Logger.log("Replacing [" + fieldNames[f] + "] with " + row[f]); body.replaceText("\\[" + fieldNames[f] + "\\]", row[f]); } var numChildren = body.getNumChildren(); for (var c = 0; c < numChildren; c++) { var child = body.getChild(c); child = child.copy(); if (child.getType() == DocumentApp.ElementType.HORIZONTALRULE) { mergedDoc.appendHorizontalRule(child); } else if (child.getType() == DocumentApp.ElementType.INLINEIMAGE) { mergedDoc.appendImage(child); } else if (child.getType() == DocumentApp.ElementType.PARAGRAPH) { mergedDoc.appendParagraph(child); } else if (child.getType() == DocumentApp.ElementType.LISTITEM) { mergedDoc.appendListItem(child); } else if (child.getType() == DocumentApp.ElementType.TABLE) { mergedDoc.appendTable(child); } else { Logger.log("Unknown element type: " + child); } } Logger.log("Appending page break"); mergedDoc.appendPageBreak(); Logger.log("Result is now " + mergedDoc.getActiveSection().getText()); } } function testMerge() { UserProperties.setProperty("templateId", "1pAXWE0uklZ8z-O_Tejuv3pWSTiSv583ptUTGPt2Knm8"); UserProperties.setProperty("spreadsheetId", "0Avea1NXBTibYdFo5QkZzWWlMYUhkclNSaFpRWUZOTUE"); doMerge(); } function doGet() { return mailMerge(); } Through the new Google Drive Add-ons, there are several mail merge possibilities available, like "Yet Another Mail Merge".
To use it, you must have a "new" Google Spreadsheet, and install the add-on via the Add Ons menu:
Search for Mail merge, and you'll find several options.
Google's own post explains how to set up the feed data in one sheet and the template in another, rather than a Google Spreadsheet + Google Doc: https://developers.google.com/apps-script/articles/mail_merge
However, the end result is for the MailApp to send an email, rather than the desired "cloned" document. I would suggest combining the tutorial and @Vidar's answer, something along the lines of replacing:
MailApp.sendEmail(rowData.emailAddress, emailSubject, emailText); with
var mergedDoc, bodyContent, // you'd have to make the DocumentTitle column for the following newTitle = rowData.DocumentTitle /* or set to a static title, etc */; // make a copy of the template document -- see http://stackoverflow.com/a/13243070/1037948 // or start a new one if you aren't using the template, but rather text from a template field if( usingTemplateFile ) { mergedDoc = templateDoc.makeCopy(newTitle) bodyContent = mergedDoc.getBody(); } else { mergedDoc = DocumentApp.create(newTitle); bodyContent = mergedDoc.getBody(); bodyContent.setText(templateFieldContents); } // tweak the fillInTemplateFromObject to accept a document Body and use .replaceText() instead of .match as in mailmerge example // .replaceText see https://developers.google.com/apps-script/reference/document/body#replaceText(String,String) fillInTemplateFromObject(bodyContent, rowData); // no append needed? Random AppScripts References:
I recommend autoCrat. It is a Google Add-On with an excellent wizard-like interface for helping you set up the merge.
I had the same issue and tried to solve it with Vidar's answer but because of deprecation it didn't work.
The actual solution is the link by @hadi in the comment on Vidar's answer.
Vidar :
This is a great answer. I cleaned it up, and updated some of the deprecated methods, and got rid of unnecessary functions, and also modified it to be run from within the spreadsheet as @JacobJanTuinstra suggested. Then I realized that there is a bug that breaks images, and I also made a workaround for the bug. I feel that it is now nice enough to be put on Github. I have posted it there and in it provided a link to your response as the starting version of the work.
– hadi Mar 4 '15 at 19:24"
https://github.com/hadaf/SheetsToDocsMerge :
A Google Apps Script that merges information from a Google Sheet into a Template created by Google Docs. The result is a new Google Docs file that is populated by the Sheet data. Just followed the steps on the Readme and I was able to create a merged document from a template Google-Doc and a Google-Sheet.
As of Feb 2019, you can now do this with the Google Docs REST API in addition to Google Apps Script as mentioned in the other answers.
Here's a blog post I wrote linking to the video, page in the docs, and a working (Python) sample app I created that does exactly what the OP requested, mail-merging data from Google Sheets into a form letter created via Google Docs.
Specifically, you need 3 Google APIs, and the code in that sample works like this:
If you're new to Google APIs, I recommend you watch this video, then this one, and finally this one first to get an idea of how to use them. To learn more about how to use Google APIs in general, check out my blog as well as a variety of Google developer videos (series 1 and series 2) I've produced. The code samples are in Python, but if you're not a Python developer, just pretend it's pseudocode as most languages are supported by Google APIs Client Libraries. :-)
After scuffling with this for awhile, I built https://www.easymailmerge.com/ to do just this: merge a Google Sheet (or a CSV file) into a Google Doc and give me back PDFs. It currently uses Mustache-style template syntax {{field name}} and supports text, email addresses, URLs and images.
I set it up for pay: no subscriptions, just pay-per-use. You can preview the merge to make sure it does what you need before paying. Once done you get a download of all the merges as PDF files that you can save and print or do anything else with.
Disclaimer: I wrote this.
