Automate your letters with Google Docs — the only document templating tool you need

Greg Vonf @ Business Automated
6 min readFeb 14, 2023

--

Create automated business letters with dynamic data from Google Sheets with Google Docs templates and Make.com

Boost your efficiency with easy to apply templates

Introduction

Creating business letters and envelopes can be a tedious, time-consuming task. Fortunately, there are tools available to make it much easier. With Make (formerly Integromat) and Google Docs, you can create automated business letters and envelopes from data stored in Google Sheets in an efficient, speedy manner.

Creating automated documents is also know as mail merge. Mail merge is a process used to create multiple documents at once using a template and data stored in a spreadsheet or database. It is an efficient way to produce multiple documents with the same formatting and content, but with personalized information. Companies such as Formstack, Docusign and Pandadoc all offer mail merge capabilities, allowing users to quickly generate personalized documents.

Using Google Docs is a great ( and cheaper) alternative to those solutions. It is an excellent way to save time and streamline the process of creating such documents. You can customize the output to best fit your needs, such as by adding company logos, signatures, and formatting. Additionally, you can easily store and access the documents you create via Google Drive, so they’re always just a few clicks away. With these tools, creating automated business letters and envelopes is no longer a daunting and time-consuming task — it’s quick and easy.

Software we are using here:

Create Templates with Google Docs

Using Google Docs, you can create templates for your business letters and envelopes, which can be customized to suit your needs. We are talking about regular Google Docs that you are familiar with. The key part is indicating the placeholders on the page that will be later replaced with data from your Google Sheets. We are using in this scenario curly brackets {{placeholder}} to mark the placeholders.

You can easily add text, images, and formatting to create professional-looking documents that look just the way you want them to. Since the template is stored online, you can also share it with others in your organization, ensuring that everyone is working from the same template. This saves time and ensures consistency across all of your documents.

Example of envelope template

Collecting data in google Google Sheets

In order to generate automated business letters, you will need to collect the necessary data in a Google Sheet. This data will be used to populate the placeholders in the letters/ envelopes. For this example, we will be sending letters with order values to customers. You can follow this example and add any additional columns and custom placeholders to the letter.

To do this, you should create a Google Sheet with the necessary columns. These columns should include: Id, First Name, Last Name, Company Name, Address, City, Zip, State, Total Order Value, Send Letter?, and Status.

This data will be used as the source material for the next step of our automation process

Automate Document Creation with Make

Make (formerly Integromat) is an automation platform that enables users to create automated workflows. With Make, you can easily connect different apps, web services, databases, and APIs together in order to create powerful automation workflows. In our example, you can connect Google Sheets and Google Docs to automate the creation of business letters and envelopes. Make will pull data from your Google Sheets and use it to populate the letter and envelope templates in Google Docs.

In Make a scenario is an automated workflow created with Make (formerly Integromat). It is a way to connect different apps, web services, databases, and APIs together in order to create powerful automation workflows. Make workflows are easy to set up and can be customized to meet your specific needs.

Each step is called a module. In our basic case we are using 5 modules.

Basic document merge scenario with Google Sheets, Docs and Gmail

Reacting to data in Google Sheets

The Make Google Sheets Search Rows module is used to search rows in a Google Sheet. It will search for rows based on the criteria you specify, such as a specific column value or date range. This module is useful for searching for specific data in your Google Sheet, such as rows that contain a certain value.

For example, you can use this module to find all rows in your sheet that contain “Send Letter?” = Yes. This will allow you to only send letters to those customers who you have manually selected that they should to receive a letter.

Set up of Make Google Sheet search rows module
We are also making sure that letter is not sent out once, by checking if Status field is empty.
We are marking rows to send by placing “Yes” in column J

Filling out Google Docs template with data

The Make Create Document from Template module is used to create a new document from a template. It takes the data from the search rows module and uses it to populate the placeholders in your template. It then saves the new Google Sheet document. All you need to do is:

  • find the template you want to use
  • match data with right placeholder
  • select location where to save the output
The process is similar for both main document as well as envelope.

To avoid scenario running if there is no rows with data we are using filter.

This filter will stop execution if there are no data bundles.

Providing feedback to Google Sheet

Once scenario is successfully executed we need to provide feedback to Google Sheets that the row was processed and document generated.

We are doing this by adding word “Done” in the right column.

Sending out email to conclude scenario

At the end we are using Gmail module to send out notification to our team that the letters are ready to print out.

Basic Gmail configuration

As an option we are also able to convert our Letter to PDF and sent it out as email attachement. You can achieve that by adding a Google Drive download module, toggling the “Show advanced settings” and selecting PDF format.

Scenario version where you send out PDF as Gmail attachment at the end.
“Show advanced settings” and selecting PDF format.
Add attachment and use the auto-mapping to download module

Scheduling

Once you have completed setting up your scenario, you can easily schedule it to run on a regular basis. This ensures that your documents are always up-to-date and that you don’t have to manually run the scenario every time you need to create a new document.

Make offers several scheduling options that make it easy to set up recurring tasks. You can schedule a task to run once, daily, weekly, monthly, yearly, or on specific days of the week/month. You can also specify the time of day when the task should run.

In our case we set scheduling to run every 15 min

Conclusion

Creating automated business letters and envelopes with Google Docs and Make is an effective method for producing documents with the same structure and content but with individualized information. You can easily set up templates using Google Docs and accumulate the required data in a Google Sheet, and then use Make to generate automated documents with dynamic data from your Google Sheet. Automating this process will save you time and effort, while guaranteeing uniformity across all of your documents.

Get the templates and complete blueprints here:

https://businessautomated.gumroad.com/l/automated-business-letters

Watch full description and walkthrough in this video:

Business Automated is an independent automation consultancy. If you would like to request custom automation for your business, visit us at https://www.business-automated.com

If you like our tutorials — buy us a coffee☕: https://www.buymeacoffee.com/business

Follow us on Twitter🐦: https://twitter.com/BAutomated

Watch more on Youtube ️📺: https://www.youtube.com/c/BusinessAutomatedTutorials

--

--

Greg Vonf @ Business Automated

Greg is the founder of Business Automated, an agency helping small businesses streamline and simplify their processes. For more visit www.business-automated.com