Google Sheets
Turn your spreadsheet data into PDF / Google Docs in minutes
DocsAutomator's Google Sheets integration is the fastest way to create professional documents from your sheets data. Let's dive in!
Prefer watching via video? Please find two videos below, a 5 minute run through and a full demo explaning all features of the integration:
New to DocsAutomator? The fastest way to get started from Google Sheets is our quick start guide: Quickstart Guide
Overview
DocsAutomator's Google Sheets integration works on a primary sheet and linked sheets basis. Documents are printed from rows in your primary sheet, while lists of data ("line items") can be added to documents from linked sheets. This allows for almost any document to be created based on your Google Sheets data.
Created PDFs (and optionally Google Docs) are automatically saved to specified columns in your primary sheet.
Triggering document creation happens via a simple webhook (just add the formula to a column in your sheet) or via a copy / paste Apps Script which adds a "create document" button to your menu bar (no coding knowledge required).
Step by step guide
1. Create automation
The first step is to create a new automation in your DocsAutomator account.
2. Select Google Sheets as your data source
3. Select your Google Doc template
Every automation in DocsAutomator needs a Google Doc template to create documents from. Please visit our Google Doc Template Guide for all the options on how to get started. If you just want to test DocsAutomator, a good starting point is to start from scratch (option in the middle).
In our example, we are using DocsAutomator's invoice template from the template gallery.
4. Prepare your Google Sheet
Now it's time to select your Google Sheet. Please have a look at the following example for the basic syntax (open via this link; feel free to copy!):
In this example, Invoices is the primary sheet and Line Items is used as a linked sheet / line item sheet. We are printing a single invoice (one row from the primary sheet) and are potentially including multiple line items (multiple rows from the linked sheet) for every single invoice. As you can see, DocsAutomator expects the first row to be a header row with column titles (that's how columns are identified).
You can of course print any type of document with DocsAutomator, but the structure has to match the example above.
5. Select your Google Sheet
6. Select primary sheet
In our example (see above), the primary sheet is called Invoices. We are printing documents for a row in this primary sheet.
7. Select Primary Column + Columns for Document Name and PDF Link
7.1 Primary Column
Your primary column identifies your rows. In our example, that column will be Invoice Number as it makes sense to identify invoices by their number. This column is used to select rows for generating previews as well as match potential linked data in linked sheets / line item sheets.
7.2. Document Name Column
Choose a column that contains a unique name for your documents.
Pro tip: Use a formula to build a unique text for your primary column (row identifier) and document name
7.3. PDF Link Column
This specifies the column where links to generated PDFs will be stored.
8. Map placeholders to columns
Now it's time to map placeholders from your template to columns in your primary sheet. In our example, we have a lot of standard invoice placeholders like client name, client address, invoice number etc. that we are mapping.
Use DocsAutomator's AI column mapping to let AI take a good guess for the right placeholder / column combination
9. (Optionally) Map line items to columns
If you want to add line items to your documents, you will find a section titled with Line Item Table 1 for the first line items table below the primary column mapping. Not every document has line items of course, this only applies if you want to add a related list of things (like line items on invoices in our example).
9.1. Line Item Sheet
The first step is to define a line item sheet. This is the sheet in your Google Sheet which contains the line items that related to your primary sheet. In our example, this line item sheet is called Line Items (but could be called anything).
9.2. Primary Column
Now select the primary column. This column identifies related rows from your primary sheet. Values need to be the same as the primary column in your primary sheet. Please have a look at the example sheet above. You will see that our primary / identifier column for the primary sheet is called Invoice Number. We have to select a column in our line item sheet that contains the same value used for invoice number. As you can see, the column Invoice contains the same values found under the column Invoice Number in our primary sheet.
Please also have a look at the video tooltip next to the title Primary Column
9.3. (Optional) Grouping + Autonumber
Optional, additional features for line items. Please have a look at Line Items Grouping + Calculations and Auto Row Numbers
9.4. Map placeholders to columns
As before for the primary sheet, please map placeholders from your template to corresponding columns in your line items sheet (use AI for this!). Once everything is mapped, it may look like this:
10. Create previews
After having saved the settings above, you're now able to generate previews on the right side. Just pick a row from the dropdown (only first 50 options are loaded; search for more). After having selected a row, click Create Preview. Below a generated preview for our example:
You can create as many previews as needed. Use this feature to make sure you're 100% happy with your generated documents.
11. Set up Document Creation in Google Sheets
Finally, it's time to set up document creation from Google Sheets. Here you have two copy/paste options (please also not the video tooltips for each in the interface):
This is a simple sheets formula that will add a link in a column to every row in your primary sheet. Documents are generated by browser request and will open a new tab in your browser.
Here is an example of the link added in our example sheet (feel free to adapt the default displayed text "GENERATE DOC"):
The benefit of using an Apps Script is that you can easily trigger document generation for multiple rows by just selecting multiple rows.
Additional options / features
Adding images
You can add images to your documents via links stored in a column. Please have a look at Dynamic Images to understand how placeholders need to be declared in your Google Docs template. Multiple images are added if links are separated by comma in your column.
PDF Expiration
Under 4. Template / Document Generation Options in your automation settings, you find the option PDF Expiration. This defines how long DocsAutomator keeps your PDFs. Please adjust according to your needs. See more here: PDF Expiration / Storage
Save Google Docs
Sometimes you may want to keep editing your document or prefer generating a Google Doc. You can turn this on under 5. Additional Actions After Document Generation and also specify a column where the Google Doc shall be stored. In addition, you can specify a Google Drive Folder where generated Google Docs shall be stored. See Saving Google Docs for more.
Save PDFs in Google Drive
You also got the option to store generated PDFs in Google Drive by specifying a folder (or leaving this empty to store in My Drive).
Send Email
DocsAutomator allows you to send generated documents via email, specifying dynamic parameters via your added data. You can also define conditions under which these emails are sent.
Last updated
