This video applies the mail merge from PowerPoint content automation to a fillable PDF: one form, one row of data per customer, and a zip of finished PDFs at the end of the run.
What's covered
- A sample fillable PDF pulled off the web, and a Google Sheet whose columns match its fields
- The five steps of the script, from reading the form to zipping the output
- The run log, and the zip it leaves behind for download
The form and the data
The PDF is the first result for "sample fillable PDF". The customer data sits in a Google Sheet with 20 rows, and its columns (name, name of dependent, and so on) line up with the fields on the form.
Five steps
- Read the PDF template.
- Download the sheet's rows as CSV through the Google Drive integration.
- Map each row's columns to the form's field names.
- Fill a copy of the form for each row with
qbash.pdf.fill. - Zip the filled PDFs into one file with
qbash.files.zip.
The mapping in step three is a small function that returns a dict of field name to value. Most columns copy straight across. The three option columns hold TRUE or FALSE in the sheet, so they become booleans for the form's checkboxes. One field on this form has a tab character in its name, "Age\t of Dependent", and the mapping uses that exact string.
Step four calls qbash.pdf.fill with the template's bytes, the row's dict, and flatten=True, which returns a PDF whose fields can no longer be edited. The 20 fills run through qbash.parallel five at a time, and each file is named Filled_<n>_<Name>.pdf.
The run
The log shows the Task reading the PDF, downloading the sheet, and writing the zip. The zip then shows up on the run as a download. Inside are 20 PDFs, each named after its customer. Opening Sofia Rossi's shows her row's values sitting in the form's fields.
Video transcript
Nobody likes filling out PDFs. Here's a fillable PDF I found on the web. I searched for sample fillable PDF. This is what I got. And then filling out PDFs is no fun.
So, let's fill out this PDF for 20 different customers all at once. Here's our customer data. I've got it loaded into a Google Sheet. You can see the columns, name, name of dependent, etc. These correspond to the fields in the PDF we just saw.
Our task is going to create one PDF per row, filling these fields, and then zip all those PDFs together in one file. Here's the task. Let's take a look at the code. Step one, read that PDF. Step two, fetch the data from the Google Sheet.
Step three, map the rows to the PDF data. Step four, fill the PDF for each row using this PDF fill SDK. And step five, zip them all together into one file. Ready to run it. Let's see.
From the logs, we can see it reading the PDF file, downloading our data, and writing the completed zip file. That file is available for download right here. All right, let's unzip the file and see what's inside. Open it up. 20 different PDFs named after each user.
Let's look at Sophia Rossi. Here we go. Name, first choice, options, dependent, age of dependent.