Automatic Certificate Creation in Google Sheets

0:00 We have a certificate of completion, A PDF that we want to send, but that PDF , we need to enter names and dates and email that to someone. So what are the pieces we need? We need a Google sheet. We need a Google sheet with the name and an email. The name you wanna put in the email you want to put, send it to. And you need a Google Slide or a Google Doc. Pretty much interchangeable here. I have a Google slide. It says certificate of completion. It has all of the texts that I need to send, and the only thing I need to replace is this name and this date. And what I've done here just to start us off, is I've created a code. I said two square brackets, name and two square brackets. This could be clearly brackets, this could be numbers, this could be

0:46 any kind of unique code that says, Hey, look for this text and replace it with the name in the sheet. That's gonna be key as we get through this. Now I'm gonna code a little bit, watch along with me, or you can grab the code down below in the description. I'm gonna go up to Extensions app script , and here I have a blank app script . Nothing has been written yet. I'm gonna create a certificate. And above my function , I want to have the URL of the slide. So I'm just gonna go copy the URL of the Google slide. I'm gonna call it in all caps, sir. Certification, URL. And I am gonna put that in quotes. In this case, I've put it in not just single quotes, but,

1:31 but back ticks. But really, it really doesn't matter. Now, my function , I'm gonna create, end up creating two functions . I'm gonna create a function called on open here. This is gonna let us use our function in our sheet as another menu item up here. I'll get to that later, but I just want to share that with you right now that we're gonna make it super easy to use this. Now our function is gonna be called Create Copy, and let's break down in what's called pseudocode or some just comments . What we're going to do, well, we're gonna get the name and email of. The person, we're gonna get the id. We're gonna need the ID of this URL of the cert, URL in order to use it in Drive App.

2:16 Basically right now slides is a slide URL, but we need to open it with Drive app. I'll show you that. It'll be really quick. We're gonna create the name of the certificate, which is gonna be just the name of the person, and we're gonna create a copy of that URL that we have above. We're gonna open that and enter the text. Or replace the text is actually what we're gonna be doing. Then we're gonna create a folder or just get the folder of PDFs so we have some place to put the PDFs. We're gonna create a PDF from this new copy. Then we're gonna set the P-D-F-U-R-L inside of our sheet here. Then we're have to trash or basically put in the garbage, the presentation

3:05 or slides copy that we created, and then we're gonna email the PDF as an attachment to the student. So this is sort of just saying, these are all the steps we're gonna take. I might start even backwards in this case because this email of A PDF is actually fairly simple. We just have Gmail app dot send email, and we need to get the email of the person we're sending. And that's over here in our Google sheet. We're gonna say some kind of subject, and so in quotes, we're gonna say. Your certificate of completion, lemme bring this up so you can see it a little bit better. We're gonna create a body of the email, which is just gonna say, Hey, you get the, the certificate. We're going do that with a variable body equals, and we're

3:50 gonna use back ticks here high and we're gonna use, uh, interpolation to say name. So we use back ticks here so that we can do dollar sign , curly brackets and insert someone's name high and then a new line that's like slash code that's a slash n. It's a code for new line. Attached is your. Certificate, and then another couple of new lines and congrats. And that's the whole body right now, you can add way more, but that body we're going to send as well as a comma. And in curly brackets we need to say attachments and colon. We're going to get what's called a PDF blob. Basically the PDF . We're gonna turn it into a blob, you'll see, and then end this parentheses. Here, not there, but over here, that's the whole email we're sending.

4:38 We're gonna send an email to the person. We're gonna say, here's the subject, here's the body, and attach the PDF . So that's the end result we're gonna get. Let's go back to the very top and get our name and email from where we are. So again, I'm gonna create a function here that's gonna allow us to click a button, but we're gonna be on some row. So we're gonna be on some active sheet , get active spreadsheet . That's just the, the spreadsheet file we have right now. And we want the active sheet . So SS dot get active sheet , that's just exactly the tab that we're on. And then we're gonna get a row, and that row is gonna be sheet dot, get active range, get row. Capital R. And what's cool about this is that we can then, so go name equals sheet, which is the sheet or the tab we're on, get range, row one, comma one, get value.

5:27 So that's getting the value of whatever row we're on. And in the first column, which is in our case. The name and the email is in the second, so we can copy this so we don't type it all over again and say email equals and do comma two. So column two. So we got the name in the email again, let me show you how this works because it might seem a little weird. But basically we go spreadsheet app, get ui, create menu. Automation menu. We can name it anything we want. Here I'm gonna add an item and it's gonna say email certificate to student and put a comma. And here we need to put in the exact function name in quotes, single quotes, double quotes.

