Auto Fill a Google Doc from a Google Form Submission

It’s been awhile since I have written anything to do with Google Apps Script, so I decided to start exploring some features of the Apps Script world that are new to me.

When I first got started with Apps Script almost 6 years ago, there were lots of things I wanted to do, but just didn’t yet have the skills to figure out or tried but ran into a limitation on the Apps Script platform. Now that Apps Script has matured, it’s time to revisit some of those early desires.

In this tutorial, we’re going to look at a workflow that should have a large swath of utility for people who read this site. We’re going to take a Google Form submission and then use that data to populate a custom template in Google Docs.

Getting Our Google Form Ready

To get started, we’ll need to create a Google Form to accept submissions and a Google Doc to act as our template.

For this example, I just created a basic form to accept some data that looks like this:

basic google form that will auto fill a google doc

As you can see nothing fancy going on here. I created a spreadsheet to accept the responses, as that is ultimately where our code will live that will generate the Google Doc.  google sheet where form responses are stored

Creating a Google Doc Template

After creating the form and the spreadsheet, I created a Google Doc in the same folder that will serve as our template document. Every time a form is submitted to the spreadsheet, our script will make a copy of this template and replace parts of the text with the data submitted to the form.

As you can see, this is a pretty basic document, but since we are just making a copy, your template could include lots of different styling, images, etc. if you so desire.

initial google doc template with template tags

There are a few important things to note about this template though. There are several ways to populate a Google Doc using Google Apps Script. One way is to programmatically create the document using code, meaning we would add the different document elements one by one using a script.

While that is possible, I found it very difficult to do, especially when trying to create a document with an kind of sophistication or styling.

The other way involves the strategy we’re taking here. We make a document that acts as a template, then we look for certain pieces of text within the template document, then replace those pieces of text with live data when our form is submitted.

Best Practices for Template Tags in Google Docs

However, since we’ll need to search all of the text in the document to replace it, it is best to make the text you want to replace unique in the document. To do that, you should use what are known in the web development world as replacement tags. In the example above, I’ve surrounded the names of the fields I want to replace in double curly braces like so {{Field to Replace}}.

While that is only one possibility, there are a few others that are commonly used:

{{Field to Replace}}
%Field to Replace%
#Field to Replace#
[[Field to Replace]]

At the end of the day, really all we are focused on is creating a unique tag that we can find later. Using some sort of character as brackets helps with this since two curly braces is an unlikely pattern to find elsewhere.

Another best practice here, just for you own sanity, would be to make sure that the text inside your brackets matches the header in the spreadsheet.

Using Code to Populate a Google Doc

Now that we’ve got everything setup, we can look at the code that will make all of this happen. For those unfamiliar with how to write a basic Google Apps Script project file, you might want to check out an earlier post where I go over the process from beginning to end.

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 fairly well commented to show the intent of each line, so I won’t rehash a bunch of it here:

function myFunction(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 file, and you get it by ID
  var file = DriveApp.getFileById('your_file_id_here'); 
  
  //We can make a copy of the template, name it, and optionally tell it what folder to live 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've got the new file created, we need to open it as a document by using its ID
  var doc = DocumentApp.openById(copy.getId()); 
  
  //Since everything 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); 
  
  //Lastly we save and close the document to persist our changes
  doc.saveAndClose(); 
}

Some things to note about this project, myFunction is what gets called when the form submission trigger runs, and it passes in the form values as the ‘e’ parameter to the function. This script makes use of both the DriveApp and DocumentApp classes of 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.

Wrapping Up

It took me a while to figure this difference out, but it can be summed up like this: all Google Docs are Drive files, but not all Drive files are Google Docs. Google Drive controls things about the file, like where it lives, who has access to it, and how we download it, but to make changes to a file that is a Google Doc we need to operate on it using the DocumentApp class.

After submitting our test form, we end up with a Google Document in the folder we specify that looks like this:

completed google doc after text has been replaced

While this is a pretty simple example, I foresee lots of use cases for a workflow like this, from creating certificates to populating invoices or purchase orders, this should be another helpful tool to keep in your Google Apps Script tool belt.

I'm working on building the most comprehensive course available on building workflows with Google Apps Script.

Join the Course Waiting List for a Huge Discount!

* indicates required

