Automate PDF creation Using Google Apps Script - Aryan Irani

hey guys welcome in this video i'm gonna be showing you how to automate pdf creation using google apps script the test case that we have for this video is as follows student details and marks in two to three subjects is stored in a google sheet and the template for the mark sheet is stored in a google doc now the teacher wants to create custom mark sheets for each student doing this manually will be very hectic for the teacher so we will be using some google apps script to automate this process let's get started [Music] the sheet that i have here this contains the name of the student roll number their marks in physics maths chemistry and followed by the email address this is the sheet this is the data that the teacher has and this is the pdf template this is the template for the mark sheet that has to be emailed to the students using some google apps script automation so it's time to write the script let's open the script editor by clicking on tools and then clicking on script editor here you can see that there's a function created just clear that out now let's just create a new function and let's name it as create underscore bulk pdf or by bracket and close it so first we need to get all the data from the sheet so in order to do that let's just create a variable called variable sheet in that i'm going to be using the spreadsheet and i'm going to get the active spreadsheet that is there close brackets and i'm going to get the sheet by name here you have various options you can get the sheet by ide and etc you can get the active sheet but just to be sure that you have the right sheet always get sheet by name so the string so you need to go back to your sheet and here it's named sheet1 you can keep it as one of marks just go here command copy if you're using a mac and ctrl c if you're using windows just click marks and put a semicolon now we have got the sheet here next we need to get the data so i'm going to create a new variable called data variable data is equal to now the sheet so we're going to be doing sheet dot get range so we need to specify a range from which the script will get the data from the sheet so we're going to get range so here you can see that you need to say the row and the column so now we don't need the first one right we don't need the name of student role number physics math schema or the email address so we're going to be starting with the second row so i'm going to write 2 comma then you can see you have to specify any column that is a starting column where do you want to start from so i'm going to start from the first column that's for sure so just write start from one then now you might have endless rows and columns of data this this this data might go to the 40th or the 5th 45th line so in a in order to going back and changing the script every time you can directly just do s the sheet dot get last row function what will this do even if a new entry comes here every now if so if i say i have ram okay ram is the new student his roll number is c41 and he's got 23 he's got 21 and he's got 19 and his email is ram 123 until gmail.com so even if i continue adding data here it will always get using the get last row function just close the brackets and now you have to specify the number of rows and the column so minus so i'm going to be writing minus 1 comma how many columns do we need to go we need the 1 2 3 four five and six so it's going to start zero one two three four five so i will write six here and now you specified the range but now you need the values so you're gonna use the get values values function here close the bracket before running your project just go ahead and save it so we're going to say pdf creation hit enter it's your project is saved and it's really now it's time to run this function before that we now we have to check whether we have got the data so in order to do that we're just going to do logger.log that is like printf and stuff just to print out the data that we have got into the script just hit save and now click on run before your code actually executes sometimes you have to accept the terms and conditions and here you can see that the execution started you've got all the data here and the execution is completed now let's validate whether the get last row function works now i'm going to go ahead and add another one i'm going to say it's sam um sanjay his role number is c42 he's got 12 he's got 15 he's got 70 and his email is sam 2 3 4 actually gmail.com okay now we go back to the pdf creation we run it again let's see if the data comes here you can see sam j so the get last row function has successfully worked we have now successfully got all the data from the sheet to the script it's time to get the variables individually in order to do that we are going to be starting out with the main headers that we required like the physics the physics maths and chemistry so we're going to be starting out with getting just this if you want to make a comment you can do double slash i'm going to getting the subjects create a variable subject sub 1 it is sheet dot get range so the get range i will be using c1 so if i do see one here followed by get value since this is just one we're going to get value and just close it here this is the same for all the the rest of the two subjects that are there i'm gonna paste this here just change this to two this is three this to d and this to e so we have got all the subjects just uh now in in order to check whether you have got everything we just do a logger dot log of subject one and uh just copy this out in order to check and subject to and subject three and just now now we can go ahead and print it but before doing that let's just comment out this login data now saving it let's run the project you can see we have got the headers physics maths and chemistry we have successfully got all the subjects that are there in the google sheet now now we will be using the data for each function in order to get all the details like the name the roll number and the marks code by each student in the paper so the for each function will work it will go e for each row it will check the name rule number physics marks chemistry and then the email address so in order to create the data for each function to write data the data that we have here so it will pass the data for each row so we are going to be doing data for each there is a for each function here and we write function this is the normal syntax the row and then i so the first the first variable that will be creating is variable name so the variable name is the row 0 because this is it's going to be starting from 0 1 2 3 4 5. so the row 0 it's going to start now you might want to check it so we will be doing logger.log again log name let's just comment out this now we need to go ahead and run it to check whether the name is come right here you can see all the name of the students have come sam harper i'm saying j it's gone enough for each and it's printed all the names so now we know that our for each function is working properly now let's do the same for all the other variables where variable underscore roll number is the row number one and just if you want you can check it in the you just write all the variables and in the end you can click the run button in order to check you've done this next we have after rule number we have marks in physics math chemistry and then the email address so we're going to just do physics underscore marks that is equal to row number rule number two then we have the variable maths marks math underscore marks that is equal to row 3 then we have the variable chemistry that is chem underscore marks that is equal to the row number four and do this and in the end variable the last one that is the email is equal to row number five now we have successfully got all of it just in now we can do a small run to check whether we've got everything properly i'm just gonna copy this we're just gonna go and change this to match marks chemistry marks oops we forgot to change the name here came here now we have successfully got everything we can maybe just comment this subject printing out from here hit save and let's run the project here you can see all the data for each student has printed a proper for each manner so you can see sam harper result number is 33 he's got 15 14 16 you can check it here say 33 15 14 and 16. now we have successfully got all the data in the form of individual variables we have now successfully got all our data in the form of individual variables that we need to replace with these ones now we're going to be going ahead and creating a new folder called mypdf under which i'm going to be creating two other small folders i'm going to be naming the first one a stem and the second one as pdf i will be explaining you later in the video why am i declaring two pdf folders we're going to go back to the script editor now we need to get or get the id of the google doc in order to replace the text and replace the variables in the doc we need to get the google doc by id and the folders by id in order to store the pdfs in it so we're going to go ahead and write variable i'm going to say doc file is equal to we use the drive app drive app.get file by id then i'm going to just get this id here you can copy this id from here it's just be on document and just before edit just copy it and paste it here and just add a semicolon next we are going to be getting the temp folder just declare this as temp underscore folder which is equal to drive app oops sorry driver drive app dot now this time since this is a folder you can get the folder by id and in order to get the temp folder by id we need to open the folder and just copy this last part that is there here let's paste the link semicolon now we're going to be getting the main pdf underscore folder which is equal to drive app dot get folder my id and we're gonna again go back to the folder and open pdf folder and just copy the link with the link and paste it in here so we have got all the links of the doc file the temp folder and the main pdf folder where we are going to be storing all the automated pdfs just click save you're done now i'm just going to go ahead and create a new function clear this out if you want to go down function i'm going to name this function as create underscore pdf close bracket open them up let's give a little spaces now i'm going to be declaring attempt file so so whenever we are going to be replacing all the data in this we cannot make changes in the original doc so in order to do that for each and every student we're going to be creating a main pdf and a temp pdf so i have to so in order to make a temp pdf we need to create a temp file of this google doc so before we move forward we need to pass all the variables in this starting from doc file go here pass it here click on temp folder go down again pass it so we need to do this for all the pdfs we don't need to pass the sheet we just need to go to the subject one the subject two and the subject three and the rest of the sub this the name is there then we have the roll numbers then we have marks in physics marks in chemistry and maths marks and the email address of the script so we have got all the data into this new function that we are creating after we are done writing this script for the function we will be calling the function in the above create above pdf function so now first i'm going to be creating a temp file that is the copy of this in in order to store the data so that we don't make any changes in our main pdf template so we're going to do this i'm going to write temp file and since we have got this doc file here so we're going to write doc file dot make dot make copy then we have to specify the folder where we're going to show so we're going to be storing it in the temp folder so this the main the pdf folder will have all the proper pdfs the main pdfs and the temp folder will have the temporary pdfs next we're going to be creating now we have to open the temp file so we're going to be doing that we're going to just create another variable called temporary dock underscore file then we now have to use the document app in order to get the body and stuff to get the document app dot open by id and we don't need to specify the id since this is just a copy of this we're just going to write the temp file dot get id get id is done now we are going to be declaring a variable called body is equal to temp doc file not get sorry dot get body so this gets all the contents of the temp doc file that we have created now in now we have the variables we have the temporary doc file we have the pdf and stuff but how do we replace the data from this sheet for each student into this pdf template to do that we are going to be using the replace text function so we're going to be getting the temp doc file dot get body close the bracket and we're going to be using the replace text function so replace text function first you need to specify the search pattern so here we have the search pattern for name is bracket name bracket close curly brackets so we need to first specify the search pattern and then we have to specify the variable that we're going to be replacing with it so i'm going to open the quotes i'm going to write name i'm going to close it and then you have to just put a comma and then use the variable that you're using so for the name we're going to be using the name variable and just end it so this is going to be same for all the variables that we have all the variables that we want to replace in the pdf dock so we're going to do the same thing for the roll number so you can just copy this down just so for the name so now for the roll number we have rol so you can cop roll you can copy it from here and paste it here and then here you just write roll number next we have the subject one so just paste it here subject one and the variable that i want to be replacing is also subject 1 the same thing for this one is going to be subject 2 and the replacement variable is going to be subject to same thing here subject 3 we're going to be using sub 3 after that we have the marks in physics maths and chemistry so physics will copy the search pattern we'll paste it here and just declare the variable physics marks same thing for maths we do the maths then and the maths variable that we have here is maths marks and the last one that we have is chemistry so we're going to be pasting chemistry and doing the chem marks just hit save you can remove this last one is not required so now that we have got the body we have replaced all the text it is time to close the dock so we're going to be clicking on temp dot file dot save and close you can see the function there and just put your semicolon now the doc that we have created is a word we need to convert this into a pdf so that the students might not make any changes in the doc also so to do that i am going to create an another variable called pdf underscore content that is equal to temp so now we so all the data that is there in temp doc file is saved in temp file so i'm going to be using the temp file dot get get eyes and i'm going to be putting here so now here we are we are come we are converting our dock into a pdf form so you can write mime type dot you have all the options you can do it into a pdf so we have converted this into a pdf now we have now we have only converted into a pdf we have not yet transferred it to this temp the main pdf folder where we want to transfer it so in order to do that we're going to create another variable this is the final variable that is called pdf underscore file so now we are going to be using the pdf folder this is the main folder where all the final mark sheets will be saved we're doing dot create so here we will be using the create file function and here we have to first pass the we have to pass the function here we're going to be doing as pdf content that we had the variable that we created about and now you have an option to set the name of the file that you want if you want you can have custom names for each student or you can just keep one standard name so for this case i am just going to be keeping a standard name that is mark sheet and after you're done just click this now hit save so now you know that the main pdfs will be stored here and the temporary docs will be stored here but the teacher might be using this code many times so this will create space in the temp file and will use up the space in there so in order to reduce it in in order to delete it we're going to be using we're going to be deleting all the files that are there in the temp folder so we're going to be doing temp folder dot remove file function to the remove file function and here we have to pass the drive app dot file so which file do we want to delete it is the temp file that we have to delete so now whenever you see you find the temp folder empty and the pdf folder will contain all the details gonna clear this out now we are done with creating the pdfs it's time to mail this to the students so in order to do that i'm going to write the mail app in order to send me an email we're going to be using the mail app dot send email function here you can see you need the message and the object so the message that is going to be that's going to be the email that is the email address of the students followed by a little subject so i am going to say name or have name plus a little space between the name and the rule number so as the subject will be name roll number comma if you want some body in it you can write these are your marks that's simply going to write this much and then just have a comma after this then you open it since now we have to add an attachment so you need to open a curly bracket to open it here and just add a semicolon at the end so here we're going to be using the attachment attachments i'm going to do and open it it's going to be a pdf so it is a pdf file this is the final file that we're going to be sending to the student so you're going to go pdf file dot get as mime mimetype.pdf we're going to be again doing it mime type oops dot pdf and you can see it is converted to the other so we have to specify the attachment is in the form of a pdf we are done now we have successfully created both the functions but the second function that is the create pdf function will not work unless we call it in the above function so we're going to copy this part and just copy it and go up here and paste it out here in the end don't forget to add a semicolon just hit save and we're just going to clear this out here so this will cause issues when we run the function this last one just hit save okay everything is ready now we have written both of the functions and we have called the function in the first function now it's time to run your code so you have to select the first function that is the create bulk pdf so just click on that and i'm going to click run you can see that the execution has started so we can go to the my pdf section and i can click on the pdfs and here you can see that our pdf has successfully been created now i can see that the 15 14 16 his name has come the role number has come now we need to check whether it has been emailed to us so we're gonna go click on email check my email and here you can see that i have got his i bought it as the ram saying c034 as the sub subject of the email and these io marks is the body followed by the pdf you can see that the execution has started and has completed successfully if you go to the pdfs the my pdf option and you click on the pdf folder you can see that all the pdfs have been created here thank you so much for watching this video i hope you have clearly understood how to create pdfs using google apps script i will be leaving the code links in the description and i'll be leaving some other details which will be helpful for you thank you so much for watching see you next time

Channel: Aryan Irani

Views: 5,353

Keywords: Google Apps Script, Google, App Script Tutorial, How to automate PDF in google app script, google apps script, first script, getting started, tutorial, Google Docs, Gmail, App, Scripts, Editor, Tutorial, Google Sheets, Excel, VBA, script, language, macro, javascript

Id: 8Nmtug2Fmn8

Length: 28min 6sec (1686 seconds)

Published: Tue Apr 20 2021