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. ruclips.net/video/ISHALBY-w54/видео.html Thank you so much @Learn Google Spreadsheets for creating the video, and thanks for @ Fernando Brito for the idea.
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!
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 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!
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!!!
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!
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
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.
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?
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
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. :(
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?
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.
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!
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."
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 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.
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
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 !!
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.
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 :)
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?
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 :)
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...
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.
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?
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
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?
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!
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); }); }
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?
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?
Duplicates the old ones, adds the new ones. Great question! I would like to know how NOT to create duplicates. I'm using this to create contracts. I suppose you could create the PDFs and then move the data to another sheet after completion. Seems unnecessary, though. Perhaps, setValues Done and use if...then? Or have it check the Done column first before creating the PDF?
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.
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 :)
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
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.
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 RUclips, there is this guy and Bob Traversy, then everyone else.
You are the best Teacher for Google Apps Script with Spreadsheets
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?
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.
ruclips.net/video/ISHALBY-w54/видео.html
Thank you so much @Learn Google Spreadsheets for creating the video, and thanks for @
Fernando Brito for the idea.
So gay .. 🤣
This is Wagner, speaking from Brasil. Simply amazing your videos! Thank you for taking your time teaching us!
you are a saviour.. automated 70% of my department work from your channel
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
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
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 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!
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
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.
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!
You are a awesome teacher sir.
Explained everything very well. Nice work👍🏻👍🏻
Great stuff! Everyone who uses Google Sheet/Excel should be on your channel!
Thanks!
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
Wow,,, super video,,, I going to practice. Thanks a Lot.
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!
Gracias, @Learn Google Spreadsheets. Muy bueno el tutorial.
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.
Hi, many thanks for your teachings, very educative and over all step by step!
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!
Very valuable content and clear explanations. Good job!
Awesome! Thanks for posting the video. Informative and educative. You explained very well.
Glad you enjoyed it!
Absolutely amazing. Content, presentation, depth, to the point, spot on! Well done man!
Much appreciated!
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?
This is SO SO SO helpful!
Kindly suggest automation for that PDF file link against that row with database.
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 an AWESOME tutorial, just what I was looking for. I subscribed for the next tutorial.
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 :)
This is the most useful channel for me
Love it very much
Thank you for creating this channel
Glad you enjoy it!
A magnificent class.
Great!!
Awesome tutorial, also a great explanation of variables, loops, and error catching thanks a bunch
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.
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.
very amazing and use full
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 guy is my frickin hero.
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!
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!
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
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.
ThankU so much badly needed this.. been searching for awhile without addons.. big big help, how about after submitted the form, auto generates PDF ?
Really useful lesson here. Once again, thanks for the class!!!!!!!!!!!!!!!!
very clear and informative. Thank you so much!
Glad you enjoyed it!
Just what I needed! And excellently presented! Thank you!
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?
I love these videos I've learned so much! Could this be used to add a cell image to the pdf?
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!
Amezing sir thankyou soo much for this
this is a great tuturial - thank you. Is there a way of making a button to create a pdf for a specific row?
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.
Hi, thank for your tutorial. That's it helpfully.
Thank you!
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...
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?
Thank you so much for these tutorials. Absolutely amazing!!!
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
You’re a beast my guy.
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. :-)
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!
Very Useful Tutorial. Please Solve my issue to create pdf in Landscape from google spreadsheet (not google doc)
Nice one👍
Excellent video and its exactly what I need to do for my project!!!
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...
That thing is a life saver. Thank you.... One question.. Is there a way we can automate the process around the scripts time limit?
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.
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?
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 tool. you made my task a lot more easier. Thanks a ton.
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 so much. This is one of the best ever exist tutorials.
Very useful script thank you.
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?
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 ?
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?
Thank you , great tutorial.
Is it possible to open a PDF file right after its creation with Apps Script in Google Sheets?
Amazing tutorial!
Is it possible to open the newly created pdf file in a new tab?
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?
Amazing comunicator Man!! Thanks a lot.
🙂
When you add more rows of data to your spreadsheet does the script re-create the pdfs already in the spreadsheet or just create new ones?
Duplicates the old ones, adds the new ones. Great question! I would like to know how NOT to create duplicates. I'm using this to create contracts. I suppose you could create the PDFs and then move the data to another sheet after completion. Seems unnecessary, though. Perhaps, setValues Done and use if...then? Or have it check the Done column first before creating the PDF?
@@cutlooseentertainment any updates please?
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.
Good 👍 thank you 😊
Great
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 :)
wonderful! especially liked the way the whole video went in a stepwise fashion, and often retrograde!!!
Amazing tutorial!! Can you please suggest a way to password protect the pdf file?
nice, very nice, big very nice!!! thank you!
Hi! Amazing tutorial, do you have a related video where it automates the sending of the PDF files to emails of each person?
This could be a great tutorial if you could share the code :)
For those who understand code less but understand step by step action
Absolutely amazing!
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?
Hi, thanks for this knowledge . Could you tell me why I received this message ''data.forEach is not a function'' ?
Hi, I am getting the same error,
did you solve it?
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?
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.