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
  • 4
    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();
}
  • 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.

  • 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:

3

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

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.

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.