Do you know Why digital marketers use google sheets?

Email Validation on Google Sheets is one of the top 10 tools available in the digital marketing community. Google sheets are the most popular and effective tools for digital marketers where they consolidate and compile the data like email address lists. 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 the capability to edit the same file by two or more users. This is why most successful digital marketers are adapting to google sheets.

How to generate leads on google sheets?

There are infinite possibilities with google sheets for digital marketers. Here we will explain how you make sure the email address list is hygienic 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 in digital marketing

Gamalogic provides efficient email validation services, maintaining the best accurate results with quick responses. 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 it into 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 addresses, Red is 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.

This Email Validation on Google Sheets is an old tutorial. New no code integration is available. Go through the addon in this link.