Keyword tracking with Google Sheets

Today, there are a lot of tools to help you track your rankings. Without going into detail between those who offer a Desktop / Mobile split, tracking positions 0, etc. I propose you to track your rankings the old-fashioned way. Not out of phlegm but simply because some cases still require us to go back to our old ways.

No R language, no Python, no Machine learning, just a Google Sheets file (and a Data Studio for the more adventurous).

Track your rankings on the fly

Let us assume that we want to follow the rankings for a particular domain and for specific keywords. We will take a look at:

• The current position.
• The page positioned.
• The page that occupies the first place on the SERP.
• The page positioned right in front of us.
• The page positioned just behind us.
• The title and the meta description (because we’re bigwigs).

If you have SemRush access, then you can specify your API key in the Settings tab and click “Yes” in B2 to retrieve the average monthly search volumes.

Via Tools > Script Editor, we can analyze the following scripts:

• Google Tricks: here, we configure all the magic to recover the data mentioned above.

// -----------------------
// RankTank Google Tricks. 
// -----------------------

/* DEFAULTS */
function presets() {

var MAX_RETRIES = 5; // Number of times to try Google before failing

return {maxRetries:MAX_RETRIES};

}

/* DELAY FETCHURL TO RESPECT QUOTAS */
function delayFetch() {
var milliseconds = Math.floor(((Math.random() * 50) + 25) * 10) // Delay 250-500ms

Utilities.sleep(milliseconds)
}

function getRank(keyword,lang,loc,userDomain) {
if(!keyword)return null;
var urls=[],resultLine=[],
resultURL,resultDomain,title="",meta="",splitURL,ranked=null,
RETRIES=5;

// Begin locale hack 1 November 2017
var g_loc=loc.slice(-2,loc.length);

if(loc=="google.com") {
g_loc="us";
}

// var url='https://www.'+loc+'/search?q='+encodeURIComponent(keyword)+'&fp=1&dpr=2&sns=1&pf=p&tch=1&num=100&rl=lang_'+lang+'&hl='+lang;
var url='https://www.'+loc+'/search?q='+encodeURIComponent(keyword)+'&gl='+g_loc+'&fp=1&dpr=2&sns=1&pf=p&tch=1&num=100&rl=lang_'+lang+'&hl='+lang;
// End local fix

//Strip www from userDomain
splitURL=userDomain.split(".");
if(splitURL[0]=="www")splitURL.shift();
userDomain=splitURL.join(".");

try {
var serp=UrlFetchApp.fetch(url,{muteHttpExceptions:true,method:"GET",followRedirects:true});
var result=serp.getContentText().split('/*""*/');
var searchResults=JSON.parse(result[3]);
var serpResults=searchResults.d.split('<h3 class="r"><a href="/url?q=');

// Let's try Google again as the API tricks us
var block=4
while(serpResults[1] == null && block<9) {
block++
searchResults=JSON.parse(result[block]);
serpResults=searchResults.d.split('<h3 class="r"><a href="/url?q=');
}

// If we still don't have a result we'll assume quota reached
if(serpResults[1] == null){
//console.log({Message: "Empty Check", initialData: serpResults[0]});
resultLine.push(["-","Google Apps (Quota Exceeded)",null,null,null,null,null]);
return resultLine; 
} else {
for(var i=1;i<serpResults.length;i++) {
// Get URL
resultURL=decodeURIComponent(serpResults[i].split('&amp;sa=U')[0]);
resultDomain=resultURL.split("://")[1];
if(resultDomain.indexOf("/")!==-1)resultDomain=resultDomain.split("/")[0];
splitURL=resultDomain.split(".");

//Strip www from resultDomain
//if(splitURL.length>2)splitURL.shift();
if(splitURL[0]=="www")splitURL.shift();
resultDomain=splitURL.join(".");

urls.push(resultURL);

if(userDomain==resultDomain&&!ranked) {
ranked=i;
//Get title
title='<'+serpResults[i].split('</h3')[0];
//Get meta
meta=serpResults[i].split('<span class="st">')[1].split('</span><br></div>')[0].split('</span></td>')[0]; 
}
}

if(urls[0]) {
//console.log(serpResults[1]);
resultLine.push([ranked||"-",urls[ranked-1]||"Not ranked",urls[ranked-2]||null,urls[ranked]||null,urls[0],title.stripTags(),meta.stripTags()]);
return resultLine;
} else {
//console.log({Message: "Broken", initialData: serpResults[1]});
resultLine.push([ranked||"-",urls[ranked-1]||"No response",urls[ranked-2]||null,urls[ranked]||null,urls[0],title.stripTags(),meta.stripTags()]);
return resultLine;
}

} 
} catch(e) { 
return JSON.stringify(e); 
}
}

