Agreed. I bought a book on this stuff and I always seem to get errors I can't fix. I love your authenticity, honesty, making mistakes. It's very human and incredibly good learning, from one teacher to another!
I love You, I love the way you explain things, I love the amount of useful things you teach using Google Apps, I love How you release a video teaching exactly what I need to implement over here. I've been waiting so long for this video in particular. If, possible, you could make some videos about integrating Google Forms with Google Contacts.
@@ExcelGoogleSheets Create and update contacts on google platform. Like, What I want to do is to gather costumers info through a google forms, save this info in a spreadsheet and create a contact. If this contact has already been registered update it.
@@fernandobrito5316 @Learn Google Spreadsheets Yes! I was thinking the same thing watching this video. For me, as a self-employed guy, writing bills is a really tedious and I am implementing the technique in this video to automate bills via spreadsheets. It would be really awesome to learn about interconnectivity between google contacts and google spreadsheets. Like how to import in and export from sheets to contacts and vice versa. Why? The "autofill" function in gmail, or to give customers access to specifics sheets for example, only works, if you have the email adress saved in google contacts (afaik). PS: Thanks for your work - really appreciate it!!
This is the video about integrating Google Forms with Google Contacts. ua-cam.com/video/ISHALBY-w54/v-deo.html Thank you so much @Learn Google Spreadsheets for creating the video, and thanks for @ Fernando Brito for the idea.
Thank you so much! You've just freed me from third-party extensions, and made me more independent in creating certificates. Ps.: I'm #Brazilian and I used the automatic translator, sorry for the mistakes.
If you have trouble with "MimeType.PDF" - replaced "tempFile.getAs(MimeType.PDF)" with "tempFile.getAs('application/pdf')" and it will work like a charm. Thanks for the great content!
This is SO SO SO helpful! It's exactly what I needed to get things done for my new business, thanks so much--as a non-software language person, this broke everything down the way I needed to "get" it. I've subscribed and will be working my way through your videos, and hopefully will find a way to thank you that's remunerative, soon. Thanks so much again!
Would love to see a tutorial on printing out or exporting ranges to PDF or maybe image files, too. :) This would really be useful since there's currently no easy way to do this with code. Thank you so much for your great videos!
Hello @learn Google Spreadsheets, I would suggest renaming this video to show it is different video from the other PDF video you created. So, this is more a 'Bulk' email AppScript, rather than 'onSubmit' form video. This will be better for users to find the correct information. Your videos are great. I implemented your solution without problems. The Google Permissions approval process has changed.
I used this tutorial and in 2h I made it to work without any problems, interface from google has changed a little bit here and there but it all works and its not hard to do and follow, THANK YOU!!!
God bless you man. I think sometimes you read people's mind, this was a life savior video as it came out the right time we needed it to generate acceptance letters to email to students. Keep up the good work.
Thanks for the informative video,it really helped me to get the automation i wanted to do from very long,i have no idea about java and any sort of scripting,but your explanation made it very easy to understand.big thanks
May I ask a quick question. I am trying to export individual rows from a product catalog. The catalog has images stored in a cell. Is it possible to load that image into the template as well? I saw you using a different function to get the dollar symbol, so similarly does loading an image require a new function?
Excellent tutorial. Question: since you use the script to create a new file and name it in drive, is there a way to create a link to the newly created file in a cell in the sheet?
This is fantastic, thanks so much! ...Is there a way to include an image (any file format) in the data transfer? For a form I have images of a written signature included in the form questions, but when I put them in a column in the spreadsheet and run it though this program, in the resulting pdf it doesn't place the image but just prints the text "cellimage."
Hey, your tutorials are amazing. Will you also please share the code which you write in the video. I have seen your website but there is nothing available regarding this code. :(
This is a great demostration to do mail merge in Google Sheets without add-on. I learned from here and try to merge but hit the 6 mins execution time limit with maybe 60 recipients. Hopefully could see your solutions or ideas someday. Thanks!
This was a great video! I've never used scripts before and was able to accomplish it. Do you have a video on how to make bulk Google Docs? I thought the PDF was the best option for my needs, but I think it would be great to know how to make Google Docs in Word format as well
You are a life saver!!! One quick question. How do I get it to look at the PDF folder and remove the old files automatically? My spreadsheet used to update information so when the spreadsheet updates I need new PDF's preferably without having to go into the folder and delete the old files manually.
I really love ur channel thank you very much for all your lessons, U R the best one thing in this lesson : When you Remove the temp file titeld "copy of template" from (Temp Folder) it goes to ROOT not to Trash i've added this Function between the original two functions: function trash() { var files = DriveApp.getFilesByName("Copy of template"); while (files.hasNext()) files.next().setTrashed(true); then added : trash(); in the end of the last function , after this line: tempFolder.removeFile(tempFile); It woorks fine But , now we have to empty the trash manually is their any wayTO EMPTY THE TRASH Automatically in the end of these scripts?
You have to use advanced Drive API service to be able to delete without clearing the trash. You can't do it with regular Apps Script DriveApp service, at least not at the moment.
Thanks for this modification! Accidentally found bunch of template files in ROOT folder and didn't manage to find where they came from. I modified this script by my needs a lot, so thought that some of mine script lines creates duplicates of template in ROOT folder. I had no idea that these copies come from temp folder. Your solution works great. Thanks :)
Hi! This has been such a huge help for me. Thank you for your amazing tutorials. We receive applications at my organisation using Google Forms. From there the data gets saved into a sheet and I want to then save PDF summaries of those applications. Your tutorial has allowed me to achieve this. The one thing I would love to be able to do in addition to this would be to now allow for duplicate files. The way the code works now, it creates PDFs for all rows each time the script is run. I'd love to be able to run the script and only create new PDFs for new application responses without creating more and more of the same. Do you have any advice on how I can find out how to do that?
@@mayash7604 Hey, you would need to change the code that's getting all the rows to getting the last non-empty row. Then use the trigger tab to run the script every time a new row is added
Thanks for all the content so far ! Im using extension to doing the same thing , which is whenever a google form submitted it will generate a pdf email. Wonder is this works by writing scripts from a newbie like me ? As the chrome app is not supported anymore end of this year...
This video has been so helpful! Thanks so much! I am trying to now get the URL of each created PDF to appear in the spreadsheet so that I can then send the link in an email. I have watched your other video that shows how to do this using data from a google form, but is there a simple set of code that can just be added to the code created in this video? Many thanks in advance.
Hello, all your videos are easy to understand, they give me a lot of solutions and help me to rethink my processes. In this case I wonder, how to group data in a pdf report having more than one item (lines of the spreadsheet ) referred to the same customer, like I 'm used to do with pivot, but to be dinamically generated, and saved in a folder. Thank you for all your videos and for the passion you put in spreading this knowledge!
Thank you very much for sharing your knowledge. Excellent work. Ask about this Tutorial: How can I incorporate an image to the PDF, if I only have the file link in a Cell? Thanks for your time. Muchas gracias por compartir tus conocimientos. Excelente trabajo. Consulta sobre este Tutorial: ¿Cómo puedo incorporar una imagen al PDF, si tengo en una Celda solo el link del archivo? Gracias por tu tiempo.
In your previous videos you used to use var instead of let and const, why did you change it here? And I understand you try to use them as variables but what are you using 2 different things there? Thank you for awesome contents!! Please keep up the great work!
Since JavaScript engine was recently updated we can now use "const" and "let" to declare variables. In this particular case you could just replace all of them with "var" and it would work just fine as well. "const" means it's constant, so it can not be changed to something else later in code, so it's not really a "variable", even though that's what I call it. "let" is a long story :)
That was an amazing tutorial. Straight to the point and perfectly explained. I implemented a version on my data which at the moment is 50 rows and 9 columns, trying to create invoices for my clients. Is it normal that the 6 minutes of allowed runtime only gets me up to about 30 pdfs created? Is there any way to optimize the script so it could potentially deal with 100 clients in less than 6 minutes? thanks for your help :)
Actually, you CAN chain .replace(). I've been using that a lot to generate bunches of customized "diplomas" for students, and it worked liket a treat. The only painful thing is to have to declare each and everyone of the placeholders. I'd rather do some sort of automatic find of the {} occurences and map them to the "fields" in the spreadsheet, but I'm not good enough to do that… Anyway, your video was the greatest I've ever found about that specific need. Thanks a ton !!
how we can get just create a pdf for specifc rows that we want, kind of choice it first, or for a specific rule, maybe for the date. for exemplo, I have every day some new data, so today I want to generate some pdfs for data from yesterday... Thank you for videos, they are pretty awsome!
This is absolutely amazing instruction! I am a novice at coding, but was able to follow along step-by-step. I typed each line along with you and created the docs and folders right along with you. I actually made it through without errors (other than the ones you showed us) the first time through! The one issue I had has been mentioned in the other comments. How would you recommend duplication prevention when adding names to the spreadsheet and running the script again?
Easy way: add a checkbox column next to your data and use FILTER function on another sheet to return rows that are checked. Then use the script on that filtered sheet. This way you can use checboxes to control which one should be created.
@@ExcelGoogleSheets Thanks! I am gonna try that this afternoon. I had thought to use your try...catch to write DONE into a column as the PDFs were created and use if...then to NOT create those, but your way sounds much easier. I'm creating contracts, so that only needs to be done once. I can manually change anything they want changed.
Thank you so much for all your tutorials, I´ve learned a lot these years with them. I was wondering if this function will work for any user that the spreadsheet is shared with. I´ve tried different ways to get a google docs for the last row of a spreadsheet and so far, only I can use it; other users that have edit access to the sheet and shared the drive folder where the doc files are stored always get an error when trying to generate the doc. Do you have an idea of what I´m missing?
Firstly, amazing video thank you. Secondly, if I wanted to add in script that whenever I click the 'Create PDFs' button in the spreadsheet, it only runs the script and creates PDFs based on new data that has been added to the spreadsheet and not old/already processed data, how would I go about doing that?
Nice just 1 question. Instead of using the button to run the code. Can i run and create the pdf each time i fill out a google form which then populates the google sheets row? Also if it can be emailed to a specific email?
Thank you for this clear brief tutorial. How about if i have a URL in one of the field, what should I put instead of replacetext? If you can send us how, it would be very helpful, thank you.
Again & again thank you..Can you please show us some steps to generate only new ones - may be adding a column & trigger it with it? Many had this concern if you noticed sir..Thank you.
Thanks such a helpful video! I feel like this would be more complete if you could show how to use the generated bulk PDFs to send individually to the people listed in the sheet. Would you have a video of this somehow in your channel?
Thank you for putting this together; it's been super helpful! I had a question about getting the formatting to match what's currently the sheet range I'm pulling from. The getDisplayValues() helped, but it didn't bring over bold, underline, and other formatting from the cells. I've also got a column with a linked website. Any guidance on how to get all of these into my final PDF would be most welcome. I really appreciate your channel, I've been using it a lot!
Figured out that I should format the key words I'm replacing in my template. The replacements from my sheet will take on the formatting that's already there in the key word.
thanks for the useful information. I have a question, if i store the pdfs in google drive and i want to share it with other people, can you modify the program so, it not only create the pdfs, but also generate the personalized pdf link to the selected cell.
thanks for the valuable tutorials , a really loved this channel and the why you explain the things , very simple . I support to start learning JS separatly but here I found my self learning lot of things about JS and GS at the same time , really thank you very much I hope also that you make some videos about organizing many forms (custom form not google forms with bootstrap ) on google sites and link every thing with GD and GS thanks again
Amazing tutorial! Didn’t think I can find a tutorial for what I needed! I am collecting my data in GoogleForm. How do I get the script to only create PDF of the new row?
Finally, I found what I was looking for. Great teacher. However, I am still stumped. That's only because I am not into all this data stuff. Do you do this as a gig work too?
Great Video! Question, I have header data and repeating body data, how would you code the If statement to loop through the header and body data before and print it out in the PDF, then go to the next header/body data to print another PDF? As it is now, every row in my Google Sheet creates a PDF.
Thank you for your videos, it's really amazing and I can't imagine I can make it!!! So excited. I have a question, whether how to solve it, as when I add new data, every time it save and run, it will run and save all previous data, if I only want a new data to save in the same folder?
Amazing tutorial mate. 👍🏻 Can anyone help, please?! Is there a way to automatically generate a PDF file every time a row is added? The PDF file only to contain the data from the newly created row.
Can u explain how to use image stored as url in column to create pdfs? Example: I have signature stored in column K as google drive links and I want to fetch image from that url to create pdfs
This was exactly what I needed, but I have run into a snag: I am using a Google Form to put data into the Google Sheet that I want to pull data out of to create the PDF. This video explains perfectly how to pull the data out and generate the PDF. THANK YOU! However, the column headers generated by Google Forms have spaces and special characters, because Google Forms automatically generates the column headers. THE QUESTION: How to I remove spaces and special characters from my column headers when referencing the data so the script doesn't fail because of spaces and special characters in the column headers? Thanks in advance for your help. :-)
Thank you so much for very intuitive and just excellent tutorials. I've been looking for a way to include dynamic images (from url in a spredsheet) when using Google Docs as a template and PDF as final output (or alternatively html template). Is this something you have or will cover?
@@ExcelGoogleSheets thanks for your reply. I understand how this tutorial shows how to create a pdf with dynamic and email the pdf, but I'm lookking for a way to have in the output file. So rather than replacing text variables in the doc template with values from the spreadsheet I was hoping for a way to dictate which image is used based on a URL in the spreadsheet. (In your tutorial we could for instance have not only the name of "John Doe" but also a picture of him. Thanks in advance and again thanks for really intuitive tutorials.
how did you make the second function run even so you picked the first function to run? what did you use to call the second function to the first function?
great video! i would like to save the new document in the same google sheets folder. I would like to find the folder id automatically. I'll make many copies of the same google sheets (in a different folder!), I don't want to have to "copy and paste" every time folder ID
Congratulations, the tutorial was very well explained, it guided me to create the functions that I needed in my script, now I ask you instead of creating bulk documents, how would I specifically create a single document? what would change in this formula? : function createBulkDocs(){ const docFile = DriveApp.getFileById("XXXXXXXXXXXXXXXXXXXX"); const tempFolder = DriveApp.getFolderById("XXXXXXXXXXXXXXXX"); const currentSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("sheet1"); const dato = currentSheet.getRange(2, 1, currentSheet.getLastRow()-1, 4).getDisplayValues(); dato.forEach(row => { createDocs(row[1],row[2],docFile,tempFolder); }); }
I feel like I should pay for watching this. Outstanding quality.
Agreed. I bought a book on this stuff and I always seem to get errors I can't fix. I love your authenticity, honesty, making mistakes. It's very human and incredibly good learning, from one teacher to another!
then donate on Patreon.
Shhh! He might hear.
just do it.. buy a thanks ..
What an incredibly lucid tutorial. When it comes to the best at explaining tech on UA-cam, there is this guy and Bob Traversy, then everyone else.
You are the best Teacher for Google Apps Script with Spreadsheets
My mind has just been blown. Thank you so much for the in-depth explanations. You have probably saved me months worth of work.
Great to hear!
@@ExcelGoogleSheets
Did you have Courses Online about app Script google sheet
@@ExcelGoogleSheets hi, can i still use the removeFile method? cos it now says that this method is deprecated. thanks
I love You, I love the way you explain things, I love the amount of useful things you teach using Google Apps, I love How you release a video teaching exactly what I need to implement over here. I've been waiting so long for this video in particular.
If, possible, you could make some videos about integrating Google Forms with Google Contacts.
This might be a stupid question, but what does Google Contacts do?
@@ExcelGoogleSheets Create and update contacts on google platform. Like, What I want to do is to gather costumers info through a google forms, save this info in a spreadsheet and create a contact. If this contact has already been registered update it.
@@fernandobrito5316 @Learn Google Spreadsheets
Yes! I was thinking the same thing watching this video. For me, as a self-employed guy, writing bills is a really tedious and I am implementing the technique in this video to automate bills via spreadsheets. It would be really awesome to learn about interconnectivity between google contacts and google spreadsheets. Like how to import in and export from sheets to contacts and vice versa. Why? The "autofill" function in gmail, or to give customers access to specifics sheets for example, only works, if you have the email adress saved in google contacts (afaik).
PS: Thanks for your work - really appreciate it!!
This is the video about integrating Google Forms with Google Contacts.
ua-cam.com/video/ISHALBY-w54/v-deo.html
Thank you so much @Learn Google Spreadsheets for creating the video, and thanks for @
Fernando Brito for the idea.
So gay .. 🤣
Could you take this a step further and show how to automatically send PDF file to a given email after it was submitted through google form?
you are a saviour.. automated 70% of my department work from your channel
really love this channel now. feels like watching drama thriller series.
Thank you so much!
You've just freed me from third-party extensions, and made me more independent in creating certificates.
Ps.: I'm #Brazilian and I used the automatic translator, sorry for the mistakes.
This is Wagner, speaking from Brasil. Simply amazing your videos! Thank you for taking your time teaching us!
If you have trouble with "MimeType.PDF" - replaced "tempFile.getAs(MimeType.PDF)" with "tempFile.getAs('application/pdf')" and it will work like a charm.
Thanks for the great content!
for me it creates PDF from initial template doc file, not from copy with replaced data. any ideas?
@@ДмитрийПаравин Sounds like you have a bug somewhere around binding data. follow the tutorial correctly step by step and you will succeed
@@itamarnakar6698 thanks for reply. i see that another user here have the same problem. may be something in google script was changed?
@@ДмитрийПаравин it s working like a charm in the new script
This is SO SO SO helpful! It's exactly what I needed to get things done for my new business, thanks so much--as a non-software language person, this broke everything down the way I needed to "get" it. I've subscribed and will be working my way through your videos, and hopefully will find a way to thank you that's remunerative, soon. Thanks so much again!
Would love to see a tutorial on printing out or exporting ranges to PDF or maybe image files, too. :) This would really be useful since there's currently no easy way to do this with code. Thank you so much for your great videos!
World's No 1 Channel
World's Best Teacher
World's Best Quality
Learn Easy and Understand
I Love And Like All Video
All Video Is Heart 💓 Touching
Hello @learn Google Spreadsheets, I would suggest renaming this video to show it is different video from the other PDF video you created. So, this is more a 'Bulk' email AppScript, rather than 'onSubmit' form video. This will be better for users to find the correct information. Your videos are great. I implemented your solution without problems. The Google Permissions approval process has changed.
have you had trouble on line 10
const = tempfile = docFile.makeCopy(tempFolder);
keeps returning docFile.maleCopy is not a function
Great stuff! Everyone who uses Google Sheet/Excel should be on your channel!
Thanks!
I used this tutorial and in 2h I made it to work without any problems, interface from google has changed a little bit here and there but it all works and its not hard to do and follow, THANK YOU!!!
Unfortunately Google changes their interface so frequently that it's impossible to keep up.
God bless you man. I think sometimes you read people's mind, this was a life savior video as it came out the right time we needed it to generate acceptance letters to email to students. Keep up the good work.
Awesome!
Thanks for the informative video,it really helped me to get the automation i wanted to do from very long,i have no idea about java and any sort of scripting,but your explanation made it very easy to understand.big thanks
You know that this is useful and needed so, I am just gonna say thank you. It saved me days and it will save days to a lot of people.
May I ask a quick question. I am trying to export individual rows from a product catalog. The catalog has images stored in a cell. Is it possible to load that image into the template as well? I saw you using a different function to get the dollar symbol, so similarly does loading an image require a new function?
Your explanations are awesome man. Is there any way to add an image to the PDFs before we email them?
Absolutely amazing. Content, presentation, depth, to the point, spot on! Well done man!
Much appreciated!
You are a awesome teacher sir.
Explained everything very well. Nice work👍🏻👍🏻
This was an AWESOME tutorial, just what I was looking for. I subscribed for the next tutorial.
Wow,,, super video,,, I going to practice. Thanks a Lot.
Amazing. Exactly what I wanted to learn how to do. The last step that would be helpful is entering the url to the pdf in a column in the spreadsheet.
Hi. Did you manage to find the solution?
@@elmodvz No, but I have tried this yet either. Let me know if you figure it out though!
Excellent tutorial. Question: since you use the script to create a new file and name it in drive, is there a way to create a link to the newly created file in a cell in the sheet?
I was looking for a way to do this as well.
This is fantastic, thanks so much! ...Is there a way to include an image (any file format) in the data transfer? For a form I have images of a written signature included in the form questions, but when I put them in a column in the spreadsheet and run it though this program, in the resulting pdf it doesn't place the image but just prints the text "cellimage."
same question here!
Hey, your tutorials are amazing. Will you also please share the code which you write in the video.
I have seen your website but there is nothing available regarding this code. :(
Check @Richard Hain comment.
@@aka-alias function createBulkPDFs(){
const docFile = DriveApp.getFileById("id");
const tempFolder = DriveApp.getFolderById("id");
const pdfFolder = DriveApp.getFolderById("id");
const currentSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("people");
const data = currentSheet.getRange(2, 1,currentSheet.getLastRow()-1,4).getDisplayValues();
let errors = [];
data.forEach(row => {
try{
createPDF(row[0],row[1],row[3],row[0] + " " + row[1],docFile,tempFolder,pdfFolder);
errors.push([""]);
} catch(err){
errors.push(["Failed"]);
}
}); //close forEach
currentSheet.getRange(2, 5,currentSheet.getLastRow()-1,1).setValues(errors);
}
function createPDF(firstName,lastName,amount,pdfName,docFile,tempFolder,pdfFolder) {
const tempFile = docFile.makeCopy(tempFolder);
const tempDocFile = DocumentApp.openById(tempFile.getId());
const body = tempDocFile.getBody();
body.replaceText("{first}", firstName);
body.replaceText("{last}", lastName);
body.replaceText("{balance}", amount);
tempDocFile.saveAndClose();
const pdfContentBlob = tempFile.getAs(MimeType.PDF);
pdfFolder.createFile(pdfContentBlob).setName(pdfName);
tempFolder.removeFile(tempFile);
}
@@MAROCDL I apreciated that. Thank you so much.
This is a great demostration to do mail merge in Google Sheets without add-on. I learned from here and try to merge but hit the 6 mins execution time limit with maybe 60 recipients. Hopefully could see your solutions or ideas someday. Thanks!
Awesome! Thanks for posting the video. Informative and educative. You explained very well.
Glad you enjoyed it!
Very valuable content and clear explanations. Good job!
Gracias, @Learn Google Spreadsheets. Muy bueno el tutorial.
Awesome! I've been looking for a video like this one, I am very grateful to you. Keep the good work!
Glad you liked it!
This was a great video! I've never used scripts before and was able to accomplish it. Do you have a video on how to make bulk Google Docs? I thought the PDF was the best option for my needs, but I think it would be great to know how to make Google Docs in Word format as well
Just download the google docs as word format :)
You are a life saver!!! One quick question. How do I get it to look at the PDF folder and remove the old files automatically? My spreadsheet used to update information so when the spreadsheet updates I need new PDF's preferably without having to go into the folder and delete the old files manually.
This is SO SO SO helpful!
Kindly suggest automation for that PDF file link against that row with database.
I really love ur channel
thank you very much for all your lessons, U R the best
one thing in this lesson :
When you Remove the temp file titeld "copy of template" from (Temp Folder) it goes to ROOT not to Trash
i've added this Function between the original two functions:
function trash() {
var files = DriveApp.getFilesByName("Copy of template");
while (files.hasNext())
files.next().setTrashed(true);
then added : trash();
in the end of the last function , after this line:
tempFolder.removeFile(tempFile);
It woorks fine
But , now we have to empty the trash manually
is their any wayTO EMPTY THE TRASH Automatically in the end of these scripts?
You have to use advanced Drive API service to be able to delete without clearing the trash. You can't do it with regular Apps Script DriveApp service, at least not at the moment.
Thanks for this modification! Accidentally found bunch of template files in ROOT folder and didn't manage to find where they came from. I modified this script by my needs a lot, so thought that some of mine script lines creates duplicates of template in ROOT folder. I had no idea that these copies come from temp folder. Your solution works great. Thanks :)
This worked like a hot pie! thank you, I love it
ThankU so much badly needed this.. been searching for awhile without addons.. big big help, how about after submitted the form, auto generates PDF ?
Just what I needed! And excellently presented! Thank you!
Hi, many thanks for your teachings, very educative and over all step by step!
Hi! This has been such a huge help for me. Thank you for your amazing tutorials. We receive applications at my organisation using Google Forms. From there the data gets saved into a sheet and I want to then save PDF summaries of those applications. Your tutorial has allowed me to achieve this. The one thing I would love to be able to do in addition to this would be to now allow for duplicate files. The way the code works now, it creates PDFs for all rows each time the script is run. I'd love to be able to run the script and only create new PDFs for new application responses without creating more and more of the same. Do you have any advice on how I can find out how to do that?
Hello, I need to do this also
@@mayash7604 Hey, you would need to change the code that's getting all the rows to getting the last non-empty row. Then use the trigger tab to run the script every time a new row is added
@@nathanpolhemus1710 Can you show us how pls?
This guy is my frickin hero.
Thanks for all the content so far ! Im using extension to doing the same thing , which is whenever a google form submitted it will generate a pdf email. Wonder is this works by writing scripts from a newbie like me ? As the chrome app is not supported anymore end of this year...
A magnificent class.
Great!!
This video has been so helpful! Thanks so much! I am trying to now get the URL of each created PDF to appear in the spreadsheet so that I can then send the link in an email. I have watched your other video that shows how to do this using data from a google form, but is there a simple set of code that can just be added to the code created in this video? Many thanks in advance.
Hello, all your videos are easy to understand, they give me a lot of solutions and help me to rethink my processes.
In this case I wonder, how to group data in a pdf report having more than one item (lines of the spreadsheet ) referred to the same customer, like I 'm used to do with pivot, but to be dinamically generated, and saved in a folder. Thank you for all your videos and for the passion you put in spreading this knowledge!
Thank you very much for sharing your knowledge. Excellent work. Ask about this Tutorial: How can I incorporate an image to the PDF, if I only have the file link in a Cell? Thanks for your time.
Muchas gracias por compartir tus conocimientos. Excelente trabajo. Consulta sobre este Tutorial: ¿Cómo puedo incorporar una imagen al PDF, si tengo en una Celda solo el link del archivo? Gracias por tu tiempo.
This is the most useful channel for me
Love it very much
Thank you for creating this channel
Glad you enjoy it!
In your previous videos you used to use var instead of let and const, why did you change it here? And I understand you try to use them as variables but what are you using 2 different things there?
Thank you for awesome contents!! Please keep up the great work!
Since JavaScript engine was recently updated we can now use "const" and "let" to declare variables. In this particular case you could just replace all of them with "var" and it would work just fine as well. "const" means it's constant, so it can not be changed to something else later in code, so it's not really a "variable", even though that's what I call it.
"let" is a long story :)
@@ExcelGoogleSheets IIRC from the olden days when computer processing speed was a concern, const executes faster. If my memory serves me right...
That was an amazing tutorial. Straight to the point and perfectly explained. I implemented a version on my data which at the moment is 50 rows and 9 columns, trying to create invoices for my clients. Is it normal that the 6 minutes of allowed runtime only gets me up to about 30 pdfs created? Is there any way to optimize the script so it could potentially deal with 100 clients in less than 6 minutes? thanks for your help :)
Actually, you CAN chain .replace(). I've been using that a lot to generate bunches of customized "diplomas" for students, and it worked liket a treat. The only painful thing is to have to declare each and everyone of the placeholders. I'd rather do some sort of automatic find of the {} occurences and map them to the "fields" in the spreadsheet, but I'm not good enough to do that… Anyway, your video was the greatest I've ever found about that specific need. Thanks a ton !!
Great!
how we can get just create a pdf for specifc rows that we want, kind of choice it first, or for a specific rule, maybe for the date. for exemplo, I have every day some new data, so today I want to generate some pdfs for data from yesterday... Thank you for videos, they are pretty awsome!
Any solution pls?
This is absolutely amazing instruction! I am a novice at coding, but was able to follow along step-by-step. I typed each line along with you and created the docs and folders right along with you. I actually made it through without errors (other than the ones you showed us) the first time through! The one issue I had has been mentioned in the other comments. How would you recommend duplication prevention when adding names to the spreadsheet and running the script again?
Easy way: add a checkbox column next to your data and use FILTER function on another sheet to return rows that are checked. Then use the script on that filtered sheet. This way you can use checboxes to control which one should be created.
@@ExcelGoogleSheets Thanks! I am gonna try that this afternoon. I had thought to use your try...catch to write DONE into a column as the PDFs were created and use if...then to NOT create those, but your way sounds much easier. I'm creating contracts, so that only needs to be done once. I can manually change anything they want changed.
@@ExcelGoogleSheets Thank you, if you can add a small tutorial about it :)
@@cutlooseentertainment Can you complete please what comes after the if exactly ?! Thank you
this is a great tuturial - thank you. Is there a way of making a button to create a pdf for a specific row?
This was exactly what I needed! You are a very good teacher. Thank you so much for this video. I have subscribed to your channel. :-)
You’re a beast my guy.
Thank you so much for all your tutorials, I´ve learned a lot these years with them. I was wondering if this function will work for any user that the spreadsheet is shared with. I´ve tried different ways to get a google docs for the last row of a spreadsheet and so far, only I can use it; other users that have edit access to the sheet and shared the drive folder where the doc files are stored always get an error when trying to generate the doc. Do you have an idea of what I´m missing?
Firstly, amazing video thank you.
Secondly, if I wanted to add in script that whenever I click the 'Create PDFs' button in the spreadsheet, it only runs the script and creates PDFs based on new data that has been added to the spreadsheet and not old/already processed data, how would I go about doing that?
hello, did you figure out how?
@@JinaneKarhani Unfortunately I didn't. I have had to resort to just deleting the rows of data which I don't need to export.
@@nicka6782 Thank you, aha but this is not practical for some end users.
Nice just 1 question.
Instead of using the button to run the code.
Can i run and create the pdf each time i fill out a google form which then populates the google sheets row? Also if it can be emailed to a specific email?
Thank you for this clear brief tutorial. How about if i have a URL in one of the field, what should I put instead of replacetext? If you can send us how, it would be very helpful, thank you.
Doesn't it just work if you treat it as text field?
@@ExcelGoogleSheets No sir, they need to click on it so it download the document..
@@joymawad9201 body.appendParagraph("link text here").setLinkUrl("www.google.com");
@@ExcelGoogleSheets I will try it thank you.
It worked perfectly, thank youuuu.
Awesome tutorial, also a great explanation of variables, loops, and error catching thanks a bunch
nice, very nice, big very nice!!! thank you!
That thing is a life saver. Thank you.... One question.. Is there a way we can automate the process around the scripts time limit?
very clear and informative. Thank you so much!
Glad you enjoyed it!
Again & again thank you..Can you please show us some steps to generate only new ones - may be adding a column & trigger it with it? Many had this concern if you noticed sir..Thank you.
I love these videos I've learned so much! Could this be used to add a cell image to the pdf?
Thanks such a helpful video! I feel like this would be more complete if you could show how to use the generated bulk PDFs to send individually to the people listed in the sheet. Would you have a video of this somehow in your channel?
Thank you for putting this together; it's been super helpful! I had a question about getting the formatting to match what's currently the sheet range I'm pulling from. The getDisplayValues() helped, but it didn't bring over bold, underline, and other formatting from the cells. I've also got a column with a linked website. Any guidance on how to get all of these into my final PDF would be most welcome. I really appreciate your channel, I've been using it a lot!
It will only get number formatting. Any other formatting will not pass though. The rest should be done in the document template.
Figured out that I should format the key words I'm replacing in my template. The replacements from my sheet will take on the formatting that's already there in the key word.
Hi! Amazing tutorial, do you have a related video where it automates the sending of the PDF files to emails of each person?
thanks for the useful information. I have a question, if i store the pdfs in google drive and i want to share it with other people, can you modify the program so, it not only create the pdfs, but also generate the personalized pdf link to the selected cell.
Really useful lesson here. Once again, thanks for the class!!!!!!!!!!!!!!!!
Hi, thank for your tutorial. That's it helpfully.
Thank you!
very amazing and use full
Excellent video and its exactly what I need to do for my project!!!
Thanks for your effort done
the maximum pdf creating are 32 PDF from Spreadsheet , how can create 100 pdf for example ?
Thank you so much. This is one of the best ever exist tutorials.
thanks for the valuable tutorials , a really loved this channel and the why you explain the things , very simple . I support to start learning JS separatly but here I found my self learning lot of things about JS and GS at the same time , really thank you very much
I hope also that you make some videos about organizing many forms (custom form not google forms with bootstrap ) on google sites and link every thing with GD and GS
thanks again
Thank you so much for these tutorials. Absolutely amazing!!!
Amazing tool. you made my task a lot more easier. Thanks a ton.
Amazing tutorial! Didn’t think I can find a tutorial for what I needed! I am collecting my data in GoogleForm. How do I get the script to only create PDF of the new row?
If possible PDF File folder auto create into sharePoint ??
Finally, I found what I was looking for. Great teacher. However, I am still stumped. That's only because I am not into all this data stuff. Do you do this as a gig work too?
this is great. what do i need to change if i want to make all rows of data into each page of one pdf file instead of make one file for each row?
Great Video! Question, I have header data and repeating body data, how would you code the If statement to loop through the header and body data before and print it out in the PDF, then go to the next header/body data to print another PDF? As it is now, every row in my Google Sheet creates a PDF.
Thank you for your videos, it's really amazing and I can't imagine I can make it!!! So excited.
I have a question, whether how to solve it, as when I add new data, every time it save and run, it will run and save all previous data, if I only want a new data to save in the same folder?
Amazing tutorial mate. 👍🏻
Can anyone help, please?!
Is there a way to automatically generate a PDF file every time a row is added? The PDF file only to contain the data from the newly created row.
Thank you , great tutorial.
Is it possible to open a PDF file right after its creation with Apps Script in Google Sheets?
Can u explain how to use image stored as url in column to create pdfs? Example: I have signature stored in column K as google drive links and I want to fetch image from that url to create pdfs
hello, did you figure it how pls?
Amazing tutorial!
Is it possible to open the newly created pdf file in a new tab?
🤯 just what I needed, thank you very much !!
This was exactly what I needed, but I have run into a snag: I am using a Google Form to put data into the Google Sheet that I want to pull data out of to create the PDF. This video explains perfectly how to pull the data out and generate the PDF. THANK YOU! However, the column headers generated by Google Forms have spaces and special characters, because Google Forms automatically generates the column headers. THE QUESTION: How to I remove spaces and special characters from my column headers when referencing the data so the script doesn't fail because of spaces and special characters in the column headers? Thanks in advance for your help. :-)
Very Useful Tutorial. Please Solve my issue to create pdf in Landscape from google spreadsheet (not google doc)
Thank you so much for very intuitive and just excellent tutorials. I've been looking for a way to include dynamic images (from url in a spredsheet) when using Google Docs as a template and PDF as final output (or alternatively html template). Is this something you have or will cover?
ua-cam.com/video/EpZGvKIHmR8/v-deo.html
@@ExcelGoogleSheets thanks for your reply. I understand how this tutorial shows how to create a pdf with dynamic and email the pdf, but I'm lookking for a way to have in the output file. So rather than replacing text variables in the doc template with values from the spreadsheet I was hoping for a way to dictate which image is used based on a URL in the spreadsheet. (In your tutorial we could for instance have not only the name of "John Doe" but also a picture of him. Thanks in advance and again thanks for really intuitive tutorials.
@@SveinnTryggvason Hi were you able to find out how to include images?
how did you make the second function run even so you picked the first function to run? what did you use to call the second function to the first function?
Absolutely amazing!
great video! i would like to save the new document in the same google sheets folder. I would like to find the folder id automatically. I'll make many copies of the same google sheets (in a different folder!), I don't want to have to "copy and paste" every time folder ID
Congratulations, the tutorial was very well explained, it guided me to create the functions that I needed in my script, now I ask you instead of creating bulk documents, how would I specifically create a single document? what would change in this formula? :
function createBulkDocs(){
const docFile = DriveApp.getFileById("XXXXXXXXXXXXXXXXXXXX");
const tempFolder = DriveApp.getFolderById("XXXXXXXXXXXXXXXX");
const currentSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("sheet1");
const dato = currentSheet.getRange(2, 1, currentSheet.getLastRow()-1, 4).getDisplayValues();
dato.forEach(row => {
createDocs(row[1],row[2],docFile,tempFolder);
});
}
Same question here. I'd like to be able to run the script when I add a new row to the source spreadsheet
Same question : but creating documents for a selected row or selected rows. Any replies yet ?