Alexa rank checker with email notifications in Google sheets

Alexa rank is considered as one of the key factors in deciding a website’s standard. Right now google.com is the site with rank 1 followed by Youtube and Facebook in second and third places.

Update June 2020 : It’s been 4 years since I started this blog and wrote this article. I still use this same Alexa rank check to date. It works perfectly fine! My Alexa rank has also gone up to 1,63,429 🙂

Alexa rank of any website can be found here – alexa.com

I started this blog last month, I wanted to track this blog’s alexa rank. I don’t want to open alexa site always and check if rank has been increased. So I just wrote some code and developed an alexa rank checker in Google sheets.

This Alexa rank checker will check blog’s alexa rank daily automatically, compare it with previous day’s rank and mail me if there is an increase in rank along with details like old rank, new rank, change in rank etc.

alexa rank checker - codingislove

If you have a website, then you can take a copy of this spreadsheet here – Alexa Rank checker and use it for your website by changing the link in website column.

Read on if you want to know how is it developed.

How to create alexa rank checker

I used a combination of Google sheets built-in functions and Google apps script code. Although It can created using only apps script code, I used both to make my work easier.

Apps script is nothing but JavaScript. It is the scripting language of Google sheets. Have a look at this post if you need a quick walk through of Google apps script.

Steps in development:

Use Alexa Rank API

Alexa has an API at data.alexa.com/data. Use this API to fetch rank by passing appropriate parameters. To fetch rank of codingislove.com, following URL is used : http://data.alexa.com/data?cli=10&dat=snbamz&url=codingislove.com

This API responds with an XML which can be parsed to fetch the rank.

Use IMPORTXML function

Use built-in IMPORTXML function to get data from Alexa API and parse it. Alexa Rank is present in POPULARITY node of XML so we use following formula to get global rank :

=IMPORTXML("http://data.alexa.com/data?cli=10&dat=snbamz&url=codingislove.com","//POPULARITY/@TEXT")

Local rank is present in COUNTRY node of XML so we use following formula to get local rank :

=IMPORTXML("http://data.alexa.com/data?cli=10&dat=snbamz&url=codingislove.com","//COUNTRY/@RANK")

These formulas just fetch alexa rank, lets write some code and add a timed trigger to check rank daily and send an email notification if rank increased.

Don’t get confused with the word ‘increase in rank’ in this post. Lower the rank, better it is.

Use Apps script (Javascript)

Have a look at code once. Following code is used :

function myFunction() {
var sheet =  SpreadsheetApp.getActiveSheet();
var col = sheet.getLastColumn();
var globalrank = sheet.getRange("B7").getValue();
var localrank = sheet.getRange("B8").getValue();
var oldglobal = sheet.getRange(2, col).getValue();
var oldlocal = sheet.getRange(3, col).getValue();
if(globalrank < oldglobal || localrank < oldlocal) {
globalchange = oldglobal - globalrank;
localchange = oldlocal - localrank;
MailApp.sendEmail({
to:"[email protected]",
subject:"Alexa rank increased",
htmlBody:"New global rank is " + globalrank + " increased by " + globalchange + " ranks, New local rank is " + localrank + " increased by " + localchange + " ranks"
});
}
sheet.getRange(1, col+1).setValue(sheet.getRange("B6").getValue());
sheet.getRange(2, col+1).setValue(globalrank);
sheet.getRange(3, col+1).setValue(localrank);
}

This code compares rank with previously stored rank and send a mail if rank has gone up. If no previous data is stored then it just stores the data with current date. Google sheets timed trigger is used to run this function daily.

Google sheets timed trigger is nothing but a scheduler, Just set the time when you want to run a function and it runs at that time. I set time to daily between 9 AM to 10 AM.

Code explanation

First we get current sheet using getActiveSheet method, then get last column of sheet using getLastColumn method. Last column is used to access previously stored data and also to store current data in next column.

Global rank and local rank formula is used in B7 and B8 of the sheet(have a look at screenshot or open spreadsheet link above). Save global rank, local rank, old global rank and new global to specific variables.

