Categories: Apps ScriptForms

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.

Notice

You should create the document in a new folder because the script creates the documents in the same folder as the template document.

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"
      }]
   });

}

Note

If you run the script from the script editor it will generate an error. The script is intended to run by using a trigger.

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.

SavoE

I'm an IT Manager and a big Google fan. In my free time, I like to write articles for How to GApps and help people on Google help communities (My Profile) and Quora. Check the About page for more. If you appreciate my work, maybe you can help me pay my hosting bill on Buy Me a Coffee.

View Comments

  • Hello and thanks very much for this tutorial. Just what I wanted and it worked first time straight out of the box :-)

    So, to take it further, is there any way I can populate the document with an image uploaded by the customer? I know the form question exists to "Let respondents upload files to Drive" but can this be done WITHOUT the respondent having to have a Google account?

    • Hi Neill,

      No, google requires the user to sign-in in order to upload a file. Maybe you can instruct the users to use a free service for image hosting like https://prnt.sc/ and paste the link in the form.

      Hope this helps.

  • Hello there;

    I would like to thank you for such a rich and accessible tutorial! My question is: is it possible to send the pdf and also a .doc version of the generated file?

    Thanks in advance!

  • HI there,

    I love this it is exactly what I have been looking for. Thank you! My question is can the script edit two docs at the same time and save them both? I need it to edit a Meeting Protocol doc and Meeting Minutes doc using the same answers given in the form. I would like this to all work together in an effort to same time and energy of creating two documents with the same info.

    • Hi Josh,

      You can create a comma separated list of email addresses and add them to the email variable. Something like this (if you have an extra email address in third item):

      var email = items[2].getResponse() + ", " + items[3].getResponse();
      

      or you can add it like this:

      var email = items[2].getResponse() + ", myemail@myaddress.com";
      

      Best regards.

  • Based on a question in the google form how can you auto fill different doc templates. For example if a question had two options A & B, if #A was selected use #A template if #B was selected use template #B?

    • Hi Jon,

      The short answer to your question would be: just use an IF statement to check for the selected template and then select the appropriate variable templateDoc. But, you can also use different questions to populate the templates, so you can design the form like that and also adapt the script. If i have time this week I will include this solution in the article, so check back.

      Best regards.

  • Your example shows the trigger being "From Spreadsheet". But methods like "getItemResponses" and "getResponse" relate to the Forms Service. Shouldn't your trigger be "From form"? This might also explain why users are getting an error of "‘getItemResponses’ of undefined"

    • Hi Tedinoz,

      You are right, I have reused an image from the previous article, and I didn't notice that. Thanks for your help. But I don't think that this can be the reason for the error since they can't select "From spreadsheet" if they create the trigger from the Form.

      Thanks again, I've changed the image.

  • Hi SavoE! This is a really elegant solution, thank you for sharing. Unfortunately I'm getting the same error as Leandro did below--TypeError: Cannot read property ‘getItemResponses’ of undefined at onFormSubmit--but I've set up a trigger onFormSubmit. Any advice would be greatly appreciated, thanks so much!

    • Hi Maya,
      You get this error when you submit the form or when you run the script?
      It's strange because this error can only be raised if you cannot catch the responses in "e", so, when you run the script manually it gives this error.

      • When I submit the form! Screenshot attached. Though i did run the script manually a couple of times before realizing what was going on--could that have affected things? Thanks so much!

        • It's best if you copy/paste your script here, maybe there is something you are missing, or it can be a simple typo.

          • No problem! I should also mention--when I fill out the form, the template duplicates correctly but the variables don't populate.Thanks again!

            Here's what I got:

            function onFormSubmit(e) {
             //open the template document by ID
             //you can find the ID in the link of the document
             var templateDoc = DriveApp.getFileById('google link');
             //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('{Var1}', items[1].getResponse());  
             body.replaceText('{Var2}', items[2].getResponse());
             body.replaceText('{Var3}', items[3].getResponse());
             body.replaceText('{Var4}', items[4].getResponse());
             body.replaceText('{Var5}', items[5].getResponse());

              
             //Save and Close the open document and set the name
             openDoc.saveAndClose();
            newTempFile.setName(items[0].getResponse() + '-' + items[1].getResponse()) }

          • I can't see anything wrong with the script. Let me test it on a different account and I'll get back to you.
            Meanwhile, can you try again by creating a new form.

  • Hi, can automate to automate pdf in another folder? I want to create template folder and other to pds, like this gdrive>template_a>invoice_1

    • Hi Leandro,

      This script only emails the PDF, but with just a few extra lines of code, it can save the PDF to a folder in your Drive. I will update the script with this solution, so check back soon.

      • SavoE, really thanks. But i received this error:

        TypeError: Cannot read property 'getItemResponses' of undefined at onFormSubmit(Código:13:26).

        And another doubt. This code is working for any form, I can't link just one?
        in my account i have several forms and all of them are script enabled.

        • Hi again,

          You get this error because you are trying to run the script from the script editor. Setup a trigger "on Form Submit" to run the script when you submit the form and you won't get this error.

          And yes, this script can run on any form.Just open the form, open the script editor and copy/paste the code. And normally, you need to adapt it to your template.

          Best regards.

Recent Posts

Google Form to Slides + Save to Drive and/or send it in email as a PDF

On your request, as an addition to the how-to: Google Form to PDF (save to Drive and/or send it in…

1 day ago

Google Form Script to Automatically Add New Choices from “Other:” Form Responses

Whit this Google Form Script you can Automatically Add the "Other:" response from the multiple-choice question or checkbox question to…

2 months ago

Google Form to PDF (save to Drive and/or send it in email)

This solution will create a PDF file from a Google Form submission using a Google Doc as a template. You…

3 months ago

Automated e-card sender – Google Spreadsheet Script to Send Birthday Cards via Email

Sending an email to wish your customers a happy birthday it's usually a thing that big companies do. There is…

4 months ago

Embed Google Form, Sheet, Chart and Doc in WordPress

You have a plugin for that too, but adding more plugins to your WordPress site can make it slower, and…

6 months ago

Add Timer to Google Form Quiz

Finally, I fund a way to add a timer to a google form, and yes, it forces the user to…

10 months ago