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
  • 3
    Copy/paste for 10,000 rows? No thanks. Word/Excel will do just fine. – Bryce Apr 7 '14 at 4:56
up vote 7 down vote accepted

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"); 
  return app;

function createFilePicker(app, title, fileType, selectionHandlerName) {
  Logger.log("Creating file picker for " + fileType);
  var docPicker = app.createDocsListDialog();
  var selectionHandler = app.createServerHandler(selectionHandlerName);
  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");
  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);
  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) {
      } else if (child.getType() == DocumentApp.ElementType.INLINEIMAGE) {
      } else if (child.getType() == DocumentApp.ElementType.PARAGRAPH) {
      } else if (child.getType() == DocumentApp.ElementType.LISTITEM) {
      } else if (child.getType() == DocumentApp.ElementType.TABLE) {
      } else {
        Logger.log("Unknown element type: " + child);
   Logger.log("Appending page break");
   Logger.log("Result is now " + mergedDoc.getActiveSection().getText());

function testMerge() {

function doGet() {
  return mailMerge();
  • 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
  • 3
    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

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

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);


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();

// 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.

Your Answer


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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