In this article, we will show you exactly how to mail merge PDF documents easily using Lido. Simply follow the steps below.
You cannot directly mail merge a PDF like you can with a Word document. However, you can use other software or tools like Adobe Acrobat or online services to merge data from a spreadsheet into a PDF form.
For this guide, we'll use Lido, a new spreadsheet built to automate repetitive tasks for you. Lido will handle the merging of your data in Google Sheets into a Google Docs template to create individual PDFs.
There is a free tier with paid plans as well. You can sign up here: https://www.lido.app/go/signup.
Here's how you can mail merge a PDF document using Lido:
First, ensure that all the data you want to merge into a Google Docs template is properly formatted in your Google Sheet. In this example, each row in the Google Sheet will generate one PDF. To ensure everything works smoothly, follow these formatting rules:
1. Your column headers must be in the first row of your sheet.
2. Column headers should not contain special characters like quotes or brackets.
3. Avoid empty columns or rows in your spreadsheet. Double-check for any empty grouped or hidden rows and columns, as these can cause issues.
Position column headers in Row 1 and ensure there are no empty columns or rows.
If you’re importing data from a Google Form, it will store the data in a Google Sheet, so these guidelines should apply to you as well.
Next, create a Google Docs template that will be populated with data from your Google Sheet. You can either start with a new document or edit an existing template.
Use the {{Column Name}} syntax to mark sections of the document that should be replaced with data from the spreadsheet. Make sure there are no spaces between the curly braces and the text.
It's essential that the text inside the curly braces {{ }} exactly matches the column header in your Google Sheet, including capitalization and spaces. For example, if you have a column labeled "First Name" in your Google Sheet, you must use {{First Name}} in the template where you want that data to appear.
On the Lido Files page, click "New file," then go to the "File" menu and click "Connect real-time data." A dialog box should pop up. Choose "Google Sheets."
Next, paste your Google Sheet URL. If this is your first time connecting a Google Sheet to Lido, you'll need to log in to a Google account that has access to the sheet you want to connect.
Choose all the columns you want to connect to Lido. Ensure you include all columns referenced as variables in your Google Docs template.
Click "Add Data."
Your Lido spreadsheet should now display the connected data from your Google Sheet, and it will look like this:
Now, add the formula to generate PDFs using your spreadsheet data. The formula is structured like this:
=CREATEPDF(<drive credential>,<template file>,<file name>,<status cell>)
Drive Credential: If this is your first time, you’ll need to add a credential to allow Lido access to read your Google Docs template and save the PDF to your drive.
Template File: Use the Google Drive file picker to select your Google Docs template.
File Name: Specify the name for the new PDF being created. You can create dynamic file names, for example: "Application Form for " & B2 (B2 being the cell containing a last name).
Status Cell: When the PDF is successfully generated, the full path to the new PDF will appear in this cell, typically in the column next to the CREATEPDF formula.
Here's a sample document: https://drive.google.com/file/d/19_gQmtwNCUPayLII4cxcRXxJKvpQW6fE/view.
A "Computed Column" in Lido automatically applies the same formula to every row and expands as your data grows.
Hover your mouse over the green table of records from your Google Sheets, and you'll see a "+" sign. Click "Add Computed Column." Name the column "Create PDF?". Begin typing the =CREATEPDF() formula in the second row.
Next, add a Google Drive credential by selecting "Add Credential" after typing =CREATEPDF and follow the prompts. Paste in the Google Docs template URL you created earlier.
Then, specify a name for the PDF, including something dynamic like "Grant Application for: " & B2 (where B2 contains the applicant's name for that row).
Add a status cell, which will indicate if a PDF has been created for that row, typically in the next column over (e.g., F2 for the second row).
Create a new "Linked Column" and name it "Status" for these status cells. A "Linked Column" should connect to another column with unique values in your data, such as a unique ID column like timestamp, order ID, or email.
Test the CREATEPDF formula by selecting "Run Action" from the cell action menu in the same cell where the CREATEPDF formula is located. Then, review the generated PDF to ensure everything works as expected.
To process all actions in the "Create PDF?" column, select "Run Column Now" from the column menu. This will run all the CREATEPDF formulas in the column.
You can manually run this column to generate your PDFs. However, if you want to automate the process so that a PDF is created automatically whenever a new row is added to your Google Sheet, keep reading.
To automate PDF creation when a new row is added, we'll set up an automation on the "Create PDF?" column where the CREATEPDF formula is located.
We want to generate a PDF only if one hasn’t already been created for that row of data. This means the PDF should only be created if the Status column (column F) is blank.
To achieve this, use a standard IF formula in your spreadsheet to display the CREATEPDF formula only if the criteria are met—specifically, if the Status field is still blank. When a new record is added to the Sheet, this will evaluate as TRUE; otherwise, it will return FALSE.
The formula will look like this:
=IF(F2 = "", CREATEPDF(<credential>, <path to template>, <file name>, <status>))
Select "Run Column on a Schedule" from the menu for the "Create PDF?" column.
Choose the interval that suits your needs—daily, hourly, etc.
At the specified time, Lido will fetch the latest records from Google Sheets, evaluate the Lido spreadsheet, and execute any action formulas, including CREATEPDF.
Let's review what we covered:
1. Lido connects directly to your Google Sheets data in real-time.
2. Lido uses the CREATEPDF action formula to generate a new PDF for each row based on a Google Docs template.
3. You can automate the process to regularly check for new records and automatically generate a PDF for each one.
Yes! The template is simply the URL of your Google Docs template file. You can create a spreadsheet formula to check another value and switch the template based on that. You can use this formula directly within CREATEPDF or in a separate column and reference it in the CREATEPDF formula.
=IF(B2="Professional Package","<path template #1>,<path to template #2>)
We hope you now know how to mail merge PDF files from Google Sheets using Lido.