20

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.

  • Have you tried to copy/paste? – Jacob Jan Tuinstra Feb 6 '13 at 7:26
  • 5
    Copy/paste for 10,000 rows? No thanks. Word/Excel will do just fine. – Bryce Apr 7 '14 at 4:56
8

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();
}
| improve this answer | |
  • 1
    Why did you chose to use a stand-a-lone app and not one build from within the spreadsheet? That will make it much easier for the OP. Secondly, why are there so many Logger calls in the script? It will make the script overly dense. – Jacob Jan Tuinstra Feb 6 '13 at 16:47
  • The Google script archive used to have some pre-built scripts... is there any particular reason yours or another would not be uploaded there? – Bryce Feb 24 '13 at 11:42
  • Didn't notice Jacob's comment until now, and as he says, it should probably be a spreadsheet script instead of standalone. I'll see if I can find time to work on it, and submit it to the Script gallery. – Vidar S. Ramdal Feb 25 '13 at 9:51
  • 5
    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
  • @hadi Good job! – Vidar S. Ramdal Mar 4 '15 at 19:41
6

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:

Screenshot from Google Spreadsheets

Search for Mail merge, and you'll find several options.

| improve this answer | |
  • Note that it is limited to 100 emails per day (for free). – pixeline Jan 4 '15 at 20:26
5

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:

| improve this answer | |
3

I recommend autoCrat. It is a Google Add-On with an excellent wizard-like interface for helping you set up the merge.

| improve this answer | |
1

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.

| improve this answer | |
0

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:

  • Read merge data using Google Sheets API
  • Loop -- for each data row:
    1. Copy template form letter using Google Drive API
    2. Build mail merge search-n-replace request
    3. Use Google Docs API to update copied template

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. :-)

| improve this answer | |
0

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.