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.
Pingback: Mail Merge to PDF Files – V2 – hari@weblog