I have wanted to do this for over a year now and someone on twitter referred me here. I am really grateful for the approach you too, that we could run it ever so often to see what the script does up to this point. I needed quite some time to finish it, but I have procrastinated doing exactly what you are showing for more than a year. Hence again: Thank You!!!
Wow, this is awesome. We are using Google Suite at work now, and several months back I told someone that there's no way to do a mail merge like function from Google Sheets to Docs. I am so glad I found this; now I can help them out, since they need to get out of their MS Office environment. Thank you!!
I'm following you for months now. Your channel helped me a lot, but this. This is just exactly what I was struggling with since last month. Thanks for your content.
Awesome tutorial. Love how you walk through your thinking process and the various iterations. Plus, I needed to do exactly this (without an add-one). Thanks!
Thank you so much! This worked perfectly for me to set up Christmas card envelopes. I love that you left in the "mistakes" so I could really learn how this was put together. So helpful!
This video helped me think differently about how to process donation statements posted in Google Sheets. Noting that the paragraph type is a concern I wondered about how images or drawings inserted into the template document would copy to the body of the document to be printed. I found this is a problem because images/drawings show as type "PARAGRAPH", and the results of the copy are not exactly as that shown in the template document. In my tests the template document has headers and footers with images (such as a logo), which I simply add to the document to be printed since they're static and used for each statement. I wonder if this would be a good addition to the mail merge tutorial, and a challenge for our excellent instructor? Would other subscribers like to see this?
yes thank you very much this is also what i needed. i am starting to understand what you are doing after 6 weeks of following your channel. I am building a database for the charity i work for and watching your channel and learning what to do in GS is very helpful.
You did an incredible job walking through that very complicated process. I have limited-to-no experience with Javascript, yet I was able to work through this tutorial and successfully create the mail merge I wanted for our fundraiser in Google Drive. I actually appreciated that you misspelled some of the titles because it helped me differentiate between what was a title I gave something and which was a command that I couldn't rename. (Again no experience). I even was able to successfully add an image to my final product with this tutorial base. Ready to laugh? My only problem: I refreshed my Google Sheet (with my source code editor open) because it is connected to a Google Form Survey. When I did, my editor closed, and I'm flummoxed for the moment on how to reopen the editor. Attempts to open the same way as the tutorial in the beginning gives me "Sorry" messages. Told you, no experience! I'll figure it out after awhile. It's saved and named somewhere. Great job! Excellent teacher.
Thank you! Super helpful. Two questions I hope you can help with: 1) If I have images in a cell in a sheet and want to merge them into the doc, how would I go about doing this? 2) I need to format dates that get merged into the doc from the spreadsheet. Any suggestions on how to do this? Thanks again!
Your videos really improve my productivity and makes my life easier. I'm curious, how do you know so much about this? This is not a very popular topic, I wasn't even sure where to find the documentation
You can actually find all these methods in Google Scripts Documentation (click on the help button on GS UI). I think you might find it surprising because you come from the spreadsheets world or something like that, but it's actually quite simple and popular to create these types of functions, just search for Google Scripts in StackOverflow
What an excellent video! I had to stop and go back several times but this was extremely thorough! I appreciate the fact that you were testing the functions throughout to be sure that it was working properly. I'm not tech savvy at all but this really helped so much. Thanks!!
Thank you for the tutorial, it was very helpful. I have a question. If the item I am trying to merge is a QR code, what would elType be and how would you code that? The QR codes would be generated in the spreadsheet using the google API. Thanks.
Thank you for being so helpful; the explanation is straightforward to understand. If I want to send Bulk emails with this pre-made template, how do I do that? Thank you
Hi. Works perfect, very explicit method. Q: I have inserted an image in a cell, then, how could i do If i want that image in my final document ? Regards.
Your tutorial is awesome. Not only is clear but you also properly designed the steps to show its purpose, in a flow of show-problem and later show-solution. Now is there any technique for automatically populate the 'final doc' whenever new row got added to the 'spreadsheet' without having to manually run the scipt? Cheers
this is amazing...thank you very much for sharing! Is there a way to have it create a new doc each time for each row of data rather than page break on the same doc?
Excellent video! Very informative and easy to follow by someone who is learning about Apps Scripts. I found your video while looking for an example of how to merge a unique image into each document (similar to what someone might do to batch create employee ID badges or personalized labels). Do you have a video that explains merging unique images or could you point me to a good example and or documentation?
Thank you SO MUCH for your excellent video! I built a mail merge (based on your video) two years ago and it has been working spectacularly .... until yesterday. Now (suddenly) it is throwing an error message: " Error Service Documents failed while accessing document with id xxxxxxx..... " It does not indicate a line or any other information. I have tried running it from a different user - same. I have tried using a different doc with a different id - same. I do not understand why [suddenly] it would not work. Please advise if possible! Again, THANK YOU!!
This is amazing....! Thank you, thank you...! I am a bit of a spelling nut so that did get me a little, but the way you explain concepts etc and the added pieces about how to see what arguments are available are *fantastic*. I'm hooked..
Thank you for this excellent tutorial. You are a very patient teacher. Being able to stop and start umpteen times to see what you actually did was great. I managed to go, in one day, from being a complete Google Spreadsheets novice to the Team Geek able to turn shedloads of data into usable reports. One glitch I couldn't manage to solve: when putting the date '26/10/2021' from a spreadsheet via a template into a report, it comes out as 'Tue Oct 26 2021 00:00:00 GMT+0100 (British Summer Time)'. I've done all the obvious stuff like format the cells in the spreadsheet but it still comes over with the hours and all the unnecessary information. Is there something I should know or another tutorial I need to look at to sort this? Thanks.
It definitely is easier for someone who is used to doing it and someone who is fairly tech literate, but I have a mail merge that needs to be completed on a regular basis by some people who aren't terribly computer savvy. It was more work for me to set it up initially, but now for all the other users, they click a button to run the script and the merge is automatically completed for them. Way easier for them to use than for me teaching them how to do a Word merge.
Great Video!! How would I translate that into Labels? I get the {name}, Etc, Etc.... but how would I format that into the final spaced as labels? For instance, could I upload a label template (word) into google drive and use this to translate INTO the word doc??
I had the same questions and after a lot of tinkering, got it to work. You need to append a Table to the final doc first and pass it to the mailMerge function. Then, inside the mailMerge function (before the forEach statement), you append a row to the table and set the row's minimum height to the height of your label. You also append a cell to the row. Then, inside the forEach statement, append the mail merge data (from this tutorial) to the cell. In my case, I wanted two columns of labels, so in the final doc, I manually set it to be two columns. I couldn't find a way do do that with Google Scripts, but fortunately, formatting it to two columns works just great. The table cell fills the entire width of the column (or document if you don't make columns). Finally, because a table has to have a paragraph before it, it seems to mess up the first column of labels on the first page. I was able to fix this by using google scripts to set the final document body font to size 1 and the document spacing to .06. You can't tell the paragraph is there and my labels work great! Lifesaver for my own project and this tutorial got me started! Thanks!
Yes! Watch the video right through. There's a line of code he adds to create a page break in the function, so you get a separate document for each line of the spreadsheet.
Is there a way to format the text we are merging to an email? Example, I want a word to be blue and bold. Is there a way to add an attachment in the merge to email?
@@guruslakkimar12 I have created a copy function which works: //copy doc function copyContentDoc(filename=null) { var sourceDoc = DocumentApp.getActiveDocument().getBody(); if(filename === null){ filename = 'Kopie van_'+DocumentApp.getActiveDocument().getName(); } var targetDoc = DocumentApp.create(filename); var totalElements = sourceDoc.getNumChildren(); for( var j = 0; j < totalElements; ++j ) { var targetBody = targetDoc.getBody(); var element = sourceDoc.getChild(j).copy(); var type = element.getType(); var attributes = element.getAttributes(); if( type == DocumentApp.ElementType.PARAGRAPH ){ targetBody.appendParagraph(element); } else if( type == DocumentApp.ElementType.TABLE){ targetBody.appendTable(element).setAttributes(attributes); } else if( type == DocumentApp.ElementType.LIST_ITEM){ targetBody.appendListItem(element).setAttributes(attributes); } else if( type == DocumentApp.ElementType.PAGE_BREAK){ targetBody.appendPageBreak(element).setAttributes(attributes); } else if( type == DocumentApp.ElementType.HEADER_SECTION){ targetDoc.addHeader().setText(element).setAttributes(attributes); } else if( type == DocumentApp.ElementType.FOOTER_SECTION){ targetDoc.addFooter().setText(element).setAttributes(attributes); } // ...add other conditions of DocumentApp.ElementType } targetDoc.saveAndClose(); return true; }
Hello, thanks for the great lessons! Do you have a tutorial how to create invoices generated with data from a Google Sheet and based on Google Docs template, and after the generation in PDF to send the invoices directly to the customers?
Thank for all. great video. but can we do it with some image in the sheet base? maybe in one column we have image that we want to changed in the docs so i can send in a email with the fuction replace in append paragraph or replace with hmtl ?
As soon as I get to the step on line 13 to appendParagraph, I receive "Service unavailable: Documents (line 13, file "Code")" Everything works up to that point.
Really great learning for a beginner like me. I could create a merged email as per my requirement, but I could not send it to the recipient. IF you can help me with that.
Thanks so much for this useful demo. It worked brilliantly! Is there a way to 'getTables' along with their contents? My Template document contains a table with a complex layout. Is that hard to handle?
That helps us create a Google Doc with separate pages. Can you finish it out and tell us how to automate sending each separate page as a separate email?
Can you teach me/ us how to make a copy of a template file in google docs then save it as PDF file in a specific folder in google drive. Thank you so much
Cool video! But i wonder. Is it possible that if a certain cell has value X then copy cel [0][1][2][3][4] If it has value Y then copy cell [10], [11],[12],[13] and if cell has value Z then copy cells [5],[6],[7],[8],[9]
This is great, wluld you please help us take this further and hook this to email script so it sends out from a list of associated email addresses in the spreadsheet? Thanks Alex
Great tutorial. Is there a way to select from multiple of templates based on the contents of a cell? for example if a cell says "x" use template "x", but if a cell says "y" use template "y" etc?
Very didactic explanation, which should get it clear for beginners, but wouldn't it be better to just use Object.keys method to create a pattern finder rather than parse them manually?
my template has a letter on the 1st page only (the whole template has 2 pages), ive replicated your tutorial and it works but the header is not copied :( also ive noticed that for the numbered lists (instead of bullet points), my problem is the number lists continues from one copy to the next so my last copy has numbered lists starting from 50. instead of 1.
it's a great job. I have some problem in applying number and table. how can i restrat the number for each page break since this video use list_item. then how can i apply table in the final document? thanks
Thank you for the video! I am running into an error (TypeError: Cannot call method "forEach" of undefined. (line 25, file "Code")) Any advice as to how to fix this? Thanks!
HELP?! Everything worked until the final run. I am getting this message and I am not sure what to do with it. Thank you! 10:55:14 AM Error Too many changes applied before saving document. Please save changes in smaller batches using Document.saveAndClose(), then reopen the document with Document.openById().
Hi This is very helpful for collecting applications forms,I have a question though. One of my columns is Image link from Google drive. Is it possible to make that image show on google doc?
How would you place an item where the table has some empty cells, e.g. if I am making a script from a cell where I have sales document, with the source worksheet i have columns named after a product and rows with different branches, some branches might not sell certain items from the table. how can I make the script to add on the mail merge with only the products that has sales value on it
You're a genius! Thank you for posting. Do you know a workaround for this error: "Please save changes in smaller batches using Document.saveAndClose(), then reopen the document with Document.openById()." I have over a 1,000 rows of data for the mail merge.
Great!! works perfect., it is possible set filters? I mean, if I need merge only the information with "Kansas City" value in the column D. Is that possible?. I thought that with only setting filters to the data it will work, but sadly doesn't :(
It's a nice classes.. I wanna learn one thing, in the nail merge how to print the sheets instead of creating the documents.. in the MS office it is available, but in Google sheet we have this limitations. Pls kindly assist
This is very interesting but can we make it more user friendly where a person can submit a list of names and address and will just fill out a template.
Hi I have found this tutorial very informative and have used and modified the script to suit my needs and it has helped a lot. I have run into a little problem recently, the script has been working for approximately 6 months and all of a sudden I get a "Service unavailable: Documents" message for the "docFinal.getBody().appendParagraph(" portion of the code. Does anyone know of a solution for this? Any help would be appreciated. Thanks
Thank you for your response, I pasted the code below: function Mail() {
var docTempleteId = "1MnkmuN...........O90"; var docFinalId = "1tFCT3On.................LUI1_s"; var wsId = "12vCn1U............................36Pc";
var docTemplete = DocumentApp.openById(docTempleteId); var docFinal = DocumentApp.openById(docFinalId) var ws = SpreadsheetApp.openById(wsId).getSheetByName("Letters");
var data = ws.getRange(4, 1, ws.getLastRow()-1, 27).getValues();
var templateParagrapths = docTemplete.getBody().getParagraphs();
Hi, I think the problem was a jpeg in the doctemplate. I changed this and added code to insert the jpeg after the merge has taken place, this seems to have sorted the problem out.
Can you tell me what the inside of { } is known as. I want my responses to be blue but do not know how to reference them in the code to ensure that all entries are blue instead of black (blue is for legal, black is for the dead, red is for the living and purple is Royalty)
Hi Guru and beyond of Apps Script guy! Sir how do we replace links with pictures and let the google do the backend job for pasting pictures to google docs
When I copy a multi-level list (LIST_ITEM's) and would like to add some level in the final doc, the formatting is lost. How can I copy a list AND keep the format in the final doc when the user edits the list?
It will be great to have a video on format with the query function (like how to query results and format as currency, etc). If you agree, up vote this comment if you agree so the author notices it
I am trying to do the same but with a background image in the template Doc. It throws the error service unavilable. How can i merge docs with background images?
I'm having all kinds of problems with this...it says "Cannot find function forEach in object Document. (line 27, file "Code"). HELP! I'm following this as closely as I can.
Thanks. I lost MS office during an Apple upgrade. This is a big help for getting the Christmas mail out!
I have wanted to do this for over a year now and someone on twitter referred me here. I am really grateful for the approach you too, that we could run it ever so often to see what the script does up to this point. I needed quite some time to finish it, but I have procrastinated doing exactly what you are showing for more than a year. Hence again: Thank You!!!
A five star tutorial. And, for non-english people very clear speech. And it have a lot of tips to learn coding in Google Apps
Wow, this is awesome. We are using Google Suite at work now, and several months back I told someone that there's no way to do a mail merge like function from Google Sheets to Docs. I am so glad I found this; now I can help them out, since they need to get out of their MS Office environment. Thank you!!
I'm following you for months now. Your channel helped me a lot, but this. This is just exactly what I was struggling with since last month. Thanks for your content.
Awesome tutorial. Love how you walk through your thinking process and the various iterations. Plus, I needed to do exactly this (without an add-one). Thanks!
this was an awesome video. I've dabbled in AppScript but I learned so much in this video just by watching your approach to this problem. Thank You!
I needed to do something very similar and this was an AMAZING solution. Very well explained and easy to follow along. Thank you!
Thank you so much! This worked perfectly for me to set up Christmas card envelopes. I love that you left in the "mistakes" so I could really learn how this was put together. So helpful!
This video helped me think differently about how to process donation statements posted in Google Sheets. Noting that the paragraph type is a concern I wondered about how images or drawings inserted into the template document would copy to the body of the document to be printed. I found this is a problem because images/drawings show as type "PARAGRAPH", and the results of the copy are not exactly as that shown in the template document. In my tests the template document has headers and footers with images (such as a logo), which I simply add to the document to be printed since they're static and used for each statement. I wonder if this would be a good addition to the mail merge tutorial, and a challenge for our excellent instructor? Would other subscribers like to see this?
thank you so much! this helped me not have to write out all of my christmas card addresses!
yes thank you very much this is also what i needed. i am starting to understand what you are doing after 6 weeks of following your channel. I am building a database for the charity i work for and watching your channel and learning what to do in GS is very helpful.
A very ingenious way to pass google spreadsheet data to a document. Thank you because it motivates me to study more in depth the topic of Scripts.
This worked well for me. Not for non-programmers, and my doc was simple.
You did an incredible job walking through that very complicated process. I have limited-to-no experience with Javascript, yet I was able to work through this tutorial and successfully create the mail merge I wanted for our fundraiser in Google Drive. I actually appreciated that you misspelled some of the titles because it helped me differentiate between what was a title I gave something and which was a command that I couldn't rename. (Again no experience). I even was able to successfully add an image to my final product with this tutorial base. Ready to laugh? My only problem: I refreshed my Google Sheet (with my source code editor open) because it is connected to a Google Form Survey. When I did, my editor closed, and I'm flummoxed for the moment on how to reopen the editor. Attempts to open the same way as the tutorial in the beginning gives me "Sorry" messages. Told you, no experience! I'll figure it out after awhile. It's saved and named somewhere. Great job! Excellent teacher.
script.google.com/home
Thank you! Super helpful.
Two questions I hope you can help with:
1) If I have images in a cell in a sheet and want to merge them into the doc, how would I go about doing this?
2) I need to format dates that get merged into the doc from the spreadsheet. Any suggestions on how to do this?
Thanks again!
Your videos really improve my productivity and makes my life easier. I'm curious, how do you know so much about this? This is not a very popular topic, I wasn't even sure where to find the documentation
You can actually find all these methods in Google Scripts Documentation (click on the help button on GS UI).
I think you might find it surprising because you come from the spreadsheets world or something like that, but it's actually quite simple and popular to create these types of functions, just search for Google Scripts in StackOverflow
same....
@@rafaelwendel1400 please more explain . tanks
What an excellent video! I had to stop and go back several times but this was extremely thorough! I appreciate the fact that you were testing the functions throughout to be sure that it was working properly. I'm not tech savvy at all but this really helped so much. Thanks!!
The tutorial was great and it can be followed easily, thank you for making it!
This is such a good instructional, thank you. Do you have a version which merges to Slides? Trying to adapt but not getting very far!
Awesome ! Thanks a lot ! All positive thoughts about your work I would like to share has allready been written below...
Hi, thank you for this great video.. You basically saved me from a huge headache
This was a very great video!! Took some time but I mastered it and saved my company some money!!!
Thank you for the tutorial, it was very helpful. I have a question. If the item I am trying to merge is a QR code, what would elType be and how would you code that? The QR codes would be generated in the spreadsheet using the google API. Thanks.
Great tutorial - I was able free up time spent on a redundant task. Thanks!
thank you very much, sir! your tutorial made it possible i can generate my 7d2d mod almost automatic
WITHOUT TYPOS!!! hell, yeah! ^^
Thank you for being so helpful; the explanation is straightforward to understand. If I want to send Bulk emails with this pre-made template, how do I do that? Thank you
Hi. Works perfect, very explicit method. Q: I have inserted an image in a cell, then, how could i do If i want that image in my final document ? Regards.
Have you figured it out?
This was awesome, thanks! How do I carry over a table, and could I instead of doing a page break do separate PDFs?
Your tutorial is awesome. Not only is clear but you also properly designed the steps to show its purpose, in a flow of show-problem and later show-solution.
Now is there any technique for automatically populate the 'final doc' whenever new row got added to the 'spreadsheet' without having to manually run the scipt? Cheers
Dude, this is amazing and so helpful. You're a champ!
this is amazing...thank you very much for sharing! Is there a way to have it create a new doc each time for each row of data rather than page break on the same doc?
Thanks for this very helpful tutorial. Works beautifully for my application!
love your video. Would you like to covered how to mail merge a table in template?
Your're the beat.
Thanks from Montréal
Excellent video! Very informative and easy to follow by someone who is learning about Apps Scripts. I found your video while looking for an example of how to merge a unique image into each document (similar to what someone might do to batch create employee ID badges or personalized labels). Do you have a video that explains merging unique images or could you point me to a good example and or documentation?
Thank you SO MUCH for your excellent video! I built a mail merge (based on your video) two years ago and it has been working spectacularly .... until yesterday. Now (suddenly) it is throwing an error message: " Error
Service Documents failed while accessing document with id xxxxxxx..... " It does not indicate a line or any other information. I have tried running it from a different user - same. I have tried using a different doc with a different id - same. I do not understand why [suddenly] it would not work. Please advise if possible!
Again, THANK YOU!!
have you found a solution?
superb! just what I was looking for! Thank you
I FREAKING LOVE YOU!!! THANK YOU!
Your videos are always great, and I use them extensively. How do you create individual Emails with the appropriate attachment for each recipient?
This is amazing....! Thank you, thank you...! I am a bit of a spelling nut so that did get me a little, but the way you explain concepts etc and the added pieces about how to see what arguments are available are *fantastic*. I'm hooked..
Thank you for this excellent tutorial. You are a very patient teacher. Being able to stop and start umpteen times to see what you actually did was great. I managed to go, in one day, from being a complete Google Spreadsheets novice to the Team Geek able to turn shedloads of data into usable reports.
One glitch I couldn't manage to solve: when putting the date '26/10/2021' from a spreadsheet via a template into a report, it comes out as 'Tue Oct 26 2021 00:00:00 GMT+0100 (British Summer Time)'. I've done all the obvious stuff like format the cells in the spreadsheet but it still comes over with the hours and all the unnecessary information.
Is there something I should know or another tutorial I need to look at to sort this? Thanks.
Really enjoyed the tutorial; Help me tremedously
This is a great sales tool for Microsoft Office. I remember their process so much easier.
It definitely is easier for someone who is used to doing it and someone who is fairly tech literate, but I have a mail merge that needs to be completed on a regular basis by some people who aren't terribly computer savvy. It was more work for me to set it up initially, but now for all the other users, they click a button to run the script and the merge is automatically completed for them. Way easier for them to use than for me teaching them how to do a Word merge.
Excellent video.. But sir.. Please use picture in mailmarge .... Please sir
That's a very helpful vid, thanks a lot!
Great Video!! How would I translate that into Labels? I get the {name}, Etc, Etc.... but how would I format that into the final spaced as labels? For instance, could I upload a label template (word) into google drive and use this to translate INTO the word doc??
I had the same questions and after a lot of tinkering, got it to work. You need to append a Table to the final doc first and pass it to the mailMerge function. Then, inside the mailMerge function (before the forEach statement), you append a row to the table and set the row's minimum height to the height of your label. You also append a cell to the row. Then, inside the forEach statement, append the mail merge data (from this tutorial) to the cell. In my case, I wanted two columns of labels, so in the final doc, I manually set it to be two columns. I couldn't find a way do do that with Google Scripts, but fortunately, formatting it to two columns works just great. The table cell fills the entire width of the column (or document if you don't make columns). Finally, because a table has to have a paragraph before it, it seems to mess up the first column of labels on the first page. I was able to fix this by using google scripts to set the final document body font to size 1 and the document spacing to .06. You can't tell the paragraph is there and my labels work great! Lifesaver for my own project and this tutorial got me started! Thanks!
This is great, I just have to ask if it is possible to get a separate final document for each line in the spreadsheet. Thanks
Yes! Watch the video right through. There's a line of code he adds to create a page break in the function, so you get a separate document for each line of the spreadsheet.
This is an amazing video. Keen to check out some more of your videos! Cheers. :D
Thank you very much for the video, it help me a lot for my work.
Glad to hear that!
Is there a way to format the text we are merging to an email? Example, I want a word to be blue and bold. Is there a way to add an attachment in the merge to email?
why error when I add listittem
Exception: The parameters (DocumentApp.ListItem) don't match the method signature for DocumentApp.Body.appendParagraph.
Yes - i am facing the same issue - did you rectification ! any solutions available ?
@@guruslakkimar12
I have created a copy function which works:
//copy doc
function copyContentDoc(filename=null) {
var sourceDoc = DocumentApp.getActiveDocument().getBody();
if(filename === null){
filename = 'Kopie van_'+DocumentApp.getActiveDocument().getName();
}
var targetDoc = DocumentApp.create(filename);
var totalElements = sourceDoc.getNumChildren();
for( var j = 0; j < totalElements; ++j ) {
var targetBody = targetDoc.getBody();
var element = sourceDoc.getChild(j).copy();
var type = element.getType();
var attributes = element.getAttributes();
if( type == DocumentApp.ElementType.PARAGRAPH ){
targetBody.appendParagraph(element);
}
else if( type == DocumentApp.ElementType.TABLE){
targetBody.appendTable(element).setAttributes(attributes);
}
else if( type == DocumentApp.ElementType.LIST_ITEM){
targetBody.appendListItem(element).setAttributes(attributes);
}
else if( type == DocumentApp.ElementType.PAGE_BREAK){
targetBody.appendPageBreak(element).setAttributes(attributes);
}
else if( type == DocumentApp.ElementType.HEADER_SECTION){
targetDoc.addHeader().setText(element).setAttributes(attributes);
}
else if( type == DocumentApp.ElementType.FOOTER_SECTION){
targetDoc.addFooter().setText(element).setAttributes(attributes);
}
// ...add other conditions of DocumentApp.ElementType
}
targetDoc.saveAndClose();
return true;
}
Hello, thanks for the great lessons! Do you have a tutorial how to create invoices generated with data from a Google Sheet and based on Google Docs template, and after the generation in PDF to send the invoices directly to the customers?
I am sorry. Can i ask how to do this if the template is tables in google docs?
Thank for all. great video. but can we do it with some image in the sheet base? maybe in one column we have image that we want to changed in the docs so i can send in a email with the fuction replace in append paragraph or replace with hmtl ?
Hi I enjoyed ur vids, but could you pls show the final result first how it looks like in ur first 1 minute, and in what scenario we'll need it... thx
As soon as I get to the step on line 13 to appendParagraph, I receive "Service unavailable: Documents (line 13, file "Code")" Everything works up to that point.
I had same issue. I removed my image - ran it - then put image back.
So helpful, thank you for putting out this content!
Thank you so much for this
Really great learning for a beginner like me.
I could create a merged email as per my requirement, but I could not send it to the recipient. IF you can help me with that.
If you want to send emails you should be using HTML not Google Docs.
Here is an example ruclips.net/video/h2z13YE3kJg/видео.html
Thanks so much for this useful demo. It worked brilliantly! Is there a way to 'getTables' along with their contents? My Template document contains a table with a complex layout. Is that hard to handle?
Great, Thanks for the tutorial.. It's helps a lot.
That helps us create a Google Doc with separate pages. Can you finish it out and tell us how to automate sending each separate page as a separate email?
Can you teach me/ us how to make a copy of a template file in google docs then save it as PDF file in a specific folder in google drive. Thank you so much
Have another vídeo to show how yo create a new single document for each ss row?
Cool video! But i wonder. Is it possible that if a certain cell has value X then copy cel [0][1][2][3][4]
If it has value Y then copy cell [10], [11],[12],[13] and if cell has value Z then copy cells [5],[6],[7],[8],[9]
This is great, wluld you please help us take this further and hook this to email script so it sends out from a list of associated email addresses in the spreadsheet? Thanks Alex
Thanks very much. Great presentation.
Great tutorial. Is there a way to select from multiple of templates based on the contents of a cell? for example if a cell says "x" use template "x", but if a cell says "y" use template "y" etc?
Create 2 templates, get the ID, set var for each of them. Use if condition in the script pointing to the template (x or y).
I have shared my coding with you using my gdrive..i hope you can help me fixing my coding or giving any ideas, thanks a lot..
Very didactic explanation, which should get it clear for beginners, but wouldn't it be better to just use Object.keys method to create a pattern finder rather than parse them manually?
Probably :) I'm sure people who already know programming will figure that out themselves.
@Rafael Wendel - why would it be better? I'm interested from the perspective of a novice coder.
my template has a letter on the 1st page only (the whole template has 2 pages), ive replicated your tutorial and it works but the header is not copied :(
also ive noticed that for the numbered lists (instead of bullet points), my problem is the number lists continues from one copy to the next so my last copy has numbered lists starting from 50. instead of 1.
it's a great job.
I have some problem in applying number and table. how can i restrat the number for each page break since this video use list_item. then how can i apply table in the final document? thanks
Hi. Love your app script videos. If you could cover export pdf files to Google drive in app script in a video. I would be much appreciated. Thanks
Thank you for the video! I am running into an error (TypeError: Cannot call method "forEach" of undefined. (line 25, file "Code")) Any advice as to how to fix this? Thanks!
I had a similar problem. You probably aren't passing in the correct number of parameters as per the function declaration.
HELP?! Everything worked until the final run. I am getting this message and I am not sure what to do with it. Thank you!
10:55:14 AM Error
Too many changes applied before saving document. Please save changes in smaller batches using Document.saveAndClose(), then reopen the document with Document.openById().
Nice tutorial. Do you have an example for mail merge between Google Sheets and GMail?
Not through GMail UI, but sending email through GMail ruclips.net/video/h2z13YE3kJg/видео.html
Hi
This is very helpful for collecting applications forms,I have a question though. One of my columns is Image link from Google drive. Is it possible to make that image show on google doc?
How would you place an item where the table has some empty cells, e.g. if I am making a script from a cell where I have sales document, with the source worksheet i have columns named after a product and rows with different branches, some branches might not sell certain items from the table. how can I make the script to add on the mail merge with only the products that has sales value on it
You're a genius! Thank you for posting.
Do you know a workaround for this error: "Please save changes in smaller batches using Document.saveAndClose(), then reopen the document with Document.openById()." I have over a 1,000 rows of data for the mail merge.
same... please help
is it possible to insert a SQL query in the script and make use of
"where" to filter? thank you for the very useful video.
No, but you can use JavaScript filter method ruclips.net/video/PT_TDhMhWsE/видео.html
thank you sir.. very usefully video..!
Great!! works perfect., it is possible set filters? I mean, if I need merge only the information with "Kansas City" value in the column D. Is that possible?. I thought that with only setting filters to the data it will work, but sadly doesn't :(
it's possible. Watch my apps script series array methods videos, in particular filter method.
Thanks! Very helpful.
Glad it was helpful!
It's a nice classes..
I wanna learn one thing, in the nail merge how to print the sheets instead of creating the documents.. in the MS office it is available, but in Google sheet we have this limitations.
Pls kindly assist
Any idea why it's creating an extra copy of the template along with the "merged" version?
Was this ever figured out?
How to put a table in google doc template and send data from google sheets to doc table?
What is the easiest solution to run this script using the doc as a template and outputting a PDF for repetitive usage from a single line of code?
This is very interesting but can we make it more user friendly where a person can submit a list of names and address and will just fill out a template.
I love these tutorials. Is there an updated version?
Other than UI looking different, nothing else changed.
Hi
I have found this tutorial very informative and have used and modified the script to suit my needs and it has helped a lot. I have run into a little problem recently, the script has been working for approximately 6 months and all of a sudden I get a "Service unavailable: Documents" message for the "docFinal.getBody().appendParagraph(" portion of the code.
Does anyone know of a solution for this? Any help would be appreciated.
Thanks
I don't think anybody can help you by looking t this. Maybe if you share the whole script we can pinpoint to something.
Thank you for your response, I pasted the code below:
function Mail() {
var docTempleteId = "1MnkmuN...........O90";
var docFinalId = "1tFCT3On.................LUI1_s";
var wsId = "12vCn1U............................36Pc";
var docTemplete = DocumentApp.openById(docTempleteId);
var docFinal = DocumentApp.openById(docFinalId)
var ws = SpreadsheetApp.openById(wsId).getSheetByName("Letters");
var data = ws.getRange(4, 1, ws.getLastRow()-1, 27).getValues();
var templateParagrapths = docTemplete.getBody().getParagraphs();
docFinal.getBody().clear()
dateTimeCorrector
data.forEach(function(r){
createMailMerge(r[13],r[2],r[3],r[4],r[22],r[23],r[26],r[24],r[12],r[6],r[5],r[14],r[15],r[16],r[17],r[18],r[19],r[20],r[25],templateParagrapths,docFinal);
});
docFinal.getBody().getParagraphs()[0].removeFromParent();
var docBody = docFinal.getBody();
docBody.setPageHeight(842).setPageWidth(595);
}
function createMailMerge(Date1, Sport, Team, Oppo, Meet, Start, Date, Fin, Ret, Trans, Venue, AdNotes, Sign, Sign1, Sign2, Sign3, Sign4, Sign5, Return, templateParagrapths,docFinal){
templateParagrapths.forEach(function(p){
var elType = p.getType();
//This is the section of code where it says that the service is unavailable........................//
if(elType == "PARAGRAPH"){
docFinal.getBody().appendParagraph(
p
.copy()
.replaceText("", Date1)
.replaceText("", Sport)
.replaceText("", Team)
.replaceText("", Oppo)
.replaceText("", Meet)
.replaceText("", Start)
.replaceText("", Date)
.replaceText("", Fin)
.replaceText("", Ret)
.replaceText("", Trans)
.replaceText("", Venue)
.replaceText("", AdNotes)
.replaceText("", Sign)
.replaceText("",Sign1)
.replaceText("",Sign2)
.replaceText("",Sign3)
.replaceText("",Sign4)
.replaceText("",Sign5)
.replaceText("",Return)
);
} else if(elType == "LIST_ITEM"){
docFinal.getBody().appendListItem(
p
.copy()
.replaceText("", Date1)
.replaceText("", Sport)
.replaceText("", Team)
.replaceText("", Oppo)
.replaceText("", Meet)
.replaceText("", Start)
.replaceText("", Date)
.replaceText("", Fin)
.replaceText("", Ret)
.replaceText("", Trans)
.replaceText("", Venue)
.replaceText("", AdNotes)
.replaceText("", Sign)
.replaceText("",Sign1)
.replaceText("",Sign2)
.replaceText("",Sign3)
.replaceText("",Sign4)
.replaceText("",Sign5)
.replaceText("",Return)
).setGlyphType(DocumentApp.GlyphType.BULLET);
}
});
docFinal.getBody().appendPageBreak();
}
function dateTimeCorrector(x, entry){
if (x == 0){
return Utilities.formatDate(entry, "GMT+2", "DD-MMM-YYYY");
}
else if (x == 6){
return Utilities.formatDate(entry, "EST","hh:mm a");
}
else if (x == 7){
return Utilities.formatDate(entry, "EST","hh:mm a");
}
else if (x == 8){
return Utilities.formatDate(entry, "EST","hh:mm a");
}
else{
return entry;
}
}
Thank you
Hi, I think the problem was a jpeg in the doctemplate. I changed this and added code to insert the jpeg after the merge has taken place, this seems to have sorted the problem out.
great presentasion, thankyou.
how to make a straight point for the end of a sentence?
Can you tell me what the inside of { } is known as. I want my responses to be blue but do not know how to reference them in the code to ensure that all entries are blue instead of black (blue is for legal, black is for the dead, red is for the living and purple is Royalty)
Hi Guru and beyond of Apps Script guy! Sir how do we replace links with pictures and let the google do the backend job for pasting pictures to google docs
When I copy a multi-level list (LIST_ITEM's) and would like to add some level in the final doc, the formatting is lost.
How can I copy a list AND keep the format in the final doc when the user edits the list?
It will be great to have a video on format with the query function (like how to query results and format as currency, etc). If you agree, up vote this comment if you agree so the author notices it
ruclips.net/video/eQKmAcdVccs/видео.html
How would I use this to update multiple documents at one time? Each row would be mapped to its own google doc. Thanks
I am trying to do the same but with a background image in the template Doc. It throws the error service unavilable. How can i merge docs with background images?
I'm having all kinds of problems with this...it says "Cannot find function forEach in object Document. (line 27, file "Code"). HELP! I'm following this as closely as I can.
did you find a solution to this?
@@mariop4211 not yet. I gave up and started using MSword for it
If it says there is no somevar.forEach that means you had a problem in the line before where you defined somevar.
@@goldenwilliams the problem for me was ensuring that I told the function to include another r(i) value
@@ExcelGoogleSheets First off thanks for the video!
Secondly I inputed a table into my template Doc, and it doesn't show in the final Doc. Any advice?
Thank you so much!
merci beaucoup bro !!! t'es un vrai crack