In this article we will show you how to automate PDF generation from records in Google Sheets. Creating a streamlined process for generating documents such as invoices, reports, and letters.
For this solution we will use Lido to automate PDF generation from your data in Google Sheets using a Google Docs template. Simply follow the steps below.
Here's how you can automate PDF generation with Lido:
First, format the data in your Google Sheet to match the structure required for a Google Doc template, especially if you're automating the creation of one PDF per row. For everything to function correctly, ensure your Google Sheet data adheres to the following formatting guidelines:
1. Place column headers in the first row of the sheet.
2. Avoid using special characters, such as quotes or brackets, in column headers.
3. Eliminate any empty columns or rows within your data, including checking for hidden or grouped rows and columns, as these can lead to issues.
Column headers should be positioned in Row 1, with no empty columns or rows present.
If you're importing data from a Google Form, it's typically stored in a Google Sheet, so these guidelines should be applicable to you as well.
Next, create the Google Doc template that will be filled with data from your Google Sheet. You can start with a new document or modify an existing template you already have.
We will indicate which sections of the document should be replaced with data from the spreadsheet using the {{Column Name}} syntax. Ensure there are no spaces between the curly braces and the text.
It's crucial that the text inside the curly brackets {{ }} matches the column header in your Google Sheet exactly, including capitalization and spaces. For instance, if there's a column labeled "First Name" in your Google Sheet, you must use {{First Name}} in the template wherever you want this value to appear.
We will use Lido, a new spreadsheet built to automate repetitive tasks, to automate PDF generation by merging our Google Sheet data into a Google Doc template. There is a free tier with paid plans as well. You can create a new account here: https://www.lido.app/go/signup.
Step 1. From the Lido Files page, click "Create a new file".
Step 2. Then, click Connect Data and select Google Sheets.
Next, paste in your Google Sheet URL. If it's your first time connecting a Google Sheet to Lido, you'll need to authenticate into a Google account that has access to the sheet you're trying to connect.
Select all of the columns that you want to connect to Lido. Make sure to connect all of the columns that are referenced as variables in your Google Doc template.
Click Add Data.
Your Lido spreadsheet should now have connected data from your Google Sheet. It will look like this:
Now we need to add the formula to create the PDFs with our spreadsheet data. The formula looks like this:
=CREATEPDF(<drive credential>,<template file>,<file name>,<status cell>)
Where:
Drive Credential - When you enter the formula you will be prompted to connect your google account. Simply follow the prompts and when done your google email will display in the cell.
Template file - This is the URL path for the google doc template we created earlier. Simply paste it into the formula.
File Name - Name of the new PDF that is being created. You can make dynamic file names such as: "Application Form for " & B2 (B2 being the cell containing a last name).
Status Cell - When the PDF is generated successfully, the file path to the new PDF will be put in this cell. Usually, it's the next column over from CREATEPDF. For example: https://drive.google.com/file/d/19_gQmtwNCUPayLII4cxcRXxJKvpQW6fE/view.
Now that we have broken down the formula, we will create ours below:
A Computed Column in Lido copies the same formula down to every row and grows as your data grows.
1. Hover your mouse over the green table of records from your Google Sheets. You'll see a "+" sign.
2. Choose "Add Computed Column".
3. Name the Column "Create PDF?".
4. Start Typing the =CREATEPDF() formula in the second row.
You can do so by choosing "Add Credential" after typing =CREATEPDF. Simply follow the prompts.
Paste in the Google Doc template URL you created earlier.
Specify name of PDF that will be created. Include something dynamic such as "Grant Application for: " & B2 (where B2 holds the name of applicant for that row of data).
This cell will record if a PDF has been created for that row. In this case, the next column over - F2 if you're in the 2nd row.
Create a new Linked Column and name it "Status" for the status cells.
A Linked Column needs to tie itself to another column with unique values in your data (a "Unique ID column"). Timestamp, orderid, or email are often good columns to use as an ID Column.
Run a test of any of THE CREATEPDF action formulas and review the generated PDF. In the same cell as the CREATEPDF choose "Run Action" from the cell action menu.
To run the whole column of actions, choose "Run Column Now" from the "Create PDF" column menu. This will run all CREATEPDF cells in the column.
You can run this column manually to create your new PDFs. However, if you'd like to automate this whole process to automate the creation of a PDF whenever a new row gets added to your Google Sheet, then read on.
To automate PDF creation when a new row is added, we'll create an automation on the "Create PDF?" column where the CREATEPDF action formula is.
We only want to create a PDF if we haven't already created one for that row of data. In other words, only if the Status column (column F) is blank.
We do this by using a regular spreadsheet IF formula, and only show CREATEPDF if our criteria is met. In this case, by making sure the Status field is still blank. If a new record is added to Sheets, this will evaluate to TRUE. Otherwise, it will show FALSE.
=IF(F2 ="",CREATEPDF(<credential>,<path to template>,<file name>,<status>))
Choose "Run Column on a Schedule" from the column menu for the "Create PDF?" column. Choose the interval that makes sense, daily, hourly, etc.
At the time you specify Lido will fetch the latest records from Google Sheets, evaluate the Lido spreadsheet, and any action formulas will be run (CREATEPDF is an action formula).
Run the Create PDF? column on a schedule by creating an automation.
Let's review what we covered!
1. Lido connects "live" to your Google sheets data.
2. Lido uses an action formula, CREATEPDF, to generate a new PDF for each row based on a Google Doc template.
3. You can set up an automation to regularly check for new records and automatically create a PDF for each one.
Yes! A template is just the URL to your actual Google Doc template file. So, you can set up a spreadsheet formula to check some other value and change the template accordingly. You can use this formula directly inside of CREATEPDF, or in its own column and refer to it in CREATEPDF.
=IF(B2="Professional Package","<path template #1>,<path to template #2>)
We hope this article has helped you and given you a good understanding of how to automate PDF generation efficiently.