12 thoughts on “Auto Fill a Google Doc from a Google Form Submission”

  1. Alx says:

    Hi Jeffrey,
    First, your YouTube tutorial on auto filling a google doc from a form, in my opinion, is the best.

    Question:
    How would you go about emailing the automatically created document if the form had an email field?
    In other words, how would you email that document to the person who submits the form if an email field was available?

    1. BrownBearWhatDoYouSee says:

      Hey Alx,
      Thanks so much for the kind words!

      What you want to do should be straight forward. After following the steps in this tutorial, basically calling saveAndClose on the doc after replacing the text, you will want to get the Drive file representation of that document using the file id again, than get that as a particular MIME type (PDF, Word Doc, etc.).

      From there, you can just attach that file as an email. I have another tutorial on using Gmail attachments that covers some of that.

      Let me know if you have any questions as you start to implement.

      Thanks for watching and reading 🙂

      JE

  2. Michael Cenkner says:

    Thanks Jeffrey, lucid, helpful, concise – relatively rare qualities among those writing about Appscript! This also happens to very relevant for something I’m thinking about (even the email question, great!).
    I’m guessing the owner of the form would be owner of the doc, but the recipient of the email could make a copy that they’d own? I could try and see, but there are rabbit holes, as you talked about in a post.
    I’ll send you an email about the project, you might be interested. Thankx

    1. BrownBearWhatDoYouSee says:

      Thanks for the kind words! Glad the post was of some use to you. Whoever authorizes the script to run will be the owner of the form, but depending on the sharing settings the recipient may/may not be able to make a copy. What I typically do in situations like this is use the file.getAs method to get the doc as a PDF, then attach that to the email. That way the person has a read-only copy. Let me know if you have any questions as you start working through your project. Thanks for reading, JE

  3. Cory D says:

    Is there a way to download a copy to your computer? For instance. When I hit submit I want it to populate a google doc and download the copy to my computer or into an email. Thanks!

    1. BrownBearWhatDoYouSee says:

      I think you would have to create a custom HTML interface to trigger a download. To send it in an email, you could just get the link to the document after you fill it with data and send that in an email body. You can look at some examples on how to send an email in this tutorial. Thanks for reading, and if you want to post code samples I’m happy to take a look.

  4. Angelo says:

    Hi Jeffrey, I’m having a problem opening the Google Doc file I’ve created which exists in the same folder as the form, spreadsheet for answers containing the script. My file name is ‘Online Protest Form’ and it’s a Google Doc.

    I’ve tried your method:
    DriveApp.getFileById(‘Online Protest Form’)

    as well as:
    DocumentApp.openById(‘Online Protest Form’)

    Is there a file extension that I’m missing?

    Thanks
    Angelo

    1. BrownBearWhatDoYouSee says:

      Hi Angelo,
      Thanks for reading. If you look at the URL in the browser address bar when you have the Google Doc open, you’ll notice a long string of alphanumeric characters: https://docs.google.com/spreadsheets/d/12GHL3cVgaq8343433434343434xwwOWUdL8FdT_-usoaEI3YmLUkY

      All Drive file types will have an ID like this stored somewhere, typically after the /d/ in the URL. If you copy and paste just that part of the URL into the code, you should get different results.

      Thanks for reading and be sure to update this thread if things still aren’t working.

      Cheers,
      Jeff

  5. angelo says:

    Bingo! .. thanks.

  6. angelo says:

    Jeffrey, I wanted to return the favor. Here is a version of your script that parses the header of the linked-spreadsheet and will automatically look for any form-field in the doc and replace. Therefore it’s generic and be used nearly automatically. It also has a no-answer handler built-in. – Thanks! Ang

    function onFormSubmit2(e) {
    var answers = e.values;
    var QandA = e.namedValues;
    var ss = SpreadsheetApp.getActive();
    var sheet = ss.getSheetByName(‘Form Responses 1’);
    var headers = sheet.getRange(1,1,1,sheet.getLastColumn()).getValues();

    //file is the template file, and you get it by ID
    var file = DriveApp.getFileById(‘your file ID’);

    // We can make a copy of the template, name it, and optionally tell it what folder to live in
    // file.makeCopy will return a Google Drive file object
    var folder = DriveApp.getFolderById(‘your folder ID’)
    // use required fields to build file name
    var copy = file.makeCopy(QandA[‘Required-1’] + ‘-‘ + QandA[‘Required-2’] + ‘-‘ + QandA[‘Required-3’] ,folder);

    // var copy = file.makeCopy();

    //Once we’ve got the new file created, we need to open it as a document by using its ID
    var doc = DocumentApp.openById(copy.getId());

    //Since everything we need to change is in the body, we need to get that
    var body = doc.getBody();

    for (var i=0; i < headers[0].length ; i++){
    if(QandA[headers[0][i]] != ''){
    body.replaceText("{{" + headers[0][i] + "}}", QandA[headers[0][i]]);
    } else {
    body.replaceText("{{" + headers[0][i] + "}}", ' ');
    };
    }

    //Lastly we save and close the document to persist our changes
    doc.saveAndClose();

  7. angelo says:

    PS .. the one improvement I need to make ..

    When you build a form/questionaire, having a “?” is very common. Unfortunately, the “?” is used as a wildcard in body.replaceText . Therefore, currently I’ve replaced my “?” with “\?” in my Google Form, so that it will find just the “?” in the Google Doc.

    For instance, my GF might have a question…

    Are you married\?, which gets appended to {{Are you married\?}} in the body.replaceText statement.

    … and in the Google Doc, it put {{Are you married?}}.

    This works.

    It would be nice to put another if/else statement in there that looks at headers[0][i] and looks for a “?” and replaces it in-place with a “\?”.

    If we did that, it would be a truly universal script.

    Thoughts?

Leave a Reply

Your email address will not be published. Required fields are marked *