6:13 Doesn't matter. We can probably keep this the same by doing double quotes. This function has to be exactly the same name as a function we're writing here, so that it connects, basically connects a click in the sheet to this action in that we're writing now. So let's save this as is. I'm gonna close app script and I'm gonna refresh my sheet by refreshing the sheet. I'm closing it and then opening it again. And what that means is the unopened right here next to help or accessibility will show up. We need one more thing. Actually, I just realized we, it's not gonna show up because we didn't add it to the ui. So at the end of this ad item, add to ui, parentheses, save, or we're gonna get an error , it's gonna say, oh, there's can't do that, so let's refresh.

6:59 And there we go. We got an automation menu, and now if I'm on a row, I can go up to automation menu, email certificate to student, and again, it'll execute that function and should enter A-P-D-F-U-R-L here. Let's go back to app script and keep working on this. I wanna add a little helper here that says, Hey, if like the email or the name isn't here, that's the two things that we really need in order to execute this perfectly. If we don't have the email, we can't send the email. If we don't have the name, we can't put that name into a certificate. So I'm gonna say, if I'm gonna say this is, uh. Punctuation that says not basically not, it's gonna exclamation mark, but means not. So if we don't have the name or, and this is two pipes that says, or we don't have the email, then we're going to do something.

7:45 We're gonna say, spreadsheet , app dot, get ui. I alert. I'll put a little emoji here, name or email missing and we're gonna return, which is basically we're gonna stop working in this app script . We're gonna save that, and I wanna show you how that works so we can, that it works now. So if I'm on another row that doesn't have anything and I go to automation menu. Click to run. It's running. I do have to authorize it. The very first time that I ever use this, I'm gonna select all . Yes. I'm gonna continue and see. It says name or email missing. Perfect. I also wanna make sure that we're not on the head of row, so I'm gonna add right above this. Same thing if row is equal to one or actually less than two, essentially. Because maybe it's zero. I don't know.

8:32 We're gonna have this exact same alert, but we're gonna say can't be on the head row and return. Oh, we need a parentheses at the end there. We can format the document. There we go. Looks nicer. Save. So now if we are on the header row , it's gonna give us another error message, essentially. Can't be on the header row , bro. All right, let's keep going and work on the sort of mishmash in the middle. We need to get the idea of the URL. So what happens is we have a URL, but we need to use Drive app in order to use it. So we need an id. So we say variable cert ID equals we're gonna use slides app open by URL. We're gonna get this certification URL as a variable here.

9:19 It's a global variable that we can use anywhere in our app script , open dot get id. Couldn't be easier. Just get the ID of this URL. We are gonna get a copy of this. So we're gonna say variable start copy equals uh, we need to first actually do variable original file equals. Drive app do get file by id Cert id. So in order to make a copy, we need to use the drive app to make that copy. So we need to get the id. So original file do make copy, and we need a name. So put a. We will put a variable here, certain name equals, and let's put in backtick. And again, we're gonna use interpolation here. So we're gonna get the name of the person, underscore cer and certification. Again, you can use any text here.

10:05 We're sort of just adding a little bit of text using their name. To create a name of the file and now from this copy, we need to get the ID cert, copy id equals cert copy dot get id. Okay, so if we concatenate this sort of altogether, if you're new to code, it's going to do drive app dot, get filed by. Id gonna go grab that slide id. Oh, I need to change that to ID there so that it actually picks up this one. Then it's going to make a copy with the name and then it's gonna get that ID of that new copy. Pretty cool. Right? So that's sort of all there. So now we want to open that and we want to replace text. So we can say variable presentation equals slides app. Do open by ID cert, copy id.

10:53 So we've made a copy. Get that copy. And do presentation. Now, this is an action. We're not doing a variable right now where this is like an action. We're gonna take that presentation and we're gonna replace all text. And in a single quote, we're gonna put our code, which is name and comma name. So we're taking the actual text with square bracket. Square bracket name, square bracket, square bracket. It. We're gonna replace it with the actual name of the person. We also want to replace the date. So we're gonna do variable today equals Now we could do new date, but that's gonna give us like the entire timestamp . We just wanna format this, so we're gonna go utilities dot format , date. We're gonna take that new date at a comma, get the time zone of the sheet we're on.

11:39 Get active spreadsheet , get time zone , comma, and now let's put in the actual format that we want this date in. Now it's gonna be. Capital M four M's. That's the entire name of the month. If we did three, it would just be JAN for January or DEC for December. But if we do four capital m's, it's gonna be the entire name of the month. Then we're gonna do the day, comma, and then four Y's for the year. So that's today formatted perfectly. Let's replace that. So we'll do presentation dot, replace all text. Parentheses, we're gonna put that date exactly that code that we had in our comp, uh, certificate of completion right here, date.

12:27 And now we're gonna comma replace that with today. So once we've replaced those items, we need to do presentation.save and close. Parentheses and we want to end up creating a PDF , but we want to do it in a certain folder. Uh, the thing about this is that it's gonna get really messy if we're creating these PDFs, and it's just generally in our drive. So we want a single Google Drive folder that's going to hold all of these. So let's do variable folder name equals completed. PDFs variable folder equals drive. App dot Get folders, sorry. Folders by name, folder name. So this will actually be folders because we also want variable folder equals,

