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:
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.
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.
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.
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.
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:
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.
After over 100+ comments on this post and more on YouTube, I’ve created a few tutorials that branch off of things that people may want to do:
Auto Fill a Google Doc Template from Google Sheets Data
This tutorial is very similar to using a Google Form to trigger the automation, but instead we runt the automation from a menu item and pull data from an existing Google Sheet. We also write the URL of the created document back to the sheet.
Frequently Asked Questions
Replacing Text with a Clickable URL
Many people have asked how they can replace the text in their Google Doc templates with a clickable link. The tutorial linked above shows how you can modify this script to insert links into your templates.
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?
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
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
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
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!
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.
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
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
Bingo! .. thanks.
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();
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?
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.
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
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.
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
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?
Not sure I’m aware of any books, but Ben Collins offers some great stuff if you are interested in some more structured materials.
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?
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
Spreadsheetclass that gets returned to add the data.Cheers,
Jeff
To clarify, just like your post related to creating documents, I want to create a new spreadsheet for each new entry.
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.
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
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 :).
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
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!
Every time I trigger from my form, I get this error, Access denied: DriveApp. at myFunction(line)
Any reason why this would be happening?
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.
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!
Can you post the contents of your script? That generally is the only way I can help troubleshoot.
JE
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();
}
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.getFileByIdyou 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
Thanks Jeff, that might have been the issue, but I also recreated the trigger and now it’s working flawlessly!
Thanks once again!
Nikola
Awesome! Glad I could be of some assistance, and thanks for reading!
Cheers, Jeff
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?
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
Sent! Thank you so much!
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!
Hi Jeni,
Thanks so much for reading and I’m glad the example script helped you all automate something.
Happy scripting,
Jeff
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();
}
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 offunction 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
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}}
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!!
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?
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.)
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
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.
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
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!
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.valuesthat 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.valuesfrom 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
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();
}
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
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.
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
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?
Additional question, is there anything related to the getFileById() on the file permission on DriveApp? Folder and file.
May i know if there’s anything to do with the folder and file authorization to allow writing and copy file to another folder?
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!!!
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
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!
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
Thanks so much for the quick response! I’ll give this a shot and see if I can get it worked out.
nếu không dùng Google Docs mà dùng bằng google bảng vẽ thì sao ạ
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.
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.
Hi Meer,
Typically the new Google Apps Script environment creates a function named
myFunctionwhere we are using a function calledautoFillGoogleDocFromFormin the tutorial. Make sure the function you are calling in the trigger is the same as what it is named in your code. All that matters is that the names match. They can be called whatever you want.Thanks for reading,
Jeff
Thank you for response.
below is the the code i have in the script and it matches the function in trigger. However it is still not generating the document upon form submission.
function autoFillGoogleDoc(e) {
var timestamp = e.values(0);
var firstName = e.values(1);
var lastName = e.values(2);
var title = e.values(3);
var templateFile = DriveApp.getFileById(“16ad1Eb7LFAG4qz-ErzL5Z_MM41g8mmuehjgwVz-VQvo”);
var templateResponseFolder = DriveApp.getFolderById(“1t92YdQJueD3ImD4FunkfbW_84wZiu5M5”);
var copy = templateFile.makeCopy(lastName + ‘, ‘ + firstName, templateResponseFolder);
var doc = DocumentApp.openById(copy.getId());
var body = doc.getBody();
body.replaceText(“{{FirstName}}”, firstName);
body.replaceText(“{{LastName}}”, lastName);
body.replaceText(“{{Title}} “, title);
doc.saveAndClose();
}
Everything in the code looks fine. What error message are you getting now? It might be helpful to check out this post on how to use the logs since that could give you some additional info. Happy to keep helping, but I’ll need more info. A screenshot of trigger settings would also help.
Thanks,
Jeff
Hi Jeff!
I’ve used your code, but seem to get the error below:
TypeError: Cannot read property ‘values’ of undefined (line 3, file “Code”)
Hi Nejc,
Please see this article on that error code for a breakdown of why that might be happening.
Let me know if something on that page doesn’t fix your issue.
Thanks for reading,
Jeff
Hey Jeff,
it helped me a lot, thanks.
There’s only one thing I would like to add in this process….
After generating the new doc, I would like to open it in a new Chrome tab.
Would it be possible? If so, how?
Thanks dude!
Hey Gustavo,
Thanks for reading! That is an interesting request, and one I haven’t gotten before. Google Apps Script doesn’t provide a way to interact with the browser directly, although you could create a web app that has HTML & JS to do this, but that would be much different than what I’ve outlined here.
I’d be interested to know what you use case is, or why you want that behavior, so I can suggest some workarounds to make the document URL easy to access after submission. For example, you could send the link in an email, or print it to a spreadsheet, etc. etc.
Thanks again for reading,
Jeff
Hey Jeff,
Thanks for answering 🙂
Yeah, I tried many workarounds before asking here, and while I was waiting yor answer, I decided to send an email using GmailApp. It’s how it’s working today.
Anyway, my case is exactly like yours. On a form submit, I need to read all the information filled, choose a template (among three), based on the information provided. And finally, replace with keys information and save it to Google Drive.
The question about opening a new tab with the new doc, was my steakholder request. Because when I was presenting him the solution (at that time, without sending email), we noticed that it was taking too long to the file appears in the Google Drive (about 2 minutes).
If you have any suggestion, please let me know.
Thanks my friend!
Hi Gustavo,
Ahhh…that makes total sense. It can take awhile for Docs to appear in Drive folders, and sometimes a manual refresh is necessary. Emails are usually the best way to trigger those kinds of status updates unless the client has Slack or some other system where we could trigger an update notification.
Kudos to you for digging with the client to figure out the core of their issue. Getting the browser thing to work would have been messy, but what you have implemented sounds much cleaner.
Regards,
Jeff
Hi Jeff!
Thank you so much for this tutorial. It is super helpful. However, I am getting the email failure with an Error Message: TypeError: e.values is not a function. How do I fix this? Thank you!!
Hi,
Sorry for the late reply. If you’re still stuck can you copy/paste the code in here for me to reference?Based on the error message, it looks like you’re using
e.valuesincorrectly, but I can be more helpful with the code. Thanks!Jeff
Hi
Excellent tutorial and a life saver.
Everything works fine except one thing. if I make an entry that includes () in the Google form then the process doesn’t seem to work.
For example, if I enter ABC (d) limited as a company, the process doesn’t not work.
I am not sure if this because the entry in Google sheets gets confused by the (d)?
Would welcome your thoughts?
Hi Asim,
What error are you seeing, or is this something that happens when the doc is populated? Take a look at this post on debugging for a few ways to get error messages. It would also help if I could see the code.
Thanks,
Jeff
It would
Hi Jeff,
I have been facing a similar problem. Script is written in form script editor and not on google sheet.
I have created a trigger also and tested the script via google form.
The script is as follows :
function autoFillGoogleDocFromForm(e) {
var timestamp = e.values[0];
var Emailaddress = e.values[1];
var Confidential = e.values[2];
var Name = e.values[3];
var Address = e.values[4];
var Designation = e.values[5];
var Telephone = e.values[6];
var Number = e.values[7];
var Email = e.values[8];
var Date = e.values[9];
var Registration = e.values[10];
var Hazard = e.values[11];
var Reporter = e.values[12];
var From = e.values[13];
var To = e.values[14];
var Aircraft = e.values[15];
var Location = e.values[16];
var Environment = e.values[17];
var Condition = e.values[18];
var Title = e.values[19];
var Details = e.values[20];
var Actions = e.values[21];
var docTempleteId = “1EqkL33juy9kdcEmiCCPi0B-Wm9cPy5q0xLkyo3qz-HE”;
var file = DriveApp.getFileById(docTempleteId);
var folder = DriveApp.getFolderById(“1T8LdNSAQmT00yKmkx4cY-JQi6R3v2HLE”)
var copy = file.makeCopy(Name , folder);
var doc = DocumentApp.openById(copy.getId());
var body = doc.getBody();
body.replaceText(“{{Name}}”,Name)
body.replaceText(“{{Address}}”,Address)
body.replaceText(“{{Designation}}”,Designation)
body.replaceText(“{{Telephone}}”,Telephone)
body.replaceText(“{{Email}}”,Email)
body.replaceText(“{{Number}}”, Number)
body.replaceText(“{{Date}}”,Date)
body.replaceText(“{{Registration}}”,Registration)
body.replaceText(“{{Confidential}}”,Confidential)
body.replaceText(“{{Hazard}}”, Hazard)
body.replaceText(“{{Reporter}}”, Reporter)
body.replaceText(“{{From}}”, From)
body.replaceText(“{{To}}”, To)
body.replaceText(“{{Aircraft}}”, Aircraft)
body.replaceText(“{{Location}}”, Location)
body.replaceText(“{{Environment}}”, Environment)
body.replaceText(“{{Condition}}”, Condition)
body.replaceText(“{{Title}}”, Title)
body.replaceText(“{{Details}}”, Details)
body.replaceText(“{{Actions}}”, Actions)
doc.saveAndClose();
}
Please advise.
Hi Kris,
The script needs to be in the Spreadsheet script editor, not the form. Th event object that gets passed to
autoFillGoogleDocFromFormis different depending on where it is bound and what triggers it. For this code sample to work, it needs to be in the spreadsheet and the trigger needs to be set as I describe in the article. There should be a comment somewhere on this post of me explaining how to make it work in the form, but it is much easier to just copy/paste the script somewhere new.Regards,
Jeff
Hi Jeff,
It worked as soon as I entered details in Spreadsheet Script Editor.
Thank you.
Hi Jeff,
It worked as soon as I entered the code in Spreadsheet script editor
Hi Jeff,
I am facing a different problem now. I want to replace empty form fields with NA. I tried !null? but it doesn’t work.
Can you help with this.
I think most empty form values would either be
undefinedor an empty string''.Hi Jeff,
Nice, Slack integration would be amazing… I’m going to search how it works, and maybe I can implement it in the future.
Thanks for sharing your opinion and your knowledge.
Regards,
Gustavo
So should it be
var From = e.values[13]!==” e.values[13] : ‘NA’;
I may have missed this but can we from a form submission enter a new section in a google doc. For example; if you need to add more than one customer information onto a google doc template then the information can be added to another line.
Hi Jon,
Thanks for reading. No, I don’t think you missed anything. What we’re doing here is a simple search and replace, but you can for sure do more complex things to the Google Doc after you make a copy. The DocumentApp class allows a lot of control over the document programmatically. I think you could accomplish what you are looking for using that.
However, it could be a bit painful to work with based on the complexity of your document, so in the past I’ve added a few extra lines in templates with variables like Item1 and Item2 which correspond to the extra items on the form. If those value are present, they get replaced with the values from the form, and if not, they get set to a blank string
"".Hopefully that helps point you in the right direction.
Thanks for reading,
Jeff
Hi! Thanks so much for this amazing tutorial. I’m happy to have come across your page! I do have a question though, is it possible to get multiple submissions into one google doc?
In your example, you have 1 submission populate into 1 row. Is it possible to have a second submission populate into a second row? Thank you so much in advance for your response!
Hi Kelly,
Thanks for reading, and apologies for the late reply. That is totally possible, but you will have to navigate the DocumentApp class a little bit to get access to the part of the document body that you want to update. I think the
appendRowmethod of the Table element would make a lot of sense in this case. Take a look at the official guides for more info on the structure of the Google Document.Thanks for reading,
Jeff
any way of adding a erase the field in the spreadsheet after it creates the completed file?
Hi Julian,
Thanks for reading. Technically you could delete the row after you create the file, but I might suggest adding another column to the sheet where you display some sort of value that indicates it has been processed, like ‘PROCESSED,’ ‘COMPLETED’, or ’10:30AM 4/12/2020′. Generally, that ends up being a lot cleaner of a pattern.
Regards,
Jeff
Hello! I am very new to this, but I found your video, and the code you included very easy to follow. I am looking to solve a slight variation. I need the sheet to populate a google SLIDE, rather than a DOC. This is due to needing a background photo, which Docs doesn’t seem to allow. I changed the code reference from DocumentsApp to SlidesApp and it did create copies of the Slides template each time a response was recorded from the form. However, the text in the slides did not populate. Is this because text in Slides is in text boxes, rather than in the body of a Doc? Please tell me that there is a simple fix for this! I have zero coding experience, but was able to implement your model. Thank you!
Hello,
Sorry for the late reply here. In short, you are on the right track that the way we work with DocumentApp and the way we work with SlidesApp are not the same. I don’t have any SlidesApp example on this site, but I was able to find this example from the official documentation that describes how to make the necessary search and replace work in SlidesApp. Hopefully that helps you in the right direction.
Thanks for reading,
Jeff
“`function createdocs(e) {
var Contracttype = e.values[1];
var Partnername = e.values[2];
var Bankname = e.values[3];
var Accountnumber = e.values[4];
var TIN = e.values[5];
var Bankcode = e.values[6];
var Businesscode = e.values[7];
var Phone = e.values[8];
var Director = e.values[9];
var Contractstartingdate = e.values[10];
var Contractendingdate = e.values[11];
var EmailAddress = e.values[12];
var file = DriveApp.getFileById(’10lZQhmbocTOmvGwklYnHNvXbdGXDmpbgFbvcnu67G7c’);
var folder = DriveApp.getFolderById(‘1O6QJZ5gRRoSUeolcVvKlMe65Wdb4rz9z’)
var copy = file.makeCopy(Partnername + ‘,’ + Contractstartingdate, folder);
var doc = DocumentApp.openById(copy.getId());
var body = doc.getBody();
body.replaceText(‘{{Partner name}}’, Partnername);
body.replaceText(‘{{Bank name}}’, Bankname);
body.replaceText(‘{{Account number}}’, Accountnumber);
body.replaceText(‘{{TIN}}’, TIN);
body.replaceText(‘{{Bank code}}’, Bankcode);
body.replaceText(‘{{Business code}}’, Businesscode);
body.replaceText(‘{{Phone}}’, Phone);
body.replaceText(‘{{Director}}’, Director);
body.replaceText(‘{{Contract starting date}}’, Contractstartingdate);
body.replaceText(‘{{Contract ending date}}’, Contractendingdate);
doc.saveAndClose();
}“`
I have adjusted your code like this, and installed the on form submit trigger as you did. but this error is occurring: TypeError: Cannot read property ‘values’ of undefined (line 3, file “Code”) . Please help me where I am failing to do right
Hi,
Thanks for reading. Check out this post where I talk about that error code in detail. Let me know if something on that page is the issue.
Lastly, I would be remiss as a IT professional if I didn’t say that I wouldn’t recommend storing data that is as sensitive as bank account numbers and routing numbers in Google Drive resources.
Regards,
Jeff
Hello Jeff
How are you?
the tut is the best
Question: what if someone fill the form and submit it and auto filled docs directly emailed to the user that fill the form!
Thanks
Hi Rehan,
Thanks for the kind words. You could totally do that with just a few lines of code to get the Google Doc as a PDF after you save the changes. Take a look at this tutorial on sending and email with attachments as that contains all of the code you would need to make this happen.
Regards,
Jeff
Hey Jeff, thanks for the brilliant tutorial. I noticed that when a team has access to the root folder, it creates a bunch of copies, all with the same name and context, but different File IDs. Any idea why this happens and what I could do to fix this?
Hmmm…this is an intersting problem. Is it possible that the script is executing as multiple people?
I would first look at the Stackdriver Logging to see if the script is getting triggered more than it needs to be. I’ve written up some examples of how to do this in an article on debugging.
Let me know if there is anything interesting there, but if not try reaching out the AppsScriptInfo community on Twitter, as they are very helpful. I can’t speak from experience using a team drive unfortunately.
Thank you so much! I have never been able to focus enough to do a script. I was able to revise it to a long transition questionnaire for my high school – AND IT WORKED! Now, I’m trying to figure out how to send the beautiful document it populated to the student’s case manager.
Hi Geana,
I’m so glad you were able to get your first script working! That is a huge accomplishment.
You could totally do that with just a few lines of code to get the Google Doc as a PDF after you save the changes and then send the PDF as an attachment to an email from Gmail. Take a look at this tutorial on sending and email with attachments as that contains all of the code you would need to make this happen.
Thanks for reading,
Jeff
Hey Jeff
Thank you so much for this code, works like a charm when i set it up with a form. However since then i have come across a way to auto populate a google sheet instead of filling out the google form myself each time.
I thought that by then changing the trigger to onchange instead of on form submit, every time i added a new row of data into the google responses sheet (that was created by the forms answer initially) i thought this would then create my doc, as it did when the form was submitted but sadly no luck.
I have tried to crowbar in this code into your code but i am a total novice and nothing seems to be working…
function createSpreadsheetOpenTrigger() {
var ss = SpreadsheetApp.getActive();
ScriptApp.newTrigger(‘myFunction’)
.forSpreadsheet(ss)
.onOpen()
.create();
}
is there any way to tweak your code please, so that the new google doc is created and filled in with the forms google sheet headers answers but without actually submitting the form? Just by adding the data straight into the responses sheet?
Thanks!
Matt
Hey Matt,
Thanks for reading and leaving a comment! I’d be interested to know how you are getting the data in without a form? I’m guessing some sort of integration, but you are on the right track with changing the event type.
I issue is that each event object, the
ethat gets passed into our function, contains different information. If you look at the Change event object, we see that it passes information about the change and what occurred, but not any of the actual values. Theoretically, you could just get the values out of the of the last row in the spreadsheet, which should be the most recent submission, and then use those variables with the rest of the code. Check out this article on how to select different rows from Google Sheets.I would also try logging out just some basic testing to make sure that whatever integration you are using will actually fire the change event before sinking a bunch of time into this.
Let me know if I can be of help,
Jeff
Fantastic tutorial, Jeff!
File uploads in Google Forms are creating URLs in the spreadsheet I’m using to populate my Google Doc using your code. Is it possible to create a hyperlink to those URLs in my Doc using customized text, such as “Link,”? Many thanks for your consideration!
~Nick
Hi Nick,
Thanks for reading and leaving some kind words. The DocumentApp is admittedly the service I know the least about, and it can get quite complex, but I think something like this could work:
//Get the file link from the event variables
var fileLink = e.values[?]
//Later, we will find the text we want to use as the link and set the linkURL.
//This line can be included after we do the search and replace
body.findText('Google Drive File Link').getElement().asText().setLinkURL(fileLink);
//save and close
Just be careful that the anchor text for the link (‘Google Drive File Link’) is unique in the document.
Let me know if you have any issues implementing the above.
Regards,
Jeff
I’m getting this error—
TypeError: Cannot read property ‘values’ of undefined (line 2, file “Code”)
What am I missing?
function autofillGoogleDocFromForm(e) {
var timestamp=e.values[0];
var Who=e.values[1];
var Updates=e.values[5];
var High=e.values[2];
var Low=e.values[3];
var Issues=e.values[4];
var Tasks=e.values[6];
var Title=e.values[7];
var templateFile= DriveApp.getFileById(“1TK0jVgnrgv_o9ncRRgfEVaNEmzI5JKenS-uvE7FrOYE”);
var templateResponseFolder = DriveApp.getFolderById(“1LMzSQYsgnx7_gftviGCegX6UHch84il9”);
var copy = templateFile.makeCopy(‘{{Title}}’, templateResponseFolder);
var doc = DocumentApp.openById(copy.getId());
var body= doc.getBody();
body.replaceText(“{{Who}}”, Who);
body.replaceText(“{{Date}}”, Date);
body.replaceText(“{{Updates}}”, Updates);
body.replaceText(“{{High}}”, High);
body.replaceText(“{{Low}}”, Low);
body.replaceText(“{{Issues}}”, Issues);
body.replaceText(“{{Taskss}}”, Tasks);
doc.saveAndClose()
}
That is a pretty common error, so I wrote out a few ways to check that in this tutorial.
Hi Jeffrey,
Thank you so much for this. I’ve just started a marketing job and have never coded anything before, but this is the perfect solution to a frustrating business need! I really feel like I understand what each part of the script is doing, which is testament to your very clear tutorial.
I do keep getting an error, however, and so I’m wondering if perhaps the API has changed, or there’s something silly I’m missing?
The error is: “ReferenceError: lastname is not defined” on Trigger “formSubmit”.
My code below (as you can see, it’s essentially your code, as I was following the tutorial—or at least I thought!—to the letter).
Any hints greatly appreciated!
Thanks so much,
Steph
***
function autoFillGoogleDocFromForm(e) {
var timestamp = e.values[0];
var firstName = e.values[1];
var lastName = e.values[2];
var title = e.values[3];
var file = DriveApp.getFileById(“1mzWdvL8acLSX5s9h3fpNTWyDoLDRv_rQqqBKMpO3GXs”);
var folder = DriveApp.getFolderById(“1vklNBUBO9tjQ_k7klJladPe6mGkCCCFd”);
var copy = file.makeCopy(lastname + ‘, ‘+firstname, folder);
var doc = DocumentApp.openById(copy.getID());
var body = doc.getBody();
body.replaceText(‘{{FirstName}}’, firstName);
body.replaceText(‘{{LastName}}’, lastName);
body.replaceText(‘{{Title}}’, title);
doc.saveAndClose();
}
Hi Steph,
Thanks for reading and the kind words. I think the issue is something small. It happens in the lines where you make the copy of the document:
var copy = file.makeCopy(lastname + ‘, ‘+firstname, folder);var doc = DocumentApp.openById(copy.getID());
When we define the
lastNameandfirstNamevariables we use what’s called camelCasing for the name, but in the line that does the copying you have everything written as lowercasefirstnameandlastname. In reality, it doesn’t matter which naming convention we use, and camelCasing is a JavaScript convention, but the variable names have to match. Just changing those should do the trick.I’m sure Google Apps Script will be really helpful to learn for marketing, as it also has a nice integration with Google Analytics.
Thanks for reading,
Jeff
Hi Jeff,
This worked to add links to the document! However, it’s applying the link to the entire line on which the anchor text appears and not just to the phrase ‘Google Drive File Link’.
Also, there are four places in the Doc the text ‘Google Drive File Link’ appears, but this code is only creating a hyperlink in the first instance. Is it possible this can be applied throughout the document?
Thanks again!
Hi Jeff,
Your tutorial has been EXCELLENT and I’m getting hung up on one part. I’ve studied your YouTube video and have read all the postings and your insight would be most helpful. I did create the trigger (as you suggested) and it just won’t create the new doc in the folder. I’m so close! Here’s the code and appreciate in advance you taking the time:
function TestingUploadingForm(e) {
var timestamp = e.values(0);
var lastName = e.values(1);
var firstName = e.values(2);
var className = e.values(3);
var File = DriveApp.getFileById(‘1gLJaBU01kfEw3uBtXf3_PH69AqLvs9UE-3MgEY23zfg’);
var Folder = DriveApp.getFolderById(‘1Deg7Qej4hOBpHsM7PsyEDwXx3Y1seDZL’);
var copy = File.makeCopy(lastName, Folder);
var doc = DocumentApp.openById(copy.getId());
var body = doc.getBody();
body.replaceText(‘{{LastName}}’, lastName);
body.replaceText(‘{{FirstName}}’, firstName);
body.replaceText(‘{{ClassName}}’, className);
doc.saveAndClose();
}
Hi Steve,
Thanks for reading. I’ve written an article about debugging Google Apps Script that should help give you some additional information about what is failing. Using one to the methods listed there, you can typically get an error message that points to the reason the script is failing.
However, I can also see a small typo in your code that could be hurting things. All of the variable declarations at the top are written like this:
var timestamp = e.values(0);We need to access
e.valuesusing square brackets like this instead of parentheses:e.values[0]You can try that to see if it might work. If you can get a more specific error message, I can be of more help. Thanks for reading,
Jeff
Jeff, thanks for your quick reply and you were 100% correct! The square brackets to access e.values [] was the key.
Your site is wonderful and look forward to supporting it and the work you do for all of us.
Let me know how to help you keep doing what you do!
Sincerely,
Steve
Jeff,
I would like the form to populate three different templates. Would I just add additional variables to call each template? All the docs can be copied into the same folder. Something like what is below, or do I need to create an app script for each individual document?
var templateFilea= DriveApp.getFileById(“@@@”);
var templateFileb= DriveApp.getFileById(“@@@”);
var templateFilec= DriveApp.getFileById(“@@@”);
var templateResponseFolder = DriveApp.getFolderById(“@@@”);
var copyA = templateFile.makeCopy(‘{{Title}}’,
var copyB = templateFile.makeCopy(‘{{Title}}’,
var copyC = templateFile.makeCopy(‘{{Title}}’, templateResponseFolder);
var docA = DocumentApp.openById(copyA.getId());
var docB = DocumentApp.openById(copyB.getId());
var docC = DocumentApp.openById(copyC.getId());
var bodyA= docA.getBody();
var bodyB= docB.getBody();
var bodyC= docC.getBody();
Hi Joseph,
Thanks for reading. Yes, you could do pretty much exactly what you have outlined here and it should work just fine. There are two ways to improve this though that are more about maintainability and readability. First, I might use more distinct names for each of the different documents, e.g.
taxDoc, personalInfoDoc, businessPropertyDocinstead ofdocA, docB, docC. The way it is, the chance of you mixing something up is higher than if we use better naming practices. Second, you could write a function for each document that you want to copy, where the body of the function is the logic specific to that document, and then call each on and pass in the relevant variables. Again, the reason for this is similar. If all of the functionality is split out into different functions, it becomes easier to reason about without mixing up variables.Thanks for reading and post back if you have any issues getting your script to work,
Jeff
“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!”
Hi Jeff! Thank you so much for the video! It helped me a lot! But now I’m having the same problem described in the message I copied above. I saw your answer to this question, but I still don’t get how I can do this… Could please give me more details?
I’m sorry, but I’m really new to this programing stuff. I’m a doctor and trying to make our lifes easier through some automation. I’ve done great progress in my project with the information in the video you made, but now I’m stuck with this editing form problem. Thank you so much!
Thank you so much for your helpful script. I’m not yet knowledgeable about app script, or coding. I’m getting the error is: “ReferenceError: Driveapp is not defined”. I can post script if best. Thanks again!
Hi Tejal,
That is a misspelling. It should be
DriveAppnotDriveapp. All of the objects and functions are case-sensitive, so just make sure you check that if you see that error again. I try to use the autocomplete feature whenever I’m unsure of how something is spelled.Thanks for reading,
Jeff
How can I create one unique document from the first form submion and the edits? The way it is, one document is created for every edit, with just the new information edited.
And is there a way to send the link of the doc to the person who answered the form?
Thanks!
Hi Victor,
Thanks for your comments/questions, and sorry it’s taken me so long to respond. The long story of what you are asking is that it’s not nearly as easy the example you found on my site. After we do the initial find and replace using our replace strings {{FirstName}}, those unique character strings are no longer in the document. I’m not saying it’s not possible, but it would be error prone since we can’t assume that any input we get from the user in the first or subsequent form submissions is unique.
I’ll continue to ponder the best way to accomplish what you are asking, but it might take me some time to write up an explanation for it.
Thanks for reading and commenting,
JE
TypeError: Cannot read property ‘0’ of undefined – Can you please help with this error?
My fields look like this:
var title = e.values[0];
var surname = e.values[1];
var fullNames = e.values[2];
var ID = e.values[3];
var taxNumber = e.values[4];
body.replaceText(“{{FullNames}}”, fullNames);
body.replaceText(“{{Surname}}”, surname);
body.replaceText(“{{ID}}”, ID);
body.replaceText(“{{IncomeTaxNumber}}”, taxNumber);
Check out this explanation: https://jeffreyeverhart.com/2016/08/06/fix-typeerror-cannot-read-property-values-undefined/
Sorry, I think I know what the problem might be. My sheet has multiple tabs, how does my script know which one to use?
This particular example will always default to use the form submission data as an input, so you having multiple sheets shouldn’t matter unless there are multiple forms being submitted.
Hey Jeff, can you help me?
I had this error–> TypeError: Cannot read property ‘values’ of undefined at autoFillGoogleDocFromForm(Código:2:21)
Check out this explanation: https://jeffreyeverhart.com/2016/08/06/fix-typeerror-cannot-read-property-values-undefined/
Hello Jeff,
I am so happy to stumble on your website. It’s the most concise yet best tutorial for this automation I’ve found in the web. I was able to run the script but ran into some hiccups. Hope you can share insights about them:
Hiccup 1: Anticipating multiple submission in a day or from the same person. I opted to use the response time stamp as the file name for the generated doc, thinking it would make the response file name unique. Instead, responses after the first test response gets renamed to the next day’s date (and so on) and without time at all.
QUESTION 1: Is there a way I can automate generated doc with a “date(YY-MM)_response number in a month” file name?
Hiccup 2: I utilised a drop-down in one of the universal questions in the form, to make it easier to be filled, like selection for “Mr./Ms./Mrs./Mdm.”. But any response from that doesn’t appear in the form.
QUESTION 2: Will drop-down questions require different coding script?
QUESTION 3: What are possible reasons for responses not appearing in a generated doc if the spelling and bracketing are all correct, and works for the other fill-in type of questions in the form?
Thank you and looking forward to your reply. 🙂
Hi thanks for reading, and sorry for the slow response.
No, there shouldn’t be any reason that dropdown answers are treated differently. If something isn’t getting copied over, I’d check that the whitespace matches between both the doc and the script. Again, if this is connected to only the dropdown issue, it makes sense that those values aren’t getting filled since we can’t find them in the first place. If you want to post some code and screenshots of the sheet, that typically helps me address instances like this.
Regards,
Jeff
Thank you so much for this. Is it possible to edit a submission via the google form which them edits or adds to the answer given on the google word doc?
Hi Julie,
This is pretty difficult to achieve, but I’m working on a solution for editing form responses. Lots of people are asking for this. When I have something working, I’ll get in touch.
Thanks for reading,
Jeff
Hello, This comment section is still active so I was hoping you could help me. I’m having trouble debugging because literally nothing happens. A new form submission appears in the google sheet but no document is made. Since there is no error I’m not sure where I should be looking.
function autoFillGoogleDocFromForm(e) {
//credit to Jeffrey Everhart
//https://jeffreyeverhart.com/2018/09/17/auto-fill-google-doc-from-google-form-submission/
// form values
var timestamp = e.values[0];
var firstName = e.values[1];
var lastName = e.values[2];
var title = e.values[3];
var status = e.values[4];
var email = e.values [6];
//fix how to contact part later.
//grab template file by ID
var file = DriveApp.getFileById(‘1dcrzDsQHS7-pl84lhQ0iIWi2MM6Xkk6bXN8BjJjmK5I’);
//make copy of templates in folder
var folder = DriveApp.getFolderById(‘1b2moxFTCROW1rEYuYjq5-_pK9lp9A35s’);
var copy = file.makeCopy(lastName + ‘,’ + firstName, folder);
//open the document
var doc = DocumentApp.openById(copy.getId());
//grab the text body and replace stuff
var body = doc.getBody();
body.replaceText(‘[[FirstName]]’, firstName);
body.replaceText(‘[[LastName]]’, lastName);
body.replaceText(‘[[Title]]’, title);
body.replaceText(‘[[email]]’, email);
doc.saveAndClose();
}
Thanks in advance for looking at this. My triggers are set to From spreadshet – on form submit.
Hi Jeff , thank you for the video and time giving to this !!
All working perfectly !!! I am struggling with “Edit Form Response”. Please can you help with this , have not idea how to fix and why its not working.
1. Make submit new form – perfect
2. Click”Edit Response” – re-type “First Name”- click”Submit”
3. File Created- in side the file”First Name” is there, BUT ALL OTHER ITEMS will be replaced with no-text /empty.
Problem : It does not pick up old filled text .
How to solve it please ?
Hi Natalia,
This is pretty difficult to achieve, but I’m working on a solution for editing form responses. Lots of people are asking for this. When I have something working, I’ll get in touch.
Thanks for reading,
Jeff
Ok so as an update I got it working but I am running into Exception: You do not have permission to call MailApp.sendEmail. Required permissions:
I read up on simple and installable triggers and I feel this should work since I manually created the from “spreedsheet- on form submit” trigger. If you could help I would really appreciate it.
function autoFillForm(e) {
//credit to Jeffrey Everhart
//https://jeffreyeverhart.com/2018/09/17/auto-fill-google-doc-from-google-form-submission/
// form values
var timestamp = e.values[0];
var firstName = e.values[1];
var lastName = e.values[2];
var title = e.values[3];
var status = e.values[4];
var email = e.values [5];
//fix how to contact part later.
//grab template file by ID
var file = DriveApp.getFileById(‘1dcrzDsQHS7-pl84lhQ0iIWi2MM6Xkk6bXN8BjJjmK5I’);
//make copy of templates in folder
var folder = DriveApp.getFolderById(‘1b2moxFTCROW1rEYuYjq5-_pK9lp9A35s’);
var copy = file.makeCopy(lastName + ‘,’ + firstName, folder);
//open the document
var doc = DocumentApp.openById(copy.getId());
//grab the text body and replace stuff
var body = doc.getBody();
body.replaceText(‘{{Title}}’, title);
body.replaceText(‘{{First Name}}’, firstName);
body.replaceText(‘{{Last Name}}’, lastName);
body.replaceText(‘{{email}}’, email);
doc.saveAndClose();
// convert doc into a pdf and attatch and send as email.
//https://jeffreyeverhart.com/2015/01/30/tech-tip-google-forms-confirmation-email-with-attachments/
var subject = “thanks for submitting”;
var body = “I look forward to working with you in the future”;
var attach = DriveApp.getFileById(copy.getId());
var pdfattach = attach.getAs(MimeType.PDF);
MailApp.sendEmail(email, subject, body, {attachments:[pdfattach]});
}
Hi Jules,
Were you asked to authorize the script to edit documents and send email? Try running the script in the script editor to see if you can trigger the authorization flow to see if that solves the issue. If you are getting that exception when submitting a test form, that means the function is getting called, and hence the trigger is working. It might be worth checking out this article that could give you some additional tools to help troubleshoot: https://jeffreyeverhart.com/2020/01/24/debugging-in-google-apps-script/
Your code appears to be fine from what I can see. Also be aware that depending on your G Suite environment, some of this stuff may be shut off.
Thanks for reading,
Jeff
FOR VICTOR , WHEN “EDIT YOUR RESPONSE” IS WORKING
function FormSubmitEmail(){
var templateid = “YOU TEMPLATE ID”;
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var row = sheet.getLastRow();
var data = sheet.getRange(5,1,sheet.getLastRow()-1,sheet.getLastColumn()).getValues();
var today = Utilities.formatDate(new Date(), “CST”, “MM/dd/yy”);
var firstname = sheet.getRange(“B”+row).getValues();
var lastname = sheet.getRange(“C”+row).getValues();
var docname = (PropertyName+”, “+OwnerName+” “+today);
var Emailaddress = sheet.getRange(“B”+row).getValues();
Logger.log(Emailaddress);
var OwnerName = sheet.getRange(“C”+row).getValues();
var PropertyName = sheet.getRange(“D”+row).getValues();
var ContractType = sheet.getRange(“E”+row).getValues();
var Price = sheet.getRange(“F”+row).getValues();
var ManagedStartingDate = sheet.getRange(“G”+row).getValues();
var OwnerPhoneNumber = sheet.getRange(“H”+row).getValues();
var OwnerEmailAddress1 = sheet.getRange(“I”+row).getValues();
var URL =sheet.getRange(“J”+row).getValues();
var folder = DriveApp.getFolderById(“FOLDER ID”);
var docid = DriveApp.getFileById(templateid).makeCopy(docname, folder).getId();
Logger.log(docid);
var doc = DocumentApp.openById(docid);
var body = doc.getActiveSection();
body.replaceText(‘{{Timestamp}}’,today );
body.replaceText(‘{{Email address}}’, Emailaddress);
body.replaceText(‘{{Owner Name}}’, OwnerName);
body.replaceText(‘{{Property Name}}’, PropertyName);
body.replaceText(‘{{Contract Type}}’, ContractType);
body.replaceText(‘{{Price}}’, Price);
body.replaceText(‘{{Managed Starting Date}}’, ManagedStartingDate);
body.replaceText(‘{{Owner Phone Number}}’, OwnerPhoneNumber);
body.replaceText(‘{{Owner Email Address1}}’, OwnerEmailAddress1);
doc.saveAndClose();
Hi Jeff – this is such a great tutorial. I’ve got it 90% working but it won’t actually replace the text in the document. It saves a copy of the template with the right name based on form response (within the template I have the proper replace text strings /names with {{ }}), it’s just not replacing the text with the form responses in the body. Any idea what I’m doing wrong?? Feel like it’s sooo close to working and would be HUGE for a project I’m working on.
thanks in advance,
Alison
function autoFillGoogleDocFromForm(e) {
var timestamp = e.values[0];
var FirstandLastName = e.values[1];
var AgencyName = e.values[2];
var templatefile = DriveApp.getFileById(‘1MzpZ5EP_WfEOzHT5Qy6OeptN_zswy4_a’);
var formsubmissionsfolder = DriveApp.getFolderById(’15FzSOnktl1G7s4ngvcVHslQB_8VSk75O’);
var copy = templatefile.makeCopy(FirstandLastName, formsubmissionsfolder);
var doc = DocumentApp.openById(copy.getId());
var body = doc.getBody();
body.replaceText(“{{FirstandLastName}}”, FirstandLastName);
body.replaceText(“{{AgencyName}}”, AgencyName);
doc.saveAndClose();
}
Hi Alison,
Thanks for reaching out. If it is creating a copy of the document, it seems like the only possible error could be in the spacing of your replacement tags in the Google Doc. These have to match what is in the code 100%, so check things like whitespace and capitalization. Let me know if that doesn’t solve your issue and we can troubleshoot from there.
Thanks for reading,
Jeff
@Jeff Everhart Thank you so much for this tutorial. It was my first time using scripts and the instructions were simple and easy to follow. My form -> Sheet -> Google doc is working perfectly.
Quick question – Is there a way to save the doc a pdf instead/or in addition to the google doc? I am hoping the “doc.saveAndClose();” has some other bits I can add to do this but my searching is coming up short.
Hi Ced,
Thanks for reading. That should be fairly straightforward with just a few additional lines. After we save and close the doc, we can get the Drive file as a PDF using the File.getAs method:
var pdf = copy.getAs('application/pdf');Then, after we have the reference to the PDF, we can create a new Drive file with the PDF:
folder.createFile('New File Name', pdf);That obviously assumes that we are saving the PDF in the same file as the Google Doc version.
Let me know if you have any issues implementing the above steps. Thanks for reading,
Jeff
Hi Jeff,
Thanks a lot for your script, it is very well explained and it helped me a lot on a project I am working on.
I still can’t figure out something.
In my google form, people have to upload a map (jpg file). I would like this picture to appear in the google doc filed by the script. But the body.replaceText(‘{{image}}’, image) replaces {{image}} in the doc by a link to the uploaded image.
What I want is the image to appear directly in the doc.
Do you know any way to make that happen ?
Thanks again,
Jul
Hi Jul,
I’ve gotten that question a few times on this video, so I’ll try to schedule in working on an example as time allows. There are at least two steps: the first step is getting the DriveFile that was uploaded as a blob, then you could insert the image into the document using a different function like so: https://stackoverflow.com/questions/7898497/add-images-to-google-document-via-google-apps-script
Thanks for reading, and if you get a solution working feel free to share it here. I will likely tackle this as well, but that may be a few weeks out. Thanks for reading and best of luck.
Jeff
Hey Jeff! Thanks for making this video.
I am stuck at “Add Trigger for Auto Fill Google Doc” and saw similar comments on YouTube about people getting stuck here.
I followed all the steps word for word and used the same document names. The script was created from within the spreadsheet.
Any suggestions? Could it be a permissions issue?
Hey Ryan,
Thanks for reading and sorry you got stuck here. What behaviors or error messages are you seeing? I’d suggest reviewing this article, particularly the part about setting error notifications for triggers: https://jeffreyeverhart.com/2020/01/24/debugging-in-google-apps-script/
I’ll need more data what is/is not happening to help you out.
Thanks,
Jeff
Any suggestions on how to modify the Google Apps Script in the article to automatically select radio buttons or checkboxes in a Google Form?
It sounds like you might be looking to do something like this: https://stackoverflow.com/questions/27844608/google-surveys-a-way-to-pass-url-parameters-into-survey
Hi Jeff,
Thank you for this coding, super helpful, do you see that this would be possible in google slides, either in text boxes or a table? If so is there anywhere you could point me to get that code?
All of this code works and produces a slide for each submission it just doesn’t replace the text.
Thanks in advance.
Hi Jaz,
Everything is pretty similar, but you can find the replace text methods for the SlidesApp which should work very similarly to what I have outlined with the DocumentApp.
Thanks for reading,
Jeff
Cross posted on your youtube as well. I your script working but i wanted to know if I could have Doc go to specific subfolders based on value in the response form? Example I have the parent folder id and in the folder i have child folders of all the values for “title” could I tell the script to put the Doc into the folder name by (title)?
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!
Hey Jeff! Thanks for sharing this. It helped me see new possibilities of automation that I thought weren’t possible within Google Suite. Even though this article helped me tremendously, I still feel the need to tweak it a bit: I also need another var that, for instance, is a PROCV working on the 5th column, based on a key at column 4. I realized I can’t make use of “var example = e.values(4);” to retrieve it. So, in other words, at the same time the new doc is generated with data from the form, I also need it to get a few more cells from columns not included in the form. Would you be so kind to point me towards the solution?
Hi Guilherme,
I’d recommend checking out this article on how to retrieve rows in Google Sheets. To get the specific range, you can use this getLastRow method to get the last row. From there you can extract the values from the row. Let me know if that doesn’t work or feel free to post some of your code as a sample.
Thanks for reading,
Jeff
Hello, thank you for the helpful tutorial,
I have a problem, it says
“TypeError :Cannot read property ‘values’ of undefined (line 2, file “Code”)
here is my code
function autoFillGoogleDocFromForm(e) {
var timestamp = e.values[0];
var name = e.values[1];
var id = e.values[2];
var technique = e.values[3];
var grafts = e.values[4];
var hotel = e.values[5];
var nights =e.values[6];
var stars = e.values[7];
var file = DriveApp.getFileById(“108Eknsy3GQ6bYSTusfjgL5AuDJjy0Y4ioA8x9kX7cqc”);
var folder = DriveApp.getFolderById(“1w4LxEiqqyQP90HsP-QcezMjdCf4hGUcl”)
var copy = file.makeCopy(name + ‘,’ + id, folder);
var doc = DocumentApp.openById(copy.getId());
var body = doc.getBody();
body.replaceText(‘{{name}}’, name);
body.replaceText(‘{{id}}’, id);
body.replaceText(‘{{technique}}’, technique);
body.replaceText(‘{{grafts}}’, grafts);
body.replaceText(‘{{hotel}}’, hotel);
body.replaceText(‘{{nights}}’, nights);
body.replaceText(‘{{stars}}’, stars);
doc.saveAndClose();
}
Can you please help me find a solution ?
I tried rewriting, recreating everything, stil lthere is a problem with the ‘values’
Check out this article: https://jeffreyeverhart.com/2016/08/06/fix-typeerror-cannot-read-property-values-undefined/
Hey Jeff,
I found a way to embed and resize photos uploaded in a form into a google doc by reading posts on stack overflow so it’s my turn to share. Here is a way to do it :
In your autoFillGoogleDocFromForm function, create a variable ‘replaceTextToImage’ which is a function allowing you to resize uploaded images in your google document as follows :
//function embedding image to document with a specified size
var replaceTextToImage = function(body, searchText, fileId) {
var width = 600; // Please set this.
var blob = DriveApp.getFileById(fileId).getBlob();
var r = body.findText(searchText).getElement();
r.asText().setText("");
var img = r.getParent().asParagraph().insertInlineImage(0, blob);
var w = img.getWidth();
var h = img.getHeight();
img.setWidth(width);
img.setHeight(width * h / w);
}
// Get the image fileID
var image = e.values[xx]; // xx being the row number in which the link to the uploaded image is
var imageID = String(image).split("=")[1]; //gives you the fileID of the uploaded image
// Use the function replaceTextToImage
replaceTextToImage(body, '{{image}}', imageID); // replaces the {{image}} tag with the uploaded image
There might be easier ways to do this but this is the one I use. I didn’t invent it, I read and adapted some bits of code found on the internet so cheers to the actual coders !
That’s awesome Jul! Thanks for writing back with a solution. Do you mind if I incorporate this into a new post since so many people ask me this question? Do you have a website or some social handle I can link to to give you some credit.
Thanks,
Jeff
Can’t edit my post but please indent line 2 to 9 of code like this :
var replaceTextToImage = function(body, searchText, fileId) {
var width = 600; // Please set this.
var blob = DriveApp.getFileById(fileId).getBlob();
var r = body.findText(searchText).getElement();
r.asText().setText(“”);
var img = r.getParent().asParagraph().insertInlineImage(0, blob);
var w = img.getWidth();
var h = img.getHeight();
img.setWidth(width);
img.setHeight(width * h / w);
}
doesn’t work either.
Please indent line 2 to 9 with four spaces
Hi,
The tutorial for this was fantastic and it works really well for what I want to do. To develop this further, I know the form responses are stored in a google sheet, what i want to do is everytime a google doc is created upon form submission is there anyway of copying the link address for that particular google doc and inserting into the correct record for the form response on the google sheet. Its a bit like getEditURL for form responses.
In addition to this, when a form is filled out is there anyway of sending an automated email to a specified email address recorded in the form response?
Sorry, I might be asking for too much here, but if you can help me out I would very much appreciate it.
Many thanks
Arif
Sorry for the late reply.
Here is a link to an example of sending a confirmation email: https://jeffreyeverhart.com/2014/01/31/tech-tip-how-to-send-a-confirmation-email-with-google-forms/
You would need to get the URL of the document using Document.getURL() and then write that url to the spreadsheet.
Thanks for reading
Hi Jeff! Been following your instruction in the video. I’m doing a gdoc which will be auto populated after filling out the gform. However, the in the populated template, the data were jumbled. How do I fix this?
Thanks for reading, if everything is working but the data is jumbled you likely have mixed up some variables somewhere or the replace templates are off. I’d recommend taking a look at those for any mistakes, and if needed delete them and readd them to the script one-by-one so you can isolate where the error is.
Regards,
Jeff
Hi Jeff,
How can I pick up the text under column [30] = FULLNAME in my google form spreadsheet response….This column[30] is manually insert and not a part of my google form and I have an arrayformula to vlookup on this column [30].
Hey Allen,
In this example, the
e.valuescontain only stuff that was submitted from the form. To get extra values from the spreadsheet, you need to use the SpreadsheetApp class to get values directly from the sheet. Check on this link for some examples on how to do that: https://jeffreyeverhart.com/2019/03/01/retrieve-rows-from-google-spreadsheet-with-google-apps-script/Thanks for reading,
Jeff
Thanks for your input and your youtube tutorials is very helpful, especially for me as a beginner in google-script. I had tried to use the getRange to reference it to a specific row, column However, I can’t make it to appear in google doc reports using this
var body = doc.getBody();
var fullName = SpreadsheetApp.getActiveSheet().getRange(1, 32).getValues();
var id = value[1] [32];
body.replaceText(“{{fullName}}”, fullName);
fullName = column32 in my spreadsheet and not a part of my forms.
Any idea what am I doing wrong? thanks.
Hello! This is amazing. I have this all working 95%. But it is that last bit that I need help:
The Goal: Parent fills out form I created to check out a music instrument. Once filled out, teacher goes in and adds instrument serial number in the google sheet entry that the form populates. At the point that the teacher adds that information, this creates the google doc that populates the data the parent and the teacher filled out, automatically. I want the trigger that creates that doc to be when the teacher goes in and adds to existing columns in the google sheet. Is this possible?
Hey Ryan,
Replied to your email
Hey Jeff,
Feel free to create a new post explaining how to embed photos in a doc from a form using the method I posted earlier, no worries for me.
As I mentioned, I just adapted bits of code found on the internet, I am not the original coder so as far as I am concerned, all greetings should go to them !
Rock on! Thanks for reading and contributing
Hi there man great great tutorial very usefull, I am encoutering a problem tho, I do have the copy of the file in the folder on my drive but when I open up the document it has not change anything, everything is working perfect, but its not replacing the text. What could this be???
I will leave my code here:
function AutoFillWorkOrder(e) {
var timestamp = e.values[0];
var Date = e.values[1];
var YourName = e.values[2];
var Order = e.values[3];
var CustomersName = e.values[4];
var Email = e.values[5];
var Phone1 = e.values[6];
var Phone2 = e.values[7];
var Quantity = e.values[8];
var MakeModel = e.values[9];
var L = e.values[10];
var W = e.values[11];
var H = e.values[12];
var WeightLbs = e.values[13];
var Deposit = e.values[14];
var COD = e.values[15];
var Total = e.values[16];
var SpecialInstructions = e.values[17];
var templatefile = DriveApp.getFileById(“1ltvfMjzHuey8vkHu65sGbuUVxfn1GnCS9Jh1WKAiOvA”);
var templateResponseFolder = DriveApp.getFolderById(“1kjaMSX7w2IOLGKpywe69TX20BWwhWEwR”);
var copy = templatefile.makeCopy(CustomersName + ‘ , ‘ + Order, templateResponseFolder);
var doc = DocumentApp.openById(Copy.getId());
var body = doc.getBody();
body.replaceText(“{{Date}}”, Date)
body.replaceText(“{{YourName}}”, YourName);
body.replaceText(“{{Order}}”, Order);
body.replaceText(“{{CustomersName}}”, CustomersName);
body.replaceText(“{{Email}}”, Email);
body.replaceText(“{{Phone1}}”, Phone1);
body.replaceText(“{{Phone2}}”, Phone2);
body.replaceText(“{{Quantity}}”, Quantity);
body.replaceText(“{{MakeModel}}”, MakeModel);
body.replaceText(“{{L}}”, L);
body.replaceText(“{{W}}”, W);
body.replaceText(“{{H}}”, H);
body.replaceText(“{{WLbs}}”, WeightLbs);
body.replaceText(“{{Deposit}}”, Deposit);
body.replaceText(“{{DOC}}”, COD);
body.replaceText(“{{Subtotal}}”, Total);
body.replaceText(“{{Total}}”, Total);
doc.saveAndClose();
}
Hello Jeff, a truly straight forward tutorial and very useful for my University organization project, but i have a problem, how to make the code that doesn’t generate a new doc file but instead updating the same doc file?. eg: i have a doc file and table in it, it has a list of students name “{{Name}}”, so when a new form submitted the {{Name}} on the list also got increment, thank you!
Thanks for reading and the kind words. I made this tutorial with the specific purpose of creating open letters and petitions, but it sounds like adding form responses to a single Google Doc table is what you are looking for: https://jeffreyeverhart.com/2020/05/18/open-letter-maker-with-google-forms-docs-and-apps-script/
Thanks for reading and comment back if you need any additional guidance.
Jeff
This was so helpful!! I very much appreciate your willingness to share your knowledge! I was wondering if you ever found a solution to the form resubmission issue. My first form submission runs perfectly, but if the form is resubmitted, the resulting file contains only the data that was edited. Thanks again!!
Sorry for the late reply, but I’m still working on the form resubmission issue. No updates there from me, sorry. JE
Is it possible to use similar code to do this WITHOUT using Google Forms?
For example, a spreadsheet where A1 = Customer Name, B1 = # Orders, and C1 = Total Price. I would then manually input the customer name and number of orders into A2 and B2 respectively, and a formula in C2 would automatically calculate the total price based on the value in B2. Then, whenever I edit this spreadsheet (the trigger), it would populate a new Doc with that information. This doesn’t work with Forms, since it can popular A2 and B2, but has a blank C2 (which is where the formula needs to be).
I think you have two options; option one would be to just calculate the total price using Google Apps Script and then include in the document. But, yes, you could also generate this from the spreadsheet. I would add a menu and trigger each row using a menu option.
Hi Jeff,
Your video was SUPER helpful in creating my linked “form to sheet to template autofill doc”. I just want to say that I had/ have no experience writing codes. I was wondering if it would be possible to take it a step further. All the google docs that are created into the autopopulated folder, is there a script that will embed links to each document into 1 excel sheet. So you can open the response excel sheet (with the columns of information from the gogole form) and add a column so that the corresponding google docs for each response are embedded. Does that make sense?
Hi Nicole,
Yes, that is possible. I’m working on a blog post at the moment that will do just that. I’ll reply to this comment when it’s ready.
Thanks,
Jeff
Thank you very much! Don’t know anything about coding, but with your clear instructions I was able to make it work. I’m going to use it a lot!
Thanks for reading/watching and leaving some kind words!
Hi Jeff,
I really liked your video…it was so easy to follow. I followed the steps and watched the video several times.
When I submit my form, a Google Doc is created and saved with the last and first name as in your example.
However, the information inside the document does not change. It still reads {{First Name}} etc.
I have tried, repeatedly, copying those fields and pasting them into the body.replaceText area.
I just can’t seem to get it to work.
Below is a copy of what I entered. I don’t know if just by looking at it you can tell anything but, any assistance you can provide would be greatly appreciated.
function autoFillGoogleDocFromForm(e) {
var timestamp = e.values[0];
var firstName = e.values[1];
var lastName = e.values[2];
var title = e.values[3];
var templateFile = DriveApp.getFileById(“1yQDz2uhpKlr3Yr9lYn4VcLgtJD1d3luvSfpd03prSYo”);
var templateResponseFolder = DriveApp.getFolderById(“1Pfj99ExJegseE46zDfuc6Ugtoj75AJcr”);
var copy = templateFile.makeCopy(lastName + ‘,’ + firstName, templateResponseFolder);
var doc = DocumentApp.openById(copy.getID());
var body = doc.getBody();
body.replaceText(‘{{First Name}}’, firstName);
body.replaceText(‘{{Last Name}}’, lastName);
body.replaceText(‘{{Title}}’, title);
doc.saveAndClose();
}
I would recommend reading this article and implementing at least the error responses in email to see if an error is getting thrown somewhere. Everything in you code looks good at a first glance: https://jeffreyeverhart.com/2020/01/24/debugging-in-google-apps-script/
I can’t get mine to work. I am getting an error message stating that the “Driveapp is not defined”. My is script is below?
function autoFillGoogleDocFromForm(e) {
var timestamp = e.values[0];
var email = e.values[1];
var firstName = e.values[2];
var lastName = e.values[3];
var templateFile = Driveapp.getFileById(“1G5Q4lS3CubVAmf6kgfLPdWRkNB0ZtPVs-cAvbvepwr0”);
var templateResponseFolder = Driveapp.getFolderById(“1XpXaB0Mi-TWEJFkDq-me3z9UQeYNBXnj”);
var copy = templateFile.makeCopy(lastName + ‘, ‘ + firstName, templateResponseFolder);
var doc = DocumentApp.openById(copy.getId()); var body = doc.getBody();
body.replacetext(“{{Email}}”, email); body.replacetext(“{{FirstName}}”, firstName); body.replacetext(“{{LastName}}”, lastName);
doc.saveAndClose(); }
It should be DriveApp, not Driveapp. Looks like that is just a typo.
Hi Jeff,
Your video is awesome! Wondering if you could help me figure out how to include something in your script. The form is generating perfectly from your Google script, I’m trying to figure out how to include my ‘getOwnName ‘ so it runs and I dont hafta ask the people to enter their name. Any Suggestions?
function getOwnName(){
var userName = Session.getEffectiveUser().getUsername();
var sheet = SpreadsheetApp.getActiveSheet();
sheet.appendRow([userName]);
}
Thanks
This is a great question. Typically, in this scenario, I set the form to collect email addresses and then use those. I think
Session.getCurrentUser()is more inline with what you are looking for. The effective user is the user who is running the script, which will in most cases be YOU for this example. The current user may give you a different result. Let me know how that goes and post back here with your results: https://developers.google.com/apps-script/reference/base/session#getActiveUser()A note though, this is a known issue with Google Apps Script, but it has trouble detecting the current user if a user is signed into more than one Google account at a time.
Hi Jeff
Thanks for this great tutorial. My skills are very limited and instead of reading through the whole post, I thought I’d just ask, if you don’t mind.
I own a Pawn Shop and we use a hand written book for pawn transactions. Its time consuming and obviously takes a while to find documents for specific customers.
My thought was to create a fallible form (name, address, tel nr etc) and use the data to populate a pre defined contract. Also some calculations must take place based on the pawn amount. And finally a unique document number must be generated for every transaction.
All documents must be stored for reprint purposes and I must be able to search the database for current and previous transactions by name, number or other possible fields.
Do you think this is do-able?
Regards,
Theo
Hey Theo,
Yes, this is very doable. I created something similar for a furniture store that uses this to generate purchase orders. You can do simple calculations in the code using JavaScript arithmetic, to calculate totals or percentages etc. There are a variety of ways to generate unique document names/numbers depending on what you want that to look like.
Thanks for reading and post back if you have any questions,
Jeff
Hi jeff!
Your blog and videos is fantastic!
I need one more help!
How do I add the uploaded pdf and jpg files from google form to google doc.
Is there javascript available for it?
I want to add signature,id proof to the google doc.
By using the above script in place of signature and id proof the google drive link is being pasted.
Waiting for your response!
Thanks for watching! I’ve been meaning to do a blog post on this, but if you look through the comments someone has figured it out and posted the code there.
Dear Jeff,
thank you for your tutorial. It’s really easy to understand. However, I come across a problem with using doc because of it’s limitation on image formating, so i was trying to use Slides instead. However, I get the error message “Exception: You do not have permission to call SlidesApp.openById.” How do I grant permission? There was no prompting or anything.
Thanks in advance. I’m new to all of these.
Sometimes you can run something in the script editor if you need to force a reauthorization after you change the scope of services you use. It also would be worth making sure you have access to the Slides resource you’re trying to access as well since you can get errors that way as well.
I’m interested to know what image formatting stuff made you switch to Slides if you want to share more details.
Thanks for reading and hope everything works out for you!
Jeff
Hi Jeff,
Thanks for this tutorial, it’s perfect! I just have one question. Instead of creating a new doc every time, I wonder if it’s possible to append new google sheet rows as a new row in the table in the google doc in a single doc when a form is submitted?
Best,
Liz
Hi Liz,
Thanks for watching/reading. It sounds like what you want to do might be covered here: https://jeffreyeverhart.com/2020/05/18/open-letter-maker-with-google-forms-docs-and-apps-script/
Regards,
Jeff
Hi Jeff,
This is a wonderful tutorial and managed to implement a real time-saver. I have seen comment 103 from Nicole who also enquired about the possibility of a new column entry for each submitted form containing the link to the generated template.
Would be fantastic if you have an idea how to implement this.
Thanks again,
Ewan
Hi Ewan,
If you watch this video, where I do a variation of what is done in this tutorial, I demonstrate how to write the link back to the sheet: https://jeffreyeverhart.com/2020/09/29/auto-fill-a-google-doc-template-from-google-sheet-data/
Thanks for watching/reading!
Jeff
Help, trying to streamline my lesson planning and thought I had done these instructions perfectly. First I got it to create a new doc in the response folder, but it wasn’t replacing the text. Now it’s not even creating a doc in my folder. I have my trigger set. I tried entering a sample form entry. My values match up with the spreadsheet. I can’t figure out what is wrong.
Here is the code:
function BeginningChoirFormtoDoc(e) {
var Timestamp = e.values[0];
var BegClass = e.values[1];
var Date = e.values[2];
var TEKS = e.values[3];
var Materials = e.values[4];
var Bellringer = e.values[5];
var Warmup = e.values[6];
var LessonActivity = e.values[7];
var Rehearsal1 = e.values[8];
var Rehearsal2 = e.values[9];
var Rehearsal3 = e.values[10];
var LessonPlansBeginnerTemplate = DriveApp.getFileById(“1x_WTtQWE6jkZUsugzI54qu4GKU3AbgU0AT8wGPhLaaE”);
var templateResponseFolder = DriveApp.getFolderById(“1JXE9CD5vJ4WC-wVo30yYmt2ZnCqtcibn”);
var copy = templatefile.makeCopy(BegClass + ‘ ‘ + Date, templateResponseFolder);
var doc = DocumentApp.openById(copy.getId());
var body = doc.getBody();
body.replaceText(“{{BegClass}}”, BegClass);
body.replaceText(“{{Date}}”, Date);
body.replaceText(“{{TEKS}}”, TEKS);
body.replaceText(“{{Materials}}”, Materials);
body.replaceText(“{{Bellringer}}”, Bellringer);
body.replaceText(“{{Warmup}}”, Warmup);
body.replaceText(“{{LessonActivity}}”, LessonActivity);
body.replaceText(“{{Rehearsal1}}”, Rehearsal1);
body.replaceText(“{{Rehearsal2}}”, Rehearsal2);
body.replaceText(“{{Rehearsal3}}”, Rehearsal3);
doc.saveAndClose();
}
Check out this post on debugging and post back if you can find any error messages that can point us in the right direction: https://jeffreyeverhart.com/2020/01/24/debugging-in-google-apps-script/
At a cursory glance, your code looks fine.
function autoFillGoogleDocFromForm(e) {
//e.values is an array of form values
var name = e.values[1];
//file is the template file, and you get it by ID
var file = DriveApp.getFileById(‘1fd1W0WX7rnraoPfDpDVqUt6gEIc9vOJ6-vJSSROKrqQ’);
//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(‘1nFKe3KpIFGydfLupHb41RdKwB7k5hqPk’)
var copy = file.makeCopy(name, 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(‘{{name}}’, name);
//Lastly we save and close the document to persist our changes
doc.saveAndClose();
}
error : TypeError: Cannot read property ‘values’ of undefined (line 3, file “Code”)
plz solve it
I explain that error in this post: https://jeffreyeverhart.com/2016/08/06/fix-typeerror-cannot-read-property-values-undefined/
I’ve been trying to modify this code and I don’t really have a lot of experience doing this type of stuff. I had it populating the document but it wasn’t pulling the data from the spreadsheet and now I am getting this error:
TypeError: Cannot read property ‘values’ of undefined (line 3, file “Code”)
I’m guessing its not finding the form or the spreadsheet but I am lost. Any help would be appreciated. Thanks
Read explanation here: https://jeffreyeverhart.com/2016/08/06/fix-typeerror-cannot-read-property-values-undefined/
It doesn’t work with me. I need this script work. But It writes Error on a row 3
What does the error say?
Hi Jeff,
Have used your script to great effect. I have an email requested in my form and to allow this email editor access to the created document, I have the following;
var doc = DocumentApp.openById(newId).addEditor(email);
The issue I have is the whole script stops working if the email added is a non Google/G-suite domain.
the error is the format ‘Exception: Invalid email: email@domain.com‘
I can’t figure a way of having the script continue to auto-fill the doc at this point.
Any suggestions would be appreciated.
Thanks
Sorry for the late reply, but you could try wrapping that line in a try/catch block so that the rest of the code can still run: https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Statements/try…catch
Regards,
Jeff
Excellent tutorial!
I would like to add a layer to this.
I would need to save/send the filled doc to a specific folder based on a provided value in the doc.
Based on the department and location. Ie. Montreal location, marketing department.
I have created a folder for each location with subfolders for each department. Different managers will have access to specific information (pay plan, employee development plan, etc.)
Any clue
So sorry for the late reply, but that sounds like a lot of conditional logic based on the form values. You would use a series of if/else blocks to determine the location of the newly created Google Doc.
Regards,
Jeff
Hope someone can help – Edit / Current Project’s Triggers / add trigger
There’s no event source for the spreadsheet
There’s only Time-driven and From Calendar
What have I missed?
Thanks
The most common issue here is that the script is attached to the form instead of the spreadsheet. Can you take a look at that for me? Thanks for commenting, Jeff
I am trying to replace text in the footer section as well as in the body.
I added the following script:
var footer = doc.getFooter();
footer.replaceText(‘{{ClientName2}}’, clientName);
footer.replaceText(‘{{CurrentDate}}’, contractDate);
However, the footer text never gets replaced. The body text is replaced perfectly. Any idea why the footer text isn’t being replaced?
The replaceText method should be available on the footer as well, so that should be available. Are you sure that the text you are trying to replace is in the footer? Here I would try getting the text of the footer and the using Logger.log to see what is in there. You can check out this article on debugging for some insight on how to work through issues like this: https://jeffreyeverhart.com/2020/01/24/debugging-in-google-apps-script/
Thanks for commenting,
Jeff
var pdf = copy.getAs(‘application/pdf’);
I’m trying to save the doc as a pdf. I see the advice you gave to others about getting the Drive file as a PDF using the File.getAs method:
var pdf = copy.getAs(‘application/pdf’);
Then, after we have the reference to the PDF, we can create a new Drive file with the PDF:
folder.createFile(‘New File Name’, pdf);
But I can’t get this to work. Could you help me figure this out? The rest of the code works perfectly and was very helpful! Thank you!
I am trying to save the doc a pdf in addition to the google doc? I see the advice you gave to others about gettting the Drive file as a PDF using the File.getAs method:
var pdf = copy.getAs(‘application/pdf’);
Then, after creating a new Drive file with the PDF:
folder.createFile(‘New File Name’, pdf);
but I can’t get this to work so I’m obviously missing something. I have copied the rest of the code and it works perfectly, but I don’t get the pdf. Could you help me please? Thank you!
I have a project where we have students self-evaluate through a google form, and we need the responses from the form submission to fill a new spreadsheet (not a Google doc) for each student responding. Using your code, I can make a copy of the Google Sheet template for each form submission, name it, and save it to a folder. However, I have not been able to replace or fill the template fields with selected data from the form submission. Is there a way to use Apps Script to replace tag values in a Google Sheet template in a similar manner you illustrated auto-filling a Google Doc from a Google Form Submission?
Hmmm…that is a great question and a use case nobody has really asked about. In a manner it would be very similar, but with some entirely different concepts. First, you would need to use the SpreadsheetApp class instead of DocumentApp to open the spreadsheet to do any modifications:
const ss = SpreadsheetApp.openById("ID_GOES_HERE");
https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app#openById(String)
From there, you have some options on what to do. There is a TextFinder class for the spreadsheet, but that might get cumbersome since it is not as straightforward as the one in DocumentApp: https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet#createTextFinder(String)
Another path would be to use the spreadsheet app to fill in data in the ranges you need, and not look for text to replace. This would require a Spreadsheet that doesn’t change for the life of the script, but might be easier to code, even though it could be tedious depending on the amount of changes that need to be made. Here are just a few links to docs on methods to use for that:
https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet#getsheetbynamename
https://developers.google.com/apps-script/reference/spreadsheet/sheet#getrangerow,-column
https://developers.google.com/apps-script/reference/spreadsheet/range#setValue(Object)
And a link to a more in depth tutorial on writing data to the spreadsheet. Please post back it you run into issues. Thanks for reading and best of luck. Cheers, Jeff
Hi Jeff,
I am trying to run a really similar automated app script, however instead on using a Google Form the data is being added through a SurveyGizmo (Alchemer) integration. I believe it isn’t working because the trigger is on form submit. I tried changing that to on edit, but it didn’t work. Do you know how I can fix this?
Thanks in advance!
These things depend highly on the method used to integrate with these third party services. It’s possible to make it work with the onEdit trigger, but you’ll need to rework how you access the data from the event object since the objects for onEdit and onFormSubmit are not the same.
Try logging the event object onEdit, and see what gets passed. You can see more details on the edit object here: https://developers.google.com/apps-script/guides/triggers/events#edit
I’ve got an article about debugging that also walks through some logging and other tips: https://jeffreyeverhart.com/2020/01/24/debugging-in-google-apps-script/
Thanks for reading and feel free to post back with some results.
Regards,
Jeff
Hello Jeff Everhart,
Helpful tutorial but I want to use Spreadsheet as a template instead of Doc. Waiting for the reply.
Sorry for the late reply, but the process for spreadsheets is less straightforward than documents: https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet#createTextFinder(String)
There is a
createTextFindermethod on the individual sheets that will let you do this. Unfortunately, I don’t have an example of my own.Thanks for reading, Jeff
This is my first time working with script, and I keep getting an error when I run it.
Exception: Unexpected error while getting the method or property getFileById on object DriveApp.
Would you be willing to look at my template, sheet, and script to see what I’m missing?
THANK YOU!
Sure thing, can you post the code here first.
Is it possible to have the edited document opened in a new tab after the form submission? For example: someone would submit a form with their information and the new Google Doc would pop up for them with their info included.
I’m not sure you can do that, but you could send them and email with the link or something like that.
Regards,
Jeff
Hi Jeff,
I’d like to say thanks for this tutorial it has been most helpful and introduced me nicely into coding for automation!
I have read all 126 comments and searched through other parts of your website for this fix or variation.
Thanks in advance to my below questions.
Question 1
Is there a way to have your temple filled and formatted the way you wanted with headers and paragraphs. Then based upon the form submission the automation either adds or removed sections from the template?
eg. I have 20-30 titles and might only want to use 20 of them.
Head – Title
Paragraph about the head
Shoulders – Title
Paragraph about shoulders
Knees – Title
Paragraph about knee.
etc-
Than in my form I have a check box with multiple answers. It will create a new file with the ones that have been selected and remove the ones that have not been selected. The template will be fully written up to have the headers and paragraphs.
is this a if statement? e.values[1] = Yes then keep/delete {{head moving}}
Question 2
Can you also create a form that has the names imported from the result of a different form?
Question 3 and 4
How can I change my code to create a new File and Folder each time?
The last 2 lines of the code does not create a saved version of the PDF I have tried to change every part of it and saw this in your answer to Question 82 Ced Ruby on this page. I couldn’t get it to save as a PFD for me.
function autofillfromgoogledocform(e) {
var timestamp = e.values[0];
var lastname = e.values[1];
var firstname = e.values[2];
var headmoving = e.values[3];
var headlifting = e.values[4];
var headturning = e.values[5];
var swaying = e.values[6];
var templatefile = DriveApp.getFileById(“15XJ_lxi54VEx78WqUU0Ul”);
var templateResponceFolder = DriveApp.getFolderById(“8-22gghx3rfOF6ry4RMyC”);
var copy = templatefile.makeCopy(lastname + “,” + firstname,templateResponceFolder);
var doc = DocumentApp.openById(copy.getId());
var body = doc.getBody();
body.replaceText(“{{firstname}}”,firstname);
body.replaceText(“{{Lastname}}”,lastname);
body.replaceText(“{{Head moving}}”,headmoving);
body.replaceText(“{{Head Lifting up}}”,headlifting);
body.replaceText(“{{Head turns early}}”,headturning);
body.replaceText(“{{Swaying}}”,swaying);
Hey Alex,
Thanks for reading and taking the time to write-up your questions. In short, you likely didn’t find answers in the comments because these are all really specific use cases of what you want to do, and will need to apply a bunch of JavaScript concepts to make all that happen. When programming, it helps to break large things down into small tasks, which helps us stay focused and not get overwhelmed. I’d recommend reading some of the stuff linked below, and then the official documents for DocumentApp and DriveApp:
https://developers.google.com/apps-script/guides/docs
https://developers.google.com/apps-script/reference/document/document-app
https://developers.google.com/apps-script/reference/drive/drive-app
https://www.w3schools.com/js/js_if_else.asp
After you’ve looked through the resources available there, try to work through one of the questions above. If you get stuck and need some additional guidance, feel free to post back. If we can constrain the issue we’re looking at to one problem, I should be able to offer better advice.
Thanks for reading,
Jeff
I have all of this entered into my scripts and it is pulling up this error, “TypeError: Cannot read property ‘values’ of undefined
autoFillGoogleDocFromForm @ Code.gs:2” I have double checked all the inputs and it is all exactly the same except for the links obviously. Looking for more help.
Thanks!
Hi Katelyn,
Common question that I’ve outlined in another post here: https://jeffreyeverhart.com/2016/08/06/fix-typeerror-cannot-read-property-values-undefined/
If it is still problematic after reading that post, feel free to comment back.
Thanks for reading,
Jeff
Here is the code I mentioned above that I am having problems with
function autoFillGoogleDocFromForm(e) {
var enteredBy = e.values[2];
var dateReceived = e.values[3];
var timeEntered = e.values[4];
var client = e.values[5];
var projectName = e.values[6];
var address = e.values[7];
var city = e.values[8];
var state = e.values[9];
var zipCode = e.values[10];
var bldgOrSuite = e.values[11];
var contactName = e.values[12];
var contactPhone = e.values[13];
var contactEmail = e.values[14];
var estimateNeededBy = e.values[15];
var referredBy = e.values[16];
var accountManager = e.values[17];
var specialInstructions = e.values[18];
var templateFile = DriveApp.getFileById(“1OQRZTKq4nAUx27GisZ9RciJQvm8254fh7FRsR3bmfc8”);
var templateResponseFolder = DriveApp.getFolderById(“1wocRF–kg7Iu6nOcQ4wmtXkqsWInX5uu”);
var copy = templateFile.makeCopy(projectName, templateResponseFolder);
var doc = DocumentApp. openById(copy.getId());
var body = doc.getbody();
body.replaceText (“{{Date}}”, dateReceived);
body.replaceText (“{{Time}}”, timeEntered);
body.replaceText (“{{Entered By}}”, enteredBy);
body.replaceText (“{{Client}}”, client);
body.replaceText (“{{Project Name}}”, projectName);
body.replaceText (“{{Address}}”, address);
body.replaceText (“{{City}}”, city);
body.replaceText (“{{State}}”, state);
body.replaceText (“{{Zip Code}}”, zipCode);
body.replaceText (“{{Bldg or Suite#}}”, bldgOrSuite);
body.replaceText (“{{Contact Name}}”, contactName);
body.replaceText (“{{Contact Phone #}}”, contactPhone);
body.replaceText (“{{Contact Email}”, contactEmail);
body.replaceText (“{{Estimate Needed By}}”, estimateNeededBy);
body.replaceText (“{{Referred By}}”, referredBy);
body.replaceText (“{{Account Manager}}”, accountManager);
body.replaceText (“{{Special Instructions}}”, specialInstructions);
doc .saveAndClose();
}
How fill in a document or a template with a list of names from a spreadsheet? NOT A MAIL MERGE, A LIST?
I want a list of club members who have not paid their dues. A list on one page to show the club president. Only 30 people in the club. Perhaps numbered so he doesn’t have to count them nor I have to edit the finished merge telling him how many. I have two hours looking for this.
Check out this post as it looks at how to add items to a table in a Google Doc: https://jeffreyeverhart.com/2020/05/18/open-letter-maker-with-google-forms-docs-and-apps-script/
And this one shows you how to get the values out of the spreadsheet: https://jeffreyeverhart.com/2020/09/29/auto-fill-a-google-doc-template-from-google-sheet-data/
Sorry for the late reply,
Jeff
Hi Jeff,
This is my first attempt with anything regarding coding so this is all new to me. I tried running the script but it gives me an error message every single time. The file does not “show up” in it’s folder nor do I think the script even runs. The sheet does have data in it so it should be pulling. If I have more data than the variables I am using will that mess with it? Here is the error message:
TypeError: Cannot read property ‘values’ of undefined
autoFillGoogleDocFromForm @ Code.gs:3
Here is the script:
function autoFillGoogleDocFromForm(e) {
//e.values is an array of form values
var timestamp = e.values[0];
var Secretarysname = e.values[1];
var Dateofmeeting = e.values[2];
var PresidingOfficer = e.values[3];
var Timeofmeetingstarted = e.values[4];
var Locationofthemeeting = e.values[5];
//file is the template file, and you get it by ID
var file = DriveApp.getFileById(‘”1lSg5-ED7DPEAnMe2iBJqS-s7_lO3SZeQjqLEgqXks5w”‘);
//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(‘1r3czC6hzF5mf7eKYRl_6aMqBODIKbmub’)
var copy = file.makeCopy(Secretarysname + ‘,’ + Dateofmeeting, 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(‘{{Secretarys Name}}’, Secretarysname);
body.replaceText(‘{{DateofMeeting}}’, Dateofmeeting);
body.replaceText(‘{{PresidingOfficer}}’, PresidingOfficer);
body.replaceText(‘{{Time the Meeting Started}}’, Timeofmeetingstarted);
body.replaceText(‘{{Location of the Meeting}}’, Locationofthemeeting);
//Lastly we save and close the document to persist our changes
doc.saveAndClose();
}
Common issue that I’ve outlined in another post here: https://jeffreyeverhart.com/2016/08/06/fix-typeerror-cannot-read-property-values-undefined/
If it still gives you issues after reading that, feel free to comment back.
Jeff
I have done all your steps in the video and when I try to debug or run the program, it says “TypeError: Cannot read property ‘Values’ of undefined.” I have gone back and made sure I typed everything correctly and generated the form, sheet, and document from the drive folder and it still will not work properly. I have google document I created to populate the data into, but I cant get past this part of defining the value. Hopefully you can help, how do I define the values as all of my variables??
Hi Grant,
Common question that I’ve outlined in another post here: https://jeffreyeverhart.com/2016/08/06/fix-typeerror-cannot-read-property-values-undefined/
If it is still problematic after reading that post, feel free to comment back.
Thanks for reading,
Jeff
This is wonderful, but I’m having a little trouble. I only want this scrip to look at specific columns in my sheet and some of the columns in-between then will be blank. It seems like this is causing issues with the script. I’m asking it to look at columns 1-4 and 16-18. It is returning the error:
TypeError: Cannot read property ‘3’ of undefined
at autoFillExpenseVoucherTemplate(Code:4:24)
Here is my whole code:
function autoFillExpenseVoucherTemplate(e) {
var date = e.values[1];
var name = e.values[2];
var payable = e.value[3];
var category = e.value[4];
var expenses = e.value[26];
var explain = e.value[27];
var amount = e.value[28];
var file = DriveApp.getFolderById(“1e6P3JUi1aAW1_tLUf-E5-L2LSMGS-InjDqhFcRzQf_g”);
var folder = DriveApp.getFolderById(“1Kvkt7_Ux8b54_nV4SdK2vAQGi_MI6VLb”)
var copy = file.makeCopy(name + “,” + date, folder);
var doc = DocumentApp.openById(copy.getId());
var body = doc.getBody();
body.replaceText(“{{Name}}”, name );
body.replaceText(“{{Date}}”, date );
body.replaceText(“{{Payable}}”, payable);
body.replaceText(“{{Category}}”, category);
body.replaceText(“{{Expense Final}}”, expenses);
body.replaceText(“{{Amount Final}}”, amount);
body.replaceText(“{{Explanation Final}}”, explain);
doc.saveAndClose()
}
Check all of the places you are using
e.values, as I see a number ofe.valuebeing used instead, which doesn’t exist. JEHi Jeff,
First of, really great tutorial you have there. It helped a lot!
I had a question I am trying to get something a bit different (but I am not that great with coding lol)
Basically, I would like to have the Google forms populate fields in different templates.
For instance, I would have template 1 with fields {{a}},{{b}} and another template 2 with fields {{a}},{{c}}
I tried to do something with your code but I don’t get the result I hope. Could you tell me how I could achieve this ?
Thanks a bunch
Hi Fabrice,
These comments don’t post until I approve them or reply, so I think I tried to offer some questions to a later post you made.
JE
Hi Jeff!
I would like to say thank you so much for your help and a year ago bombarded with you with all the questions and a year later i finally solved this problem once and for all! Here are the few mistakes i discovered
1. Run the script in Forms (went through all your articles on this and found that I need to add this script in Spreadsheets).
2. Set all the appropriate trigger and tried test run the form. WORKED!
Another is can you help me point to the right direction on how to save the Docs link in that particular spreadsheet row and send an email with the attachment to the person who submitted that form?
Thank you Mr. Everhart for making this tutorial, I tried it on my own document template and was successful! I apologize if this is too basic a question but I have absolutely no experience with coding, how do you make the form information that is inserted in the document conform to the formatting that is in the document? The form answer gets inserted into my document but in some parts of the document I need the information to be bold or all capitalized or a larger font so that it will match the rest of my document. Can you give an example of the code that makes the information match the document formatting or suggest a link to google support that answers this question? Thank you again for your time.
Hi Sarah,
Thanks for the question, and sadly there isn’t anything simple about document formatting with Apps Script. One of the reasons I like the replacement tag technique so much is that it is easy to implement. If you look at the chart here that describes all of the elements that make up the structure of a Google Doc, you can see there is a lot. To style those elements, you basically have to traverse the hierarchy and call styling methods on them. Sorry there isn’t a more straightforward way to explain how to do this, but thanks for reading.
Cheer,
Jeff
Hello Jeff,
Thanks for the great tutorial. I don’t know what happened to my previous post so sorry if there is double posting there.
I wanted to ask you if you had a way for the form to fill different templates for instance in my case I have two google docs templates I hope to fill. Wanted to know if you had an input on what change to make to the code.
Thanks a bunch.
Hi Fabrice,
Thanks for reaching out. The simplest way I can think of would be to use an if statement to do this. You would need to check a condition based on one of the inputs, and then either choose the template in an if/else, or depending on the complexity of the template actually do the replacement in there as well.
Best of luck!
Jeff
Hi Jeff,
Thanks for this tutorial! I am brand new to google scripts, but generally understand what you’ve outlined. I’m trying to use this to collect signatures for a collective letter. Is there a way to adapt this code so that the responses all go into the same document? So that rather than having individual templates with each response in their own doc, the responses are collected in a singular list/table in one doc. Thanks!
Hi Katie,
I actually have a post on creating an open letter generator here: https://jeffreyeverhart.com/2020/05/18/open-letter-maker-with-google-forms-docs-and-apps-script/
Thanks for reading, JE
Hello,
How can I write the URL of the created document back to the sheet using this method? (Auto Fill a Google Doc from a Google Form Submission)
You can see an example of that happening in this code here: https://jeffreyeverhart.com/2020/09/29/auto-fill-a-google-doc-template-from-google-sheet-data/
Another way to approach it would be to find the row with a corresponding timestamp after you’ve generated the document. Thanks for reading, Jeff
Hello and first of all thank you for some great tutorials – excellent stuff.
I’ve used your “Auto Fill a Google Doc from a Google Form Submission” and “Google Forms Confirmation Email with Attachments” scripts, both of which work perfectly for me as separate entities. Thank you.
What I would now like to do is to amalgamate them so that my site visitor is able to complete a form and their data personalises a document which is then sent back to them as a personalised PDF email attachment.
I’ve tried all sorts of ways of joining the two scripts but without success. My latest is shown below but I’m getting the error message sent to me “Script function not found: myFunction”
Any assistance you can offer would be apprecaited.
Neill
function autoFillGoogleDocFromForm(e) {
//e.values is an array of form values
var timestamp = e.values[0];
var name = e.values[1];
var email = e.values[2];
//file is the template file, and you get it by ID
var file = DriveApp.getFileById(‘1KHe9qYobCfJw5Zn492UUjFXlygti8AENGZlKssE-EvQ’);
//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(‘1OH0_dhK8OOH7LLY3KWmh6QuofqePJZ_7’)
var copy = file.makeCopy(lastName + ‘,’ + name, 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}}’, name);
//Lastly we save and close the document to persist our changes
doc.saveAndClose();
var subject = “Thanks for submitting”;
var body = “We look forward to racing with you. Don’t forget to fill out and bring the attached waiver on race day.”;
var waiver = DriveApp.getFileById(“1KHe9qYobCfJw5Zn492UUjFXlygti8AENGZlKssE-EvQ”);
var liabilityWaiver = waiver.getAs(MimeType.PDF);
MailApp.sendEmail(email, subject, body, {attachments:[liabilityWaiver]});
}
All of this code looks good. You might just double check that the trigger is invoking the
autoFillGoogleDocFromFormfunction instead ofmyFunction, as that is what the error message suggests to me.Hi Jeff,
As soon as I use a variable declaration name in another part of the script, the declaration name changes from gray (blue in your video) to black and the error code says all variables are undefined, including the “e” in e.values. Otherwise, it is creating the file in the destination folder, but none of the form values are transferring to the doc and only the text I entered into the templateFile.makeCopy with parenthesis is showing in the file name (ie variable names are not present). Please let me know if you have a solution or if you need additional information and THANK YOU for your great tutorials!
Hi Jeff,
Problem solved. Not sure what I did, but I played around with it and it’s working perfectly! Thanks again.
Thank you SO MUCH for sharing this. Amazing tutorial and it has really changed my work.
I have a second google form with different information, usually submitted days or even weeks later, that I would like to populate the second half of my google doc. Both google forms contain the same ID number as the first question, and the google doc is titled with that ID Number. Is there a way to make the second google form submission open the partially completed google doc and populate the remaining fields?
Yup, you can get a list of Drive files by name using this method: https://developers.google.com/apps-script/reference/drive/drive-app#getfilesbynamename
From there you’d need to check the list of files for the one you want, then open it with the
DocumentApp. After that, the steps are pretty much the same: open the body, replace the tags, save and close. The process for getting the file by name from Drive is a little awkward, but very doable. Interesting approach to the problem. Thanks for reading. Cheers – JeffHi Jeff, Thanks for this great tutorial – got it to work perfectly. I was wondering if you knew a way that I could use a multiple choice question in the google form to determine if the form would print as a google doc or not. I don’t want every submission of the form to become a google doc. This is my first app script. Thanks for any help you can provide.
Thanks for reading. Yes, you can do that with an if statement. You can use the if statement to check if the value of your multiple choice question is what you want, and then execute the code inside the block.
if (multipleChoice === "Print It"){
// run code to create doc here
}
Thank you for the feedback! And my apologies, I just reposted the comment thinking I had forgotten to submit – please ignore that one!
I think I’ve hit a roadblock, I am using this to get the file
var file = DriveApp.getFilesByName(ClaimantID);
But I am not sure how to get it open, I only see OpenbyURL and OpenbyID as DocumentApp functions. Any advice?
Hey Jeff! Loved your post on integrating Google Forms and Docs. My company is thinking about a tool that’d spit out a Doc based on entries to a Form. Only trick is that the Form entries would be split across a few different webpages and would still need to feed into the same Doc. Would love your thoughts on whether that’s possible and ballpark what it’d cost. Thanks!
Ed
Hey Ed, thanks for the reply. Going to follow-up via email. Thanks, Jeff
Hi Jeff first of all awesome tutorial on “Auto Fill Google Doc from Google Form Using Google Apps Script”
I need a little help with my Script, I already checked my code and it’s working fine, but the issue that I’m dealing with is an Error from the DriveAPI
‘ Exception: You do not have permission to call DriveApp.File.makeCopy. Required permissions: https://www.googleapis.com/auth/drive
at autoFillDoc ‘
this is the only error I get once the script gets running and it doesn’t create the file.
I already set up the OAuth scopes on the appscript.json file, but it keeps displaying the same error.
The only scope that I tried was this cause of the error message
…
“oauthScopes”: [
“https://www.googleapis.com/auth/drive”,
]
…
It’s there a way to fix the error??
Are you logged into multiple accounts? That can sometimes mess with auth stuff.