See if rank has gone up and send an email using sendEmail function. Detailed explanation about sendEmail function can be found here – Send Email from Google sheets

Last 3 lines store current data to next column of the sheet. Code is almost self explanatory once you have a look at it.

Now just add a daily timed trigger to myFunction by clicking on the clock button in script editor, as shown in screenshot below.

alexa rank checker - codingislove

You can take a copy of this Alexa rank checker here – Alexa rank checker

Wrapping up

If you want to make this completely using apps script, then UrlFetchApp can be used to fetch XML and XmlService can be used to parse XML.

Quick tip

Google sheets automatically generates graphs and charts for data in the sheet. once you have 4 to 5 days of rank data, click on any cell with data and click on Explore button in bottom right of the sheet!

If you have any questions or feedback, comment below.

Ranjith kumar
3.5 2 votes
Article Rating
Subscribe
Notify of
guest

28 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
rajesh
rajesh
7 years ago

interesting…

MUKESH MAJUMDER
6 years ago

Thanks alot,
What an article. Finally found an solution with full details, keep sharing.

Alex
Alex
6 years ago

Hey, Country (local) rank check does not work anymore… any fix?

Alex
Alex
6 years ago
Reply to  Ranjith kumar

Ah I see. Thanks for the quick reply!

clinton
clinton
6 years ago

Thanks so much for these little tutorials. Really helpful and much appreciated.

K
K
6 years ago

Hello!

I’m looking to have an email sent if a cell within a column populates.

I have this set up, but I’m unsure if
sheet.getRange(“B7:B90”).getValue();
works properly with the column range.

How can I get this working properly?

Thanks!

JJ
JJ
6 years ago

So can a google sheet automatically work based on a timer, even when it’s not in use? If it is like so and I got this right, this is really useful and could be used in a lot of interesting ways. Can you confirm I got it right? 🙂

Suraj
6 years ago

Hi Ranjith,

thanks for the spreadsheet, i was looking for the same since morning and astonishing that most of the bigger and reputed site’s code was not working. But, your code worked perfectly. Alex commented earlier that local rank is not working but when i checked it is working perfectly.

Thanks for the tool,

James Renforth
James Renforth
6 years ago

Thanks Ranjith for your help getting the conditional email script working.
James

doreso
doreso
5 years ago

Hi dear Ranjith,
I am a beginner and just changed the url to track my website’s Alexa Rank.
The table at the bottom immediatly shown my site’s rank. Thats cool 🙂
But The upper part date & Rank Columns are still showing your website’s statistics. Is there a way to change them too to show my own site’s Alexa daily data ?
Thanks a lot for sharing this wonderful work with us ! Take care.

Giya
Giya
5 years ago

Hi Ranjith…Your tutorials are really helpful. Thanks so much!!

Deepak
5 years ago

Hi Ranjith,

Great explanations, finally I’ve landed on a right place to get resolved.

I have my own business where I want to send mails to either clients or to myself being a notification or reminder. Let me explain you, say I have a campaign running on from 01.05.2018 till 31.05.2018, here I want a reminder mail to be sent to me/client for campaign extension based on my spreadsheets with a notification on mail stating “reminder” at least 3-4 days before the expiry of 31.05.2018.

Hope you got my query, Kindly do the needful.

Thanks in advance!!

Ankur Jain
5 years ago

Excellent Script!
I have modified it a bit and transposed it to columns and sorted the dates from new to old.

Anvar
5 years ago

Nice work !

I did other tool for getting the SEO Title and Meta Description

https://anvar.in/seo/meta-description-free-tool.html

smarties
smarties
4 years ago

I am really glad to read this weblog posts which contains plenty of helpful
data, thanks for providing these data.

Kristiana White
4 years ago

Thank you super useful.

Rishad
Rishad
4 years ago

it’s really an awesome tricks to track Alexa ranking, i was looking something like this, thanks for shearing

dunnose
dunnose
2 years ago

Hello from France, this is quite an interesting article for me. Thanks a lot

mohammad
mohammad
1 year ago

Syntax error: SyntaxError: Unexpected token ‘;’ line: 8 file: Untitled.gs
whats this err