Learn Google Apps Script Project Lesson Create PDF from Docs Using Google Sheet Data send Emails

project that we're going to be creating in this lesson is going to be using a spreadsheet as a source for the data populating a google doc that's going to be used as a template creating a pdf that's going to be saved to the drive and then also emailing that out to the user if we add in another user we press the email out and send email it'll run the script in the background using the data from the spreadsheet it's going to populate and create a brand new dock from the template which will look like this so there's the name mike smith there's mike and five so it's populated the data from the spreadsheet into the google doc template and then sent that as an email with the pdf as an attachment and also update it and customize the email that was sent out and that's all coming up in this lesson log into your google account go over to your google drive and we're going to start by creating a brand new spreadsheet so there's a new button on the left hand side select the new button and from the new button go down to google sheets and this will open up a brand new google sheet let's give the google sheet a name i'm going to give it a name of just the data tester and this is going to be where we're going to have the source content for creating the pdfs and we're also going to need to create a google doc so go back into the drive select new and under the google docs we're going to create a brand new google doc the google doc is going to be used for the template so give it a name and i'm going to call it template tester and we're going to populate the information within the google doc using the data from the data from the google spreadsheet so give the columns some headings i'm going to have one called id the second one called first the third one called last sort of the first name last name and email so these are the fields that we're going to be tracking and updating within the spreadsheet and then we're going to be adding a fifth column and the fifth column is going to be where we're going to track that the emails have been sent so the pdfs have been created and the emails have been sent also within the drive we're going to create a brand new folder and this is where we're going to store the pdfs as we're creating them so i'm just calling this tester and you can give it whatever name you want i'm going to select the template that we created and the sheet that we created select both of them drag them into the tester folder so that everything is sitting within the one folder within the tester folder create another brand new folder and we're going to call this pdfs so this is where the created and the generated pdfs are going to go so that we can separate our template and our data from the actual pdfs that get created using the google apps script let's set up the template so go into and open up the google doc so this is where you can add in a company logo and this is all what's going to be going into the pdf that we're going to be creating so you can also give your company name and any other graphics or images that you want and then set up the template so you can add in various fields that you want populated so in this case i'm going to be populating the first and the last name so what i'm going to do is to in order to distinguish it from the rest of the content i'm going to add curly brackets and i'm going to uppercase the fields that i want to update and populate and then these fields are going to be matching the field names here within the heading of the spreadsheet and we're going to do a replace within the apps script code in order to replace the fields with the correct data so create the document and then you can add in some additional content into the document so here i'm just typing in congratulations i'm going to use the first value field here in addition to the id field so that's going to just get the data coming from the spreadsheet and then i've just added in a signature there so we're going to be generating these from the template from the google doc template as pdfs using the google apps script next open up the google sheet and we need to populate some data into the sheet so give them some ids there's first names last names and then the email addresses when you're using google gmail you can also add an append to your email address by using the plus one so this is just my testing gmail account so i'm going to be sending the emails as they get generated to these accounts we're going to also be using a bound script as we're going to be adding a ui menu into the google sheet for easy way to send the emails so anyone can open up the sheet and they'll be able to send that so we'll be doing that in at the conclusion of this lesson so under the extensions tab select google apps script and this is going to open up the script editor give the project a name i'm going to call it tester pdf just give that the name i'm also going to make this bigger so at any point you can open up the by right clicking within the editor area the command palette now i'm just increasing the font size of course you don't have to do this but this is just so that the code is a lot more readable within the editor so i'm going to do it one more time all i'm doing is opening up the command palette and selecting one of the commands and one of the commands in the editor too is to increase the font size so once you've created and once you've accessed the editor we're going to create a function and update the function name i'm just going to call it sender and from here we're going to need some values so what we want to do is we want to select the spreadsheet and we're going to select the sheet contents so that we can iterate through the sheet contents and go through them line by line row by row in order to send and generate the documents from the template so we're going to use the spreadsheet app service and then open by id now you can also open the active sheet as well or you could open it by id both are able to open up the same sheet document as eventually we're going to be adding this within the ui menu but if you don't have it within a bound script if you're doing it within a standalone script then you need to use the id in order to identify the spreadsheet i'm going to set up the sheet id as a global value so that's just the sheet id that i've got from the web url and i uppercase usually the global values so this is just a variable that we can then use in order to open up the selected sheet and we also want to get just the first sheet so rename the first sheet to data and in order to get the sheet object we're going to get sheet by name and we know the name that we've just set to data so this is expecting a string value to be set as the sheet name let's get the data from the spreadsheet and we're going to create another variable just call this one data so now that we've got the sheet object and using the get data range will allow us to select all of the cells within the spreadsheet within that range that have data so it's going to select all of the values that are currently basically the ones that are highlighted here going to the last column in the last row with any data within that range and then we want to get this data as values so we're going to be using the method get values which is going to return the data from the spreadsheet into an array-like format and we're going to output that data within the logger log let's run the sender we're going to also have to accept permissions for the application in order to run the script and access the data from the spreadsheet select the google account that you want to authorize to run the script then go down to the bottom where it says go to the tester pdf which is the name of the google apps script that we're using these are the permissions that we're currently permitting and then just select allow which will allow the spreadsheet in order to access the content using the google apps script so here we've got the data that's sitting within the spreadsheet we also want to get the data and we only want to get and use the data within the rows as we know that the first row is going to be the heading data so we don't need that and we just want to return back the rows so using a data slice 1 will slice out only the rows of content that we want to iterate through we also want to then create a loop with the rows data so we can get rid of the logger right now because everything is working so using the for each method we're going to loop through and get each row of data and we're also going to pull back the index value of those rows so the four each is an iterator which allows us to loop through an array of data and that's what we have for the spreadsheet so this is going to return back each row of content and if you want to see the contents of each row you can use the logger once again so run through and it returns back each row and these are going to be the columns are going to be accessible using their index values in order to return back the data that's sitting within the column next let's select the dock and this is the dock that we're using as the template so we're going to give it a global value for its id go over and open up the dock and then within the web url that's the easiest way to select the id of the file and each file will have a unique id so this allows us to select the document and select the document using its id so what we want to do is we want to set up the template and using the const this is the document template that we want to use using the drive app i'm going to get file by id and this is where we would use the document id in order to identify the file that we want to use and because we're going to be updating the file what we want to do is we want to actually make a copy of the file and we're going to be updating that duplicated file and not working off of the template so that's going to leave the template intact and allow us to duplicate the file so let's go ahead and we're going to make a copy of the file so we've got the file object as temp and we also want to place it within the pdfs folder so this is our working folder that we're going to be working out of so go ahead and grab that id of the folder id and anytime you want to place your documents on a particular folder you need to have the id of that folder in order to place the documents otherwise it will just be placed within the root directory so next we're going to select the folder and this is going to be the folder where the files are going to go into this is also using the drive app service and then we're getting the folder by id and this is where we would add in the folder id that we just selected so that we can identify the location of where we want to copy the new file into and we want to make multiple copies of the file so for each row we've got new data that we want to add into the file so this is where we can use a make copy so we're selecting the template file and using a method called make copy and where we're placing it we're placing it within the folder object so what this will do is this will make a copy of the template file and place it within the folder so we can run the sender now we're going to have to accept permissions once again because now we've added in new permissions whereas we're using the drive app service there's the permissions that we've added and as we let the script run through what it's doing is it's going through and it's making a copy of each one of the documents so this is a copy of the template it's not the original template and it's running through and doing four copies of it but because the name is going to be the same we're going to have just the copy of template tester because we haven't specified a name so there's our four copies they're all going to be exact copies of the template so we can remove those for now and that was just to show that we are creating a file on each iteration so what we want to do is we want to select the file as a document and then update and edit the document body so using the document app service and then open by id and we have the file and we can get the id of the file by using the get id method so that's going to allow us to open up the document object and then in order to edit the content we need to select the body of the document so we can use the get body method in order to select the body of the document and what we want to do is we want to replace some of the contents that are contained within the body now in order to do this we have to loop through each one of the heading values so that we can populate and do a replace of that data so for now let's do one where we're going to take the body object and then using the replace text method we need to identify the value that we want to replace so the string value that we want to replace so right now we're just going to replace first and that's going to be the string that we want to replace that it's going to be checking for and looking for and then the value that we want to replace it with and we can get the name values and these are going to be located within the row as under the first row there so within the row using the index value of 1 we can replace the text of each one of the bodies so let's go ahead and run the function and we need to once again accept permissions because right now we're going to be using the document app service so we've got new permissions for the script in order to be able to run properly so we're once again we're creating a bunch of pdf a bunch of documents that are going to be using the template but this time we're going to be populating the data and we're doing a replace of the word first so it's being replaced as we're opening it up there's the values that are being replaced and this is once again coming from the template so now it's created four new documents but it's replaced the first word name so we want to also make this a little bit more dynamic and we already have the values that we want to replace within the heading of the sheet so let's update this to make this more dynamic let's go ahead and we're going to populate the content of the document with the sheet data and we have the information contained within the data using index value of 0 so that's going to provide the first row of content from the spreadsheet so data 0 is an array of the first items in the sheet and we're going to loop through each one of those and that's going to return back the heading value and i'm going to also capture the index using the value of i so again we're iterating through all of the headings and that will be id first last email and sent and we're gonna make some updates to them so first we're gonna take the value and this is gonna be the header information so we're going to be updating the heading and using the javascript method of to uppercase will allow us to transform the content of the heading to be uppercase and then that way we can use it within the body replace in order to replace the content so here we've got hard-coded the word first so let's update this and i'm going to be using the back ticks so those are the template literals and what we're going to be doing is replacing the value that's contained within the curly brackets so we've got the curly brackets within the string and then we want to replace it using the header 1 value so the header one value is that value that's coming from here you can also do that within one statement if you're caught do the heading and then to upper case you can also do that within one statement as well so what this will do now is this is gonna loop through and it's going to update the heading let's also set some unique names for the document so taking the document and set the name and this is just expecting a string value for whatever we want to set the name to and i'm just going to use the value of row 0 so that will be the id and then the value of row 1 as the name of the document so that will rename the documents and save that and go over to the pdfs area what we're going to do is we're going to remove all of those and run the code and we're going to see what that looks like so now it's giving us names that are coming from the sheet and it's renamed all of the document copies that we've made let's check to see if we've had updates so it looks like there's uh what it's doing is it's still adding all the values as row one so we want to try to get around that where instead of the row one we can use the row with its index value as we're looping through and we're going to get the index value so that was the update there so now it will populate the data properly and dynamically and that's going to be once again coming from the spreadsheet data to get the document as a blob that will allow us to then use that and email out the document as a pdf so using the doc object select the get as method and within the method we're going to select it and get the meme type as pdf so this will return back a blob formatted pdf version of the document so once we're done with the document we can then close the document and the reason that we're using the save and close method will allow us to finalize the replace of the text and then we can select the document and use the document with its updated version so what we want to do is we want to send this out as an email so select the email address and the email address that it's going to is going to be contained within the fourth column so it's going to have an index value of three so using the value from a row three we'll get the email and then you can create the rest of the content for the subject and also customize that so what i'll do is for the subject we'll just use the values of the row 0 and row 1 and then i'll add to it new file created and that's actually just going to send the pdf version of the file and then within the body the message body we can have a greeting so i'm just using the back picks there and we can say hi and then using the value from the spreadsheet once again we're going to use the value from the row with the index value of zero welcome we've created and now using the mail app service send the email to the user and mail the send mail can take an object formatted structure so we first need to do the to email and then the subject and this is just going to take the subject value that we just created using the variable and then it's got an html body so this can also be html code within the message body value and these are all just coming from the values that we have up here so the message body and then to set the blob as an attachment so we're attaching it as within the attachments and this is expecting an array format so you can have multiple attachments and here's where we can select and send the blob and we're just going to use the blob object and then the same that we did over here where we get meme type pdf get as meme type pdf which will create a dynamic pdf of the blob and it's going to send it to the email address and that's the email there so i don't have anything yet in the inbox we need to accept the permissions as we've added in new the mail permissions and now it's gonna run through and it's gonna send an email to each one of those values that we have within the google sheet and there's the first email that came through dynamically created the pdf and populated the pdf information and sent it to us so notice we're still making them as docs so what we want to do if we want to save the pdfs that we're constructing that we're creating dynamically we can create the pdf object from the blob object and then we can just save that within the folder as well so here is where if you want to add in the pdf and if you want to do something with the pdf or you can not use the setting as a variable and just use the folder and this is the folder that we're dropping it into so what we want to do is we want to create a file from the blob and we can set a name of the file and because this file is going to be a pdf so set it as extension pdf and we can use the same string values here for the name of the pdf so what this will do is this is going to create a pdf and i'm going to be then removing out the files that we're creating so once we're done with the file object what we can do is we can do a file and set trashed to be true for the file and let's uh move this in so where we're setting the file when we're making a copy we can also then clear out and remove the file after we've finished with it so that will move the docs and we're actually only going to be creating the pdfs from that line of code so let's run through it again and now it's going to be creating pdfs and also emailing out the pdfs and then also removing out the docs as they get created so there's the new round coming through the updated pdfs and then we're also storing the pdfs into the drive and these are the populated ones that we have and this is all with the data coming from the spreadsheet so if you want to store them as pdfs you can keep them as pdfs using the file or you can also just send them just as emails so you don't have to store them into your drive so then you don't need this line here or you can set the pdf and set that to trashed and that will also remove out the pdfs from the folder but it will just still create them and once we refresh that that last one there is gone let's update the sent column and that way we know that the data has been sent and we can also put a condition to check to see if the sent column has a value and if it doesn't have a value then we know we can send the file and if it's already been sent then we're not going to send it again because we're going to be adding the ui button for the user to be able to click it so we don't want to keep sending it if the data and if the message has already been sent so let's add in another statement here where we're going to be using a variable let's call it tempo and from the sheet we need to get the range there where we want to select the value and we do have the row values as we're iterating through we've got an index value that we can then use to indicate what the row is and because the index value is going to be 0 base but the rows start at 1 and we actually want to select and select the row starting at row number two we do the index and then add two to it and then we want to select the column that we want to update so the column is going to be have an index value a row and column value of five and we just want to it update one row and one column value so it's going to select the cell immediately to the left of the email which is going to be going under the sent column and then we can select the tempo object and set a value once we have the range and we'll just put whatever the current date is so that will populate that information and then we just need to add in a condition that's going to check to see if the value in row number in the row with a value of 4 has a value or not and if it doesn't have a value then we can continue to make it if it does have a value then we want to skip over making the document for this value so let's add in the condition as we're iterating through that we've got row with the index value of 4 and we want to make sure that it's blank and if it is then that means that we can run through this creation and create all of the files otherwise we're just going to simply skip it so i'm going to just put paste that in and now if there's a value within the sent it should skip those ones when we run the function so we should only be sending to those two emails let me clear out the pdfs once again and run the function and we can also go in the emails so it's only gonna be populating to those two so that's it's already picked up the second one there and it should populate the date into the row so it populated the current date into the row and then it skipped over the other ones because they've already been sent so now it's only just created the two where we've got jack and new as the first names and then also only sent out to those two so that allows us to run the function multiple times and if there's a value within the sent it's not going to try to send out another email once we have the function working as we expect and it's working properly then we can add the last step where we're going to be adding the function to the sheet ui so that anyone can send emails using this google apps script so we're selecting the spreadsheet app and then using the get ui method and this is going to return back the ui object and this is only available within the bound script so it's not going to be available within a standalone script as it won't be able to select the active item we want to create a menu object so this is going to be the label on the menu i'm just going to call it email out and then the tab in the menu we can add a bunch of tabs and we can set that using the add item and this will just say send email and you can customize this as needed and then it also requires a string value of the function and we name the function sender so that has to correspond to sender so these have to be the same and this is also expecting a string value of the function name and then lastly we add to the ui so that will add that item into the menu item so whenever anyone opens the spreadsheet it's going to run this trigger and it's going to allow us to run the function that we've just written within the google apps script without having to open up the script and using the script editor in order to run it so this is a much more seamless process that can be applied for other users and in order to trigger the on open we can run the on open function so that will simulate the on open adding the item to the menu or we could refresh the spreadsheet within the browser and that's going to actually close the google script where we could always open it under the extensions apps script and now we've got the function for email out so let's uh remove the value of sent from the column e so now that that one's just got a blank value so we should be able to just send the value to that particular row of data by pressing the email out and send emails it's going to be running the script in the background and then once it's completes running the script it should have that updated value it's got the pdf that was created within the folder and then also it sent out the email with the data from the user and sent that out as an email so that completes and concludes this google apps script project where we are able to use a doc as a template that we populate data from the spreadsheet and create a pdf from that data and the template and we also send that as an email and then update the spreadsheet that that email has been sent

Channel: Laurence Svekis

Views: 14,941

Keywords: Laurence Svekis, Svekis, Courses, Udemy, Learning, Coding, Learn code, JavaScript, HTML, CSS, JS code, Code example, elearning, Learn to code, how to code, learn web, web design, web development, application development, coding course, online course, responsive design, modern web design

Id: ikf-oJsStd4

Length: 32min 46sec (1966 seconds)

Published: Wed Jul 06 2022