Google Form Script to AutoFill and Email a Doc Template

I have used a similar solution for creating documents from a template, but it was a spreadsheet script and used a sheet as a template. Later on, I switch to a Google Doc template, as it was easier for my coworkers to change the content.

Since nobody here was interested in the spreadsheet, I decided to readapt the Script to work from Google Form and directly create the documents from a Google Doc template. Also, the script can have extra code to convert the doc to a PDF and email it.

So, In this tutorial, we are going to create a Google Form that will use a script to create a document from a template, save it in your drive, and, if needed, convert and send it as PDF to an email address.

Catch the responses in Google Form Script with trigger

The main difference from the spreadsheet script is this part. In Google Spreadsheet you can use “namedValues” object or “values” array to catch the responses, or you can simply get the range from the Form response sheet.

In Google Form Script, when using an installable trigger “On form submit”, google passes the event object as a FormResponse object. So, to read the responses you need to treat them as FormResponse object and get them by using “getItemResponses()” method. In short, here is a simple Google script that reads the Form responses sent by the “On form submit” trigger:

function onSubmit(e){
  var items = e.response.getItemResponses();
  for (i in items){
    Logger.log("getItem().getTitle()=%s, getResponse()=%s", items[i].getItem().getTitle(), items[i].getResponse());
  }
}

Read more about Google Form events here.

After we catch the response, the next step is to open the template doc, create a copy of it and search for the placeholders and replace them with values from the Google Form.

So, the first step is to:

Create a Google doc template

Create a new Google Doc in your Drive and design your template. For the places where you need to insert the data from the form, shortly placeholders, use curly brackets with a short text, something like so: {Name}. Your Doc should look like this:

The script will look for the text {date} and replace it with the date from the Google Form, the same for the {Name}. For my example I use just this two placeholder, but you can use as much as you need.

Here is the link to my template doc: Template Doc.

Create a Google Form

There are no special requirements on how to build your form, but I recommend using only these types of questions: short answer, paragraph, multiple choice, drop-down, date, and time. These types of questions only return one answer that can easily be inserted into the doc. Get more info here: Google Forms Question Types.

So, if you just wanna test this example, create a Form and add the questions: Name (short answer question), Date (date question), and Email (short answer question). You can also create a copy of my form by following this link: Create a copy of “Populate template doc” Form.

Next, open the script editor from the three-dot menu and insert the script.

Using the Script to Populate the Template

Now, that you have your Form and Template ready, you can add the script. As I mentioned earlier, open the script editor and paste this script:

function onFormSubmit(e) {
  //open the template document by ID
  //you can find the ID in the link of the document
  var templateDoc = DriveApp.getFileById('12EZQW2k757k4ZVPy1VyH_y-Wgx4zPF7g7zUCl-PEsH0');
  //create a copy of the template, we don't wanna mess up the template doc
  var newTempFile = templateDoc.makeCopy();
  
  //open the new template document for editing
  var openDoc = DocumentApp.openById(newTempFile.getId());
  var body = openDoc.getBody();
  
  //get the responses triggered by On Form Submit
  var items = e.response.getItemResponses();
  
  //find the text in the template doc and replace it with the Form response
  //items[0].getResponse() is the first response in the Form
  body.replaceText('{Name}', items[0].getResponse());  
  body.replaceText('{date}', items[1].getResponse());
  
  //Save and Close the open document and set the name
  openDoc.saveAndClose();
  newTempFile.setName(items[0].getResponse() + '-' + items[1].getResponse())

  //If you don't need the option to send email with PDF,
  //just delete this section of the script, leave the last curly bracket

  // convert the new document to PDF and mail it
  var theBlob = newTempFile.getBlob().getAs('application/pdf');
  var email = items[2].getResponse();
  var subject = 'Your new documnet';
  var body = 'Hello ' + items[0].getResponse() + ', <br/>Here is your new document.';
  GmailApp.sendEmail(email, subject, body, {
    htmlBody: body,
    attachments: [{
      fileName: items[0].getResponse() + ".pdf",
      content: theBlob.getBytes(),
      mimeType: "application/pdf"
      }]
   });

}

This short script will open the template doc and make a copy of it, we don’t wanna change the original template. Next, it will get the responses from the Form and using the “replaceText” method, it will replace our placeholders with the text from the Form. Also, it will set a new name for the document, in my example, by using the Name and the Date.

If the order of the question in your Form is different then mine, it will set a wrong text for the placeholder. So, you have to change the “items” number to match your order. In the next image you can see that, for example, the Name question is the First question so I use 0 to get the answer: items[0].getResponse()

What to Change

The first thing you need to change is the ID of your template document, located on line 4. Get your ID from the link of your template document and insert it here.

The next thing you need to change is the name of the placeholders and the number in the “items” response to refer to your form/template. In my example, I have just two placeholders to change: {Name} and {date}, you can see the statements located on line 17 and 18. Normally, you can add more statements like this if you have more form questions/placeholders to change.

Script has an option to send an email with the PDF created from the document, if you don’t need this option just delete everything from line 28 to 39. If you want to send emails, just check/change the “items” number from the Form responses to reflect your Form. For example, if you collect the email address in the first question in your form, you need to change the “items” number to 0 on line 29, like this:

var email = items[0].getResponse();

Create a Trigger: On Form Submit

In order for the script to run correctly, it needs to be activated through a trigger. So, to add a trigger, click on the “Current projects triggers” icon, from the toolbar.

Next, from the Projects triggers window, add a new trigger:

From the New trigger dialog box, select the trigger “On Form Submit” and click on Save.

After saving the trigger, follow the on-screen instruction to review and accept the permission. This process can get confusing, so if you have any problems, check this guide: Google Script Authorization: Review and Accept the Permission Guide.

Now, you can go to your Form and submit a response to test it. If everything is fine, you can check your Drive folder to see if the Document is created, and also your email for the PDF.

Bonus: Save the PDF file in your Drive

Some of you have requested to include a code in the script, so you can save the PDF in your Drive, not just mail it. I don’t recommend this, especially if you are on a free Google account, since the PDF counts towards the quota limit. Anyway, it’s just a few lines of code so here it is. Insert this code at the end of the script, just before the last closing bracket (paste it on line 40)

  // save the PDF to a Folder by using the folder ID
  var folder = DriveApp.getFolderById('14nUcGSmThsGvAGcxcWdpH11BoNcc0lUb');
  var savePDF = folder.createFile(theBlob);
  savePDF.setName(items[0].getResponse() + '-' + items[1].getResponse() + '.pdf');

Since you need to be more organized, it’s better to save the PDF in a different folder. This is why I have included the first line of code, so you can add the Folder ID where you want the PDFs saved. So, create a Folder in your drive, probably a sub-folder to the original folder, and get the Folder ID from the link. Paste it to replace mine, and now you can send in a Form response. Check the Folder for the PDF to see if everything is OK.

If you have any problems implementing this solution, check the “My executions” sections in script.google.com and post the error you get in the comment section.