In this article:

How to autofill a Google Doc template from a Google Sheet

Auto-filling a Google Doc template with data from Google Sheets streamlines the process of generating documents such as invoices, reports, and letters.

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.

Prepare your Google Sheet

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

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 is stored in a Google Sheet so these instructions are for you as well.

Create a Google Doc template

Next, we'll need to make the Google Doc template that will get populated with the data from our Google Sheet. 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 with spreadsheet 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}}.

The contents of your Google Doc template

Connect your Google Sheet to Lido

We will use Lido, a new spreadsheet built to automate repetitive tasks, to merge our Google Sheet data into the Google Doc template to create new Google Docs. There is a free tier with paid plans as well. You can create a new account here:

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:

Add the =CREATEGOOGLEDOC() formula

Now we need to add the formula to create the Google Docs with our spreadsheet data.

The formula looks like this: 

    =CREATEGOOGLEDOC(<drive credential>,<template file>,<file name>,<status cell>)

Drive Credential - if its your first time, you'll need to add a credential to give Lido access to reading and updating your Google Docs.

Template file - use the file picker for Google Drive and choose your Google Doc template file.

File Name - name of the new Google Doc that is being created. You can make dynamic file names such as:

     "Application Form for " & B2

Status Cell - when the Google doc is generated successfully, the full path to the new Google Doc will be put in this cell. Usually, its the next column over from CREATEGOOGLEDOC.

      for example:

Step 1: Create a computed column in your spreadsheet table.
  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 Doc" (or whatever)
  4. Start Typing the =CREATEGOOGLEDOC() formula in the second row.



Computed Column

A Computed Column in Lido copies the same formula down to every row and grows as your data grows.


Step 2: Add a Google Drive Credential.

You can do so by choosing "Add Credential" in the first argument of CREATEGOOGLEDOC. Follow the prompts.

Step 3: Finish out the CREATEGOOGLEDOC formula

  1. Choose your Google Doc temlate
  2. specify name of doc. Include something dynamic such as "Grant Application for: " & B2 (where B2 holds the name of applicant for that row of data).
  3. Include the status cell (in this case, the next column over - F2 if you're in the 2nd row. .

Step 4. Create a new Linked Column called "Status"

Create a new Linked Column and name it "Status".

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.

Creating a Linked Column for "Status"



Linked Column

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.




Create a PDF

If you want to create a PDF instead of (or in addition to) a Google Doc, you can use the =CREATEPDF() formula in place of =CREATEGOOGLEDOC(). It takes in the same formula inputs.


Run a Test

Run a test of any of CREATEGOOGLEDOC action formulas and review the generated Google Doc.

  1. In the same cell as the CREATEGOOGLEDOC choose "Run Action" from the cell action menu.

Run the Column

To run the whole column of actions, choose "Run Column Now" from the "Create Doc" column menu. This will run all CREATEGOOGLEDOCS cells in the column

Run the whole column of actions at once

You can run this column manually to generate your new Google Docs. However, if you'd like to automate this whole process to automatically create a Google Doc whenever a new row gets added to your Google Sheet, then read on.

Automate it

Automatically create a Google doc when a new row is added.

Step 1. only create a Google Doc if one hasn't already been created.

We only want to create a Google Doc 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 CREATEGOOGLEDOC if our criteria is met. In this case, by making sure the Status field is still blank (it's a new record that's been added and a Google Doc hasn't been created for it yet) . Otherwise, it will show FALSE.

     =IF(F2 ="",CREATEGOOGLEDOC(<credential>,<path to template>,<file name>,<status>))

Only new records will have CREATEGOOGLEDOC run for them

Step 2. Choose "Run Column on a Schedule" from the column menu for the "Create Doc" 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 (CREATEGOOGLEDOC is an action formula)

Create a new Column automation by choosing "Run Column on a Schedule" from the "Create Doc" column menu


Let's review what we covered!

  1. Lido connects "live" to your Google sheets data.
  2. Lido uses an action formula, CREATEGOOGLEDOC, to generate a new Google Doc 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 Google Doc.

Frequently asked questions

Can I choose a different template depending on the values in my spreadsheet?

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.

     =IF(B2="Professional Package","<path template #1>,<path to template #2>)

Automate repetitive tasks with Lido

Save hours on repetitive and tedious work. Lido is a new spreadsheet that connects your spreadsheets, forms, PDFs, and email inbox.

Select trigger
When a cell value in Google Sheets changes
Check Google Sheets for today's date
When a new row is added to Google Sheets
On new Google Form submission
Send me a daily reminder
On new Typeform submission
When a cell value in database changes
Check database for today's date
When a new row is added in database
When a new HubSpot customer is created
Select action
Creates a one- or multiple-day calendar event with optional attendees
Adds an object to Hubspot
Create a Slack channel, and optionally add a topic or members
Makes any HTTP request
Replaces [@column_name] values in a Google Doc with the corresponding table row's values
Replaces [@column_name] values in a Google Doc with the corresponding table row's values, then export it as a PDF
Makes any HTTP request and returns the response
Inserts given array below defined values in given worksheet
Sends an email using your Google account
Sends an email using your Microsoft Outlook account
Sends a Slack message
Sends an SMS message using your Twilio account
Updates cells with given values
Updates a property of a Hubspot object
Trigger is required
Action is required
Get started