00:00 In this video, we're going to do something pretty cool. We're going to create a Google Form that adds a unique ID and sends a confirmation email to the people who sign up through this Google Form and ask them to confirm through a URL.
00:15 Here's a lot of moving pieces, so follow along as I do this from scratch. We've already created, at this point, our Google Form.
00:24 And we're going to write a little bit of code. So, in our Google Form, we're getting, uh, our email and some information, that's all.
00:32 We're going to be adding this column ID, and then we're going to add a column E, Confirmed, and we're going to get that confirmation from the URL.
00:41 I'll click, a lot of moving parts. We're going to have to send the email obviously back. But we, when we create that URL that they're going to click, we're going to have to deploy a web app.
00:53 It's all in Apps Script, so, it's fairly simple execution, but it's fairly moving parts. Uhm, let's do this step-by-step. Let's create the unique ID for each response.
01:06 We're going to have to create ID, uh, function with an event, and we're going to add a trigger to that of when we when it's a form submission, so that E will be very helpful.
01:16 We're going to have a variable row to know where that, uh, range is. We're going to know variable uuid equals utilities dot get uuid.
01:35 and then we're going to have the sheet. It's going to be called responses. So variable responses equals spreadsheet app dot get active spreadsheet dot get sheet by name responses.
01:54 And there. So when we get A form submission we want on responses. Dot get range, get that row that we're on, and the fourth column I do believe that is going to be our responses ID.
02:10 There. Put that in there. Set value UUID. So let's save this and set up the trigger. Triggers on the left and on the bottom right once we go to triggers So we add trigger, choose a function, create ID.
02:24 I'm going to scroll down and do on form submit. Let's save that. First time we save it, we'll have to authorize it.
02:37 Once it's created, let's go and fill out this form. Let's go to tools, manage form, go to live form. We're going to put in a bogus email first.
02:49 For now, caret time, click submit. Let's go back and see. We have our ID. Cool. So, now we have a unique ID with every single email it sent, ah, that's, every form that's submitted.
03:09 Now, we can continue. With this create ID, and probably we'll, might rename it. Or, we might, not. Alright, well, what's the next steps we need to do?
03:24 We've created a unique ID. Let's send an email with that unique ID. So, here we'll do, I'll do mail app.
03:34 You can use, actually, we'll use Gmail app. For this, send email. We need who it's to. We're going to get our email.
03:46 E.namedValues. And in parentheses, we're going to put the name of the value we want, which is email. Variable email equals this.
04:05 So, send email, who it's to, email, who, the subject, confirming, uh, our body. Let's create a variable called body and do it separately.
04:19 So we'll go, variable body equals, Click this link to make sure you submitted. And then we'll add a URL.
04:33 What is this URL? Let's go up to the top and create variable URL equals and a string. This string is going to be something, but let's just do HTTPS Google.com for now.
04:47 This we will change later. Uhm, plus, let's add the UUID here. And let's double check that the email that we're sending when that form is submitted is the same email, uh, same ID that's, uh, sent.
05:04 Alright, let's go back to our form. Submit another response. email Hardy actually we need to use a real email that will be able to access Andrew at testbettersheets.co. Hello!
05:21 Ah, the information doesn't matter. So let's see if we have any errors in our executions. Yep, we do. We're here.
05:37 Okay, so it says it doesn't have the permissions. It says click here to provide permissions. Maybe we didn't, oh, it's probably because we didn't save, uhm, we didn't add this.
06:01 I know why. If you create the trigger after you add the email, it'll authorize then and there. So let's try this again.
06:08 Send another response. There's my email. Hi again. Let's see. Go back and see if there's any executions here.
06:23 It's running. And I got an email. Here we go. Let's see it. Please click this link. There's the UUID.
06:34 It ends in FFA. Let's see if that is the correct one here. FFA, yep. 1241. Cool. So we're sending an email from a form response and saying please click this link.
06:47 Well, what happens when we click that link? That's what we've got to do next. So we're sending that email. So we have a text with confirmation with the UUID.
06:57 We need the user to be able to click it. And what is going to happen is we're going to find that UUID and say hey, this person is confirmed.
07:05 You could also, without the UUID, use just the email address, I assume here. Same thing is going to happen. All right.
07:14 Let's go and do that in our Apps Script. We're going to create a new function. And it's specifically going to be called function doGet with a capital G. we're going to have variable uuid equals, we need to be able to parse this URL.
07:49 It's going to be parameter.uuid. Why is that? Well, once we get this URL, we're going to add the ID here as well.
08:02 We're going to say, plus, uh, plus, quote, question mark, uuid equals. And we'll have this uuid in the URL.
08:14 We need to get the URL first. So, let's just look at this and see. Logger.log uuid. I'm going to save this.
08:23 And then I'm going to click deploy. New deployment. I'm going to create a web app. We're going to execute it as me, anyone has access, deploy.
08:39 Here's the web app URL we're going to use. So let's re-use that here in URL. And this URL, if we submit it with a UUID, we should be logging this here.
08:52 So let's see if that works. Let's go back and submit another response. Email andrew.betasheet.co. Something else. Submit. Let's go look at our email.
09:11 So now we have a new email. And check this out. Please click this link. So this is the link. I'm actually going to open it as a incognito window to test it because sometimes I will admit that weird things happen when you click this and you're already logged in to your account.
09:30 So we'll open it. Actually, it's going to give us an error here. I know that. Uhm, we need to return something.
09:38 Return 200. That's the code we need to say, hey, you submitted. We can actually, uhm, create HTML service.
09:48 Create text. Actually, no, not HTML, sorry.
10:05 ContentService.createTextOutput, you're confirmed. Let's do that. Oh, we don't need to redeploy it, but we do have to edit our URL, Deploy, manage deployments.
10:23 We have this. We just edit it. And give it the new version, new URL, return, deploy. This is the same exact URL as here.
10:40 I think so. Yep, it is. All right, save it. So, now when we, spot one. I wonder if it worked, actually.
10:51 Let's look at executions, do get, here's a do get, and it should log. Uh, let's see, we need, when we go here, we need to go to responses, we need to find the ID and put in confirmed.
11:06 Let's code that as well. So up here, we will, basically say, uh, UUIDs list, actually UUID list equals, we need variable SSE.
11:38 Equals SpreadsheetApp.getActiveSpreadsheet. We're just gonna add, getSheetByName. So we don't have to click that every single time, or write that every single time.
11:58 A, uh, not A, sorry. D column. D to colon D. GetValues. This is an array of UUIDs. For i equals zero, i is less than UUIDList.length, For loop, this is, hey, search through these and find the one that you match.
12:29 And the column, which is five, or e, five, setValue, confirmed.
13:01 Let's deploy manageDeployments. We're going to give it the new version, new stuff. Click deploy.
13:14 Done. Now, let's test it. Send in an email. Send in something else. And let's go look here.
13:28 UID should be created. Email should be sent. Here is our email.
13:46 Open link in incognito window. You're confirmed, but let's see if we have put it in here. Yes. Oh, we did, but it's on the wrong row. Oh, no.
14:00 Let's go fix that. That should be an easy fix. This should be, Oh, is this not right? Oh, because we're looking at the seconds.
14:11 We actually had two here. That should be it. Now we're done. So we can test that again. We got all the way through.
14:21 I want to test it again and share with you some troubles that you might run into with your emails. Let's go and fill out another response.
14:30 So if we use an email here that is not a Gmail account of anyone, it's going to have some issues sending, like, hotmail has some issues, uhm, weird emails.
14:45 Sometimes, I would say about 5% of emails fail, uhm, when people enter them in themselves. So you may have some issues there.
14:52 Are you done? We're almost done. We're going to go to our email and look for a confirmation email. We have it. It's not in our email yet.
15:32 Ah, there it is. Just got there. So it took a minute. So that helps. That happens sometimes. The Gmail sending from Google Sheets doesn't happen immediately.
15:46 It does have to go through Google Drive a bit. Here's this one. We're going to click this link. We're just going to open it in Cognito Window.
15:59 Someone else would click it normally. You're confirmed. Let's go back and double check. Confirmed. Oh, it's still, you know what?
16:08 I made a little bit of an error. Go back to our editor. We saved this with s++2, but we didn't deploy it.
16:15 So any changes we do with the doGet, we have to also deploy. work. So that's another issue that may come up and we have the same. Uh, if you save any kind of edits to this doGet, make sure you deploy it.
16:38 Submit another response. There we go. And we'll look for our email in our inbox or outbox.
16:56 So this one sent pretty quick. Let's look at it. And be able to click it in, uh, incognito window. Again, normal people, if you're not logged in, anyone else is going to be able to just click that link and get here.
17:13 You're confirmed! Let's see, are we? Let's confirm. Yes, we got the right row, finally! So, we have been able to send an email when someone submits a form with their email box address.
17:32 With their email address. And we're able to create a unique ID. we're able to to send that unique ID through email with a confirmation URL to say, hey, please confirm that you're actually getting this.
17:49 And we run into some troubles, but all of your emails are going to be from your spreadsheet owner's account, so if you're doing this in someone else's account, they're going to send those emails.
18:03 Emails must be sendable from an HTML, so, again, any emails that are weird or odd, uh, might not send. But hopefully, you don't run into many issues and you have fun with this.
18:17 If you're doing this yourself, sending an email confirmation with a URL, with a unique ID, let me know how you do.
18:25 I really want to see these out in the wild. These new automations and Apps Scripts. Awesome, thanks for watching.