A step-by-step guide on how to create personalized PDF using dynamic variables from Google Sheets and email them if needed.
Table of Contents
Use cases for PDF Mail Merge Workflow
A PDF mail merge is similar to a traditional mail merge but specifically involves creating personalized PDF documents from a template. In this process, you use a single PDF document as a template and merge it with a data source like a spreadsheet or database. The data source contains information such as names, addresses, or other details that are unique to each recipient. The mail merge process automatically inserts these details into designated fields within the PDF, generating customized PDFs for each entry in the data source. This is particularly useful for creating personalized forms, letters, labels, or certificates in bulk.
Even when the data source is something like Google Sheets and the output isn’t intended for emailing the term “mail merge” has been retained. It’s now broadly used to describe any process where template documents are automatically filled with individual data entries from a data source, regardless of whether the final product is intended for email, digital distribution, or physical mail. However, in this blog post we’ll deal with the case of sending out a personalized email with a PDF that includes dynamic variables. As emailing the PDF is the last step, you can omitt it if it is irrelevant for your use case.
Creating a template
Create a document template on your Google Drive and insert {{variable}} for personalization
Creating a data base (Google Sheet)
Create a Google sheet you’d want to use as your database to store variables needed for personalized PDFs. Variables can be names, email address (necessary for emailing the PDFs), order details or anythin else.
Automate PDF creation with dynamic variables
Use a Read a Row action
- Establish a connection between Zenphi and your desired Google drive.
- Specify the file and the sheet you want Zenphi to read rows from
Use Load columns button
Use this action to automatically preload the columns and map them to the variables in your flow. For example, you have to tell Zenphi that Name in your Google sheet should be used to replace the variable {{Name}} in your Google doc.
Use Foreach action
What this means is that the flow you’re creating will be repeated for every row. Specify the action that should be taken for each row — in this case, it’s Read a row. In other words, you’re telling Zenphi that you need it to read not just one row. But all the rows in the sheet and perform a flow you describe below for every variable.
Add Generate a Document Step
This action will be taken not once but as a part of FOREACH flow (meaning, it will repeated multiple times).
- Add connection to your Google drive
- Specify where to save your new document
- Specify the file name (for example {{Name}}_policy.doc)
You can use template offered by Zenphi that will automatically map all your variables in Google sheet to Google doc. - Toggle on Generate PDF
Make sure all variables you have in your document template are mapped on this step!
Emailing the PDF
Use Send email action
Set up the connection between Zenphi and your Gmail account
You have to be authorized Google workspace Admin! You also can only use Google Workspace, not your personal Gmail account!
Set up the sender's email address
You can hard-code it
Set up the receiver's email address
For this, you have to tell Zenphi that email address should be taken from the Foreach Loop action
Set up subject
Hard-code or use a variable from your Google Sheet
Set up the body
Here you can also write the copy of your email using individual variable for personalization (for example, Name). Or you can create fully personalized copy and store it in Google sheet, adding a reference to the {{copy}} variable on this stage.
Add your PDF as attachments
Specify a dynamic value of your PDF file as an attahment.
Use Share Access action
If you’re using a link to the PDF created and saved on your Google Drive instead of the file itself, you’d want to provide access to this document to a person who receives your email without providing access to all your Google Drive.
In this case you need to add Share Access action between Generate a Document and Send an Email action.
- Make sure you provide a correct role to the user
- Use a user’s email address stored in your Google sheet to identify the user who has access to this file (again, use Loop foreach => email address to tell Zenphi where emails are stored)
Customize & Test PDF mail Merge Flow
The final step would be triggering the flow manually and make it run once to make sure everything’s correct. Check workflow logs as well.
You can also read our guide on how to automate a task of sending out confirmation emails upon Google Forms submission. Or how to automatically send out different emails based on Google Forms Control value.
We hope this guide has provided valuable insights into automating your workflow with Zenphi.
Talk to us
Not sure how to start your PDF mail merge automation? Let us guide you on every step! Our consultancy is free of charge.
