Email validation using google sheets integration using Gamalogic API

Do you know Why digital marketers use google sheets

Google sheets are the most popular and effective tools for digital marketers where they consolidate and compile the data like email address list. And it is a free service hosted on the cloud with access to multiple devices in real-time. It is an alternative to MS Excel with having the capability to edit on the same file by two or more users. This is why most of the successful digital marketers are adapting on google sheets.

There are infinity possibilities with google sheets for digital marketers. Here we will explain how you make sure the email address list is hygiene in google sheets. In the modern competitive market, you need to make sure your effort is efficient. You can go through our previous blog about the importance of email validation on digital marketing

Gamalogic provides efficient email validation services, maintaining the best accurate results with quick response. Before going further make sure you have a valid Gamalogic email validation API key. You can get it for free once you register with us here.

1000 email address list will be validated at a time

Step 1

Open your google sheets with the email address listed in column A.

Step 2

Click Tools from the menu and navigate to Script Editor. Then a new window pops up with a new file code.gs.

Step 3

Copy the below code and paste in code.gs and then save it with any project name.

function displayData()
{
var apikey = 'APIKEY'
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
count = sheet.getLastRow();
var row_list = ss.getDataRange().getValues()
if (count >1001)
{ Browser.msgBox("Maximum Email Address allowed is 1000")
return;
}
else
{
payload = create_payload(row_list)
result = get_result(payload,apikey)
}
if (result.getResponseCode() == 200) {
var params = JSON.parse(result.getContentText());
var result = params.gamalogic_emailid_vrfy;
var array_result = [];
var color = [];
for (var i =0; i < count ; i++)
{
data_result = [];
color[i] = new Array(8);
data_result[0] = result[i]["do_you_mean"]
data_result[1] = result[i]["is_role"]
data_result[2] = result[i]["is_unknown"]
data_result[3] = result[i]["is_valid"]
data_result[4] = result[i]["is_syntax_valid"]
data_result[5] = result[i]["is_catchall"]
data_result[6] = result[i]["message"]
data_result[7] = result[i]["is_disposable"]
color[i][1] = "red";color[i][2] = "red";color[i][3] = "red";color[i][4]
= "red";color[i][5] = "red";color[i][6] = "red";color[i][7] = "red";
if (!data_result[3] && !data_result[2]) {color[i][1] = "red",color[i][2]
= "red",color[i][3] = "red",color[i][4] = "red",color[i][5] = "red",color[i][6]
= "red",color[i][7] = "red"}
else if (data_result[5] && data_result[5]) {color[i][1] = "yellow",color[i][2]
= "yellow",color[i][3] = "yellow",color[i][4] = "yellow",color[i][5] = "yellow",
color[i][6] = "yellow",color[i][7] = "yellow" }
else if (data_result[2]) {color[i][1] = "grey",color[i][2] = "grey",color[i][1] =
"grey",color[i][3] = "grey",color[i][4] = "grey",color[i][5] = "grey",color[i][6]
= "grey",color[i][7] = "grey"}
else if (data_result[3] && !data_result[5]) {color[i][1] = "green",color[i][2]
= "green",color[i][3] = "green",color[i][4] = "green",color[i][5] = "green",color[i][6]
= "green",color[i][7] = "green"}
array_result.push(data_result)
}
sheet.getRange(1, 2, count, 8).setValues(array_result).setBackgroundColors(color);
sheet.insertRowBefore(1)
var rows = sheet.getDataRange();
var lr = sheet.getRange('A1:I1');
lr.setBackground(null);
lr = sheet.getRange('A2:I2');
var head = new Array(8);
head[0] = 'Email Address'
head[1] = 'do_you_mean'
head[2] = 'is_role'
head[3] = 'is_unknown'
head[4] = 'is_valid'
head[5] = 'is_syntax_valid'
head[6] = 'is_catchall'
head[7] = 'message'
head[8] = 'is_disposable'
sheet.getRange(1, 1, 1, 9).setValues([head]).setFontWeight("bold");
sheet.insertRowBefore(1)
sheet.getRange(1,1).setValue(['Total number of email address']).setFontWeight("bold");
sheet.getRange(1,2).setValue([count]);
sheet.getRange(1,3).setValue(['Credits Balance']).setFontWeight("bold");
getBalance(apikey);
SpreadsheetApp.flush();
}
else{Browser.msgBox("Contact support@gamalogic.com")
return;}
}
function getBalance(apikey) {
var response = UrlFetchApp.fetch("https://gamalogic.com/creditbalance/?apikey="+apikey);
var json = response.getContentText();
var data = JSON.parse(json);
var sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange(1,4).setValue(data['Credit_Balance'])
}
function create_payload(row_list) {
var payload ={"gamalogic_emailid_vrfy": []}
for (var i =0; i < count ; i++)
{
payload["gamalogic_emailid_vrfy"].push({"emailid" : row_list[i][0] });
}
count_address = Object.keys(payload["gamalogic_emailid_vrfy"]).length
payload =JSON.stringify(payload)
return payload
}
function get_result(payload,apikey) {
var url = "https://gamalogic.com/bulkemailvrf/?apikey="+apikey
var options =
{
"method" : "GET",
"payload" : payload,
'contentType': 'application/json'
};
var result = UrlFetchApp.fetch(url, options);
return result
}

Step 4

You will need to change the API key in line 3. Copy Gamalogic API key from the dashboard and replace it with APIKEY in the script. Before running the script make sure the function about to run is displayData. Then run the script. You will need to allow your account to access the google script.

You will see the script will start and then validation will start on the google sheet page.

Rows in green are Valid address, Red are not deliverable or invalid ones. Yellow catch-all or accept all emails and Greys are unknown.

In the header, the total number of email addresses will be displayed with balance credits.

To purchase more credits contact our support team at support@gamalogic.com

Go through our video tutorial and comment your suggestions. Thank you


Get started today!

Get instant 10,000 credits for free on registering with us.

TRY FOR FREE
arrow