Hey there, my friends! Today, I want to share with you a super cool trick using Google Apps Script. We’re going to explore a workflow that will be incredibly useful for all of you. We’ll take a Google Form submission and use that data to automatically fill in a customized template in Google Docs. How cool is that?
Preparing our Google Form
To get started, let’s create a Google Form to accept submissions, as well as a Google Document that will serve as our template. In this example, I’ve created a basic form to collect some data.
As you can see, nothing too fancy here. I’ve also created a Google Sheet to store the responses, as this is where our code will run to generate the Google Document.
Creating a Google Document Template
After creating the form and the spreadsheet, I created a Google Document in the same folder that will serve as our template. Whenever a form is submitted to the spreadsheet, our script will make a copy of the template document and replace certain parts of the text with the form data.
As you can see, it’s a pretty basic document. Since we’re just making a copy, your template can include different styles, images, etc. if you’d like.
Now, here’s an important note about the template. There are multiple ways to fill a Google Document using Google Apps Script. One way is to programmatically create the document using code, where we add the different document elements one by one using a script. While that’s possible, I found it quite challenging, especially when it comes to creating a document with a certain level of sophistication or style.
The other way, which we’ll be using here, is a much simpler strategy. We create a document that acts as a template, then we search for certain pieces of text in the template document and replace them with the live data when our form is submitted.
Best Practices for Template Tags in Google Docs
Since we have to search the entire document for text replacement, it’s best to make the text you want to replace unique within the document. For this, you’ll need to use what’s known in web development as placeholder tags. In the example above, I’ve surrounded the field names I want to replace with double curly braces like this: {{Field to replace}}.
While this is just one option, there are a few others that are commonly used:
{{Field to replace}}
%Field to replace%
[[Field to replace]]Ultimately, the key is to create a unique tag that we can easily find later. Using a character like square brackets makes it easier to search because double curly braces are unlikely to be found elsewhere.
Another good practice here, just for your own sanity, is to make sure that the text inside your placeholders matches the header in the spreadsheet.
Using Code to Fill a Google Document
Now that everything is set up, let’s take a look at the code that will make all of this happen. For those who aren’t familiar with how to write a basic Google Apps Script project file, you might want to check out a previous article where I detail the process from A to Z.
For this tutorial, I’ll assume that everyone knows how to open Tools > Script editor to edit a script file and set a form submission trigger on a particular script.
The code below is well-commented to show the intention of each line, so I won’t repeat much here:
function autoFillGoogleDocFromForm(e) {
// e.values is an array of form values
var timestamp = e.values[0];
var firstName = e.values[1];
var lastName = e.values[2];
var title = e.values[3];
// file is the template document, and we get it by its ID
var file = DriveApp.getFileById('your_file_id_here');
// We can make a copy of the template, give it a name, and optionally tell it which folder to reside in
// file.makeCopy will return a Google Drive file object
var folder = DriveApp.getFolderById('your_folder_id_here');
var copy = file.makeCopy(lastName + ',' + firstName, folder);
// Once we have the new file, we need to open it as a document using its ID
var doc = DocumentApp.openById(copy.getId());
// Since all we need to change is in the body, we need to get that
var body = doc.getBody();
// Then, we call all of our replaceText methods
body.replaceText('{{First Name}}', firstName);
body.replaceText('{{Last Name}}', lastName);
body.replaceText('{{Title}}', title);
// Finally, we save and close the document to preserve our changes
doc.saveAndClose();
}A couple of things to note about this project: autoFillGoogleDocFromForm is what’s called when the form submission trigger is activated, and it passes the form values as the parameter e to the function. This script uses both the DriveApp and DocumentApp classes from Google Apps Script, so be careful when writing your own version to distinguish between what is a Google Drive file and what is a Google Document.
Setting up a Trigger for Form Submission
With all the scripts and Drive resources in place, we need to add a trigger to our project that will run the script every time a form is submitted. To do this, we can access the Edit menu in the script editor, then click on the option Current project’s triggers.
The trigger editor will open in a new window and show all triggers associated with a project if there are any. To add a trigger, click on the Add trigger button at the bottom right of the screen, which will open a modal menu with options to configure the trigger.
First, we want to choose the function that will be triggered. In this case, we’ll select “autoFillGoogleDocFromForm”. We can leave the Deployment as “Head”, unless you know what you’re doing here. Next, we want to select “From spreadsheet” as the event source and the event type “On form submit”. All of these settings ensure that the correct data is passed to our script when triggered.
The last parameter, which is an optional recommendation, determines how often you are alerted about errors. My recommendation here is to set it to immediate notification. Once you’ve set that, you can click Save, and our trigger will be active. You can try it out by submitting a test form.
In Conclusion
It took me a while to understand this difference, but it can be summed up like this: all Google Documents are Drive files, but not all Drive files are Google Documents. Google Drive controls aspects of the file, such as its location, the people who have access to it, and how we download it, but to make changes to a file that is a Google Document, we need to harness it using the DocumentApp class.
After submitting our test form, we get a Google Document in the specified folder that looks like this:
While this example is pretty simple, I envision many use cases for a workflow like this, from generating certificates to inputting invoices or purchase orders. It should be a handy tool to have in your Google Apps Script toolbox.
To dive deeper into this concept, I’ve created some additional tutorials that expand on features that some people might want to use:
This tutorial is very similar to using a Google Form to trigger the automation, but instead, we’re running the automation from a menu item and pulling data from an existing Google Sheet. We also write the URL of the created document back into the sheet.
Frequently Asked Questions
Many people have asked how they can replace text in their Google Document templates with a clickable link. The linked tutorial above shows how you can modify this script to insert links into your templates.
Remember, my lovely friends, the possibilities are endless with Google Apps Script. So go ahead and automate your life away, and keep on learning with Crawlan.com!
