Unlike Microsoft Office, Google Docs and Sheets don't have the ability to perform mail merge operations. If you're not familiar with mail merges or if you've never done one before, here's a quick explanation.


A mail merge takes data from a spreadsheet and then uses that data to fill in placeholders in a document or email.

So, let's say you want to send 50 letters out to potential donors and you have those potential donors' contact information in a spreadsheet. A mail merge would allow you to create one template version of your letter, then make 50 copies of it, all with the donors' information inserted into the appropriate spots. Examples would include the recipient's address, the salutation ("Dear Dr. Dre,"), or their phone number.


There's an add-on for Google Sheets called AutoCrat that can add this feature to Google Sheets. Before you start digging into it, however, it does have some limitations you should be aware of:

  • Autocrat may not be able to merge really large data sets into a single document. I've tested it with spreadsheets with as many as 250 rows of data and it worked fine, but if you're planning to merge a sheet with more than 250 rows, you may need to break it up into pieces.
  • AutoCrat is a 3rd-Party add-on to Google Sheets. For it to work, it needs to be able to access the data in your Drive. While it's published by a nonprofit educational organization and has a solid reputation, you may want to think twice before using it to process extremely sensitive data.


This tutorial will walk you through the steps of preparing for your first mail merge, installing the AutoCrat extension, and performing the merge.


Prerequisites

Before you get started, you'll need two things already composed and ready.

  1. Your Source Data (A Google Sheet)
  2. A Template Document (A Google Doc)

Your source data is the information that Autocrat will use to fill in the names and addresses (and whatever other information you want to use) in your finished documents. It's important that this data set is as clean as you can make it. That is, it should look something like this:

The first row (row 1) of your data set should have clear, short, descriptive headers. The data in each column should also be uniform and complete.

For example, if your 'State' column has abbreviations ("NJ"), full state names ("New Jersey"), blank cells, or other irregularities, you'll see some problems with your mail merge. It's best practice to separate every piece of data that you can into separate columns. In other words, it's much more useful to have a Title, a First Name, and a Last Name column rather than just the entire name in one cell. 


The other thing you'll need is a template document. This document will be the model for all of the documents created by your merge.


If you're creating a letter, for instance, you should compose the letter first. At every point in the letter where you'd like to import data from your spreadsheet (such as in the salutation or address), you'll want to insert a tag. Tags in autoCrat are simply the names of your spreadsheet columns, enclosed in double angle brackets, like this: <<First Name>>. It's helpful if your tags match your the data columns exactly (including case and number of spaces).

Check out the example below, and notice how all of my tags match the columns in my spreadsheet.


Once you're all set with your template document and your Google Sheet o' data, you're ready to get AutoCrat-ing!


Installing AutoCrat

1. Open the Google Sheet that's going to be your data source. Open the "Extensions" menu, hover over "Add-ons", and select "Get add-ons...". This will take you to the 




2. Do a search for AutoCrat, then choose to install the version published by New Visions CloudLab. Click the big 'Install' button:






3. You'll be prompted to grant permissions to the autoCrat application. This seems like a lot of access to your data, and it's true, it is. For autoCrat to work, however, it does need access to all of these privileges to send email, read documents, create documents, etc. After accepting access (if you choose to), You'll get a message that autoCrat has been installed:


Creating A Job

1. To start a new AutoCrat job, you'll want to open the Extensions menu, hover over autoCrat, and then click on Open. 




AutoCrat will prompt you to create a New Job (which you should do), then it will walk you through the process, step by step. First, give your merge job a name.


2. Next, choose the template document that you're planning to use. Do this by selecting From Drive, then searching for your document, then clicking Select.




Pro-Tip: AutoCrat will show you your most recently updated documents first, so make a change to your template doc before running autoCrat and it will be near the top of the list, no searching required.


3. AutoCrat will fetch some template details, which may take a minute or two. On the next screen, you need to select your 'Merge Tab'.


This is the tab of your spreadsheet that contains your source data. AutoCrat will think for a minute, and try and match every column to a <<tag>> found in your template document. If you've spelled everything correctly, each of the tags will be mapped to a column. Scroll down the list and make sure that every tag is mapped correctly. You can fix any errors manually, then click Next when you're done.


4. Next up, you need to choose what your finished document will be called, what format it will be, and whether you want one document or lots of documents.

If your plan is to print your documents, a single document is the way to go, since you only have to open it once and press 'print' once. If your plan instead is to send electronic copies to each of your recipients, multiple documents will make that easier. In my example, I want to print my documents, so I'm going to choose 'Single document', then click Next.


5. Choose the destination folder in Google Drive where you'd like this new document to be created, then click Next. ProTip: This can be a struggle for Drive to find the folder you want, so my tried-and-true tactic is to create your destination folder right before you create your merge. Then, your new folder will appear at the top of the list.


6. Add dynamic folder reference...this is an advanced option. It allows you to send individual documents to different folders specified in your spreadsheet. This can be extremely useful in some cases, but we're going to skip it for now.


7. Set Merge Condition...we're going to skip over this, but it can be a really useful feature too. For instance, if you only want to send letters to recipients in Wyoming, you could set "Column State equals WY". 


8. Do you want to share your final documents via email? You can do that here. It's important to remember that this option is ONLY useful if you've chosen to create separate documents. Also, Autocrat will only allow you to create basic emails to your recipients. You can't easily insert image or rich text formatting into an AutoCrat-generated email.


9. Job Triggers; you probably want to leave these off, unless you want your merge to run automatically on a schedule. Click Save to finish configuring your job.



Run Your Merge

1. Open autoCrat again (if it's not already open) and locate your saved merge job. Hover over it with your cursor and click on the play icon:


2. Your job will start running.

Depending on how much data you have, this may take a few minutes. You'll know the merge is completed when the autoCrat extension dialog box disappears, and you can double-check on your source data. When the merge finishes, you'll see a bunch of new columns appear with data about the merge and a link to the created document.


3. You can click on one of the links to view your created document(s). In my example, autoCrat created a 98-page PDF where each page is a new copy of the letter with different address and greeting information.



That covers the basics of using AutoCrat, but as you can probably tell, the tool is capable of much more. AutoCrat can be especially powerful when combined with Google Forms. To take your AutoCrat game to the next level, here are some other resources: