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.

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.

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.

• Author
• Recent Posts

Ranjith kumar

A CA- by education, self taught coder by passion, loves to explore new technologies and believes in learn by doing.

Latest posts by Ranjith kumar (see all)

• Flutter lookup failed in @fields error (solved) - July 14, 2023
• Free open source alternative to Notion along with AI - July 13, 2023
• Threads API for developers for programmatic access - July 12, 2023