13:16 and now we, this is like a really quirky thing about Drive app and Google. Docs and how it works. Essentially, we have to go folders. Has next parentheses, then a question mark and then say folders next that basically if it has a next one, uh, go to that next one. But if it has no, next one we're going to do colon drive, app do create folder name. Again, quirky. But essentially what this is all doing is saying like, if this folder with this name doesn't exist, make it, uh, but if it does exist, then go and get that folder. And what do we wanna do with that folder? We wanna create a PDF . So we're gonna say PDF blob equals blob equals CT copy.

14:05 So this is a copy of. The, this is the copy that we've now, uh, made a presentation from and we're gonna get as. And it's capital mime type, capital M, capital t, pdf . Set the name. Remember we've set that name before and we're going to put in back ticks, PDF , but it's going to be the interpolation of dollar sign , curly brackets. We have this certain name up here, certain name. So we're just gonna name it that PDF variable PDF file equals folder. Create file and put that PDF blob right in there. And we need the uh, URL variable P-F-U-R-L equals PDF file dot get url.

14:50 Actually we did that right here. Once we get that PDF , once it's saved to the file, a folder. Once we get this URL and we're gonna put that into the sheet later. Actually, we'll do that right now. Uh, not that but sheet dot get range . We're still in the row. We're on. Uh, column three, set value. P-D-F-U-R-L. Now we want to trash the original copy. So we've created a copy in slides, saved it as a PDF . We want to go back and delete that slide. You can do this optionally. I like to do this just to clean up my drive, not have a, the original template , then a copy, then A PDF and only send that PDF , right?

15:36 I can send create this PDF on the fly. So get filed by id. CT copy id, oops, that's not it. Cert copy id. And then after the parentheses set, trashed and true. So actually what it's gonna do is it's gonna delete it in 30 days where it's actually just gonna end up in your, uh, trash folder. But again, this is just the slide copy. We still have that PDF , and we still need to email it to someone. But if you noticed, we have this PDF blob that we've created, and that's what we're attaching here. We have the name already. So the last thing we might wanna do is a spreadsheet app, docket UI alert, and do a nice checkbox certificate.

16:21 Created and emailed and put a little mail. Let's save and let's see that it's all working again. We've already authorized, if you haven't done it yet, when we go to run this, you'll be asked to authorize, so I am using my own name. I'm gonna send it to my own email and we'll check it. We'll check if we have any, uh, types of errors. We're gonna find them here. I'll walk through. Maybe I typed something wrong, typed it in a bad way, whatever. We'll see if it works or not. As typed. And it's running still. There it is. Certificate created. No, gotta fix that. Created. Here's the PDF . And there it is, Andy Pandy. And it is today's date that I'm recording this. Very cool.

17:07 Right. Very, very awesome. Didn't have to have any errors or failures. Some common errors you might run into is you typed something wrong, typed a wrong parentheses, or didn't end a parentheses or have like an interpolation wrong. Maybe you're not using interpolation, maybe you're using single uh quotes, not back ticks. I would recommend using back ticks and this interpolation. There is another way to do this. Essentially, you can say, I'll do it. Uh, name plus. Then just a string certification. You could do that if you wanted. And down here, I just want to fix the created. There we go. Save. And now, every single road that I have a name and an email address, I can create. I can just put my cursor on there. Go up to the automation menu. It's gonna get the row, it's gonna get the name, it's gonna get an email.

17:52 It's gonna create that PDF , and then send it. Very, very cool. Right. Let's double check that it's actually being sent. Let's check my sent folder. Actually, my inbox. Sorry. Here's my inbox. Here it is. Congrats attached. It's not attached. Look at that. Let's fix that. I wonder if we just change this to cons. I wonder if that variable is making it, uh, let's try doing that again. Oh, I see. It's not that that's, that's not it at all. It's because I misspelled attachments. I put an E there. Attachments. That is a misspelling that will ruin your day. Okay, let's try that again. Let's delete this. I'm not actually deleting the PDF , I'm deleting the URL here. We actually have to go back and delete it, but let's try it again. It says it's been completed. Let's see.

18:38 Yes, it is. Hi. Attached to your certificate. Here's the PDF . It's attached. It's in my email, it's in my sent box. It has been sent to that email. Awesome. Very, very cool. Right. Wow. What a tiny little error . A tiny little mistyping can change everything. So again, down below in the description is a link to get exactly this, uh, exactly this code. Get it? You might want to use a different, uh, slide presentation slide your all. I made it really easy to put yours up here. Cool. Enjoy. You are watching better sheets here on YouTube. Make sure you check out this video or this video and subscribe right now to get more tips, tricks, how tos, get more out of your Google

19:24 sheets than you ever have before. I'm excited to be making a ton more videos here. Ask me questions down in the comments and I will answer them in future videos. But for right now, right here, one of these videos is gonna be your next Google sheet.