Mail Merge to PDF Files

Mail merge is a great feature (truth be told, everything that automates a repetitive job is a great feature).

One of the tedious repetitive task our team keeps doing often is generating PDF files for our employees (usually one file per person). We are going to automate this using Google Docs, Google Sheets, and Google App Scripts.

Step 1: Create a new Google Sheet

Open the sheet https://docs.google.com/spreadsheets/d/1hUtDYGwir8l4TkMTXej-1KW3TURS1gaPEipkX1MDO7Y/edit?usp=sharing and create a copy of the sheet using ‘File -> Make a copy’ option.

Step 2: Create the template Google Doc

Open the document https://docs.google.com/document/d/1yAk0C5I-ycHxMB8jA3Anpu8LNLLKzxtu8N6PM5FG8H8/edit?usp=sharing and create a copy of the doc using ‘File -> Make a copy’ option

The URL of the new document starts with https://docs.google.com/document/d/. Everything after the /d/ and before the /edit is the ID of the document. Copy this value in the ‘Config’ sheet in the column right next to ‘Template ID’.

If you made a copy of the original sheet, you will replace the ID of your Google doc with “1yAk0C5I-ycHxMB8jA3Anpu8LNLLKzxtu8N6PM5FG8H8”

Step 3: Create the output Google folder

Create a new empty folder. This will be the folder where we generate the files. The URL will look like https://drive.google.com/drive/folders/1flyC596UBnKNnqOTKyA4ZYhMgP80BVW_. The id of the folder is everything after https://drive.google.com/drive/folders/.

If you made a copy of the original sheet, you should replace the ID of your folder with 1flyC596UBnKNnqOTKyA4ZYhMgP80BVW_

Step 4: Create Google Apps Script

When you make a copy of the Google Sheets file, it automatically clones the script along with it. You can find it by following the option ‘Tools -> Script Editor’. The latest version of the code can be found at https://gist.github.com/sp2hari/49a74793200e3c7b169b508609c7466c

Step 5: Run the script

The above permission window comes up only for the first time when you run the script. Post that, it will automatically create the files in the output folder.

Hopefully, this helps in automating the PDF generation process. If you have any feedback regarding the script, you can comment in the Github link shared above.

One comment

  1. Pingback: Mail Merge to PDF Files – V2 – hari@weblog

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.