String.prototype.stripTags = function() {
var s=this.replace(/(<\/span><br>)/g, "\n");
return s.replace(/<[^>]+>/igm, '').replace(/&#39;/igm,"'").replace(/&nbsp;/igm," ").replace(/&amp;/igm,"&").replace(/&quot;/igm,'"');
}

• Sheet Interface: as soon as you add or delete a domain and keywords, the results will be updated directly.

/* Interface between sheet and RankTank_GoogleTricks.gs */

function onEdit(e) {
return null

var newKeyword,i=0;

var FUNCTION_SHEET="internalFunctions";
var MAIN_SHEET="Position Tracker";
var OUTPUT_SHEET_FIRST_RANGE="E11:N11";
var OUTPUT_SHEET_FIRST_CELL="E12";
var COL_KEYWORDS=3; //Column with keywords
var ROW_KEYWORDS=11; //First row with keywords in
var DEBUG_CELL="C8";
var DOMAIN_CELL="C6";
var LANG_CELL="B9";
var LOC_CELL="B6";

var sheet=e||SpreadsheetApp.getActive();
var src=sheet.source||sheet.getSheetByName(MAIN_SHEET);

var mainSheet=e.source.getActiveSheet();
var functionSheet=e.source.getSheetByName(FUNCTION_SHEET);
if(mainSheet.getName() !== MAIN_SHEET) return null; //We only want to watch the main sheet

//if(functionSheet.getActiveCell().getValue()=="") return null;

var debugCell=mainSheet.getRange(DEBUG_CELL).clearContent();
var domain=mainSheet.getRange(DOMAIN_CELL).getValue();
var lang=functionSheet.getRange(LANG_CELL).getValue();
var loc=functionSheet.getRange(LOC_CELL).getValue();

var input={cell:mainSheet.getActiveCell(),
col:mainSheet.getActiveCell().getColumn(),
row:mainSheet.getActiveCell().getRow(),
value:mainSheet.getActiveCell().getValue()};

if(input.col===COL_KEYWORDS && input.row>=ROW_KEYWORDS) {
//User added new keyword
//var userResultRange=input.cell.offset(0,2);
var commandCell=functionSheet.getRange("D3").offset(input.row-11,0);
//var resultCell=commandCell.offset(0, 1);
//var resultRange=commandCell.offset(0, 2);
var currResult=resultCell.getValue();

var functionSheetRank=functionSheet.getRange(input.row-8,5);
var functionSheetURL=functionSheet.getRange(input.row-8,6);
var functionSheetAbove=functionSheet.getRange(input.row-8,7);
var functionSheetBelow=functionSheet.getRange(input.row-8,8);
var functionSheetTitle=functionSheet.getRange(input.row-8,9);
var functionSheetDesc=functionSheet.getRange(input.row-8,10);

var displaySheetRank=mainSheet.getRange(input.row,input.col+2).clearContent();
var displaySheetURL=mainSheet.getRange(input.row,input.col+3).clearContent();
var displaySheetAbove=mainSheet.getRange(input.row,input.col+7).clearContent();
var displaySheetBelow=mainSheet.getRange(input.row,input.col+8).clearContent();
var displaySheetTitle=mainSheet.getRange(input.row,input.col+9).clearContent();
var displaySheetDesc=mainSheet.getRange(input.row,input.col+10).clearContent();

if(input.value==="") {
//Keyword was removed. clear values
commandCell.clearContent();
functionSheetRank.clearContent();
functionSheetURL.clearContent();
functionSheetAbove.clearContent();
functionSheetBelow.clearContent();
functionSheetTitle.clearContent();
functionSheetDesc.clearContent();
return null;
}

debugCell.setValue("Getting rank for '"+input.value+"' on "+domain+" for "+loc+" in "+lang);
commandCell.setValue("fetch"); //Formula watches for this
displaySheetRank.setValue("Starting...");
var i=0;
while(resultCell.getValue()=="") {
i++;
//userResultRange.setValue("Working ("+i+")");
}

functionSheetRank.copyTo(displaySheetRank, {contentsOnly:true});
functionSheetURL.copyTo(displaySheetURL, {contentsOnly:true});
functionSheetAbove.copyTo(displaySheetAbove, {contentsOnly:true});
functionSheetBelow.copyTo(displaySheetBelow, {contentsOnly:true});
functionSheetTitle.copyTo(displaySheetTitle, {contentsOnly:true});
functionSheetDesc.copyTo(displaySheetDesc, {contentsOnly:true});

commandCell.setValue("done");

debugCell.clearContent();

}

return null;

}

• Internal Functions: if the tool is updated, you may be notified. This script also allows you to request the SemRush API once you have added your Key API in the Settings tab.

function checkForUpdate(toolCode, currentVersion) {
  currentVersion=parseInt(currentVersion);
  var updateInformation;
  var updateURL="https://spreadsheets.google.com/feeds/list/1QBKbhx47fK9bAlRMd9Qmi01kBoeaFHvZQevFuRF4nKA/od6/public/values?alt=json&single=true";
  var updateDataRaw = UrlFetchApp.fetch(updateURL, {muteHttpExceptions: true }); //Get info from the Google Sheet update server
  var updateData=JSON.parse(updateDataRaw);
  for(var i=0;i<updateData.feed.entry.length;i++) { if(updateData.feed.entry[i].gsx$code.$t==toolCode) { //Logger.log(updateData.feed.entry[i].gsx$code.$t); var updateVersion=parseInt(updateData.feed.entry[i].gsx$version.$t), updateLink=updateData.feed.entry[i].gsx$updatelink.$t, updateNotes=updateData.feed.entry[i].gsx$whatsnew.$t var updateMessage=(updateVersion>currentVersion) ?
        "VERSION "+currentVersion+"\n\n*** UPDATE ***\nCLICK HERE\nVERSION "+updateVersion :
        "VERSION "+currentVersion+"\n\nUp to date!";
      var updateIcon=(updateVersion>currentVersion) ? "❌" : "✅";
      updateInformation=[updateMessage,updateIcon,updateVersion]; //updateLink,updateNotes
    }
  }
  return updateInformation;
}

function getSEMrushKeywordData(query,db,apiKey) { 
  var apiUrl="http://api.semrush.com/?type=phrase_this&key="+apiKey+"&export_columns=Nq&phrase="+query+"&database="+db;
  var result= UrlFetchApp.fetch(apiUrl,{"muteHttpExceptions":true}).getContentText().split(/\r?\n/);
  return (result.length>1)?result[1]:"-";
}

Note that they belong to Jaco van Wik of Rank Tank, I only added what appears next in the article.

Archive your rankings

To have a history of our rankings, we will add an archiving function to our script panel named: archiver.

In the script editor, click on File > New > Script file.
All that remains is to copy and paste the following lines:

function onOpen() {
var spreadsheet = SpreadsheetApp.getActive();
var menuItems = [];
menuItems.push({name: 'MAJ Archivage', functionName: 'archiver'});
menuItems.push(null);
spreadsheet.addMenu('Mise à jour des données', menuItems);
}

function updateGlobal_() {
var dataTab = 'archivage';
var dataSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(dataTab);
var startRow = 6;
var lastColumn = dataSheet.getLastColumn();

var sourceRow = dataSheet.getRange(2, 1, 1, lastColumn);
dataSheet.insertRows(startRow);

var destRow = dataSheet.getRange(startRow, 1, 1, lastColumn);
sourceRow.copyTo(destRow, {contentsOnly: true});

return true;
}

When the file is reopened, the “Data update” tab will be displayed:

As soon as the update function is activated, the “Archive” sheet will be updated automatically. The script allows to copy the most recent values, and add a line above the previous one in order to paste the values.

Automate your rankings reporting

Those who are in fond of automation will certainly know the “Trigger” option. I’m sure you’ve understood my point. By defining a specific trigger, your data will be updated automatically. No need to come to the file to manually record your positions (we could even consider sending an email automatically once the update is done…).

Still in the script editor, click on the small clock “Triggers of the current project”.

Here, it is sufficient to add the functions one by one as a trigger. In my example, I don’t call the function that requests the SemRush API.

Ingesting your data in Google Data Studio

To close the loop, you may want to integrate your data into one of your Data Studio dashboards. This is relatively easy since the tool allows you to do it natively. That’s why it’s so interesting to work via Google Sheets.

Since the Google Sheets connector is fussy about the order of rows and columns, we create a new sheet in our file called “Connector”.

This script is also added, following the previous one (hereafter the complete script):

function onOpen() {
var spreadsheet = SpreadsheetApp.getActive();
var menuItems = [];
menuItems.push({name: 'MAJ Archivage', functionName: 'archiver'});
menuItems.push(null);
menuItems.push({name: 'MAJ Data Studio', functionName: 'datastudio'});
menuItems.push(null);
spreadsheet.addMenu('Mise à jour des données', menuItems);
}

function archiver() {
var dataTab = 'archivage';
var dataSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(dataTab);
var startRow = 6;
var lastColumn = dataSheet.getLastColumn();

var sourceRow = dataSheet.getRange(2, 1, 1, lastColumn);
dataSheet.insertRows(startRow);

var destRow = dataSheet.getRange(startRow, 1, 1, lastColumn);
sourceRow.copyTo(destRow, {contentsOnly: true});

return true;
}

function datastudio() {
var sss = SpreadsheetApp.getActiveSpreadsheet();
var ss = sss.getSheetByName('Position Tracker');
var range = ss.getRange('A7:E');
var data = range.getValues();

var tss = SpreadsheetApp.getActiveSpreadsheet();
var ts = tss.getSheetByName('Connecteur');
ts.getRange(ts.getLastRow()+1, 1, data.length, data[0].length).setValues(data);
}

A new report is created and a new data source is added. Once in the connector selector, we choose Google Sheets. We allow access to the data. We choose our “old-school-ranking-tool” spreadsheet. Select the Connector sheet (without unchecking both options). Then click on Add to report. Care is taken to check the different fields and associated types.

Finally, a table is added. And that’s it 🍿 Our data will be updated either manually (or via the script to copy and paste the values into the right sheet or automatically via the different triggers).