In this article, we will show you how to automate document creation with Google Sheets using Lido. Simply follow the steps below!
Being able to automate document creation streamlines the process for businesses of creating invoices, reports, and letters based on customer or business data.
This automation saves time, reduces manual entry errors, and ensures consistency across documents. It's particularly useful for businesses and individuals who frequently generate documents with similar structures but varying content.
For this solution, we will use Lido, a new spreadsheet built to automate repetitive tasks for you. Lido merges your data from Google Sheets into a template created in Google Docs and then saves a PDF version of it on your Google Drive.
There is a free tier with paid plans as well. You can create a new account here: https://www.lido.app/go/signup.
If you prefer a Google Doc instead of a PDF, there are instructions below to do this as well:
First, we need to get all of our data that we want merged into a Google Doc template into the right format in our Google Sheet. In this example, we're going to generate one Google Doc per Google Sheet row.
To make sure everything works properly, make sure your Google Sheet data conforms to these formatting rules:
1. Your column headers must be in the first row of your sheet.
2. Column headers cannot contain special characters, like quotes or brackets.
3. No empty columns or rows separating your spreadsheet data. Double check for things like empty grouped or hidden rows and columns, since these can cause problems.
This is an example of a correctly formatted Google Sheet. The column headers are in Row 1, and there are no empty columns or rows:
If your data is coming from a Google Form, your data should be stored in a Google Sheet, so these instructions are for you as well.
First, we'll need to make the Google Doc template that will get populated with Google Sheets data to automate document creation. You can either start from scratch or adapt an existing template that you have.
We'll designate which parts of the document will be replaced by Google sheets data by using the {{Column Name}} syntax. Make sure there are no spaces between the curly braces and letters!
The key here is that whatever is inside of the curly brackets must exactly match the name of the column header in your Google Sheet, including the same capitalization and spaces.
So, for example, if in our Google Sheet, we have a column for First Name, then everywhere we want that value to be inserted into the template we'll need to put {{First Name}}.
Below is an example of what your Google Docs template might look like:
From the Lido Files page, click on "New file." Then, from the "File" menu, click "Connect real-time 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>)
Drive Credential: If it's your first time, you'll need to add a credential to give Lido access to your Google Doc template and saving a PDF to your drive.
Template file: Use the file picker for Google Drive and choose your Google Doc template file.
File Name: This should be the name of the new PDF that is being created. You can make dynamic file names such as: "Application Form for" & B2
Status Cell: When the PDF is generated successfully, the full path to the new PDF will be put in this cell. Usually, it's the next column over from CREATEPDF.
Hover your mouse over the green table of records from your Google Sheets. You'll see a "+" sign. Choose "Add Computed Column". Name the Column "Create PDF?". Start typing the =CREATEPDF() formula in the second row.
A Computed Column in Lido copies the same formula down to every row and expands as your data grows.
Then, add a Google Drive credential. You can do so by choosing "Add Credential" in the first argument of CREATEPDF. Follow the prompts. Choose your Google Doc template.
Specify the name of the 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).
Include the status cell (in this case, the next column over, F2, if you're in the 2nd row).
Create a new Linked Column called "Status". A "Linked Column" in Lido lets you add notes to external data and stays linked to your source data (such as records in Google Sheets) by tying itself to a column in the source data that has unique values. Timestamp, ID columns, or email can be good ID columns for a "Linked Column."
If you want to create a Google Doc instead of (or in addition to) a PDF, you can use the =CREATEGOOGLEDOC() formula in place of =CREATEPDF(). It takes in the same formula inputs.
Note: After you create the "Status" column, you'll need to reset the 4th argument of CREATEPDF to make sure it's the correct column as it'll get pushed over one 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, and automatically generate PDFs for each record in the table.
You can run this column manually to generate your new PDFs.
However, if you'd like to automate document creation and create a PDF whenever a new row is added to your Google Sheet, then read on.
To automate the creation of a PDF when a new row is added to a Google Sheet, we'll create an automation on the "Create PDF?" column where the CREATEPDF action formula is.
We only want to generate a document if we haven't already created one for that row of data. In other words, only if the "Status" column 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 your sheet, 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 data 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. If you would like to generate a Google Doc instead, follow the same steps but use CREATEGOOGLEDOC.
3. You can set up an automation to regularly check for new spreadsheet rows 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 other values 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 you now know how to automate document creation using Lido.