This post is an exploration of what’s possible with formulas alone.
I’m going to use formulas to create a dynamic table of contents that updates automatically when you change the sheet names.
The key to it all is the CELL function, which can extract the address of a cell.
Freezing rows and/or columns is a useful and simple technique to lock rows (or columns) of your spreadsheet so that they remain in view even as you scroll down your datasets. You’re anchoring them in place.
Go to the menu: View > Freeze
Choose how many rows or columns you want to freeze:
In this tutorial, you’ll learn how to merge cells in Google Sheets, when to use merged cells in Google Sheets, the pros and cons of using merged cells, and finally, how to identify them with Apps Script.
Continue reading How To Merge Cells In Google Sheets And When To Be Careful
In this tutorial, we’ll create a checklist template in Google Sheets.
We’ll use checkboxes, conditional formatting and a sparkline to build a checklist template like this:
Update May 2022:
Sadly, the Alexa service, on which this post is based, has been discontinued, so the techniques shown below no longer work. However, the Apps Script to save the data on a daily basis is still a valid technique, and so I leave this post up for that reason.
Original Post:
This tutorial will show you how to create an Alexa Rank tracker in Google Sheets, using a couple of formulas and a few lines of code.
Alexa Rank is a third-party tool that measures how popular a website is. The lower your ranking, the higher your site traffic is.
For example, Google is ranked #1 and Facebook and Wikipedia also have very low rankings (and giant traffic). The full tool has a host of useful features, but I’ll show you how you can get a website’s Alexa Rank number and build an archive in your Google Sheet.
Here’s my website Alexa Rank over time:
I’ve been running this Sheet since December 2016, about 1 year after my website was created. In that period, my Alexa global ranking has dropped from 320,000 to 30,000, and my Alexa US ranking has dropped from 160,000 to 15,000.
The first step is to setup a small settings Sheet with the formulas to import the Alexa Rank tracking data.
There are two columns: one for the global ranking figure and one for the US ranking figure.
Cells B2 and C2 are the same, containing the URL of the website in the Alexa Rank tracker: https://www.alexa.com/siteinfo/benlcollins.com
In cell B3, enter this formula to import the global rank:
=VALUE(REGEXEXTRACT(JOIN("|",ARRAY_CONSTRAIN(IMPORTDATA(B2),30,1)),"global.(.+)\|us"))In cell C3, enter this formula to import the US rank:
=VALUE(REGEXEXTRACT(JOIN("|",ARRAY_CONSTRAIN(IMPORTDATA(C2),30,1)),"us:.(.+)\|\}\|rating"))These formulas work by importing the content of the Alexa site info for the given website, and them parsing it with a Google Sheets REGEX formula to extract the relevant numbers.
For more information on these formulas, and an alternative Alexa formula, have a look at this post: How to import social media statistics into Google Sheets: The Import Cookbook
On row 4, in cells B4 and C4 are two manually typed values for the ranking, which are just used as backup values in case the import formula fails (which has happened only a handful of times in the past few years).
Periodically, I’ll paste in the latest formula values as text on row 4, to keep the backup as current as possible.
On row 5, use the IFERROR function in Google Sheets to catch errors and use the backup values instead:
=IFERROR(B3,B4)and
=IFERROR(C3,C4)That’s it for the settings Sheet.
Add another blank Sheet to your Alexa Rank tracker Sheet, with 3 columns: date, global rank and US rank.
Call it alexa_rank.
Open your script editor: Tools > Script editor
And add the following code:
function saveAlexaData() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const dataSheet = ss.getSheetByName('alexa_rank');
const settingsSheet = ss.getSheetByName('settings');
// get the url, follower count and date from the first three cells
const d = new Date();
const global_count = settingsSheet.getRange(5,2).getValue();
const us_count = settingsSheet.getRange(5,3).getValue();
// append new ranking data to Sheet
dataSheet.appendRow([d,global_count,us_count]);
// format date string cell
dataSheet.getRange(dataSheet.getLastRow(),1).setNumberFormat('MMM-YY');
}
Save and Run this script.
(You’ll be prompted to grant the script permission to access your Sheet files the first time you run it.)
It adds a row of data with the date and ranking data to your Sheet.
Run again if you want to see it add new data (but you’ll want to delete this row to avoid duplication).
Now let’s set it up to run on a daily basis.
Under the Triggers option in the left hand sidebar menu, create a new trigger.
Set it to time-driven and run it once a day.
The formulas reflect the value of the Alexa Rank at the current time. The script saves a copy of those ranking values at that point in time. Once the script has been running for a while, you’ll have an archive of historic data.
The final step is to highlight your table of ranking data and Insert > Chart
Format it as you wish.
Voilà! You can now see your Alexa Rank over time.
Using Google Sheets as a basic web scraper
How to import social media statistics into Google Sheets: The Import Cookbook