***Update as of 4 June 2021***
Autocrat is returning an error that the API developer key is invalid. Read more
Do you need to create a mail merge but don’t want to use Microsoft Excel or Word?
YellowWebMonkey has the solution for you: you can set up a mail merge using Google Drive and an add-on called “Autocrat”. Autocrat makes it easy to merge data from Google Sheets into Google Docs or into a PDF.
A mail merge is an extremely useful tool for personalizing forms or emails for many individuals at once. It will import data from another source and uses the information from that data source to fill in placeholders throughout the message to make it customized to a specific user (e.g. Name, Address, etc.) Form letters/emails are the most common use for mail merges, but there are many other applications as well. For example, YellowWebMonkey uses Autocrat to update the language in our annual maintenance agreements. We’ve also used Autocrat for a nonprofit’s silent auction donation list to output the bid sheets and descriptions.
Below are instructions to help you set up a mail merge in Google Documents using Autocrat:
1. We suggest installing the add-on AUTOCRAT.
Use this link: https://workspace.google.com/marketplace/app/autocrat/539341275670
2. In the Google Sheet with your data:
– Go to ADD-ONS
– Select AUTOCRAT, LAUNCH, NEW JOB
3. Name your job and hit NEXT.
4. Choose a template.
The template can be a Google Document or Google Spreadsheet and this is how Autocrat knows what your final document/documents will look like. You can format the template any way that you like, putting in <> anywhere you want to dynamically pull in data from your spreadsheet (e.g. <>)
*Tags are a string of text wrapped in double less than (<<) and double greater than (>>) symbols. When making tags, match them to the column headers in your Google Sheet exactly, and AutoCrat will auto-map them for you. The tags are case sensitive so make sure they match.
5. Map your source data to the template.
For this step you will need to map (or match) the tags in your document template to the columns in your document source. If the tags match the header exactly, they will be mapped for you automatically. If they do not match exactly, you will need to map them manually.
AutoCrat allows you to indicate the type of data that is being merged:
Standard: normal text field that will be merged into the document exactly as it appears in the spreadsheet
Image: AutoCrat can pull an image URL from a sheet and merge it into a document (as an image, not a URL). The image must be publicly hosted, not stored on a drive. You can provide an image size in pixels or percentage. This can also be left blank and the image will by merged at 100% of it’s actual size.
Hyperlink: Autocrat can identify text as a URL so that it is live (clickable) in the document. You can also include a “link label” if you want to hide the URL behind a word or sentence.
6. Set up the File Settings by naming the file that each merged document will be given.
You can use <> or <> to give each document a timestamp. Choose file type as either Google document or PDF. Some hyperlinks may not work correctly if imported directly into a PDF, but you can import them to a Google document and then convert to PDF to solve this issue.
7. Choose a destination folder for each merged document to be dropped into.
8. Add a dynamic folder reference (optional)
which will drop a merged document into certain folders and not others.
9. Set Merge Condition (Optional)
which tells AutoCrat whether or not to merge a particular row of data. You can also add additional conditions that must be met in order for a row of data to be merged.
10. Share Docs and Send Emails.
In order to share documents, you must send an email. Change the settings to allow others to edit, view only, or make comments to a document. You can send an email from your email address, or from a no-reply email.
11. Add/Remove Job Triggers
can be optionally set up to run when forms are submitted or at certain time intervals.
12. Click Save.
13. Click the Play icon to run the job, the pencil icon to edit the job, the eye icon to preview the job, or the trashcan icon to delete the job.
If you are running a large job, you may choose to use the eye icon as a test/preview so that you can verify the mail merge is set up as you’d like it. This will keep you from having to delete many files if you have to rerun the job after making changes.
