0:00 So you want to get emails from a Google site into Google Sheets. I'm going to show you two ways. You're probably going to know the first way, which is using a Google Form, but I'm going to show you a better way to get emails.
0:12 Now, this is pretty simple, right? You can create a form in a Google Sheet, and we can just fill it out in the form.
0:21 So on your Google site, you can do one of two things. You can create a link to this Google Form.
0:27 You probably know this already. on Google Sites. Next, you can also embed a Google Form. So over here on the right side, you can say Forms.
0:37 Go to a recent form that was created, which is probably the one, the most recent one we created. We'll insert it, and it will look something like this.
0:46 It will look like a Google Form. Now, this is good and well, but it looks like a Google Form. We really want to make this very simple.
0:56 We want to make it just a little bar. We might be getting e-mails into our Google Sheet for lead magnets, for events, maybe sign-ups for any kind of services, leads, all kinds of things.
1:11 And so, having a Google Form is all well and good, but it just looks like a Google Form. We want to do something a little bit more elegant and a little bit more, uh, minimal, let's say.
1:25 So, I'm going to show you all of the steps you need to get just a simple form, which is, will be filled out by some text.
1:32 When someone hits the submit button, it will not submit to your Google Form, but will submit to your Google Sheet.
1:38 So, we will create a sheet called emails and here it is blank, but we will fill it with emails from our Google site.
1:46 I will show you how. Go up to extensions, app script. Once you open it, you'll get something called x function my function.
1:52 This is what we will end up doing here, this four lines of code. I will do this with you right now, but I just want to show you how much it looks like it's not that much at all.
2:02 You just need to know what to do. So, our function is going to be called do post, P-O-S-T, capital P.
2:09 You will have some event here. A lot of people will make this just the letter E. We will actually use event right now.
2:19 What we're going to do is we're going to one, create a HTML form, very simple HTML form. I'll show you the code for that too.
2:25 If you are a BetterSheets member and watching this on BetterSheets down below. Get this sheet, get the script. It's all ready and done for you.
2:32 You can copy paste it, put it into your sheet right away. Ah, I will show you that HTML code. And we are going to get from that HTML code ah a button click.
2:43 That button click will execute a URL which we will create with Google Apps Script. We're going to create a web app URL and append to it a little bit of text which is the email that somebody fills in.
2:55 So at the end of this, it will be a simple form. somebody enters their email address. And it will submit it to your sheet without Google Forms.
3:06 So we need a do post. We need to take that event. Ah, we're going to append a text which says email equals and then the text that the someone fills out.
3:16 So it'll be the parameter is going to be called email. So we'll do variable email equals event dot parameter dot email.
3:25 Now this text at the end, dot email, is literally whatever text you want. We can call it email address, maybe?
3:31 Let's call it email address to show you a little bit different between the code down below and what we're doing here.
3:37 We're going to need to get all of the emails on to the sheet. We're going to call it emails. We're going to do variable emails equals spreadsheet app dot get active spreadsheet with parentheses dot get sheet by name and the sheet name is emails.
3:56 Let's make sure it is all caps emails. So whatever text we put in here, we're going to connect it here.
4:04 Now we're going to actually append this email address to the sheet. So we will do emails dot append row and in the parentheses in an array, these ah square brackets, we're going to just type in email.
4:21 This is the variable up here. Whatever email address gets sent in our event, in our URL, it's going to be sent down here and it'll be added to the emails page.
4:31 We will then tell that ah form that was submitted, hey, everything went okay. So we'll say return content service dot create text output and the text output is 200.
4:45 200 is just a code between computers. It says a okay. Everything is fine. So I'm going to go and delete this down here because we just retyped it all.
4:56 I will now deploy this project and I will hit new deployment. I'm going to save this. Select web app up here.
5:04 Select the web app up here. We will need to execute it as ourselves, but anyone who has access, changes to anyone, anyone who has access will have access.
5:15 That's what we need to do. It may say only myself when you do this first time. Change it to anyone.
5:21 Click deploy. And when we click deploy, you may have to authorize it if it's the first time you're doing it.
5:26 This is the second time I'm doing it. This web app URL is what we need. We need a copy of this.
5:32 Hit done. Now go over to email. If you grabbed this script already from me at BetterSheets.co, great. That's the email.
5:42 Right here on line 7, web app deployment URL. Actually I will copy this again just so you see. This web app deployment URL in brackets brackets is what we need our, what we just got.
5:57 So what we just got, instead of the brackets here. Thank you. Thank you, sir. We're just going to paste the URL that we just got.
6:05 There you So take all of this. This is a very simple HTML form. It has the text enter your email with a colon.
6:13 It has a box of an input box and then a button that says submit. Uh and this script is just saying hey, once this button is clicked, take whatever is in that text box and send it to this URL.
6:27 So we will take all of this. We'll actually cut it out of here. So that we don't have it again.
6:34 We will go to our site. Instead of um a Google form, we will go up to the top and click insert embed in your Google site.
6:43 Instead of a URL, we are going to embed code and we will paste the code. This is everything that I've already created for you.
6:49 You can copy paste it. Put in your own web app URL and it will work with your sheets. Embed code.
6:54 This is what it looks like. We will insert it and it's just a simple form. All right. Let's publish this.
7:02 And we will see what it looks like and if it works. So I will view publish site. So we have a publish site.
7:11 I'll take it. I will actually open it in an incognito window. That's one weird thing that happens with Google Apps Script.
7:17 If you are running it yourself, you're logged in, you may need to test it with an incognito window. So now we have just a simple form.
7:25 Instead of a Google form that looks like a Google form, enter your email. I'm gonna enter a funky email. gmail.camfytest at gmail.com.
7:36 Hit submit. It will open a new window and it should say something like 200. Yeah, 200. That's the code we put in.
7:42 We can put a message there if we want. Let's go look at our sheet and see it's not there. I see the problem.
7:49 I actually changed email address. We'll, we're just gonna use email because that's what's in the code. I did a little bit of funky thing, where in here, let's look at this, we say form dot email.
8:02 So that's the value, um sorry, email equals. That email equals is what we need to have in our uh uh our script.
8:13 So actually we can do this a different way. We can keep email address here. We need to edit this that says email address.
8:21 That's all we need to do. Hit next. Save. Now publish. Let's publish the change. Let's test this again. We're going to go over view published site.
8:33 Open an incognito window. Let's just get the bare URL. We will open it again. Let's refresh it. I don't know which one, window it was.
8:51 Alright, let's CAMFITEST at gmail.com, hit submit, we get a 200 error, a 200 error, a 200 code. Let's go check our sheet and there it is.
9:04 That's fantastic. So now we have created in less than 10 minutes a form that doesn't look like a Google form that just takes whatever text is there and puts it on our sheet.
9:14 So this is again great for email, ah emails, for event signups, for small little ah lead businesses, lead magnets if you need to send out, if you have them, if you want to do something pretty quick and you don't want to show that Google form on your Google site.
9:29 all the code that I have here. I will actually change this back to email so that this script here works, ah if you want to change the form, um the parameter, it's right here, email, that's what we changed before.
9:47 Ah and everything will work well for you now if you're just getting emails. Alright excited that you can add something really cool to your Google site.
9:56 Hopefully it works for you as explained.