How to Validate Email Addresses for FREE in 17 Minutes

How to Validate Email Addresses using a free API and Google Sheets.

Allows up to 1,000 free email validations per month.

Watch for free on YouTube:
https://www.youtube.com/watch?v=kGuSR6at4w4

Read the Transcript:

(00:00) hey so I found this email validator API a mailbox layer they give you a thousand API requests every month so I want to create this little video and tutorial to show you how I did this basically here's what I do I have an email address here and all I have to do is write check and then a two and what it does is it calls the API and asks is this uh MX record right now and we'll we'll go through this is you can also do a lot of cool stuff here I'll show you what else you can do and what else you can check of emails

(00:36) you can check the validation is it the correct is it correctly done is it correct smt 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 but first what you need to do these check out mailboxlayer.com it is free for a thousand a month just get the free plan and go to your dashboard you're going to need your API access key the sheet that I have set up here I have API key on APK you cannot put it anywhere else just A1 and we will I'll code the whole thing

(01:17) for you in this video and show you all of the things you can edit as well 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 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 we need to go to up to extensions apps script so do that and what you're going to see is not this if you're doing this from scratch you're not going to see

(01:53) this you're going to see function my function but if you are a better sheets member and you're watching this on bettersheets.com then you will be able to get this sheet and this apps script down below just copy it there but I'm going to rewrite this all again and it's going to go I'll show you from scratch so here we have this thing that I copied here and what this is is literally just a URL it is apilayer.

(02:23) 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 in the tab called API key if you're doing this from scratch and you want to follow along create a new uh sheet or a new tab and call it API key we definitely need to make sure it's called API key so we'll have function we're going to check email I'm going to call this check email we're going to have a variable email in there and then we're

(02:52) going to 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 I'm going to take off this SMTP and format for now we can always add them later later but check out this if I just take this com command C and put it into a URL into uh into a Chrome browser this is the return I get this Json is what I get and let's look at it we'll look at Json beautifier we'll go to the first one we'll see what

(03:27) that looks like and this is what it is it gives us a structured data back and it gives us if if that if 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 we get the domain which is all the stuff after the AD Sign it validates the format so it's a name at domain dot something it gives us the MX found that's great we want to know that and we may want to know the SMTP we also will tell us true or false if it is a catch-all address so if it's like info

(04:03) add or hiat it gives us the rollback if there is one and disposable true or false if it's a free email I think that says if it's at gmail.com or at hotmail.com and then it gives us a score so what we actually care about is if it's MX is found in 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 so we have this URL how do we how do we get the response back in our Google sheet it's really only one line of code URL fetch app dot Fetch and we're going

(04:39) to put a URL here we literally can and I'll show you this we can put exactly this URL in here and if we save this and we just go anywhere in our sheet and we're just going to 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 need to say return response but we want to get the content text which means we want to get the text back but let's see what

(05:19) does the response just look like I don't know 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.get content text and I think this is magically going to give us back something it's Double J there we go we get 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

(06:09) exactly the same response as we get if we just enter the this crazy API URL into our Chrome browser all right 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 okay first off we need to actually get the rid of this API key here we're going to do variable URL equals we're 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 we want this

(06:53) text and we'll put it in quotes and then we'll have email we want to put in our variable email each time we run this code we want to put it in the function call so variable apiqls 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 well no it is in A1 we've memorized that and get value we need to actually get the value of the API key and now all of this line will literally go go to API key go to A1 grab that

(07:35) value and put it in here in this variable API key so that's why we can put that next to this access key equals in our URL and then we need email great what are we missing here we can also log it if we want we don't need to do that right now what we do need to fix this right here fetch URL so this variable URL will go right here on line 13 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.parse

(08:22) text now what part do we want to do we want to get the MX sound equals Json Dot and we literally just type in MX found why do we do that 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 the domain let's see uh let's call this check actually let's leave that as check email and just return MX found we go back to our sheet now we have it already here

(09:11) but let's look at our new one we have 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 we'll see if this works 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 check email A3 and it's true the MX has been found the MX record uh MX record is just going to the server and saying is there a MX a mail record here on this server

(09:46) we also can do SMPT check let's do that and all we have to do is check change this MX found we can also type this instead of MX file we can answer and now it'll go through and check the email as well for the SM TP it will check it we can go to you know Andrew at hotmail.com probably is going to be true I think it's let's see if it's not just misspelled but if it's Neil at bettersheets.

(10:29) com I don't think that's going to come up true I don't think so but that better sheets.com should ah it is because actually I have a catch-all I wonder if the catch all will show up so we have catch all here possible catch all let's see change that to catch all and now we will see it is this a catch all or not these are not ketchup I think it's going to be like info at better sheets deco might do it or [Music] um this might give us back something I mean these are literally not catch-all so that's good so what you can do to make sure that

(11:21) this is getting the right information you want you can even create new functions check for MX 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 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 score and now we go equals check for wait what was it checked for MX A2 we paste that all the way down we can check for score equals check for score

(12:24) 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 validated we get scores we get MX records we can get s t SMTP let's add that as well so we'll check for what is it again SM TP so we'll just call this SMTP and if I want it so here's I'm going to give you a little bonus MTP 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:20) 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 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 hotmail.

(13:56) co 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 we can see if it's malformed as well I think it'll give us just a false here or nothing see nothing at at let's see if that if this is probably nope nothing because I think it fails the what is it one of these user domain format valid 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 let's save and right before we do anything else

(14:50) equals check format A2 can even check this first and then check the other ones later sort of within oh we get nothing it's giving us no response back let's see if it's at.com foreign oh I think it's because these are actually not correct characters I think that was the issue there oh it is true that is the correct form oh my God this is raw let's see let's get a wrong format here it's still the same oh this is not very good maybe okay still get nothing uh this is not a correct format okay we get nothing if it's not not a

(15:39) correct format all right but really cool thing is uh so to review we are accessing this apilayer.net uh this mail sorry what is this called failed box layer we are using their free API you can see my usage right now has been let's see 170 requests already I've gone through 17 of my monthly validation allocation it's a thousand requests uh once you get this set up you should be able to validate records up to you know a thousand records a month you can use MX record smtps you can also get scores if you just want to rate it based on the

(16:18) score so this is a low score here it's actually correct this is of catch-all email zero this is not a correct email that's perfect so this score is really telling us yeah these are not good and this is a malformed email so we can see that in MX record oh 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 re-watch this video and check out how to write

(16:51) all of this I hope you enjoyed this and enjoy the email validation template if you have it I will reset my API key once we stop this video.

Now that you know how to validate emails in a sheet, learn how to email from your sheets.