00:00 Hey, so I found this email validator API, a mailbox layer that give you a thousand API requests every month. So I wanted to create this little video and tutorial to show you how I did this.
00:14 Basically, here's what I do. I have an email address here, and all I have to do is right check, and then a two and what it does is it calls the API and asks is this MX record right now and we'll we'll go through this this you can also do a lot of cool stuff here show you what else you can do and what
00:36 else you can check of emails you can check the validation is it the correct Is it correctly done? Is it correct SMT?
00:46 SMTP? MX record. This is checking the MX record, but we can get a lot of cool stuff. So I'll go through it all.
00:53 But first, what you need to do is check out mailboxlayer.com It is free for a thousand a month. Just get the free plan and go to your dashboard.
01:09 You're going to need your API access key. The sheet that I have set up here, I have API key on API key.
01:16 You cannot put it anywhere else, just A1. And we will, I'll code the whole thing for you in this video and show you all of the things you can edit as well.
01:26 So on your account dashboard, once you get your API key, you're going to see this validate email address. And this right here is actually the API call, and all it is is a URL.
01:39 This might look scary. To you, but we can copy this and also looks scary because those are dark mode for some reason, a black background.
01:49 We need to go to up to extensions app script. So do that. And what you're going to see is not this.
01:55 If you're doing this from scratch, you're not going to see this. You're going to see function. Function. But if you are a BetterSheets member and you're watching this on BetterSheets.co, then you will be able to get this sheet and this Apps Script down below.
02:10 Just copy it there. But I'm going to write this all again, and it's going to go, I'll show you from scratch.
02:20 So here, Thing that I copied here and what this is is literally just a URL. It is app API layer net API slash check and then this is a Question mark with access key and then you're gonna put your access key here But don't do that yet because I did tell you just put your API key here the tab called API
02:43 key. If you're doing this from scratch and you want to follow along, create a new sheet or a new tab and call it API key.
02:51 We definitely need to make sure it's called API key. So we'll have function. We're gonna check email. I'm gonna call this check email.
02:58 We're gonna have a variable email in there and then we're gonna have a couple of Brackets here. We're gonna put all this stuff in the brackets, but I just want to keep showing you that this all will work Even as just a URL.
03:11 I'm gonna take off this SMTP and format for now we can always add them layer later But check out this if I just take this command C and put it into to a URL into a Chrome browser.
03:27 This is the return I get. This JSON is what I get. And let's look at it. We'll look at JSON Beautifier.
03:32 We'll go to the first one. We'll see what that looks like. And this is what it is. It gives us a structured data back.
03:39 And it gives us, If they think that we misspelled the email, it will give us a response pack. We can get the user, which is just the stuff before the at sign.
03:50 We can get the domain, which is all the stuff after the at sign. It validates the format, so it's a name at domain dot something.
04:01 It gives us the the MX found. That's great. We want to know that. And we may want to know the SMTP.
04:08 We also will tell us true or false if it is a catch all address. So if it's like info ad or high ad, it gives us the rollback if there is one and disposable true or false.
04:19 If it's a free email, I think that says if it's It's at gmail.com or at hotmail.com, and then it gives us a score.
04:26 So what we actually care about is if MX has found an SMTP check, so we will do that. But you can get all the other stuff, and I'll show you exactly how to do that in this video.
04:37 So we have this URL. How do we get the response back? In our Google sheet. It's really only one line of code URL fetch app dot fetch.
04:49 And we're going to put a URL here. We literally can and I'll show you this. We can put exactly this URL in here.
04:59 And if we save this and we just go anywhere in our sheet. And we're just gonna do equals check email I think we can do that let's see we want to get actually in order to get a return we need to do variable response equals that and then return something in our in our function we You need to say return
05:23 response. But we want to get the content text, which means we want to get the text back. But let's see what does the response just look like, I don't know.
05:34 We'll see here, check email. It'll be right there. If we have anything, if we say, actually, let's do that. And we get nothing let's delete and try again and we get nothing so let's look at response dot get content text and I think this is magically going to give us back something let's double check
06:04 there we go we got function native code because something went wrong okay i just saved it i didn't change anything saved it and ran it again and here we go we get our response and it's exactly the same response as we get if we just enter the this crazy API URL into our Chrome browser.
06:29 Alright, let's keep going on it because we do not need this whole response back. What we need to do is we want to actually access parts of this JSON.
06:40 Okay, first off we need to actually Get the rid of this API key here We are going to do variable URL equals.
06:49 I'm going to grab the first part of this up to API key Quote and then add API key. We'll add that later as a variable plus and then And we want this text.
07:06 And we'll put it in quotes. And then we'll have email. And we want to put in our variable email. Each time we run this code, we want to put it in the function call.
07:16 So variable API QLs do this. And we need to spreadsheet app dot get active. Spreadsheet dot get sheet by name it's going to be in API key if you did it correctly get range we'll know it is in a one we've memorized that and get value we need to actually get the value of the API key and now all of this
07:43 this line will literally go, go to API key, go to A1, grab that value, and put it in here in this variable API key.
07:52 So that's why we can put that next to this access key equals in our URL. And then we need email, great.
08:04 What are we missing here? We can also log it if we want. We don't need to do that right now.
08:09 But we do need to fix this right here. Fetch URL. So this variable URL will go right here on line 13.
08:18 Fetch URL. But once we fetch it, this get content text is not what we need. We want to create variable text equals this then we could do variable JSON equals JSON in all caps dot parse text Now, what part do we want to do?
08:47 We want to get the MX found equals JSON dot, and we literally just type in MX found. Why do we do that?
08:56 In our viewer, in our JSON, this is email colon. Did you mean colon user colon? Domain colon this these are the things that we can access with that JSON dot so we can use we can actually get the user back in their domain let's see let's call this check actually let's leave that as check email and just
09:23 return turn MX found. We go back to our sheet. Now we have it already here, but let's look at our new one.
09:32 We're using this check that I wrote before, but now we have this new one, check email. We need to put the variable email in there, so we'll go check email A2, and we'll see if this works.
09:45 We get false. Why is that false is because this is a bad email. This is not the correct email Copy and paste this down so we can also do check email a 3 and it's true The MX has been found the MX record MX record is just going to the server and saying is there a male MX a male record here on the a server
10:05 . We also can do SMPT check. Let's do that. And all we have to do is change this MX found. We can also type the this.
10:25 Instead of MX, we can answer. And now, it'll go through and check the email as well for the SMTP. It will check it.
10:37 We can go to, you know, andrew at hotmail.com. Probably is going to be true. Let's see if it's not just misspelled, but if it's a kneel at better sheets Co I don't think that's gonna come up true.
10:56 I don't think so, but that better sheets that kosher. Ah It is because actually I have a catch-all I wonder if they catch all else up so we have catch all here possible catch all let's see change that to catch all and now we will see is this a catch all or not false false false false these are not catch
11:23 all I think it's gonna be Like info at Betasheep's Deco might do it. Or, this might give us back something.
11:38 I mean, these are literally not catch-all, so that's good. So what you can do to make sure that this is a good idea to make sure that this is a good idea to make sure that this is a good idea to make sure that this is a good idea to make sure that this is a good idea to make sure that this is a good
16:22 idea to make sure that this is a good idea. getting the right information you want. You can even create new functions, check for MX.
11:52 And then instead of catch all, we'll do MX found. We can copy this function, paste it and we can get score as well.
12:05 We can check for score and let's just change this answer to score and we can create new rows so we'll say MX record we'll do MX record record, score, and now we go equals check for, what was it?
12:33 Check for MXA2, copy paste that all the way down. We can check for score equals check. For score a2 and we get the score so now with all of this information back we can make a really interesting well-defined answer for is this a correct email or not is this email value validated.
13:06 We get scores, we get MX records, we can get SMTP. Let's add that as well. So we'll check for, what is it again, SMTP.
13:25 So we'll just call it SMTP. This SMTP and if I want it so here's I'm going to give you a little bonus SMTP little bonus tip here at the end of this video if we want to create these new functions but we might not remember these names.
13:45 What we can do is add a comment above and call it at custom function we just have to do this right above every each one of these save that and now when I go to my function check They're all here, they're all here already written.
14:08 I can check for SMTP, A2. It is a auto-filled function in our sheet. Very interesting, so now we can get, okay, this is probably andrew at hotmail.co.
14:22 I wonder if that's gonna, great, these are. That's good, that is false, because I don't think that's a real thing.
14:29 We can see if it's malformed as well. I think it'll give us just a false here. Nothing. Let's see. Nothing.
14:41 At. At. Let's see if that, If this probably, nope, nothing, because I think it fails the, what is it? One of these user domain format valid.
14:55 Let's, I think it'll check that first probably. So let's create format valid just to double check. Format, check, format. So here I changed it a little bit.
15:14 Let's save. And right before we do anything else, equals check format A2. Can even check this first. And then check the other ones later, sort of with it.
15:29 Oh, we get nothing. It's giving us no response back. Let's see if it's at .com. Oh, I think it's because these are actually not correct characters.
15:46 The issue there oh it is true that is the correct form oh my god this is wrong let's see let's get a wrong format here still the same oh this is not very good maybe okay still get nothing that is not a correct format.
16:08 Okay, we get nothing if it's not a correct format. Alright, but really cool thing is, so to review, we are accessing this API layer.net.
16:17 This mail, sorry, what is this called? Mailbox layer. We are using their free API. You can see my usage right now has been, let's 170 requests already.
16:29 I've gone through 17% of my monthly validation allocation. It's a thousand requests. Once you get this set up, you should be able to validate records up to a thousand records a month.
16:43 You can use MX record, SMTPs. You can use MX records, also get scores if you just want to rate it based on the score.
16:50 So this is a low score here. That's actually correct. This is a catch-all email. Zero. This is not a correct email.
16:58 That's perfect. So the score is really telling us, yeah, these are not good. And this is a malformed email. So we can see that in the MX record.
17:07 This is format valid so I'm gonna leave this here so again just change out your API key here if you just want to use this go ahead and use it if you want to retype and create your own functions here go ahead and rewatch this video and check out how to write all of this I hope you enjoyed this and and
17:27 enjoy the email validation template if you have it. I will reset my API key once we stop this video. Bye.