In this short video, I show how to auto fill a Google Doc from a Google Form submission using Google Apps Script. If you just want the code, or want to read along, check out the article on my site: jeffreyeverhart.com/2018/09/1...
Hey Jeff - I am doing a Fill In the Rhyme Workshop for a DEI Class I am launching. This was perfect for saving my students a step by taking their form and completing the document I will give them to rehearse from. You just helped me write my first little script - I am learning solidity as a hobby, so this was neat to incorporate into my education project. Worked like a charm. I got one error -misplaced comma that I quickly noticed and worked perfectly after that. Great job man - great pace, great detail, super job brother!
Jeff, I'm just getting back into coding and could not stand the online courses. I did your simple example, and it is indirectly related to one of my projects, and I am hooked. The code is simple but it gives me a start. Good job and thanks.
Jeff, 2 years after you posted, I was able to follow along and increase my paperwork productivity. Amazing. Now I have to research how to use a drop-down menu to print more extensive verbiage.
Thank you so much! I can't believe Google hasn't made a proper function between form and doc yet. I came here to learn this because I was trying to make one-link access to a form on my Role Playing Discord, for people to submit their Original Characters for roleplay, and then THAT response into a reference doc that I could share with all the players on discord about each player. Woo!
Thank you for this! I am a middle school teacher and in the course of less then an hour, I was able to make a form, and edit a template for lesson plans. It should save me dozens of hours in the future!
Great job Jeff. I really appreciate the walk through. I am a school leader and I wanted an easy way to create walk through forms to then populate them and then I could send them to the teachers in my building. Thank you so much!
Hi, I am working on something similar. But I would like to send an email of the doc created to the teacher. Any help would be greatly appreciated. Thank you
To be honest: this was way too complicated for me and I'm still stuck with the problem I am trying to solve 😂 But I am liking and comment for the algo, because I really like your energy in the tutorial. Thanks!
Nice video. I took it as a base of a project using a sheet template instead. As several users will be sending responses at the same time, I preferred to use batch processes with triggers.
15 minutes video = 3 lessons anticipated in the class room with rookies. I have 80 this year, so I should branch out for the German speaking students on the part vocational track towards a trainee position. I should be able to use the "title" to demonstrate the possibility of formatting the text for the more casual tone in Switzerland of maskuline and feminine in the text, to deviate from the unisex polite standard German "Sie" . A completed traineeship is for several topics qualifying for the University acceptance, including ETH Zürich.
I really loved this tutorial. Very well detailed, and the code part is also very well explained. I have a question. If I want to put all the answers in one Google like a list of names, nationalities, age, etc; how would it be?
This was so helpful for me and my colleagues as we streamline some form submissions at our school. As a chemistry teacher- I know zero coding terms so please bear with me! We were wondering if there is a way to add code around line 10 for the file name to add a 1,2,3 ect if the same person submits the form. Ex. John Doe 1, John Doe 2. We have students who should complete the form once, but sometimes redo if they need to. (I know we can look at time stamps- we are just trying to streamline it). We also wondered if there is a way for the new document created's link to be inserted in the spreadsheet's row for that document. This tutorial was amazingly helpful! Thank you so much for it as well as the vast amount of information on your site!
I've used this tutorial before to auto fill a google doc from a form, but I'm wanting to put my doc template in a google sheet. The main purpose is for that sheet to look similar to a doc if I were to print but to also have the ability use the "sum" function available in a sheet to add up info that will be added after the sheet is auto created from the script.
Great video for me to understand, a non-engineer! I do have one question - I have a question on the Google Form that is a multiple check box option so that translates to it inputting the title to "Example [1st choice]". How do I put in the var function since it doesn't recognize the brackets and also because I can't change the titles for the columns in Sheets? Thanks in advance!
Hello Jeff!! Thanks for this extremely helpful video. I hope I can get your help on these two questions: 1) How would I go about also creating an auto-saved PDF version of that google doc (a pdf that will download when i click the link in the column) perhaps in the column adjacent to this one: is this possible (do you have a link on your website for this as a tutorial?) 2) How do I get the script to STOP or NOT run if the actual spreadsheet row is empty? Sometimes I have a few extra empty rows in my GoogleSheet, and this script will keep running and essentially create empty documents. I assume this is something I can add as an "if" statement in the rows.forEach(function(row,index){}, and ive been trying to experiment but not able to figure it out. In otherwords, something for example, "if the first column of my spreadsheet is empty, DONT create a document, (e.g., return))". I tried adding in "if (row[1 === 0]) return;", which i understand to mean, if Column B in my spreadsheet is empty, skip this row, but that doesnt seem to work). ****EDITING MY QUESTION as i think i found my answer to my second question. I added if (row[1] == "") return; and that seemed to work :) Thanks in advance for your help for question 1 , and happy new year!!
Hi Jeff, firstly thanks very much for the great video, it was exactly what I was looking for. I created my own template made a few changes to your code and it worked perfectly..! However, I would prefer to store all my data in columns rather rows...How do I go about changing your code to achieve this?
Thanks Jeff, have got this working for my own form - brilliant. However, is there a piece of code that can be included in the script to autoprint the newly created files?
I am curious about implementing a checkbox grid style of answer. For example, if I wanted to choose a specific location, then certain team members to assign to a project from that location, then it creates columns for each location. Is it possible to do an "if not blank" style of function to auto-populate the appropriate team members into the template doc? I have put in a e.namedVariable but then columns must be the same name and it doesn't tell the name of the location that way.
Thanks for the tutorial. I have one question, does the table in the google doc append whenever someone submits a form or it will go to a new google doc?
Hi Jeff, loved the video! Is there any chance of building a bridge between Docs and Forms that when the form submits data it populates a new doc on an existing template?
Hi jeff! Your video was very helpful to me. Thanks alot🔥❤️ I don't know anything about coding but after watching your video I made a form that will help me in my work. Just need one more help- How to attach uploaded file from google form to doc? Again thanks a lot u r a savior!🔥
Thanks for all the kind words. I've got a post/video in the works that describes just that thing, but for now someone figured out how to do it and posted the code in the comments on the linked blog post.
Great video !! Could the replacement function work with a multiple choice question submission from a form ? I've noticed that multiple values in a single cell are separated by a comma ?
Thank you very much for the video! It worked super! One question: I have a table with thirty possible lines, which will not always be filled. Is there any way that, if the form field is blank, the document is also blank?
If you just pass a blank value into the replaceText function it will replace the tag with the blank value, so that can work depending on what your doc is set up like
This has been a great help, I am wondering if it is possible to save the doc to a specific folder and that folder would be correlated to a name from a drop down inside of the form itself?
Jeff, this was really clear and easy to follow. If you are still answering questions, I'd love some advice. I want to create a form that appends to a Google Doc each time it's submitted, not create a new doc with each submission. Basically, to take notes (date, subject, note text). Each note would append to the file (gives me a searchable file to find command lines, etc). Can that be done? I suspect most of what you described here would be used.
I do that here using a table, so that might work, but you could always set it up to append a paragraph or something else: jeffreyeverhart.com/2020/05/18/open-letter-maker-with-google-forms-docs-and-apps-script/
Thank you Jeff for this tutorial. I have a question. Is it possible to get the responses to go into a table within the same document? I am looking to input all of the responds on 1 table within 1 document. The table will have 15 rolls.
Thanks for watching. You can check out this blog post for an example that writes stuff to a table: jeffreyeverhart.com/2020/05/18/open-letter-maker-with-google-forms-docs-and-apps-script/
Hi Jeff. Thank you for this very helpful info. I just have a question. Do we always have to put tables in order for the script to work? I tried doing it but I did not add tables on the docs, I just added the 2 "{{}}" unfortunately it did not work.
Super helpful. To take it further, do you know if you can have multiple functions that correspond to their own different google doc templates based on an answer given in google form? For example, if you had a drop down question in google forms, could you have a different google doc template for each option in the drop down question that is only activated when that answer is submitted on form submission?
Yeah, you could for sure do that. You would need to use conditional logic in the same function to determine which template is used and replaced. I would recommend looking at the JavaScript Switch operator for that. Thanks for watching!
@@jeffeverhart Iv been learning a lot about conditional logic, but haven't been able to apply it successfully to the script in this video. any suggestions of syntax that will allow for? this?
Hey Jeff, First of all, this video was so amazing! Thank you so much for making this. I do have a couple of questions that hopefully you can address. 1. If I was to enable the form to upload a photo, How would I go about ensuring that the google doc pasted in the actual photo instead of just a link to where the file is located on my google drive? Is this possible to do? How would I go about doing it? 2. I was trying to make it to where the populated file would then email itself to whoever filled out the form with their answers. I have it configured to where the form emails the person a .PDF copy of the google but I would like it to send a word doc instead. Preferably a .DOCX. When I use the MimeType function, it only works when I use .PDF. If I put in any other file type extension i get an error. The form still populates a new doc in the folder but it won't send the email of that particular doc. Any suggestions on fixing these issues?
This is so helpful! What might I do if I have a question on my form that allows for checkbox/multiple answers? How would we deal these out to specific cells?
Hi, thank you for this tutorial. One question I hope you can answer: instead of generating new templates for every new form responses, how can I add new forms response data to existing docs depending on the options chosen in the docs (example: person A selects it's them filling out the form in the first option, so everything thereafter gets added to their specific doc template?!). I really hope you can help! Thank you
This is fanstastic, easy to use. One question, I got it working great except it duplicates on form submission and creates two identical docs in my destination folder. any thoughts on what I goofed up?
hi Jeff, magnificent!! after trying, i managed to make it work.. anyway if I make another column in the sheets for example D2, containing "=left(a2,3)".... and i declare "var nick = e.values[4];".... and "body.replacetext('{{nick}}', nick);"..... the {{nick}} in the docs stay untouched, while the {{firstname}}, etc can be changed.. do you know what's wrong?
I have checkboxes and multiple-choice responses mixed with text responses in a Google form. Following your video, I'm getting responses on the newly created document on questions 1-5 and then I stop getting the responses on question 6 which is a select all that apply checkbox response. Question 7 is a text response and then question 8 is again a select all that apply checkbox response. What do I need to enter for the checkbox responses to be added to my document and if there are multiple checkboxes selected how can they be separated by a comma? I found a comment on your website about using the textToPrint command but I think my situation is different? And how would I code for the multiple-choice responses? Thanks in advance.
How does it know which spreadsheet to pull data from? When you're in Sheets, and you click Tools -> Script Editor, does it automatically assume you want to pull data from that spreadsheet? I'm having issues because I've parsed out my data into separate sheets and want to pull data from those, but they share the same file ID as the original. Is there a way to specify that I want the function to look at another sheet within the workbook? Great video, btw. I'm very new to all of this script language, but this was just what I needed.
In this example, the script doesn't actually pull data from the sheet so much as it is passed into our script from the form submission event. To access different sheets, you can use 'SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Name of Sheet")' to select a specific sheet in a workbook. From there, I recommend you check out my post on getting data from Google Sheets: jeffreyeverhart.com/2019/03/01/retrieve-rows-from-google-spreadsheet-with-google-apps-script/
Thank you very much! So very useful for my needs. I wonder if you can help me improve it a bit, though: I also need another var that, for instance, is a PROCV working on the 5th column, based on the key at column 4. I realized I can't make use of "var example = e.values(4);" to retrieve it. Would you be so kind to point me towards the solution?
Sorry for the late reply, but if you are using calculations in the sheet, it might be easier to do something like this: jeffreyeverhart.com/2020/09/29/auto-fill-a-google-doc-template-from-google-sheet-data/
Hey! Non developer here trying to better automate client status reports. With each new form submission will I be able to have the info added to the same document every time or will it create a new document?
This is excellent, and it worked after I fixed a rather stupid error on my part. In my script, I typed var doc = DocumentApp.openById(copy.getID()); The error function on the trigger told me copy.getID won't work. I changed it to getId, and it worked! Syntax is so, so important! But the trigger debugging feature caught my silly mistake.
Thank you, Jeff! Could I have several tables in various locations throughout the Google Doc, and have the replace text throughout the Doc, or even not have a table referenced at all in the Google Doc and only use string text to replace? This information will be very useful for business purposes!
Is it possible to conditionally format paragraphs based on responses? An example would be the selection field will populate one paragraph if a certain selection and another with a different selection.
Short answer is absolutely you can do that. You would want to write an if/else statement that checks the value you want to look at, then run different replace function based on that check: www.w3schools.com/js/js_if_else.asp
Thank you sooo much for this video! So incredibly helpful. If I may ask a question... trying to do one step further. Would love to push the new document to a folder based on a location response. For example the form asks for what school location - based on this answer would love to have it go to the applicable folder. I thought maybe I could do this based on the new name of the document (school name). Any ideas? Really appreciate you!
Yes, you can do this by creating an if/else step in the code that examines the variable that store the value for the school. From there you can get the specific folder by either id or the name and then use that to store the copy you make. Thanks for watching/reading!
@@jeffeverhart thanks for your great sharing jeff, could you share the code on how to do it:- 1. split the folder base on class etc 2. is it possible if I use this to test a student, the doc will only be created if the student pass the test? (my intention is to generate a certificate once student passed their quiz)
Hi Jeff, Great video! I'm not sure if this question has been answered but I have a template made in docs that I make a copy of each time I need to fill it out. Is it possible with this method to have it autofill a new copy from a pre-made template? Basically, I need a new doc created from every form submission from a template that's already been created.
@@wadekmali This seems to be the closest thing I can find. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-TSDXfdo3JVU.html I haven't had a chance to try it as I am still awaiting admin approval but if you do, let me know!
If you look at the video at this timestamp, the script does make a copy of the original template. This creates a new doc per submission, and keeps the original: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-ziLtj5-_D7c.html
Is there a way to get lots of responses to populate into a Google doc instead of a spreadsheet? I need a lot of information from people to automatically go to certain places on a specific form, but not a spreadsheet.
Is there any way to generate responses into the Doc from a checkbox of items on the Form, inserting only the items checked? Does that make sense? Thank you for the great tutorial! I am working on creating this for my team.
Hello! I have used your video to set this up previously, but now when I go back to try and do it again, the code won't work with the curly brackets for the replacetext portion. Any ideas? I am getting super frustrated. I have tried three times and completely created new forms, docs, etc. each time.
Thanks so much! If I create a form&doc as you outlined and share it with others who want to use the form for their own students to fill out, do you know if they will encounter any issues not being the creator?
Thanks for watching! No, that shouldn't be an issue. Just remember that since you set the trigger on the form, everything will be processed like your user made the action, which typically means you will also own all of the documents created.
How do you get it to update the same document over and over and add new rows of data to the same google doc? For instance, if I created a journal and I wanted my Google Forms entries to populate into my doc continuously?
Very helpful, but every so often (maybe 10% of the time) the form submission will fail to trigger the document creation, and nothing gets logged. Is there a way to re-run the trigger for the most recent form submission? Currently, if I try to manually re-run the trigger, I get an error, "TypeError: Cannot read properties of undefined (reading 'values')". I assume this is because it's going to the next line on the spreadsheet, which is blank. EDIT: I ended up using your follow-up video, "Auto Fill Google Doc Template from Google Sheets Data Using Google Apps Script". That method works as a workaround for when the form submission trigger fails.
Hi Jeff! Thank you for the great video. When I go to add a trigger there is no "from spreadsheet" option for event source. Any idea why this might be and any recs on how I can fix this? Thanks!
@@jeffeverhart I had the problem and then realized that strangely when I went to create a trigger I had been switched to one of my different Google accounts. To fix this I just temporarily signed out of my other accounts and that fixed the problem.
Hi, it is a great video. Can I add an extra column in the spreadsheet which will replace the additional placeholder of the template? The extra column is not available in the google form.
If I would like into parse form data into SpreadSheet rather than Document app, is there any parameter on the code need to be changed (aside from the template url id)?
Yeah, a lot actually. SpreadsheetApp and DocumentApp are very different pieces of functionality with different methods. Once you have pieces of data as variables it is easy to do other things with them though. I'd recommend checking out this article on writing data to a google sheet for some patterns: mashe.hawksey.info/2018/02/google-apps-script-patterns-writing-rows-of-data-to-google-sheets/
Is there anyway to populate a table located in your header that already has some data filled in example: company logo and company name are filled in but I have 6 other rows that need filled
is there a way to put a date rage in one document. It would be awesome if all rows could be pulled into one document. For example let's say you have 10 employees and wanted to generate a report showing all 10 on 1 document not 10 separate docs.
Hey Jeff, quick question, if I wanted to place {{firstName}} in several places in my template, how would I have the script replace each {{firstName}} with the variable taken from the form?
Hey Spencer, thanks for watching. According to the docs the replaceText function should replace all instances of the pattern in your document: developers.google.com/apps-script/reference/document/body#replaceText(String,String)
@@jeffeverhart Could you show an example of the replaceText function with the {{firstName}} for several places in the template? Seems like a common question. Basically fill out the form and use it to replace {{firstName}} wherever it is at (multiple places) in the template.
Hi Jeff, Thank you so much for the video! We are a counseling organization doing counseling for kids in foster care, and I rigged this so that our counselors can do notes from it. One problem. I think I need to add a wait function. About 4 percent of the time I get this: "Exception: Service Documents failed while accessing document" emailed to me. Google support was no help, of course, so what would be the proper method to add a wait function? I'm not a coder of any sort, but I'm thinking this should appear after "var body = doc.getBody();" in the code. Correct?
Hey Jeff, Thank you for your video, will it be possible to replace text with a image if my clients upload a .PNG file in forms, right now it comes with the URL text as expected, but what to do if i want the picture to show in the docs? i hope you will take time to answer because this function could help me a lot in other project to
Hi Jeff, This video is amazing! Helped me a lot! Could you please help me to expand the code to generate the data into PDF as well? Thank you very much!
Sorry for the late reply, but you can see an example of that here: jeffreyeverhart.com/2015/01/30/tech-tip-google-forms-confirmation-email-with-attachments/
Did anyone figure this out? I'm completely stumped lol. It seems like there should be a simple command to download the .pdf after we define the variable but I can't find one.
Hi Jeff. This is such a useful video! I was wondering if it is possible to autogenerate documents from just a google sheet, without having to enter it through forms? so that if i have a list of twenty names, i can just bulk upload the data into a sheet and twenty different documents are created? what would the trigger be? thank you!
Hi, thanks for watching. I've had so many people ask for an automation that runs from a sheet instead of a form, so I made a video and blog post that describes exactly that: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-iLALWX0_OYs.html Thanks for watching!