- Create a new Google Sheets file
- Go to Extensions → Apps Script
- Remove all the code and copy-paste the following instead
- Get your SimilarWeb API Key
- Go https://account.similarweb.com/journey/registration and register
- Go to the following page to get your API key
- Generate a key and copy it
- Paste it into your Google Apps Script project instead of YOUR_API_KEY
- Save your project and go back to your sheet
- Use =getTraffic({domain}) like a classic Google Sheets Formula
The new function in Google Sheets:
The code to paste in Apps Script inside Google Sheets:
// Set-up
// 1. Replace `similarWeb_api_key` with your SimilarWeb API Key.
// It will look like this on this file:
// const similarWeb_api_key = "XXXXXXXXXXXXXXXXXXXXXXX"
const similarWeb_api_key = "YOUR_API_KEY"
// 2. Save the Apps Script project
//
// 3. In your sheet, simply use the function =getTraffic({domain})
/**
* Get Traffic Estimation from Domain.
*
* @param {string} companyName.
* @return domainName.
* @customfunction
*/
function getTraffic(domain) {
var url = "https://api.similarweb.com/v1/similar-rank/"+domain+"/rank?api_key=" + similarWeb_api_key;
var options = {
"muteHttpExceptions": true,
}
var response = UrlFetchApp.fetch(url, options);
var responseCode = response.getResponseCode();
var responseBody = response.getContentText();
if (responseCode === 200) {
Logger.log(response)
Logger.log(JSON.parse(response).similar_rank.rank)
similar_rank = JSON.parse(response).similar_rank.rank
estimated_traffic = calculateTraffic(similar_rank)
// display the results in 2 columns
var results = new Array(1);
let info = new Array(3);
info[0]=similar_rank
info[1]=estimated_traffic
results[0]=info
return results
} else {
Logger.log(Utilities.formatString("Request failed for %s, got %d: %s", responseCode, responseBody));
return false;
}
}
function calculateTraffic(rank) {
if (rank > 3079440) {
return "Not Enough Traffic";
}
const data = [ [0, 86400000000],
[20, 1400000000],
[118, 200000000],
[207, 113000000],
[336, 85000000],
[1068, 43000000],
[1564, 26000000],
[2818, 24000000],
[3653, 11000000],
[6000, 8000000],
[13465, 3000000],
[17734, 2000000],
[22000, 4000000],
[57316, 773400],
[111510, 300000],
[141314, 420000],
[513447, 50000],
[1539720, 27000],
[3079440, 13000],
];
let lowerBound = 0;
let upperBound = 0;
let lowerTraffic = 0;
let upperTraffic = 0;
for (let i = 0; i < data.length; i++) {
if (data[i][0] >= rank) {
upperBound = data[i][0];
upperTraffic = data[i][1];
lowerBound = data[i - 1][0];
lowerTraffic = data[i - 1][1];
break;
}
}
if (rank === 5) {
return (lowerTraffic + upperTraffic) / 4;
} else if (rank === 10) {
return (lowerTraffic + upperTraffic) / 2;
} else if (rank === 15) {
return (lowerTraffic + upperTraffic) * (3 / 4);
} else {
return lowerTraffic + (rank - lowerBound) * (upperTraffic - lowerTraffic) / (upperBound - lowerBound);
}
}
