Thank you for the incredible tutorial. It was very easy to follow and helped someone like myself, who had never coded with Apps Scripts, to get a sense of how the language works and be able to personalize my script for my intended application. I've already build off of your script to automate the filling of multiple documents at the clinic I work at, which will dramatically decrease the burden on our doctors and nurses, and allow them overall more time to focus on patient care. Thank you for helping us make that difference!
This tutorial really helped my company save time and mistakes on filling out paperwork. The autofill feature we created with this, and his "Adding Custom Menus" video took some tinkering, but we got it to work, and everyone's delighted at this low cost, low maintenance solution. Thank you Jeff!
The UI in Google Sheets is slightly updated from when this was recorded, but even with my rudimentary coding knowledge I was able to modify this to work. THANK YOU, this is amazing.
@Jeff Everhart out of curiosity, is there a way to use template literals to have the scrip generate a new folder, and store the document in that folder?
How come this guy only has 2.2k followers? Doing such a great job sharing this type of content so carefully so anybody just stepping in this would be able to understand? thank you So much man!! God bless you!
Thanks Jeff. This will come really handy for us. 1. Data collected through google form. 2. Data dumps into spreadsheet 3. Use this script to create a letter for sending out to clients
I just started a role at a new company and docs and sheets are a major part of the workflow. This gives me a great quick way to automate things without standing up a full Python or Node.js application to do it.
Thank you so much for this tutorial! It is so much easier to understand and adapt to different situations when you take the time to explain what every line does instead of just importing a pile of code from some website.
This is just what I need! Hopefully I can execute this on my own template, I don’t really understand any programming language but I’ve surviving by copying codes out of tutorials and browsing the net. Thank you so much for sharing your knowledge!
This was my first dive into Google Scripts or any code for the matter. And it was a successful one! This saved me a lot of hassle and put me on a way of Excel / Sheets skill development and put a lot of ideas into my mind about working with code. You are truly a special kind of person for doing this, at least in my life! Thank you very much.
Thanks for this Jeff. I want the rows of the sheet to populate sections of a single document, not create multiple documents. Which line of code to I delete or modify so that the loop sends the data of each row to the single document?
Thank you for a great tutorial. This is going to save me and my husband so much time with filling out legal documents for our real estate business. I am a complete novice when it comes to coding, but this was very easy to follow and understand. The only thing I had a problem with was an error saying that the document was inaccessible. But, I realized that I had been using a .docx (Microsoft Word) document and that google docs didn’t recognize it within the code, even though it could open the file and edit it. Just thought I’d mention that in case anyone else had the same problem.
At this point, I'm honestly more used to your voice on 0.5x speed haha. Again, thanks so much for this tutorial, it has helped my business a lot to automate some of our systems. I'm officially crowning you as the dad of Google Sheets haha!
That is actually a very good tutorial! I first watched a crappy one called "Autofill Google Doc Template from Google Sheets | Google Apps Script" from the channel Outright System... The guy just stole your code, without even giving you credit, and pasted it without any explanation! Some people have no shame and are awful educators, really. But thank YOU for the great explanation and time you took to actually teach us!
Thank you for this resource! This may be a silly question - This is my first attempt at exploring apps script or coding. Can I do this for just one row of data at a time? My spreadsheet is form responses and some of them require a template checklist of next steps for the team, but not all. It's constantly collecting responses. I do have a column that records whether or not a template needs to be generated.
Great video. I have a question though. Your Google app script primarily works for one column and multiple rows in google doc. Can you help us with Google app script where it generates multiple columns and rows table on Google doc using the data that is available on a single row of Google sheet?
This video is the best! you explained it so well. I needed to add a few more pieces to my code. Having gone into this with no idea how to use AppScript did not matter! This video pointed out to me in the right way to ask my questions online and I was able to get everything to work! Thank you Jeff!
Thank you a million Jeffrey! I had a question. What if an employee has two rows (for example an employee has two different jobs, each with their own separate pay and hours, etc.)? Is there a way to have details from two separate rows populate into a document, separately? Reply
This was so helpful!! Thank you - I created a merge doc weeks ago and went back to your website to find out about formatting (currency)... and found the answer in you FAQ! Massive help - thank you!
Your tutorials are excellent, you do a great job of explaining as you go along. I originally used your prior video, "Auto Fill Google Doc from Google Form Using Google Apps Script," but found on occasion it would fail to generate the doc, without explanation (i.e. someone submits a form, it goes to the spreadsheet, but the doc just doesn't happen.) The method you demo here allowed me to set up a manual workaround for those occurrences.
Hey Jeff, Thanks so much for this ! Out of interest how would i take a cell that has a URL in it and replace a tag in the doc with the image rather than the URL ?
Thank you Jeff for the great tutorial. I had one question, is there a way to create an if statement to read a value in a row to see which google doc template the script should use? I started it with a function with if statements having it read the value in row[x] === "PARTICULAR LANGUAGE" then return the value for the googleDocTemplate = DriveApp.getFileById ('FILE ID'). Sorry if this is a bit confusing. I have multiple templates for different employee variables and want to write the code once versus have multiple codes for each template. I want the script to do the work.
I keep getting the error: Exception: The parameters (String) don't match the method signature for DocumentApp.Body.replaceText. Any idea what may need to be fixed? I have everything plugged in as you do in the video.
Thanks so, so much for this. I'm just dipping my toes into coding and wouldn't have been able to do this sort of thing without your helpful, clear instructions and provision of the template! I made some mistakes in my version of it, but because I was able to rewatch your tutorial, I learned not only what I did wrong, but how to identify where in the code the mistake was made.
This was incredibly easy to follow! Thank you so much! I have several cells that have formulas that rely on other cells. If the target cells are blank, it returns a "" or blank. This script creates documents for all of these rows. Using your tutorial, I was able to add a line of script that if the first name was blank it moves on!
thank you! you really put in the effort to explain everything so that beginners like me can understand the reasoning behind each line and therefore customize the script later on.
Thank you so much for taking the time with these tutorials. Very straight to the point and an easy steps. It's really helping me a lot with my business automations! THANK YOU!
Thank you so much for the video! I have never coded anything before, and I was able to follow your steps and create a huge time-saver for our school's secretary. I appreciated how you explained everything so clearly - it really helped me understand what I was doing and also helped me find mistakes when the errors popped up. :)
Absolutely fantastic video, thank you so much! I just have one question. I have hyperlinks in one column of my spreadsheet, how can I put those in the document? I tried doing it just like your tutorial, but it replaces only the text, not its formatting (i.e. hyperlinks). Is there a workaround?
I have an issue. rows.forEach --> when I try to execute it says Rows is not defined. The data is not being returned as an Array so I cannot use the .forEach. ? Make sense?
Thank you so much. Concise, clear and competent instructions! A great contrast to a lot of other help out here. It's helped me sort out a formatting issue for an online form. What I would really love is to email the resulting doc (form submission output file) as a PDF to myself/my colleague. I have not been able to resolve the variable for the output filename... thanks for any help you are able to give.
This is brilliant! Any chance you can add the script for when there is a choice of templates to pull on depending on a criteria specified in the form????
Thanks so much for this easy to follow vid. i did have a question as i was hopeing to add on to this. is there a way to have it create and new sub folder in the folder named one of the cells then save the doc it is creating into that? meaning every time it created a new url and doc it is also creating a folder for that doc to go into? again thanks for the video
Thank you for your instructions. They are very well done. Do you have any options for how to output just the date from a cell in the month/day/year format?
Great video! simpel and to the point. One question, what if I have a chunk of text and tables in my template, and I want to make it appear or dissappear in the output doc based on a clickbox in the sheets docs. How would I do that? I have a template with descriptions for different services, but I need that segment of text/tables only if the service is procured. So I want to have a list of services in sheets, click on the ones the customer procures.
Hey, thanks for the amazing Content! I got a question, i am trying to change the destination folder to 5 diffrent folders depending on the Text that is in cell 1 - so if cell 1 says "John" then i want the File to be created in the FOlder "Documents John" if the Cell says "Lisa" hen i want the File to be created in the Folder "Documents Lisa". i got 5 Names in Total - so 5 Folders in Total. I guess i need to do it with an "if" statement, but i dont get it to work..
Hi Jeff Thanks for the tutorial Is there a way I can prevent regenerating all documents every time I run the generate new form Script and only generate the new document with the recent data entered or even better if I can specify the certain document I intended to have or If possible only edit the previously generated documents and generate the newly added data rows
Jeff, This tutorial video was great! I know nothing about coding, but I followed your video and it worked! I have one big question - in my sheet at the bottom of each column, I have a SUM function. This SUM function row will be moving further down the sheet as I add more content to the sheet. How can I prevent it from reading this row?? Thank you!
Thank you for the tutorial, really helpful. DO you know if there is a way to create multiple of the same table but in the same doc instead of doing it in new docs? Thanks!
Is there a way to write this code so that if you add more information to the sheet you can rerun it only for those that do not have a link for a google doc? Thank you!!!
Hi @jeff first of all thank you for this tutorial...i have a question. I have made this but i have formula for every blank row. When i run the autofill its creating a doc even without any inputs. Is there a way wherein the scirpt will ignore the formula? Thank you so mch!
Hi Jeff! Thank you for this. I'm looking to import "in-line" text into a Google Doc from a Google Sheet? I think this is really a close way to do it. I'm trying to make a report like this: You webpage got X,XXX visits this month and we posted X,XXX pieces of content. This would pull from a Google Sheet with all that information, but would also be able to tell which month it's in so it would update monthly. Could I use this script to do that or would it need to be different? I don't need to creat new documents, just update the one document from multiple already created Spreadsheets. Thanks for the resource!
Hi, thanks for the tutorial! Very helpful and easy to follow. I have a question: instead of text or dates, I have numbers to transfer from GSheets to GDocs. With your (wonderful) script I get numbers without format. For example I have a number in GSheets formatted as 170,000 and in GDocs it comes out as 170000. Same happens for percentanges (from 25.1% to 0.251) Is there a way to respect the original formatting? Thank you!
Hi Jeff, First of all, Thank you very much for all of this. So it works well except for the name of the document. If I write "const copy = googleDocTemplate.makeCopy('${row[1]},${row[2]} Invitation Letter', destinationFolder);", documents created are called " {row[1]},{row[2]} Invitation Letter. Instead of the name of the invitee. Do you see any error in above syntax? Thanks
Based on what I can see and the behavior you describe, I would double check that there are backticks surrounding the string that gets passed into the makeCopy function, not single or double quotes
Great Video! i did have a question. I want to implement this to an inventory / equipment maintenance system. Will this auto update the Google Docs with information when its changed in the spreadsheet? if not, is there a way to do so?
Hi Jeff, Thanks for this video. Super helpful. I have a question. I would also like to pull data from a second sheet on the spreadsheet. I've been working on it over the weekend and still can't seem to figure it out. Would you be able to assist?
Thank you for the great tutorial. I was wondering if it is possible to have the filled Docs merge into a single Doc, rather a separate doc for each employee. Thanks
Hi, great video. One question is there a way to input data from google sheet in a bunch of EXISTING google docs? Basically, I have around 100 google docs (10 pages each). Just one of those pages needs to be changed o a monthly basis.
Thanks agin! Question: Say I have two documents to be auto-filled depending on from which sheet the data comes (both sheets are within the same overall spreadsheet file). I.e., if the data is in Sheet A, it fills "Contract" doc, and if it's in Sheet B, it fills "Offer" doc. I assume the script edit should take place somewhere around the " .getSheetByName('Contract')" line, where I'd add something like, " .getSheetByName('Offer')"? Apart from the docs being titled differently, they're using the exact same tags. Any advice?
you could create two groups of variables 'googleDocTemplate' 'sheet' and 'rows' for each sheet (templateContract, sheetContract, rowsContract, templateOffer, sheetOffer, rowsOffer) and then run the function inside a forEach loop once for each group
Hi Jeff, I have run into an issue with the body.replaceText code. It works perfectly to a certain point and then stops for the middle of the document, then finishes correctly for the end of the document. I've double checked that I wrote it the same and I really have no background of writing code , this is my first attempt! Any tips?
I have a column of times but when I have the script it pumps out "Sat Dec 30 1899 12:30:00 GMT+0000 (Greenwich Mean Time)". The only value in the cell was the time in automatic format. These times are submitted via the google form's time entry. Is there any script to make it display time in a user friendly way, and ignores all this extra fluff that never showed up in the google sheet in the first place.
Hi Jeff, amazing video and great teaching skills. I was wondering if instead of creating google docs we are able to create PDFs named with a field and saved them to a google shared drive?
Hi Jeff. Thank you for your video - I successfully used it to auto fill a doc saving so much time. I have a question for you: is it possible to add another function for the same spreadsheet? I wrote another that is similar in the hopes of making a separate doc. I checked new project, entered the coding and it saved under “my projects”. I did get an error message and I’m wondering if it’s because I’m working off the same spreadsheet? I can’t seem to figure out whether I can use it w/the same spreadsheet and if so, how?
Hi there. Love this video. As others have said, I am hoping to save some time in creating documents in my role as a Parent-Teacher Group chairperson. Anyway, I am getting an error on the final step "ReferenceError: setValue is not defined". This is my 1st go with coding, but I have checked the code several times. A quick search did bring up some results about their being a problem with the setValue function in newer versions of Apps Script. Is this the case? If so, any help on how to fix it?
Hello! Thanks! Your video helped a lot. I have 1 question! Why decimal coma's becomes decimal dot's after running script? In my sheets i use decimal coma's.
Hi Jeff, this is a fantastic tutorial. It worked flawlessly for me. Thank you. I do have an issue that is not covered. A few of my data area hyperlinks. Is there a why to copy the 'format' to docs, so that they appear as a link in the new document that is being generated? Sorry if it has already beeing asked, could not find it.
This is really useful! Is there a way to automatically link the new pages you create back to the parent spreadsheet, so that certain columns can be filled in when the sheet for a particular row is modified?
Also, when I pull the date column into the document, I am getting the hour/minute time as well as what time zone it is in. I do not want this and only want the date format. do you happen to know what might be going on here?
Hi Jeff - My date data when added to the google Doc is always showing 1 day behind the data which is in the Goggle Sheet. Is there a quick fix for this? I am UK based
Your tutorials are great, a big thank You! Is it possible to automatically make googledocs from a "mastersheet" which updates whenever some new stuff appears in the mastersheet? I would then have, say about 40 docs, and at the same time add a spedific information to every doc without making a new copy. My mastersheet works with outrange and the doc should be stable, so it can be shared with the person whos information it contains.
The issue with keeping a Doc and a sheet data store in "sync" is that once we remove the replacement tags, it's difficult to tell where to update with the new text. The replacement tags are guaranteed to be unique, whereas as populated text does not have that same guarantee
Our finance department has a Google Doc Template for adding new items to our inventory system and I was wondering if there was a way to populate a table in the Template with data from a spreadsheet. Using replaceText I was able to populate the first row but I am not able to get the data in the 2nd row of the spreadsheet to populate in the 2nd row of the table in the Doc template, no mater what I try the first row is all that is returned. Please any help is greatly appreciated.
tried googling this for you and found a stackoverflow article, search this: differences-between-getrange-getdatarange-and-getactiverange here's part of the answer: " getRange() has multiple implementations. Sheet.getRange(row, column) Sheet.getRange(row, column, numRows) Sheet.getRange(row, column, numRows, numColumns) Sheet.getRange(a1notation) " So you'd have to play around with the getRange parameters in your code, to select a part of the range instead of it all.
Hi, on the other hand, I was just checking one more aspect of it, is there a way to copy content of one document into another one with all the formatting. like one document will act as a template and then on the click of the custom menu like yours, we will replace all the contents from the other document. Both the document will have statis/constant URL, document ID. so that I can use the same link in the automation ? is there a way .. request you to please help here...
I'm curious about is whether or not we actually need to use {to create a tag. Could I just use the first and last name? That might sound like a weird question but my goal is to update there document instead of replacing it. Did a lot of reasons why a person might want to do this. In my case I create Google. Invoices that I send out the clients who then have to share that same URL with 20 different people in order to get me paid. It has happened many times wherever create a new document after an update and I would get paid the wrong amount because the most current URL did not make it up the chain of command. From our contacts, here's what I have set up. When I book a client the data comes through form an invoice is created. Upon form submission their data along with the invoice is copied to a second sheet. My thinking is if I update the form response sheet, the data on the second spreadsheet should still match everything on the invoice. I want be able to set the data range on the second sheet as a variable for my tags. If I run my update invoice script, my hope is the script will see the data as the tags on the document we could get by the URL, and then replace those tags with what is currently on the booking for response sheet. To make this easier, we can do one row at a time. I don't need to Loop through the entire. I know I can type in a specific number and save that as a variable in the script, so it would only go through that one row. Is what I'm saying making any sense? Does this sound feasible?
I cannot get the Menu item to populate- Google sheets updated in fall 2021 so under tools there is not an option for Script Editor, I have typed the code in Apps Script but cannot get it to run with my spreadsheet. Any advice?
Best code I found to perform this function! Elegant, to the point and handles spreadsheets with hundreds of rows (the known google bug issue notwithstanding). Thank you so much :D
How would I modify the code to automate filling out a different sheet, rather than a doc? I've got certain cells with {{startDate}} (and other tags in other cells as well), and I want the script to work the same way, but within that spreadsheet. body.replaceText unfortunately doesn't work on a spreadsheet, as far as I can figure out.
@@jeffeverhart Thanks for your help! I'm teaching myself from the ground up, and your tutorial has been so helpful. The most challenging part, like with this sheet vs. doc problem, is that I don't know what I don't know yet!
Hi Jeff- Was able to follow this video and got things working nicely. Once my form is submitted and the Google Doc is created, is there a way to automatically email the DOC out to a list of people I specify? Thanks in advance!
Thank you very much for this! it was very helpful! just one question. Is it possible to add an image to the template from the google sheet? and how would that be??
That's the best tutorial out there. Thank you so much for all the explications, you're a really good teacher. I wish I could give that video 100 likes!
Hi there. This is great. However, even though I get the documents generated, the fields though in the google docs are not populated with the data. I would appreciate any help. Thanks
This is somethig I need for my classroom to generate a home detention doc that generates a form for parents re student last first period date/time, and behavior from a pull down list (will this work if the cell is a list of behaviors?). Also how could one create a script that will generate the same information twice on one page so there can be an English and Spanish on one doc one page or two page to be printed 2-sided.
Thanks so much for the tutorial, it was really helpful. I have a question; How can I select a different template depending on the value of a cell? For example, if the value is equal to XX, a template is selected, while if the cell is equal to YY, a different one is chosen.
Add an additional if/else statement before you make a copy. Use one template in the first block, then the second template in the second block. There are more advanced design patterns if you really want to get into it, but for one-off scripts, just copy and paste, then change the relevant ids.
@@brandonwilson1600 Don't you also have to swap out the "const googleDocTemplate = DriveApp.getFileById(" line right after the Create new GoogleDoc function? What makes it search among a few different templates?
@@pmont72782 Yes, it would be exactly that. Each branch of the If/elseif/else would have it's own version of that line with a different ID, and potentially different code leading to how to fill out the cells, depending on how complicated the resulting structure ends up being.
Or you could set it up as "const googleDocTemplate1 = DriveApp.getFileById(" "const googleDocTemplate2 = DriveApp.getFileById(" ... or "const googleDocTemplatePurchasing = DriveApp.getFileById(" "const googleDocTemplateInvoicing = DriveApp.getFileById(" ... etc. and then just use the appropriate template file in the appropriate branch.
Thank you for the incredible tutorial. It was very easy to follow and helped someone like myself, who had never coded with Apps Scripts, to get a sense of how the language works and be able to personalize my script for my intended application. I've already build off of your script to automate the filling of multiple documents at the clinic I work at, which will dramatically decrease the burden on our doctors and nurses, and allow them overall more time to focus on patient care. Thank you for helping us make that difference!
Thanks for watching!
This tutorial really helped my company save time and mistakes on filling out paperwork. The autofill feature we created with this, and his "Adding Custom Menus" video took some tinkering, but we got it to work, and everyone's delighted at this low cost, low maintenance solution. Thank you Jeff!
Thanks for the kind words and thanks for watching!
The UI in Google Sheets is slightly updated from when this was recorded, but even with my rudimentary coding knowledge I was able to modify this to work. THANK YOU, this is amazing.
@Jeff Everhart out of curiosity, is there a way to use template literals to have the scrip generate a new folder, and store the document in that folder?
Update: Yes, there is! I fed the script to ChatGPT and asked if it could modify to generate a new folder. I can share if anyone wants
Oh wow, that's pretty cool
@@moving2fast2 Please do share!
This script was FANTASTIC! I used it to create a hundred individual data forms from a Google Sheet with nearly 100 individual data points. Thank you!!
How come this guy only has 2.2k followers? Doing such a great job sharing this type of content so carefully so anybody just stepping in this would be able to understand?
thank you So much man!! God bless you!
Thanks Jeff.
This will come really handy for us.
1. Data collected through google form.
2. Data dumps into spreadsheet
3. Use this script to create a letter for sending out to clients
I just started a role at a new company and docs and sheets are a major part of the workflow. This gives me a great quick way to automate things without standing up a full Python or Node.js application to do it.
Thank you so much for this tutorial! It is so much easier to understand and adapt to different situations when you take the time to explain what every line does instead of just importing a pile of code from some website.
This is just what I need! Hopefully I can execute this on my own template, I don’t really understand any programming language but I’ve surviving by copying codes out of tutorials and browsing the net. Thank you so much for sharing your knowledge!
Awesome! Thanks for watching
This was my first dive into Google Scripts or any code for the matter. And it was a successful one!
This saved me a lot of hassle and put me on a way of Excel / Sheets skill development and put a lot of ideas into my mind about working with code.
You are truly a special kind of person for doing this, at least in my life! Thank you very much.
Thanks for watching!
You are incredible. I was trying for so many days. I have no coding knowledge but just a determination to solve a problem we had. Thank you!!
Thanks for this Jeff. I want the rows of the sheet to populate sections of a single document, not create multiple documents. Which line of code to I delete or modify so that the loop sends the data of each row to the single document?
Wanted to see if anyone could respond to this awesome question, don’t need to make so many documents.
instead of trying to do that i think it would be easier to just merge all the documents you create
Amazing video !
Allowed me to create 10 docs for a client that usually takes me 20 minutes each to create.
Thanks so much Jeff 😍
This is exactly what I was missing in my function..... grabbing the link to the created Doc and posting it back to the spreadsheet. Thanks again.
Awesome, glad part of that was helpful to you! Thanks for watching
You just saved myself and a handful of my colleagues hours of work. THANK YOU for this tutorial!!!
Glad I could help!
Excellent tutorial, I really like that you take your time to explain what every line does, really helps a complete beginner to understand the basics.
Thank you for a great tutorial. This is going to save me and my husband so much time with filling out legal documents for our real estate business. I am a complete novice when it comes to coding, but this was very easy to follow and understand. The only thing I had a problem with was an error saying that the document was inaccessible. But, I realized that I had been using a .docx (Microsoft Word) document and that google docs didn’t recognize it within the code, even though it could open the file and edit it. Just thought I’d mention that in case anyone else had the same problem.
is it just me or the 'real estate business' part is tmi
Thank you for the hint about docx, we have tried days to solve this.
At this point, I'm honestly more used to your voice on 0.5x speed haha. Again, thanks so much for this tutorial, it has helped my business a lot to automate some of our systems. I'm officially crowning you as the dad of Google Sheets haha!
That is actually a very good tutorial!
I first watched a crappy one called "Autofill Google Doc Template from Google Sheets | Google Apps Script" from the channel Outright System... The guy just stole your code, without even giving you credit, and pasted it without any explanation! Some people have no shame and are awful educators, really.
But thank YOU for the great explanation and time you took to actually teach us!
Thank you for this resource! This may be a silly question - This is my first attempt at exploring apps script or coding. Can I do this for just one row of data at a time? My spreadsheet is form responses and some of them require a template checklist of next steps for the team, but not all. It's constantly collecting responses. I do have a column that records whether or not a template needs to be generated.
They've shuffled some of the buttons around, but this still works like a charm.
Well done, and good show!
Great video. I have a question though. Your Google app script primarily works for one column and multiple rows in google doc. Can you help us with Google app script where it generates multiple columns and rows table on Google doc using the data that is available on a single row of Google sheet?
This video is the best! you explained it so well. I needed to add a few more pieces to my code. Having gone into this with no idea how to use AppScript did not matter! This video pointed out to me in the right way to ask my questions online and I was able to get everything to work! Thank you Jeff!
Awesome, that is great to hear. Cheers -Jeff
Thank you a million Jeffrey! I had a question. What if an employee has two rows (for example an employee has two different jobs, each with their own separate pay and hours, etc.)? Is there a way to have details from two separate rows populate into a document, separately?
Reply
This was so helpful!! Thank you - I created a merge doc weeks ago and went back to your website to find out about formatting (currency)... and found the answer in you FAQ! Massive help - thank you!
Excellent class, we added an extra menu item and function for that item on the menu. Thank you so much, it was such a pleasure to learning with you.
Your tutorials are excellent, you do a great job of explaining as you go along. I originally used your prior video, "Auto Fill Google Doc from Google Form Using Google Apps Script," but found on occasion it would fail to generate the doc, without explanation (i.e. someone submits a form, it goes to the spreadsheet, but the doc just doesn't happen.) The method you demo here allowed me to set up a manual workaround for those occurrences.
Hey Jeff,
Thanks so much for this !
Out of interest how would i take a cell that has a URL in it and replace a tag in the doc with the image rather than the URL ?
Thank you Jeff for the great tutorial. I had one question, is there a way to create an if statement to read a value in a row to see which google doc template the script should use? I started it with a function with if statements having it read the value in row[x] === "PARTICULAR LANGUAGE" then return the value for the googleDocTemplate = DriveApp.getFileById ('FILE ID'). Sorry if this is a bit confusing. I have multiple templates for different employee variables and want to write the code once versus have multiple codes for each template. I want the script to do the work.
I keep getting the error: Exception: The parameters (String) don't match the method signature for DocumentApp.Body.replaceText. Any idea what may need to be fixed? I have everything plugged in as you do in the video.
Thanks so, so much for this. I'm just dipping my toes into coding and wouldn't have been able to do this sort of thing without your helpful, clear instructions and provision of the template! I made some mistakes in my version of it, but because I was able to rewatch your tutorial, I learned not only what I did wrong, but how to identify where in the code the mistake was made.
Awesome, glad this was a helpful resource. Best of luck -JE
This was incredibly easy to follow! Thank you so much!
I have several cells that have formulas that rely on other cells. If the target cells are blank, it returns a "" or blank. This script creates documents for all of these rows. Using your tutorial, I was able to add a line of script that if the first name was blank it moves on!
Hello, Can you let me know if you've found a solution to your problem?
I did!
function criarpdf () {
const ui = SpreadsheetApp.getUi();
const menu = ui.createMenu('Cria um PDF');
menu.addItem('Cria um PDF, e Envia para um E-mail', 'CriarEEnviarPDFPorEmail');
menu.addToUi();
}
function CriarEEnviarPDFPorEmail () {
const googleDocTemplate = DriveApp.getFileById('1Z1rHTPsl0N65oSx7ILrnzDvEhGCmnoMHInAGB0s6Ffk');
const destinationFolder = DriveApp.getFolderById('1GXbtYSVR5i4LYQWnezY84Hih4liWX4nO');
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data');
const rows = sheet.getDataRange().getValues();
rows.forEach(function(row, index){
if (index === 0) return;
if (row[3] || row[4]) return; // verifica se já há um URL na coluna 4 ou 5
const copy = googleDocTemplate.makeCopy(`${row[1]}, ${row[0]} Employee Details`, destinationFolder);
const doc = DocumentApp.openById(copy.getId());
const body = doc.getBody();
const friendlyDate = new Date(row[3]).toLocaleDateString();
// Faz as substituições usando o objeto keyIndexMap
Object.keys(keyIndexMap).forEach(function(key) {
body.replaceText(`{{${key}}}`, row[keyIndexMap[key]]);
});
doc.saveAndClose();
const email = row[2];
const url = doc.getUrl();
sheet.getRange(index + 1, 4).setValue(url);
var pdf = DriveApp.getFileById(doc.getId()).getAs(MimeType.PDF);
GmailApp.sendEmail(email, "New Employee Details Document", "Please find attached your Employee Details Document",
{attachments: [pdf]});
});
}
thank you! you really put in the effort to explain everything so that beginners like me can understand the reasoning behind each line and therefore customize the script later on.
Thanks for the kind words
Thank you SO MUCH!!! It's so hard to find good tutorials on Google App Script.
Thank you so much for taking the time with these tutorials. Very straight to the point and an easy steps. It's really helping me a lot with my business automations! THANK YOU!
Thanks for watching!!!
You, Are, The, Best! Couple adjustments and it works FLAWLESSLY for my use case!
You just saved a whole lot of time to lots of people including me, brilliant tutorial!
Thank you so much for the video! I have never coded anything before, and I was able to follow your steps and create a huge time-saver for our school's secretary. I appreciated how you explained everything so clearly - it really helped me understand what I was doing and also helped me find mistakes when the errors popped up. :)
Thanks for the kind words! Glad it helped you out and got you started.
Amazing tutorial! You made this so simple to follow and understand. This will save me an unbelievable amount of time creating ICAs. Thank you so much.
Absolutely fantastic video, thank you so much! I just have one question. I have hyperlinks in one column of my spreadsheet, how can I put those in the document? I tried doing it just like your tutorial, but it replaces only the text, not its formatting (i.e. hyperlinks). Is there a workaround?
You Legend, thank you dude!
You're welcome!
I have an issue. rows.forEach --> when I try to execute it says Rows is not defined. The data is not being returned as an Array so I cannot use the .forEach. ? Make sense?
Nevermind -- missed the line defining rows. Thank you so much!!
Thank you so much. Concise, clear and competent instructions! A great contrast to a lot of other help out here. It's helped me sort out a formatting issue for an online form. What I would really love is to email the resulting doc (form submission output file) as a PDF to myself/my colleague. I have not been able to resolve the variable for the output filename... thanks for any help you are able to give.
You can find an example of that here: jeffreyeverhart.com/2015/01/30/tech-tip-google-forms-confirmation-email-with-attachments/
This is brilliant! Any chance you can add the script for when there is a choice of templates to pull on depending on a criteria specified in the form????
Thanks so much for this easy to follow vid. i did have a question as i was hopeing to add on to this. is there a way to have it create and new sub folder in the folder named one of the cells then save the doc it is creating into that? meaning every time it created a new url and doc it is also creating a folder for that doc to go into?
again thanks for the video
Thank you for your instructions. They are very well done. Do you have any options for how to output just the date from a cell in the month/day/year format?
Great video! simpel and to the point. One question, what if I have a chunk of text and tables in my template, and I want to make it appear or dissappear in the output doc based on a clickbox in the sheets docs. How would I do that? I have a template with descriptions for different services, but I need that segment of text/tables only if the service is procured. So I want to have a list of services in sheets, click on the ones the customer procures.
Hey, thanks for the amazing Content! I got a question, i am trying to change the destination folder to 5 diffrent folders depending on the Text that is in cell 1 - so if cell 1 says "John" then i want the File to be created in the FOlder "Documents John" if the Cell says "Lisa" hen i want the File to be created in the Folder "Documents Lisa". i got 5 Names in Total - so 5 Folders in Total. I guess i need to do it with an "if" statement, but i dont get it to work..
This is AMAZING. not only do i understand the what, but I also understand the why. You are a great instructor.
Hi Jeff
Thanks for the tutorial
Is there a way I can prevent regenerating all documents every time I run the generate new form Script and only generate the new document with the recent data entered
or even better if I can specify the certain document I intended to have or If possible only edit the previously generated documents and generate the newly added data rows
Jeff, This tutorial video was great! I know nothing about coding, but I followed your video and it worked! I have one big question - in my sheet at the bottom of each column, I have a SUM function. This SUM function row will be moving further down the sheet as I add more content to the sheet. How can I prevent it from reading this row?? Thank you!
Thank you for the tutorial, really helpful. DO you know if there is a way to create multiple of the same table but in the same doc instead of doing it in new docs? Thanks!
Hi! Great tutorial! I have one question is it possible to use this to fill like 3 different templates depending on conditions from sheet?
Is there a way to write this code so that if you add more information to the sheet you can rerun it only for those that do not have a link for a google doc? Thank you!!!
Thank you so much for sharing this, and for the accurate explanation. It was super usefull to me 3 years after you originally posted it.
Thanks! :)
Hi @jeff first of all thank you for this tutorial...i have a question. I have made this but i have formula for every blank row. When i run the autofill its creating a doc even without any inputs. Is there a way wherein the scirpt will ignore the formula? Thank you so mch!
Hi Jeff! Thank you for this. I'm looking to import "in-line" text into a Google Doc from a Google Sheet? I think this is really a close way to do it. I'm trying to make a report like this: You webpage got X,XXX visits this month and we posted X,XXX pieces of content. This would pull from a Google Sheet with all that information, but would also be able to tell which month it's in so it would update monthly. Could I use this script to do that or would it need to be different? I don't need to creat new documents, just update the one document from multiple already created Spreadsheets. Thanks for the resource!
Hi, thanks for the tutorial! Very helpful and easy to follow.
I have a question: instead of text or dates, I have numbers to transfer from GSheets to GDocs. With your (wonderful) script I get numbers without format. For example I have a number in GSheets formatted as 170,000 and in GDocs it comes out as 170000. Same happens for percentanges (from 25.1% to 0.251) Is there a way to respect the original formatting? Thank you!
Hi Jeff, First of all, Thank you very much for all of this. So it works well except for the name of the document. If I write "const copy = googleDocTemplate.makeCopy('${row[1]},${row[2]} Invitation Letter', destinationFolder);", documents created are called " {row[1]},{row[2]} Invitation Letter. Instead of the name of the invitee. Do you see any error in above syntax? Thanks
Based on what I can see and the behavior you describe, I would double check that there are backticks surrounding the string that gets passed into the makeCopy function, not single or double quotes
@@jeffeverhart thank you so much for this tutorial, the backtick worked
Great Video! i did have a question. I want to implement this to an inventory / equipment maintenance system. Will this auto update the Google Docs with information when its changed in the spreadsheet? if not, is there a way to do so?
Hello there, thanks for the amazing tutorial, I just had a question is there something similar for google slides? as in to autogenerate google slides!
Hi Jeff,
Thanks for this video. Super helpful. I have a question. I would also like to pull data from a second sheet on the spreadsheet. I've been working on it over the weekend and still can't seem to figure it out. Would you be able to assist?
Thank you for the great tutorial. I was wondering if it is possible to have the filled Docs merge into a single Doc, rather a separate doc for each employee. Thanks
Hi, great video. One question is there a way to input data from google sheet in a bunch of EXISTING google docs?
Basically, I have around 100 google docs (10 pages each). Just one of those pages needs to be changed o a monthly basis.
Thanks agin! Question: Say I have two documents to be auto-filled depending on from which sheet the data comes (both sheets are within the same overall spreadsheet file). I.e., if the data is in Sheet A, it fills "Contract" doc, and if it's in Sheet B, it fills "Offer" doc. I assume the script edit should take place somewhere around the " .getSheetByName('Contract')" line, where I'd add something like, " .getSheetByName('Offer')"? Apart from the docs being titled differently, they're using the exact same tags. Any advice?
you could create two groups of variables 'googleDocTemplate' 'sheet' and 'rows' for each sheet (templateContract, sheetContract, rowsContract, templateOffer, sheetOffer, rowsOffer) and then run the function inside a forEach loop once for each group
Hi Jeff,
I have run into an issue with the body.replaceText code. It works perfectly to a certain point and then stops for the middle of the document, then finishes correctly for the end of the document. I've double checked that I wrote it the same and I really have no background of writing code , this is my first attempt! Any tips?
I have a column of times but when I have the script it pumps out "Sat Dec 30 1899 12:30:00 GMT+0000 (Greenwich Mean Time)". The only value in the cell was the time in automatic format. These times are submitted via the google form's time entry. Is there any script to make it display time in a user friendly way, and ignores all this extra fluff that never showed up in the google sheet in the first place.
Hi Jeff, amazing video and great teaching skills. I was wondering if instead of creating google docs we are able to create PDFs named with a field and saved them to a google shared drive?
Hi Jeff. Thank you for your video - I successfully used it to auto fill a doc saving so much time. I have a question for you: is it possible to add another function for the same spreadsheet? I wrote another that is similar in the hopes of making a separate doc. I checked new project, entered the coding and it saved under “my projects”. I did get an error message and I’m wondering if it’s because I’m working off the same spreadsheet? I can’t seem to figure out whether I can use it w/the same spreadsheet and if so, how?
Thanks Jeff, it will help me a lot.
But if I wanted to send this document file to my webmail in a word format? It can be done?
Amazing, thank you so much. Just wondering how I can get the script to read 'all sheets' and create pdf's instead of docs from the data?
sir did u find solution? if yes then please let me know
Hi Jeff may I ask, is this code usable if I plan to use a Google spreadsheet as a template instead of a Google docs template?
This is amazing!! Thank a million. just one question, how can i have the document URL to be a 'hyperlink' instead of the long url?
Hi there. Love this video. As others have said, I am hoping to save some time in creating documents in my role as a Parent-Teacher Group chairperson. Anyway, I am getting an error on the final step "ReferenceError: setValue is not defined". This is my 1st go with coding, but I have checked the code several times. A quick search did bring up some results about their being a problem with the setValue function in newer versions of Apps Script. Is this the case? If so, any help on how to fix it?
Hi, I'm having the same issue. The problem is with row 16. Did you ever get the fix?
Hello! Thanks! Your video helped a lot. I have 1 question! Why decimal coma's becomes decimal dot's after running script? In my sheets i use decimal coma's.
Hi Jeff, this is a fantastic tutorial. It worked flawlessly for me. Thank you. I do have an issue that is not covered. A few of my data area hyperlinks. Is there a why to copy the 'format' to docs, so that they appear as a link in the new document that is being generated? Sorry if it has already beeing asked, could not find it.
Hi Gisele! Facing the same situation here! Did you manage to find a solution? Thankssss!
Thanks for this! Is there a way for the docs to be updated if data from the sheet is changed?
This is really useful! Is there a way to automatically link the new pages you create back to the parent spreadsheet, so that certain columns can be filled in when the sheet for a particular row is modified?
Also, when I pull the date column into the document, I am getting the hour/minute time as well as what time zone it is in. I do not want this and only want the date format. do you happen to know what might be going on here?
Hi Jeff - My date data when added to the google Doc is always showing 1 day behind the data which is in the Goggle Sheet. Is there a quick fix for this? I am UK based
Your tutorials are great, a big thank You! Is it possible to automatically make googledocs from a "mastersheet" which updates whenever some new stuff appears in the mastersheet? I would then have, say about 40 docs, and at the same time add a spedific information to every doc without making a new copy. My mastersheet works with outrange and the doc should be stable, so it can be shared with the person whos information it contains.
The issue with keeping a Doc and a sheet data store in "sync" is that once we remove the replacement tags, it's difficult to tell where to update with the new text. The replacement tags are guaranteed to be unique, whereas as populated text does not have that same guarantee
Our finance department has a Google Doc Template for adding new items to our inventory system and I was wondering if there was a way to populate a table in the Template with data from a spreadsheet. Using replaceText I was able to populate the first row but I am not able to get the data in the 2nd row of the spreadsheet to populate in the 2nd row of the table in the Doc template, no mater what I try the first row is all that is returned. Please any help is greatly appreciated.
Still lots of help until now tks so much !!!
Walking through this very slowly thank you! How do you only get a range of the data, instead of the whole sheet?
tried googling this for you and found a stackoverflow article, search this:
differences-between-getrange-getdatarange-and-getactiverange
here's part of the answer:
"
getRange() has multiple implementations.
Sheet.getRange(row, column)
Sheet.getRange(row, column, numRows)
Sheet.getRange(row, column, numRows, numColumns)
Sheet.getRange(a1notation)
"
So you'd have to play around with the getRange parameters in your code, to select a part of the range instead of it all.
Hi, on the other hand, I was just checking one more aspect of it, is there a way to copy content of one document into another one with all the formatting.
like one document will act as a template and then on the click of the custom menu like yours, we will replace all the contents from the other document.
Both the document will have statis/constant URL, document ID. so that I can use the same link in the automation ?
is there a way .. request you to please help here...
I'm curious about is whether or not we actually need to use {to create a tag. Could I just use the first and last name? That might sound like a weird question but my goal is to update there document instead of replacing it. Did a lot of reasons why a person might want to do this. In my case I create Google. Invoices that I send out the clients who then have to share that same URL with 20 different people in order to get me paid. It has happened many times wherever create a new document after an update and I would get paid the wrong amount because the most current URL did not make it up the chain of command.
From our contacts, here's what I have set up. When I book a client the data comes through form an invoice is created. Upon form submission their data along with the invoice is copied to a second sheet. My thinking is if I update the form response sheet, the data on the second spreadsheet should still match everything on the invoice. I want be able to set the data range on the second sheet as a variable for my tags. If I run my update invoice script, my hope is the script will see the data as the tags on the document we could get by the URL, and then replace those tags with what is currently on the booking for response sheet. To make this easier, we can do one row at a time. I don't need to Loop through the entire. I know I can type in a specific number and save that as a variable in the script, so it would only go through that one row.
Is what I'm saying making any sense? Does this sound feasible?
I cannot get the Menu item to populate- Google sheets updated in fall 2021 so under tools there is not an option for Script Editor, I have typed the code in Apps Script but cannot get it to run with my spreadsheet. Any advice?
Thank you very much, Jeff. Your video helps a lot. Is there a way to copy 'named range' from sheets to docs?
Great video and instructions. One of the fields I want to populate is in the footer, is this possible ?
Thanks, Very Helpfull. How ta make the function run Automatic when adding new data in sheet?
Best code I found to perform this function! Elegant, to the point and handles spreadsheets with hundreds of rows (the known google bug issue notwithstanding). Thank you so much :D
Thanks for the kind words and thanks for watching _ JE
Jeff, thanks so much for the tutorial. Each step is explained extremely well. I just used it to automate creating invoices for my freelance work.
Great use of the script, best of luck - JE
How would I modify the code to automate filling out a different sheet, rather than a doc?
I've got certain cells with {{startDate}} (and other tags in other cells as well), and I want the script to work the same way, but within that spreadsheet.
body.replaceText unfortunately doesn't work on a spreadsheet, as far as I can figure out.
It is more complex but you can use this developers.google.com/apps-script/reference/spreadsheet/sheet#createtextfinderfindtext
@@jeffeverhart Thanks for your help! I'm teaching myself from the ground up, and your tutorial has been so helpful. The most challenging part, like with this sheet vs. doc problem, is that I don't know what I don't know yet!
hey Jeff, thanks for the video, i have a question, how to also add a image from googlesheet to doc using this same code
Hi Jeff- Was able to follow this video and got things working nicely. Once my form is submitted and the Google Doc is created, is there a way to automatically email the DOC out to a list of people I specify? Thanks in advance!
Thank you very much for this! it was very helpful! just one question. Is it possible to add an image to the template from the google sheet? and how would that be??
That's the best tutorial out there. Thank you so much for all the explications, you're a really good teacher. I wish I could give that video 100 likes!
Hi there. This is great. However, even though I get the documents generated, the fields though in the google docs are not populated with the data. I would appreciate any help. Thanks
This is somethig I need for my classroom to generate a home detention doc that generates a form for parents re student last first period date/time, and behavior from a pull down list (will this work if the cell is a list of behaviors?). Also how could one create a script that will generate the same information twice on one page so there can be an English and Spanish on one doc one page or two page to be printed 2-sided.
Thanks so much for the tutorial, it was really helpful. I have a question; How can I select a different template depending on the value of a cell? For example, if the value is equal to XX, a template is selected, while if the cell is equal to YY, a different one is chosen.
Add an additional if/else statement before you make a copy. Use one template in the first block, then the second template in the second block. There are more advanced design patterns if you really want to get into it, but for one-off scripts, just copy and paste, then change the relevant ids.
Did you ever figure this out? I'm in the same boat
@@brandonwilson1600 Don't you also have to swap out the "const googleDocTemplate = DriveApp.getFileById(" line right after the Create new GoogleDoc function? What makes it search among a few different templates?
@@pmont72782 Yes, it would be exactly that. Each branch of the If/elseif/else would have it's own version of that line with a different ID, and potentially different code leading to how to fill out the cells, depending on how complicated the resulting structure ends up being.
Or you could set it up as
"const googleDocTemplate1 = DriveApp.getFileById("
"const googleDocTemplate2 = DriveApp.getFileById("
...
or
"const googleDocTemplatePurchasing = DriveApp.getFileById("
"const googleDocTemplateInvoicing = DriveApp.getFileById("
...
etc.
and then just use the appropriate template file in the appropriate branch.