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 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 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.

Setting a Trigger On Form Submit

Now that we have all scripts and Drive resources in place, we need to add a trigger to our project so that the script runs whenever a form is submitted. To do that, we can navigate to the ‘Edit’ menu in the script editor, and then click on the “Current project’s triggers” option.

an image of the edit menu of google apps script interface with the current project's triggers highlighted

 

The trigger editor will open up in a new window and show us all of the triggers associated with a project if there are any. To add a trigger, click the ‘Add Trigger’ button in the bottom right of the screen, which will open up a modal menu with options for setting the trigger.

a modal window to add a trigger in google apps script

First, we want to choose the function that will get triggered, in this case ‘autoFillGoogleDocFromForm’ will get selected. We can leave the deployment as ‘Head,’ unless you know what you are doing here. Then, we want to select ‘From spreadsheet’ as our event source and “On form submit” event type. All of those settings ensure that the correct data gets passed to our script when it is triggered.

The last setting, which is an optional recommendation, determines how often you are notified of errors. My recommendation is set that to notify you immediately. Once you’ve sorted that, we can click save and our trigger will be active. You can give it a try by submitting a test form.

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.

191 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?

  8. Mohit says:

    Hi Jeffrey,

    I am looking to add below functionality in my google form:
    I need multiple answers to be entered and recorded as per user’s choice. For ex: if user wants to add 1 phone number he enter 1 , 3 phone numbers he eters 3 different numbers and so on..

    Would appreciate if anyone can help me with this.

    1. BrownBearWhatDoYouSee says:

      Hi Mohit,

      It sounds like you want to make a Google Form with dynamic inputs, where I click a plus button and get another space to add a phone number as many times as I want. Does that sound accurate?

      If so, unfortunately there isn’t a way that I know of to make that experience using Google Forms. When I’ve done things like that in the past, I’ve always set an upper limit on the number of response they can provide and make all fields beyond the first one optional. So, if they can submit up to five phone numbers, make 5 phone inputs with only the first one required.

      App Maker might let you get a bit more fancy with the interface, but I don’t know that for sure.

      You last ditch effort would be to create a custom HTML form that is served by Google Apps and ties into a spreadsheet. This would allow you to design the interface anyway you want, as long as you can develop a spreadsheet to capture the data in a structured way. However, I’d say this is out of reach for most people without a significant amount of experience with HTML and JS.

      Let me know if there are other details you can provide that might help us find a solution for you.

      Thanks for reading,
      Jeff

  9. Hoang says:

    Hi Jeff, thank you for this awesome article. I’m trying to do this for automation in some tasks in my company. One question that I need to ask you: Do you know how to replaceText in Spreadsheet or Slide? I check the Apps Script Reference for Spreadsheet and Slide but I can’t find anything similar to Document Body replaceText(…) function. Thank you very much.

    1. BrownBearWhatDoYouSee says:

      Hi Hoang,

      See this link regarding the Slide.replaceAllText method of the Slide class. That should be what you are looking for, as I know it is possible to replace text in a slide as I’ve done it in other project.

      Thanks for reading,
      Jeff

  10. Syukron says:

    Hi Jeffrey! Tahanks a lot. Your tutorial had been a great help for a starter like me. Anyway, can you recommend a book to read about this kind of thing? about coding on google spreadsheet and other google doc platform?

    1. BrownBearWhatDoYouSee says:

      Not sure I’m aware of any books, but Ben Collins offers some great stuff if you are interested in some more structured materials.

  11. Todd says:

    This post was very helpful. Thank, you. Somewhat related to your post regarding auto-creating and filling a new doc using submitted form data, is it possible to do the same thing with sheets? In other words, is it possible to use apps script to auto-create and fill a new spreadsheet with select data from a form submission?

    1. BrownBearWhatDoYouSee says:

      Hey Todd,

      Thanks for reading. It does indeed look possible using the SpreadsheetApp.create method to create a new spreadsheet. It seems like from there you’d need to use the Spreadsheet class that gets returned to add the data.

      Cheers,
      Jeff

  12. Todd says:

    To clarify, just like your post related to creating documents, I want to create a new spreadsheet for each new entry.

  13. Beth says:

    Greetings, Jeff,
    This tutorial is nothing less than amazing! I cannot express enough how grateful I am that you choose to use your talents in this way. I was tasked to develop a google form that would create and email a document (pdf) in order to streamline a very cumbersome, time consuming process at work. I have to admit, I had absolutely no idea what I was doing. My knowledge of any sort of coding or scripts is rudimentary (at best). I figured there had to be a way, so I began researching. I was thrilled to find your tutorial. My google form/email/pdf attachment is working so well, other departments want a similar product. Thank you so very much.

    1. BrownBearWhatDoYouSee says:

      Hi Beth,

      Thank you for the thoughtful reply. I’m so glad that my post was helpful to you. Be sure to check back as I’ll continue to add content related to Google Apps Script.

      Thanks for reading,
      Jeff

  14. Jen Morris says:

    This is awesome Jeff and so close to what I am looking for!! I just need to add one component that I’m having trouble with. I would like the document to be created from the google sheet AFTER a vlookup has been run from the form responses. Does that make sense? How do I add that step in the script? I tried adding a macro to copy the vlookup formula after the response is submitted, but it’s not working.

    Any help/suggestions would be great. I cannot do any “Add-ons” so I need to do this via script and I’m not as cool as you :).

  15. Jen Morris says:

    I’m wondering too if a macro would work? Have the macro run before the new doc is created to include the additional information? Where would I put that in the script??

    Thanks

    1. BrownBearWhatDoYouSee says:

      My inclination would be to translate the steps in the macro to a function in Google Apps Script. Can you describe what is happening in the macro?

      In my example, the doc is created directly from the form event input, but there may be another trigger like onEdit that could be more applicable. I’m also really not that good with spreadsheets LOL, so I’m not that cool : )

      Thanks for reading!

  16. Jasen Waterson says:

    Every time I trigger from my form, I get this error, Access denied: DriveApp. at myFunction(line)

    Any reason why this would be happening?

    1. BrownBearWhatDoYouSee says:

      This would suggest that you don’t have adequate privileges on the Drive folder/file you want to operate on. I’d check that first and comment back if that isn’t the issue.

  17. NIKOLA SPASOVSKI says:

    Awesome tool to automate certain processes, however, I must be doing something wrong and as I am not getting any error messages, I don’t know where to look for the issue.
    When I fill out the form, nothing changes in the folder ie the new Doc file doesn’t create.
    If I try to run the script in the script editor window, I get this error “TypeError: Cannot read property “values” from undefined. (line 3, file “Code”)” which I understand is dur to the fact I run it from within the script editor and is not triggered by a form submission.

    Any help is highly appreciated!

    1. BrownBearWhatDoYouSee says:

      Can you post the contents of your script? That generally is the only way I can help troubleshoot.

      JE

  18. NIKOLA SPASOVSKI says:

    Sure, thanks so much! Here is the complete script:

    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(‘https://docs.google.com/spreadsheets/d/158ifecBYqhn0AeRYJBIquh1PO_g5ciSyKnizoV_15NA/edit#gid=217946722’);

    //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(‘https://drive.google.com/drive/u/1/folders/1aw5GHihJLxEj702Kx3eW1pVwlmR9Pmli’)
    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();
    }

    1. BrownBearWhatDoYouSee says:

      Ok, thanks! The first thing I can see is that you are using full URLs to docs and folders instead of the ids; for example, in the first call to DriveApp.getFileById you pass in this url: https://docs.google.com/spreadsheets/d/158ifecBYqhn0AeRYJBIquh1PO_g5ciSyKnizoV_15NA/edit#gid=217946722’

      The id is actually just the part after the /d, so this alphanumeric string 158ifecBYqhn0AeRYJBIquh1PO_g5ciSyKnizoV_15NA.

      Try to address those two points, run the script again and let me know if you get different results.

      Thanks for reading, Jeff

  19. NIKOLA SPASOVSKI says:

    Thanks Jeff, that might have been the issue, but I also recreated the trigger and now it’s working flawlessly!

    Thanks once again!
    Nikola

    1. BrownBearWhatDoYouSee says:

      Awesome! Glad I could be of some assistance, and thanks for reading!
      Cheers, Jeff

  20. lalla says:

    I have just spent two sleepless weeks looking for a replacement service to do this for a company I work for.
    We want to replace our current services since when there is any issue they point fingers at each other and nothing gets resolved so business stops.
    I don’t know if I can figure out the code to do this but you sure make it seem possible.
    Would sure like to attempt it but I know I’m going to need some help…. but not as much as I need sleep.
    Oh and can this work for 2 documents to be created from the same data in the sheet?

    1. BrownBearWhatDoYouSee says:

      Hi Lalla,

      Thanks for reaching out. This can work for really as many documents as you want. I’ve created a lot of setups like this for other business that function pretty much at 100% most of the time using very little resources. If you’d like to explore what that could look like for your company, send me an email at jeffeverhart383@gmail.com so we can discuss further.

      If you want to go it alone, feel free to post some code snippets here if you run into trouble.

      Regards,
      Jeff

  21. Lalla says:

    Sent! Thank you so much!

  22. Jeni Greene says:

    I know ZERO about scripts, but this worked like a CHARM for me and I am so happy my heart is racing! This is just what I needed to help make our school’s process for adding events to the master calendar a simple and seamless process. THANK YOU!

    1. BrownBearWhatDoYouSee says:

      Hi Jeni,

      Thanks so much for reading and I’m glad the example script helped you all automate something.

      Happy scripting,
      Jeff

  23. Thomas says:

    I hope the problem I am having is simple as it seems that everyone is liking the results of your script thus far. Each time my script triggers, it fails with “ReferenceError: “e” is not defined. (line 3, file “Code”)”.
    Am I supposed to define that in the script somehow?

    function myFunction() {
    //e.values is an array of form values
    var timestamp = e.values[0];
    var name = e.values[1];
    var emailaddress = e.values[2];
    var donationvalue = e.values[3];
    var donationtype = e.values[4];
    var dateofdonation = e.values[5];
    var streetaddress = e.values[6];
    var state = e.values[7];
    var postalcode = e.values[8];
    var email = e.values[9];
    var city = e.values[10];

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

    //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(‘1MvDTY2oO8iT_GT9ZQ7TBRFUuXIw3Vzho’)
    var copy = file.makeCopy(emailaddress + ‘,’ + name, timestamp);

    //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(‘{{Timestamp}}’, timestamp);
    body.replaceText(‘{{Name}}’, Name);
    body.replaceText(‘{{Email Address}}’, emailaddress);
    body.replaceText(‘{{Donation Value}}’, donationvalue);
    body.replaceText(‘{{Donation Type}}’, donationtype);
    body.replaceText(‘{{Date of Donation}}’, dateofdonation);
    body.replaceText(‘{{Street Address}}’, streetaddress);
    body.replaceText(‘{{State}}’, state);
    body.replaceText(‘{{Postal Code}}’, postalcode);
    body.replaceText(‘{{email}}’, email);
    body.replaceText(‘{{City}}’, city);

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

    }

    1. BrownBearWhatDoYouSee says:

      Hi Thomas, yup your error seems pretty simple to fix. Any function triggered on form submit, expects a form submission event as a parameter. This is what we access to get the values submitted via the form. I see in your script that you have function myFunction() instead of function myFunction(e) in the first line. If you “pass” that lowercase “e” inside myFunction, that “e” represents the form submission event, which you use to access all of the submission data in the subsequent lines. Let me know if you have any other questions. Thanks for reading!
      Jeff

  24. Thomas says:

    Awesome! That was easy. I also had to fix an error I made here:
    var folder = DriveApp.getFolderById(‘1MvDTY2oO8iT_GT9ZQ7TBRFUuXIw3Vzho’)
    var copy = file.makeCopy(emailaddress + ‘,’ + name, timestamp);

    So now when triggered, it will create the new document from my template but the fields are not replaced. The new document looks exactly as my template but with a new file name. I am sent the following error:
    Function: myFunction
    Error message: ReferenceError: Name is not defined
    trigger method: formSubmit

    Do the fields in my document have to be in a table, like yours? I assumed it would replace anything in the brackets…
    {{City}}, {{State}} {{Postal Code}}

  25. Thomas says:

    Nevermind, I figured it out. IT told me the problem but I was too dense to see it. Name had wrong case…
    WORKS NOW! Thank you so much!!

  26. Thomas says:

    OK, Ignore my question above. I figured that out. What I hope is my final question is this. The document is being populated with incorrect data. If the tag says {{Name}} it will fill it with the timestamp, etc.
    Do the e.values[1]; [2]; [3]; etc actually reference to something in the form? If so, how do I identify which field corresponds to that number?

  27. Thomas says:

    OK, Ignore my question above. I figured that out. What I hope is my final question is this. The document is being populated with incorrect data. If the tag says {{Name}} it will fill it with the timestamp, etc.
    Do the e.values[1]; [2]; [3]; etc actually reference to something in the form? If so, how do I identify which field corresponds to that number?
    (I seem to be having trouble posting this question. I apologize if it comes across multiple times.)

    1. BrownBearWhatDoYouSee says:

      e.values is an array of form data, which generally corresponds to the shape of the spreadsheet. In this case, the first spreadsheet column with be e.values[0] since JavaScript arrays start their indexes at 0 instead of 1. The second spreadsheet column would be e.values[1] and so on. Give that a try and let me know if you still have issues.

      JE

  28. Thomas says:

    Jeff,
    It’s working like a champ now. This will be so helpful to my organization. As a non-profit, we need to keep good records of any donations to our org. We were having a hard time keeping receipts in order between multiple people in multiple locations. This will be helpful to log the info into a spreadsheet and provide a printable receipt no matter where we are. Thank you so much for your time and patience.

    1. BrownBearWhatDoYouSee says:

      Thomas,
      That’s awesome. I’m glad I could be of help to you all and your cause. Best of luck on future projects, and thanks for reading.

      Cheers,
      Jeff

  29. Zach Musser says:

    Hi Jeff,
    Thanks for the great tutorial. Creating and filling a template has proven much easier for a newb like me than attempting to create and populate a new document from form submissions as I had been doing previously. My only question is, how do I get an edited resubmission to populate all the data placeholders? As of now, a first-time form submission runs flawlessly, but if a user edits their response and resubmits, the resulting file contains only the data that was edited on the resubmission. Thanks!

    1. BrownBearWhatDoYouSee says:

      Hi Zach, Thanks for writing. I get a lot of questions about Google Apps Script, so I’m always happy when I can answer something new 🙂

      Your situation does seem very tricky. When a form is resubmitted, only the changed data is a part of e.values that comes with the spreadsheet onFormSubmit event, so as you note, only that data gets replaced in the template. To get all of the data on each submission or resubmission, we need to change where our script lives and the trigger that executes it.

      The Google Form Form Submit event is different and has all of the responses regardless of whether it is a first time submission or a resubmission. However, the pattern for accessing the form data that way is different that what I have written about here. You can find some guidance on dealing with the FormResponse object in this StackOverflow post.

      To get you started, you can open up the script editor in the Google Form, then past this function into the code editor:

      function logFormResponse(e) {
      var responses = e.response.getItemResponses().map(function(item){ return item.getResponse()});
      Logger.log(responses)
      }

      This will collect all of the responses into an array, similar to how they come in e.values from the spreadsheet event, and then Log them to the Stackdriver Logs so you can make sure you are getting the right values. From there, you should just be able to reassign the variables to indexes in the new array and the rest of the code to deal with the doc should just work when it is ported over to the script bound to the form.

      Thanks for reading,
      Jeff

  30. tasso says:

    Please help. I for some reason cannot get it to generate the document. It gets the information into the spreadsheet, but wont go to the document..

    function myFunction(e) {
    //e.values is an array of form values
    var timestamp = e.values[0];
    var owner = e.values[1];
    var client = e.values[2];
    var reviewer = e.values[3];
    var address = e.values[4];
    var date = e.values[5];
    var claim = e.values[6];
    var tor = e.values[7];
    var city = e.values[8];
    var province = e.values[9];
    var win = e.values[10];
    var hum = e.values[11];
    var pr = e.values[12];
    var manager = e.values[13];
    var pmphone = e.values[14];
    var pmemail = e.values[15];
    var insphone = e.vaues[16];
    var insurer = e.values[17];
    var adj = e.values[18];

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

    //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(’12YPLC6R8mqEM28n-U6J72ux7CTCPLn3B’)
    var copy = file.makeCopy(owner + ‘,’ + timestamp, 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(‘##owner##’, owner);
    body.replaceText(‘##cleint##’, client);
    body.replaceText(‘##reviewer##’, reviewer);
    body.replaceText(‘##address##’, address);
    body.replaceText(‘##date##’, date);
    body.replaceText(‘##claim##’, claim);
    body.replaceText(‘##tor##’, tor);
    body.replaceText(‘##city##’, city);
    body.replaceText(‘##province##’, province);
    body.replaceText(‘##win##’, win);
    body.replaceText(‘##hum##’, hum);
    body.replaceText(‘##pr##’, pr);
    body.replaceText(‘##manager##’, manager);
    body.replaceText(‘##pmphone##’, pmphone);
    body.replaceText(‘##pmemail##’, pmemail);
    body.replaceText(‘##insphone##’, insphone);
    body.replaceText(‘##insurer##’, insurer);
    body.replaceText(‘##adj##’, adj);

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

    }

    1. BrownBearWhatDoYouSee says:

      I realized after a few people were having issues that a step was left out of this tutorial. I’m guessing you didn’t set a trigger since your code looks fine. Please reference the “Setting a Trigger” section of the updated post, or watch the video where it walks you through that: https://jeffreyeverhart.com/2018/09/17/auto-fill-google-doc-from-google-form-submission/

      Thanks for reading, and please post back if setting a trigger isn’t your issue.

      Thanks,
      Jeff

  31. Shabana says:

    Hi Jeff, i have the same issue as the last user. Before that, your tutorial was so easy to understand and straightforward. However, i have the same issue as the last user, but the only thing is that i have already added in the trigger and it is still not generating the gdoc. But when i run to debug the code, i get this error message “TypeError: Cannot read property ‘values’ of undefined (line 4, file “Code”)”. Please help. Thank you so much.

    1. BrownBearWhatDoYouSee says:

      Hi Shabana,

      Thanks for the kind words. You will need to submit a test form to test the script. For the script to run, we need e.values, which is supplied by the form submission event. Running the code in the debugger doesn’t provide that data. You can read more info in this article if you want, but try submitting a test form and let me know if its still not working.

      Thanks for reading,
      Jeff

  32. Shabana says:

    Understood. Read the article. What i did was i created fresh form and response sheet and put back the code in the script editor in spreadsheet. Then, i created new trigger to run the autofill function. Still no new copy of the document in the drive. Did i missed out something?

  33. Shabana says:

    Additional question, is there anything related to the getFileById() on the file permission on DriveApp? Folder and file.

  34. Shabana says:

    May i know if there’s anything to do with the folder and file authorization to allow writing and copy file to another folder?

  35. ValerieM says:

    Jeff, this tutorial has saved me countless hours of research and code writing! I’m a novice in the coding business and your instruction was so clear and straight forward. Thank you!
    I’m wondering is it possible to have this script point to a specific TAB in the spreadsheet? For example, my Form Responses tab populates onto a 2nd Tab where I have a list of Voucher numbers that get assigned to each form response. The Document that gets generated from the Script needs to contain this Voucher number. Any help you can provide would be greatly appreciated!!!

    1. BrownBearWhatDoYouSee says:

      Hi Valerie,

      Thanks for the kind words! In short, it is pretty easy to grab values from a specific Tab (Sheet) in a Spreadsheet using this method: SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SheetName").getDataRange().getValues().
      Depending on how your spreadsheet is set up, that may get trickier. In the example on this post, we take the values from the form submission event, so anything that is calculated in another column or field isn’t available in those values. If you need more guidance, feel free to post back. I might recommend checking out this other post on getting data from Google Sheets for a bit more on that subject.

      Thanks for reading,
      Jeff

  36. Will Gillen says:

    Hi Jeff,

    Super helpful post so far! I’m wondering if it’s possible to use a list of checkboxes for blocks of text that you’d like populated in the document instead of having someone enter text into a field. For example:

    Question: which of these would you like populated in the document? (Choose all that apply)
    [] option 1
    [ ] option 2
    [ ] option 3

    There would be boilerplate text for each option that would be populated into the document sequentially. I’m not really sure where to start to get something like this working. Thanks!

    1. BrownBearWhatDoYouSee says:

      Hi Will,

      Thanks for reading. That is for sure doable. The checkbox values come back as a comma separated list of values that were checked, so we can just check if the value we get from e.values includes the option, and then dynamically build the string we use to replace text in the Google Doc:

      function handleCheckboxes (e) {
      const checkboxes = e.values[1];
      //The checkbox value is a comma separated string: "Orange, Pineapple, Strawberry"
      let textToPrint = "Hi, I see you like the following fruits: ";
      if (checkboxes.includes("Orange")) {
      textToPrint += "Orange"
      }
      if (checkboxes.includes("Pineapple")) {
      textToPrint += " Pineapple"
      }
      if (checkboxes.includes("Strawberry")) {
      textToPrint += " Strawberry"
      }

      if (checkboxes.includes("Apple")) {
      textToPrint += " Apple"
      }

      if (checkboxes.includes("Watermelon")) {
      textToPrint += " Watermelon"
      }

      Logger.log(textToPrint);
      const lastRow = SpreadsheetApp.getActiveSheet().getLastRow();
      const rangeToSet = SpreadsheetApp.getActiveSheet().getRange(lastRow, 3).setValue(textToPrint);
      }

      This is a pretty basic example. I set-up a pretty basic proof of concept with this form and this spreadsheet if you want to see an example. You can copy the sheet using this link if you want to play around.

      Thanks again for reading and commenting. If you find this stuff interesting, I’d suggest signing up for the mailing list, as I send out new tutorials once a month. There should be a form on the bottom of every post

      Cheers,
      Jeff

  37. Will says:

    Thanks so much for the quick response! I’ll give this a shot and see if I can get it worked out.

  38. trà says:

    nếu không dùng Google Docs mà dùng bằng google bảng vẽ thì sao ạ

    1. BrownBearWhatDoYouSee says:

      This is what I got from Google translate: “What if it’s not using Google Docs but using google drawing board?”

      There is no Apps Script class, as far as I am aware, that allows you to manipulate Google Drawings.

  39. Meer Ahmed says:

    Hi Jeff.
    This is the first time in app script. i have followed your instructions in the video to write the script you are teaching in video. However when i submit the form, i get a email saying Error Message.
    script function not found:autofillgoogledocfromform

    I really appreciate your help to solve this issue.