Google Apps Script Projects for Beginners Create PDF WebForm

1. SkillshareIntro: Welcome to the Google Apps Scripts projects course, where I'm gonna be showing you how you can use Google Apps Script in really useful web projects. My name is Lawrence, I'm gonna be your instructor for the course. I come to with many years of web development experience, as well as I'm a Google Developer Expert. That means I really enjoy working with Google Apps Script got a few projects that are included within the course. So there are PDF downloadable guides to help you along the lessons of the course. The first project that we're gonna be looking at is going to be creating a project that's going to allow us to get a good feel for what we can do with Google Apps Script, selecting Google Docs, creating a Google document, updating the content within the Google document. Also how you can copy a Google document, use it as a template and then getting data from a Google spreadsheet, populating that data into a Google Doc, using it effectively as a template that you can generate files from generating content on the fly using the script with content and data coming from the spreadsheet outputting into brand new Google Doc files, source code is included. And I do encourage you to try that code as you go through the lessons of the course. So let's get started coding and building some web applications with Google Apps Script. 2. 1 QuickStart Google Apps Script: It's gonna be an introductory lesson. We're going to be introducing you to how to create Google Apps, Script, select, and access the editor, some of the features that are available within the editor, and then how to write and update the function in order to run a block of code. What this code is going to be doing is it's gonna create a string with my name and then output creating a document with only parameter within the document is sending in the string value. So what it's going to be doing is it's going to be using the document app and the create method in order to generate a document. And then once that document is returned, it's going to add that into the variable called doc. And then once we've got the object information within dock on that newly created document, then we can get values such as the ID using the getID method. It will run the application. What it does is it generates a brand new doc using this name and returns back the ID. And the ID value is going to be the unique value for each one of the Google files once it generates the document. And sometimes you have to refresh or you have to wait a few minutes. And I can now click the document and I can see that the ID that I have within the log is actually going to match the ID that I have for the document. The Google Apps Script, right-shift powerful way to interact with the Google services. Go over to Google.com, forward slash script, forward slash start. And of course you are going to have a Google account in order to login and create Google Apps Script. You saw also login to your Google account as well. Once you go to the website, you can select Start Scripting. And this is going to open up the scripting interface dashboard where you can select to create a brand new project. There's also some helpful information where you can learn more about Google Apps Script. And the website is script.google.com, forward slash, forward slash Start, select New Project. That's going to open up the project dashboard. And this is where we can write and create Google Apps Script always suggest that when you do create a new project, give it a meaningful name. So I'm going to call it test1. And this is gonna be the op script that we're gonna be running some tests with in order to introduce you to more with what you can do with Google Apps Script. So by default, the interface, we'll start with a default function. And the purpose of the function is that this allows us to select the block of code that we want to run. We can create additional Google files, was script files and HTML files. And we're gonna be talking about that a little bit later on in the lessons. The mean scripting interface is within the editor. There's also an overview. You can set triggers which allows you to do some automation for your Google Script. There's executions so that you can use when you're debugging. And there's also project settings. You can cover those and interact with those, making updates to the project options. There's also where you can share the project. So anyone that you share the project with will have access to the Google Script. There's different levels of sharing that you can select. You can select users as either editors or viewers. And editor will be able to update script and a viewer will only be able to view the script. You can also share the link to the script file and select that who has access and who can open up the link. And that's down there at the bottom. And this is standard for any Google documents, standard sharing. There's also a support information. You can get documentation training updates, can take a quick tour of Google Apps Script. And there's also an option to send the feedback. Just like with other Google Apps, you can quickly select other apps. So you can go over to Dr. you can go over to YouTube, Calendar and other Google apps really quickly with the navigation interface, going to zoom the editor font in a bit. You can also select and get the command palette pressing F1 or right-click anywhere on the open space. So that gives you the command palette. And from here, you can select the various commands, also have shortcuts for those commands. I'm just doing an editor zoom in. So that will allow me to make the font larger. So it's a little bit easier to read while I'm doing the recordings of the video. So make it slightly larger, and that will make the font size larger. Also within the command palette, you can open up the command palette and search out for different functions that you want to make use of. Those these are really useful once you get more familiar with the editor in order to code faster. Now let's create a function. And this function, we want to create a document within the drive. So I'm gonna call it a maker one and just give it a meaningful name. So we're making a doc within this function and set up a variable. What's gonna be returned back is gonna be the document that we created. So you don't have to return back to the document. But if you want to get more information about the document, then that's where you can set the document properties and get document information from the document object using the document App Service. And each service within Google Apps Script has a number of different methods that are available. You can select those methods. And in this case, we wanted to create and creating. And the only parameter that it does require is gonna be the document name. So we can also set up a step separate value for that. And this is just gonna be a general string value where we're gonna be using that in order to create a document name. I'm going to just use my name as the name for the document. And then within the Create, Let's go ahead and we're gonna create the document using the document name. We want to get some information about the document that we're creating. So we'll show you how you can do that within the logger. When you use the logger, this is a way also to debug your application. And you can output details within the execution log. Once we've created the dark object within this statement, we can then get some information from the document object. So if we want to get the id value of that, we can use the getID method. What this will do is this is going to output the ID of the document that we've just created. So save the code. You can go up here in the save project, or you can use the shortcut in order to save it. And that's just Command F will allow you to save it. And then from the selection where you've got the functions, you can within the drop-down menu, select the functions that you have. The function names are going to correspond with what we have for the functions within the code. So right now we only have the one function called maker one. So that's our only selection. Select the function that you want to run, the block of code that you wanted to execute, and then select the Run button. Since we're running this the first time, we're going to have to accept permissions. We're using document app and this is going to require it. This is a service that's gonna require our Google account in order to interact with some of the permissions within the drive, as well as being able to create documents. So we can select to review permissions, select the account that we want to use in order to run the application. Google will give us a message that tells us that this application has not yet been verified by Google. And so there is a verification process that you can submit your apps to. If you do have other people that are gonna be interacting with the OPS, then you are able to do that. So selecting the advanced will open up and let you know who the developer is of the app. So this is my Google account that I'm using in order to create the application. So I'm okay with it. And I select the go-to Test1, which is going to correspond with the name of the application that we've created. Once you select that, it will let you know what permissions are being given to the application by its name. And the count that is gonna be providing those permissions is gonna be this Google account. So that's also the one that I'm creating the app with. If you want to remove permissions, you can go to my account.google.com forward slash permissions, or you can click the Google Account link within this pop-up window. Once you've read through the permissions, you can select Allow. And this is going to allow the script in order to execute and output the results we get within the log. And this is an execution log. So anything you logged within the logger log, it outputs within the execution log. So this is where we're outputting the ID. So this is similar to what you would use with console log, where you can debug your application, get values from within your application output variable information. Select that ID. And this is going to be representing the ID of the document that we've just created. If we go over to the drive. So there's the document that's been created. It used the name for the document name that we provided. And this is the script that we've created. So the script is test1 and the document is Lawrence swatches. You can click the document, open up the document and take a look at the ID within the web URL. And this is actually going to match the ID that we just selected from the execution log. Because what we're doing within the execution logs, we're selecting the document object that we just created within the previous statement and outputting, returning back the ID. And there's a number of different methods that provide you advanced functionality that you could add in to your scripts in order to get content back once you've created the document object. So go ahead and try it out for yourself. Create your own app script, and create a function that's going to generate a document and you can be ready to move on to the next lesson. 3. 2 Update Doc with Code: Creating an element and selecting the already created a document, and then updating some of the properties, Adding appending paragraphs to the document body, and then selecting style properties and setting those as attributes to the elements as they get created, generating new page elements with the style properties being associated with those. The previous lesson we saw how we can create a document. Let's create a new function and we're going to create a document. And then we're also going to update some of the contents of the document, creating a function called maker to. And I'll just give this a test to name of test2. It will create the document and we want to select the ID of the documents so we can get the id value of the document by using the getID method. We can just copy that and that will return back a value of the ID. Next, we want to do is we want to use the document object. Within the document object, we wanted to select the body of the document. And so this will give us the body. And now we can then interact with the content that's contained within the document body. So selecting the document object, we use the method called get body that will return the body of the newly created document and allow us to interact with the body. Once we have the body, we can select and we can append a paragraph into the body. So if we don't need a callback, we can just do an append paragraph. And the paragraph is gonna be expecting a string value. And this is where we can use the ID of the content. Let's set up and create another variable, and I'll just call that temp. This is where we can add content into. I'll use LET so that we can update some of the content. So if we want to set the ID value, we can use the template literal. So these are the backticks to the left of the one on most keyboards. And this is allows us to create a string, this string value, it will have the ID, and then the ID is gonna be brought in from the variable that we've created with the ID. And then we'll also output that into the log. So save that. And now we've got a new function called maker to within the drop-down select beaker two and hit Run that will execute and create a document. We still got the ID that we're tracking within the logger. We can go over to the drive. And within the drive now we have our new document that we just created. We can click it and open it and see that we've written content into it. And that's gonna be the ID value that we had within the Google Apps Script. We can also select this ID. You can also select the ID by opening up the file, going into the URL of the file. These are all going to be unique, and this is how you can identify the various documents that you want to interact with, code, with the script, selecting the ID and the ID is the important part where it gives us an ability to reconnect to that same document. We don't always have to create the same document. And if we want to update the document, that all we need to do is be able to track it by its ID. So that's gonna be expecting a string value that we're tracking as the ID and create a new function called update one. Next, we want to select the document object, just as we did when we created it, but we want to select it as dock. It's already been created and we can use the document app service. And we can then open by ID. And all it requires is the id value of the file. And that's what we already have here up here that provides us the document. And now we're at the same point where we've got the dock and we can get the ID of the dock. We can also get the body of it. This one isn't gonna be creating a new file. So we can just simply log out the ID, or I'll call this one idx so that we have a different value there for a different variable name. Let's run update one. So what it's doing is it's simply selecting the document and outputting it into the console. Once we have the document selected in order to get the body, we can get the body content just as we did before when we created the element. We don't actually need to output the ID because this is gonna be the same document that we're interacting with. And once we have the body, Let's go ahead and we'll select Update, appending the new paragraph to the body of the document. We'll just call this ID to, even though it's gonna be still using the same ID value. Let's select and update. We run the code and we get a second value there. And now we've got id2 with the new variable value that we've sent through and we've appended the new paragraph. Once we creating the paragraph, we can also select the element. So this is gonna call back and return back the object that we're working with. If we want to select out the element that we've just created. So that's gonna be creating a new paragraph. I'll give it an id value of three. This returns it back as an object. And the objects are all usable and they all come with different methods. Once we've created it, we can also update some of the properties contained of that element. So we're creating it as an element object set. We can get attributes and we can set attributes. So let's go ahead and we're going to get the attributes within the logger log. I'm going to output the attributes from the element that we've selected. These are the attributes that are currently for the element that we just created. And that's the new one that we just dependent. So we've got within the attributes, we've got background color null, foreground color of null. So let's select this as a value. I'm going to just comment that so that we can reference it when we're updating and setting some attributes. Now that we've got the element, just as we can get attributes, we can set attributes within the object in order to set attributes. So that requires the rounded brackets. And we need to specify which attributes we want to set into the document object. It's a lot easier to track this if we create a style object in order to track this as an object, because it's gonna require this content within an object format. So let's go ahead and set some of the properties for the background color. And because it's an object, we're using the colon and let's use a hex value. Whatever we want to set for the background color. You can set the different values for the properties. Selecting the foreground color as well. I'm going to set a hex value for that. And then once you've created the style object within the set attributes, add in the style that you want to apply to the element, save it. And now let's run the code and let's see what happens. Now when we go in. This is the new element that was created. So it's going to have the ID for, and it's got a green background and a white font. Let's create a second one. And this time we can use the different bold values as well. So if we want to set it to be bold, to be true. And also let's update this to six. That's going to be the ID six. Now we're setting these and we can also update the colors of different values. Save that. And let's run that one more time. Now we've made an update, we've made it red, and it's bolded the textContent. Go ahead and try to be ready to move on to the next lesson. 4. 3 Copy Google Doc with Code: This lesson we're going to be introducing the Drive app service. So this allows us to make a copy of a document. We select the document we want to copy by ID, and that makes a duplicate of the document. We can also, once we've selected the document, we can update some of the texts that's contained within the document by using the replace text method that allows us to use a document as a template with the different keys that we're looking for. The string characters that we're replacing with new values. Then the last function is gonna be taking those two and putting it together where we're gonna be using a template document, creating a new document copy of it. And then going through that document copy, updating with an object of information, updating the text content so that we're able to generate documents from a template. That's coming up in this lesson. We're gonna be copying the doc that we've been working on. It will make a duplicate of it on the drive, copy out the function. And this is going to require us to use the drive up. And we'll just call this update to. So we still need the ID. In this case. What we're gonna be doing is we are going to be selecting the document using the ID. We're going to use the Drive app in order to make a copy of the document. This is going to be the new doc. Using the Drive App Service, we get the file by ID. We can use the same idea and just as we did with the document up, we require the ID in order to select the file object. For now let's log it into the log. And just to make sure that when you have the new object ready. So that's gonna be under update to select the Function, run it. And because we are going to be requesting you permissions, we have to go through the permissions screens once again, in order to accept permissions for our app and for our account to use to be used within the app, we can see in the execution log that we've selected the existing document. It returns back the name of the document, and this is the document that we've selected and now we can make a copy of that document. This is gonna be actually the original document and then the new document will be another value. So this is the existing document or we can call it our target document. Document. And that's where we can use the drive up to open the file by ID. So that gives us the file object. And then now in order to create a new file, we select the file object so the target document, because now that it's within an object format, we can use the method make copies, so it's gonna make a new copy of that document. Now, we're going to output the new document and we can get the ID of that new document. So both of these IDs should now be different. We're getting the idea of the new document and we'll also get the ID of the targeted documents. So that's the original document that we've got. Let's run the update two. We need to get an accept permissions because once again, we're asking our opt R script in order to update content within the drive. So this time we're editing, creating, and deleting. We're accepting the permissions for our account to be used within the code in order to create the new application, then use file. There's a new file that we've created. When we go into the drive, we should now see that there's a second file with the copy of the test too, and that's the file that was just created. So that's the new file that we've just created, and that's the file copy of that original one. You can also update and set documents to trashed so you can remove them from the drive if you want. With the new document. We can set the property of it and set trashed. And if that's expecting a Boolean value, so if you set it to trashed to, this will still create a new document, but it's going to actually remove it from the drive once it creates it, it gives us an ID of a document. If we go over to the drive, this document is actually gonna be sitting within the trashed. We can open it up. We can see this document, but we see also that the document is sitting in the trash, can, take it out of the trash. That's how we can remove documents and add them into the trash. And then if they are trashed, they're gonna sit within the trash for the next 30 days or depends on what the settings are within the drive. We selected the document. We want to create a copy of the document and we want to update some of the content of that document in order to update some of the values. Let's create documents. Once again, we'll select one of the documents that we've been working with. And I'll call this one. This is gonna be our template document. Use that as the ID. We can get rid of the other documents that we have on the drive. So just remove these and go back into the script. So now that we have the value of the ID, create a new function. This is the function we're going to select the contents of that file and we'll make some updates to it. And typically you'd want to have one file that you use as a template. So you're not going to be updating or removing anything from it on 10th of the document. Let's do that where we're used the document open by ID, using the ID value to open it. Then let's get the body of the document. Getting the body. Then once we have the body, we can clear the contents of the body. So save that. And now let's run the update for a function that should clear out any of the content that we have within the body. Let's create our template. So that will say a welcome message and require some of the parameters. So getting the firstName and then also the last name were used the app script in order to update these values. Welcome to the website. Your ID is, and then whatever we wanted for the ID value. Then of course you can add in additional content into the message as needed. So that can be our signature that we're adding into the document. You can always edit the content of it to update the line weight to the right-hand side, make this larger supply some of the styling. Once you've created your template that you want to use. Now we can select that file because we've already got its ID and we're going to update some of the contents of it. So we're not going to be clearing it, but we're getting the body. I'm going to add in some object information that we can use. So this is the custom user information, and then we'll use that to populate the fields of the peach. So here we can create this as an object. And within this object to have a first-name. That's gonna be expecting a string value, lastName, and also a string value. Then the last parameter that we want to update was the ID. So this can be a numeric value. I'll set the ID to 100, want to run through and select the page elements and do a replace on them. And we can do a body replace text. So this is a method that's built-in that can search for the contents of the page. And we can apply some updates to it. If we want to update the value of first, we would select that as a string. So that's the key that we're gonna be looking for. And then from the user, first, we'll update that property value with it. And it will run through and update lost as well. Then ID with whatever we have for the value, an update these user values. Now let's run the code. And that should update these values to the new values that we have sent in. Now the problem is that because this is a template that we're using where not able to were actually updating the template. So we don't really want to do that. We want to bring it back to the original values. So we don't want to update these. We want to have them as the keys that we're looking for. Let's bring these back to the keys that we're looking for. We'll create a copy of it. And that's gonna be the file that we're gonna be updating. So this will be fill template one function. We're going to be selecting, making a copy of the document, selecting the document as the template. So make sure that you do have the ID. Correct. And once again, this is the new one that we're using as the template. So let's update the ID of that file. So we're selecting it and this is gonna be our template doc. Template doc. We're getting that element and then we're going to be duplicating it with a new document. And then within the new document, we want to select the body and then make an update of the body contents. So let's add that in where we're getting the body from the document. Before we try to update it. Let's see if this runs because right now we're not selecting it by the ID and we need to get the ID of it and then use the document app in order to select the body. Let's output the result for body will run the filter template. And we see that we're throwing an error because we don't have it within the document object. We need to get the ID of the new doc and select that element. So that's gonna be the dark value that we're using and using the document, document op service. And we're opening by ID. And then we can get the ID of the new document. Where we do the get ID method. That will return back the idea of the document, allowing us to use the document app service in order to select the document. And then we can select the body of the document. This time it should work where we're selecting the body of the document. We've got the document body section selected. Now we can use the values that we have in order to update and replace the text content of it. Let's run that. Then within the drive, we've got a new doc that we've just created. Let's check out what we've got within the new document. And there's our updated document that we've just generated. And this is all coming from the template and we're updating the template contents are duplicating the document and updating the template document contents. 5. 4 Spreadsheet Data: Gonna be creating a function that's going to connect to a spreadsheet. Get the data from the spreadsheet, getting all of the values within an array, and then looping through the rows of values and updating some of the values of those items. So right now we've got all of them set to false. We can set the value and the script is now going to update them and set them all to be true. So we'll run the script, we get the output of the rule of content and then the value that we're updating. So we've changed them all to be true. We can also change them all to be false. Once again, whatever value is being entered into here where we're doing the set value. And now let's run the code. Now, we will set all of the values back to false. So go over to the drive and create a brand new spreadsheet. And this is a spreadsheet that we're going to use to get data that we're going to be outputting into the dock. So we're going to need some parameters. So FirstName, LastName, and ID. We can also include an email address. So populate some information so that we can make use of this. And what we wanna do is we want to loop through the spreadsheet data and then create the docs accordingly. And then here we can set an email. We can have a last column there created can be a Boolean value. So either false or true in just populating it with some data. And we're going to be selecting the contents of this spreadsheet. Let's select the spreadsheet by its ID, go back into the app script and create a function. We'll get the sheet data values, setup an ID so that we can select the sheet contents and then get the spreadsheet service using the spreadsheet app where we can open by IV, specifying the ID of the spreadsheet that we wanted to open within the logger log will log out the sheet spreadsheet object, and then we'll select the sheet within the spreadsheet object. When we run this, we're going to have to accept permissions again as we're accessing and giving our script new permissions to use within our account. So this is gonna be edit, create, and delete Google Sheet spreadsheet content. Once you allow that, it's going to allow the script to run and return back to the spreadsheet object. So we want to select the sheet within the spreadsheet object. I'll give this a name. I'll call it data tester. Go back in here, and now we want to select the spreadsheet to use within the spreadsheets and the sheet. I'm going to just give it a name of data so that we can select the sheet of the data that we want to use. So using the spreadsheet object. So we want to get sheet by name. And then you know the name of the sheet is going to be data. So let's select the sheet. And then we need to select a range of content that we want and then get the values within that content. It run the script again. And now we've got a sheet object. Within the sheet object. Now we can get the data values. Within the sheet, get data range. We need to select the range that we want to work with. We want to select all of the data within that particular sheet. So that's what we're going to use the get data range method. And then we want to get the values from the sheet. This is going to return back the contents of the sheet within an array format. Let's run that again. And now we've got all of the contents of the sheet. We want to loop through the data contents of the sheet. We also want to remove out the first row of content because that's going to be where the heading information is going to be contained. We don't want to have to select the heading information. We can either slice out the remaining rows of content. So let's do that where we've got the rows and using the data array. And then the slice method, we're going to just slice it. The remaining rows of content. That's going to be minus the first row where we've got the heading information. And that actually should be data slice. Now we just have the content and we're ready to loop through the rows where we can use the for each loop. And this is going to return back the row within the log. Let's just log out what we have for the row. This is gonna be an object and returns back each row of content. So we've got the firstName, the lastName, the ID, the email, and the Boolean, whether we've sent it or not. We don't need to track the rows anymore. So this way we can get the sheet in data. We can also update the sheet data as well. So if we wanted to update those values as we're looping through and we got the created. So get that particular value of created. So this is going to be the temporary field where we're getting the sheet object and then get the range within the row. We can also return back the index value. And we can use that index value to identify which row we're actually going to be on. And just as we got the range when we use the get data range for getting the values, we need to also get a range in order to set the values. You can have 23 or four arguments within the get range method. First one is that we're going to need a value that we can get that with the index plus one. Because the eraser going to be zero-based and the row is in the sheet are going to start at one. So that's where we've got the role value is gonna be index plus one. Then the column that we want to select, the column E is going to be column number five, selecting the column. And then the number of rows and the number of columns that we want to return back. So we'll just want to return back 11. So temp is going to be selecting the particular range. And we can see when we do the log, we can get the temp, get the values from temp, or we can get value. This should return back the Boolean values that are in temp, that's got all of those values. And we need to actually increment it by one. Because what happened here is we're actually getting the heading value because we're not accounting for when we sliced out the first row. This is going to actually be index plus two. Let's run through it again, just make sure that everything is proper. And if we want to get the first column and get the values of the first column just to make sure that they match up with what we've got for the row range. Can change that back to five. Now that we've got the range, we can also set a value for. It. Just says We got values. We can set values. And I'm going to do this on a separate line. So using the temporary object that we just selected and selecting temp set value. And I'll set this value to be true. So now when we go back into the sheet, I've updated all of the sheet data and now it says, true to the sheet data, you can update the sheet date of this way. You can select it and update the sheet data. And I'll just set it back to be false. We wanted to check to see if we've created it. And if we have created that, we're going to add a URL there of the file that we've created. So it's coming up in the next lesson. Go ahead and connect to the sheet data. Output your sheet data into the log, and then also select a sheet column and update the value within the sheet column and you'll be ready to move on to the next lesson. 6. 5 New Docs with Sheet Data: Wherever we are creating a script, dot is going to select data from the spreadsheet. So FirstName, LastName and ID, and then using the template, populate those fields into the dock, creating brand new docs and then adding them into this folder. And that's what the script is gonna do. So we're selecting the spreadsheet data. We're selecting the mean folder that we want to add it in. And as well as the template doc running through those, selecting all of those objects, and then looping through the data from the spreadsheet, creating a new file. And then that's gonna be selected. Then when once we select the document, we can get the body of it and then update the text of it, and then update the spreadsheet with that new URL of the doc that we just created. So running the code will update and populate the web URL. And it'll also add in the docks into the main folder. Once they are created, they are using the spreadsheet information to name the dock with the person's firstName. And it's populating the information into the spreadsheet using the data from the sheet. And it's also updating the spreadsheet with the path to where the document is located. So creating the documents from the template, we're using spreadsheet data. Go ahead and create a new function. What this function will do is this is gonna get the sheet data. And as we look through the sheet data, it's going to create the brand new docs. And we want to create a separate folder, dot Ken, house those within the Google Drive, create a new folder. And this will have all of the dots, thus we're creating them. So we want to get the folder ID within the drive. If you go to the web URL, this where you can select the folder ID. And this is just like the documents where it's got a unique ID for the folder. And we want to create all of the dots within that folder. Call it maker Doc, make her one. The folder ID that we want to add the documents into. Also, we've got the sheet ID. I've got a bunch of different documents that we're selecting. And we also want to have the doc ID, which is going to be the template. This is where our template object is going to be. Bring all of that in, and this is going to be the template ID. So TID, we've got 43 different IDs and we're gonna be creating using the data from the spreadsheet and then selecting the document as a template and adding it within the drive in this particular location of the drive. So first off, let's set up the mean Dr area where we're going to select the main folder. That's going to be where we're gonna be adding the content into using the Drive app service. Then getting folder by ID. We're opening up the folder using the folder ID. So it's the FID That's going to create the main folder object for us to be interacting with where we create elements. We also wanted to get the sheet data copying what we did in the previous lesson. Where we're opening the spreadsheet. Using the spreadsheet App Service. We're selecting the sheet. And we can actually chain these together within the spreadsheet object. We can select out and get the spreadsheet object within this, the one line. So getting the sheet by name, and that's gonna be the sheet object that we're trying to interact with the sheet of data. Then as we get the sheet, we're getting the data range and getting the values. We can also slice thought. This can be just the data. Let's see what that looks like within the data. We don't need the heading information. Let's run the doc maker one. And now we've got all of the sheet data within an array that's ready to be looped through. Just as we did the for each as we're looping through them. Let's add that in as well. So instead of the rows, we're looping through the data, we're getting each row of content. I'll comment out these two because we don't need them quite yet. And run that one more time. So we should get each role of content. So now we can use that in order to populate values within the spreadsheet. So creating where we've got the temporary file. So we want to create those files. We already have the temperate temp template object. We can select the template document by ID because that one's not going to be changing. Then the new docs that we're creating, we can create that within the for-each loop. So these are the new documents that we're creating. We can use some of the values that we have within the sheet. Within the first item, we've got the firstName. So how about we use those as the sheet names where we're getting the row and we'll use the index value of 0. So that will create a new document. And let's add the file to the main folder. That's the main folder that we want to add the file to using the Add file method. Then we need to select the file object that we want to add there. Run the code. We should be able to go into the drive and that docs folder that we've got. When we refresh it, it should have all of the files that are created. We have an updated the file contents yet, but it is creating the files. So let's just remove those. Next up. We know that we've got the new document. We can update the contents of the document. Just as we did before. We're gonna be doing a replace of the body and replacing that content. We want to get the body of the document first and then do a replace of that document. Selecting the body object and then using the new doc object, get body. So that will get the body contents. Then once we've got it within a body object that we can do a replace the appropriate text and content. Do you have to open it by the ID, just as we did before. So that way we can properly select BAD ITA to recreating the document where moving it into the folder. Then we're selecting the document using the document up so that we can edit the contents of that document using the document App Service. We get the body and then we go through the body and we do a replace of the body contents. So we just need to update and add in the proper properties. So row 123 and just double-check, you can just use their index values for the row contents that we want to replace. And then once we've replaced the role, this is where we can select the sheet by its index value. And in this case we need to have it as one because we didn't slice it. We're looping through all of the data object and we can get rid of the temp because we're not going to be needing that. We can simply set this to true. Since we've already updated and created it. We can also get the path to the document as well. So you're saying the documents and get URL that will add in the URL path of the document that we've just created. Let's run through the document maker and see what happens within the spreadsheet. And it looks like there's an error there. So the new document, this should actually be dark, Goodbody because we're not getting it off the document. Object. So try that one more time and we should be updating the created with the URL of the path to the document that we've created. So it looks like it added it, but it added at one row up. So we can get rid of that and we can try that one more time where we're updating. And we need to add, I guess to, to that row. So we need to add that back in, go into the docs folder. And these are the documents that we've just created. Can take a look at those just to see what we've got there. So we've populated the information. This is all data that's coming from the spreadsheet and created these custom docs with that information. Let's remove that. And we'll run the script one more time. This time it should pop properly populated within the spreadsheet with the URLs to where the location is of the files that we've just created. Then, now you can click those. You can see the file contents and these are the updated files from the template. Go ahead and try it out. And coming up next, we'll do some more with the document says we're creating them and we can convert them into PDFs. So that's coming up next. 7. 6 Docs to PDF and Email: This lesson we're going to be generating PDFs from a Docs. So we've got a folder that's got several ducks that we've generated. And what we want to do is we want to loop through the contents of the folder, select all of the files within the docs folder, and generate brand new files. And these are going to be PDF versions of the files within this PDF folder. So let's run the code and as we see, the code gets executed, then you files get added. When I open these files, then they're going to contain the base PDF version of the dock. So it's gonna be the same content, but this is just gonna be within a PDF version. So a quick way to convert docks into PDFs and then store them into a separate folder. All of these in Google Apps Script create a new function. And this function is going to serve as a way to make a PDF from a duck. Then from here, we'll select the document that we want to use. Using its ID. We can use sues the template and we'll make a PDF of the template document. Getting the ID of the document that we want to use. And then what we want to do is we want to select that document and convert it into a PDF. Also, we can specify which folder we want to add it into. Let's create a new folder. And this is where the PDFs are gonna go. Within the PDF folder, destination folder, where the PDFs of the ducks are gonna go. So this is going to be the main folder or folder ID, whatever, whatever name we want to give it. So that's the folder that we wanted to create the files in. So we're gonna be opening up and selecting the document and then converting it into a PDF and then adding it into the folder. Let's go ahead and we're going to select the folder. And select the folder using the Drive app service and get folder by ID. And this is where we use the folder ID value. So it's going to select the folder that we wanted to add the content into. Then we're going to create a temporary doc. This is gonna be the PDF or we can just call it temp for now. And using the drive up, we're gonna make a copy of the current file. Drive up. We want to get the file by ID. File by ID. That's gonna be the ID value. So that's gonna be the template file or whatever file we want to use and create the file with getting the temporary file. Then what we want to do, so we can chain together the methods. So we want to make a copy of the file. So using the make copy method. And then where we want to place it is going to be in the folder. Let's try that or we're gonna be making a copy of the file and adding it into that folder. When we go into the PDF folder and we refresh it, There's a copy of the template. You can save some lines of code by just having all of the statements together. This is going to be the temporary file object log or log temp. And if we log that information out, this is going to be the copy of the template that's returning back that file object. Let's get that file as a PDF version of the file. That's getting the file object. And then we can get the file as getting the file. We want to select it as a meme type PDF. So now it's gonna have a PDF object within the log. Let's logout PDF and see what we have for the PDF. It's getting it as a blob. And once it's within a blob format, then you can use it and create PDFs or other different file types from it. This is actually going to be a blob. In order to create the PDF file. This is gonna be the actual PDF file. Using the blob. We want to create it within the folder. Create file. You can create files from the blobs. And getting the Blob object. We can also set a name to it. We can use whatever the current file is using that file name. File as a PDF with the PDF extension that's going to return back a string value. And then I'm just gonna give it the PDF extension. Gotta remove out that reference to log it. It's run the code. Now when we go into the drive, There's our PDF version of the file, but it's just been created. It's created a version from the template. What we want to do is we want to loop through and make PDFs of all of the files that we have within this docs folder and convert them into PDFs using the code. This is gonna be our source folder. We're going to loop through all of the ducts and the PDFs, which is called many PDFs. Source folder ID. It's the source folder ID. And then the destination ID is going to be the same as we had done here for the folder ID. And what we wanna do is we want to loop through and get all of the files within the source folder. So let's select the files within files object. Using the source folder. Let's select the folder by ID and folder. I'll call it source for a source folder. And we'll use this source ID to select this one. And then also let's create an object for the destination folder. That's the destination folder. And this can use the destination ID using the source folder. We're going to get all of the files from the folder files. And this is going to return back an object that we can loop through. So we'll loop through while files. Using the next method. This is going to check to see if there's a next file. While there is the next file that we haven't looked at yet, this is going to come back as true. Once that next is no longer existing, then it's going to come back as false. And actually this should be hasNext, as we're checking to see if it has next value. That's the has next method. Checks to see and returns back a Boolean whether there's an additional file. In order to select the file object, we go from the file's array and then that's where we can select Next. That's actually going to return back the file object into the variable file. And from here, we can select that file and we can also set it up and convert it into a PDF so you can take the file object. And just as we did within the other function, where we can get the file by ID. And since it's already a file object, you can already make a copy of it. We don't even have to get it by ID because it's gonna be already within the file object. And we can just simply make a copy of it within the folder. Also, if we want to save ourselves a line of code, we can convert a definite directly into a blob by getting the, getting it as a blob. So that's gonna be returning back that current file as a blob. And then we can convert it into a PDF using the destination folder. It's the dust F, where we're creating a file from the blob. And then we're setting that as the name that's coming from the file object. Setting that to PDF. What this function is gonna do is it's going to loop through the folder, select all of the files, get it as a file object, and then select it, make a copy of it, and create it as a blob, which is then going to be converted into a PDF. So run the code, looks like folder is not defined, so we need to sucked and it make a folder. And this is actually going to be the destination folder. So run that one more time. So it's looping through the docs. Let's see what we've got within the PDF folder. So it's making copies of the file and also creating the PDFs. Let's update this function so that we were not creating the docks and that we just creating the PDFs unless you want to create the ducts. So just give it many, many PDFs. One. Then as we're getting the file object, we can shorten this. And we can use a function called get blob method. And this will return back that file object as a blob. And then here we can create the blob and just create the PDF. So save that and we'll go out to the PDF folder and clear out the PDFs that were already created. Let's go back into the ducts and I believe we've got a duplicate there. Just remove that, go back into the PDF folder. And now let's run this new function where we're just getting the file directly as a blob. So we're not recreating and not creating another doc. And what this will do is this will create all of the files just as PDFs. Go ahead and try it and create PDFs from your ducts within the folder. 8. 7 Send as Email PDF: Gonna be creating a function that's going to use a Google Doc as a template, populate the information into the template. And that's gonna be coming from the spreadsheet data. And then also using the spreadsheet data to send emails to the e-mail addresses within the spreadsheet, generating the docs as PDFs and sending those out. The users run the code. It executes, selects the temporary folder, and it's adding the files into the temporary folder, but then it's also removing and deleting them. So it's moving them into the trash, generating the docks. And as we can see, this file is sent into the trash. We're not going to store anything within the folder. But when we go to the inbox, we see that we've got all of these files are now generated. When we open it up, we've got a custom message and we select the PDF. And we've got also the customized PDF that was generated within the app script that was sent out to the user. This lesson, we're going to be looping through the sheet data and then updating and outputting dot content, sending it as a DOC over to an email address. Let's create the function. I'll call it center one. So this is the function that's going to select the sheet data. We do have the sheet contents and we'll use the ID for the sheet. And selecting the spreadsheet in order to get the contents of it. That's the sheet object. Using the spreadsheet app service. We can get, we can open by ID. And then we've got the id value that we can use. I'll also set up a variable. Let's kinda contain the ID so that we can select it and reference it. So selecting an opening it by ID within the logger. Logger, I usually do try to just make sure that I do get the sheet object before I tried to do any additional code. So make sure that we don't running any errors or any issues with it. Once we've selected it, then we can get sheet by name. And then this is going to be selecting the sheet. The name of the sheet that we're using is going to be data. We're just selecting it that sheet. Then once again we can run the function. And we've seen now we've selected the sheet object. We want to select the contents of the sheet. Just as we did before. We can get all of the data range. So we need to select the range using the get data range method that will return back the full range of content from the sheet. I'm actually going to set this in a separate object because this way we can still select the sheet object and update content into the sheet. As we're getting the data from the sheet object, we're getting the data range. And then let's get the values and output the values into the log just to make sure that we are selecting the values properly. That's all of the sheet information. Notice that we are picking up the first row which has the headers. So we want to remove that, and we'll do that with a slice, one that will slice the content and remove just the first row. So that gives us all of the data content that we have within the spreadsheet. So what we want to do is want to loop through the data sheet and using for each. This is going to be the role of content. Return back with an array format. So we'll loop through each one and select out the contents, and then we'll update the content from the template. Let's select our template object using the document open by ID. And this is where we need the document ID. Create a variable to hold that. So that's the doc ID. And select that so that we have a template that we can use in order to select id. That's our template. Because we've got the headings of the sheets where we've got first last ID. We can also use that information as we're selecting the content and we can loop through that content. And that's gonna be contained within the first row of data. So let's actually slice that and then we'll get the rows as a separate value. And that will be used the data slice where we're looped through and we're going to loop through each row item. This way, we can use the contents from data 0 and we can loop through the for-loop. Do that then let's make a quick update so that we can make a copy of the template file. And we can't copy it using the document op, but we can select it and make a copy of it by using the Drive app service. Then this is where we're going to get the file by ID. So get filed by ID and then we're going to open it by its ID value. We also want to select a destination folder. This is the folder where we're going to copy the file into. So I've created a temporary folder, select the ID of the temporary folder, and then using the drive up, get folder by ID. And this will allow us to select the folder directly and allow us to automatically copy the new file into it. Once we've got the temporary file, we can make a copy of that file. So we've got the file that we wanted to use, and then we've got the folder where you want to add it into. So temp. And we want to make a copy of the file and added into that main folder. We want to return that back as an object. This can be wherever the file object is. Then once we've got the copy of it, in order to get the document, we could use the document app Service and opened by ID. And this is where I need to get the ID of the file. So file and get the id value of the file to return back that string value that will return it back as a usable dark. And then now in order to get the body, we just get the body of the document that allows us to interact with the contents of the body within the logger log. For now, let's logout the body just to make sure that the code is working, that we don't have any bugs in the code. And we'll do a quick run-through of the code. What it should be doing is it should be selecting the files and making copies of the files or of the template file. And it's got three copies of the template that's ready to make updates to that template file and populate the information from the spreadsheet. As we're looping through, we can update some of the properties of that spreadsheet. So let's loop through. And now that we've got the heading information that's gonna be contained within data one within the first row using the index value of data. And this is also gonna be a loop. So we can loop through each one of these headings. Within the document body. We can do a replace of the content using that value for the heading. So let's do a body object, then replace text. And of course, your string values for the headings have to match with what we've got within the template. Otherwise this won't work. You can also hard-coded for each individual line. I'm going to use the template literals. And the characters that we're gonna be looking for are going to be the heading value. Once it finds the heading value, we're going to be replacing it with whatever we've got for the current row. And using the index value, it's looping through. And it's going to use the index value here and do a replace of the body contents. Will also take the file and set the name of the file. This will match with what we've got for the row, for the first item in the row. Actually it's just match it to the first and the second. Run. The code. Now going into temp can remove out those previous files that we had there. And it's creating those files with the new populated information as it's generating those generating the new ducts. So what we can do is we can take the doc, convert it into a PDF, and then send it as an e-mail. Let's go ahead and we'll do that. We'll get the blob, the document that's using the dock. And if you get it as and we're going to select type to be a PDF. Pdf. Pdf. It's returning the doc back as a blob. Now let's construct the e-mail contents where we wanted to get the e-mail address of where we wanted to send the data to. So that's going to be contained within the row values. Within the fourth item within the row array. It's going to be row with an index value of three. Is the e-mail where we want to send it to the subject for the e-mail. And then the body. And have a custom message if we want. So I'm going to use the template literals, so the TTX. And I'll use the values that are within the spreadsheet. And just say checkout the attachment. And then now let's use the mail app Service and send email. And we'll send the e-mail object. Sending it as an HTML e-mail, specifying who were sending the e-mail to. So that can just be the email address. Parameter is subject. We've already got a value for subject. And it's just using these variables here. Then for the HTML body object, this is where the body contents can go. Then AS for attachments, we're going to attach the Blob Content, get as meme type PDF. That's going to send and convert it into a blob and send the email. And then for the doc, then for the file, Let's set the trashed to a Boolean of true. So that will remove the file that we've just copied from the temporary folder. It looks like I use body again. So this is gonna be the H body and remove out of that from the logger and save it. And let's run the code, see what happens. We need to accept permissions because we're getting new permissions for the op. Select the account that you want to use to run the code. And there's the new permissions that we're adding, send email as you. And that's the mail app service. So that allows us to send emails. And let's see what we've got within temp. These are the previous files. And now let's check our email. There's the e-mails that we've just created and santos, that's the first email. So welcome. And there's a spelling mistake there, so we need to correct that. And it looks like it's sent the emails, but it never updated the PDFs. So let's take a closer look and see what happened here and then also update this spelling mistake. The reason that it wasn't updating the PDF is that we need to close the writing to the document. Once we've finished writing to the dock, do a Save and Close function. That way we can save close it before we run and send the e-mail. Let's clear these files out. And we'll run it again. Then go to the inbox and clear those files as well. That's our new file. Now we've got the updated version of the file as a PDF being sent out in and out as an e-mail to the users from the spreadsheet. And we're not keeping anything within the temp. So those files as they're being created, then we're also sending them into the trash, setting the trashed as true. So unless you want to keep them, you can also keep the doc versions of the files or you can create PDF versions in the folder as we've seen in the earlier lessons. So go ahead and try it out and create your own version of the application that's gonna send emails using the template, populating the information from the spreadsheet into the template doc, and then emailing them out as PDFs to the users.