Check the SERP Rankings for 100 keywords per day with Google Custom Search Engine + App Script and save the results to Google Sheets. Schedule it for daily runs and you get a fully automated solution – Totally FREE!
You do not need a paid tool to check your SERP keyword rankings. I will show you how easy it is to build and automate your ranking checks with Google App Script and Google CSE. What you need to do initially is to run daily checks for your keywords and analyze the ranking data over time directly in google sheets or use it as datasource for a more advanced google data studio dashboard. Just make your copy of the spreadsheet and follow the instructions – then you will have your personal keyword tracker in less than 10 minutes!
Get your FREE SERP Ranking Checker Spreadsheet Copy here.
If you are searching for a free solution of tracking the SERP results of your most important keywords: Keep on reading! I will describe the full process to set up a monitoring solution build with different Google Cloud Services.
- Setup the Google Custom Search Engine
- Scrape SERPs with Google App Script
- Store results into a new spreadsheet tab
- Schedule daily runs for the ranking checker
After applying the steps above, you will have a totally free solution to check the rankings of 100 keywords per day. You will find a ready to use spreadsheet here. Make your personal copy, add your search engine setting, insert your keywords and start monitoring your search rankings. It is no problem to enter more than 100 Keywords – the script automatically splits the keyword set and processes it in multiple daily partitions.
If you enter e.g. 700 keywords in your list, the full processing takes 7 days (7 * 100 Free Quota Requests).
Step by Step Guide for setting up your SERP Ranking Checker
- Make a copy of this spreadsheet: Go to menu: “File->Make a copy”
- Enter Keywords in the “Keywords” Tab: Enter your keywords you want to monitor. If you use the free quota of Google custom search API, the script processes 100 ranking checks per day. If your keyword list is bigger than the free quota, the script goes on with the next (unchecked) keyword automatically. This means you can e.g. check 700 keywords on a weekly basis – totally free.
- Enter Google Custom Search Credentials in the “Settings” Tab: You need to setup a Google custom search engine. Provide the API Key and the Search ID in the “Settings” Tab. Also set your country and language where you want to monitor SERP results. If you don’t have an API Key and Search ID yet you can follow the steps under Prerequisites section on this page.
- Fetch Rankings in “Check-Rankings” Menu Item: Choose “Run Manually” for a one time crawl or “Enable Scheduler” for daily checks. In the background, a daily schedule is created for automating the rank checking script.
- Use Results in “SERP Rankings” for Data Analysis: You can use e.g. Google Data Studio on top of the Spreadsheet Data or you can add your charts directly in this spreadsheet.
When you make a copy, you will also have the Google app script code (javascript) available in your Google sheet, so no action is required in this case. Here is the code that is calling the Google custom search engine API and saving the results into the spreadsheet:
function checkRankings() {
var ss=SpreadsheetApp.getActiveSpreadsheet();
var settingsSheet=ss.getSheetByName('Settings');
var sheet=ss.getSheetByName('Keywords');
var sheetLastRow=sheet.getLastRow();
var dailyLimit=settingsSheet.getRange(2,3).getValue();
var hl=settingsSheet.getRange(2,4).getValue();
var gl=settingsSheet.getRange(2,5).getValue();
var searchId=settingsSheet.getRange(2,2).getValue();
var key=settingsSheet.getRange(2,1).getValue();
if(searchId==""||key=="")
{
throw new Error( "Empty credentials! Please provide them in settings sheet" );
}
if(dailyLimit=="")
{
throw new Error( "Enter daily limit" );
}
if(sheetLastRow<=1)
{
throw new Error( "Please enter keywords" );
}
if(Number(dailyLimit)>Number(sheetLastRow))
{
dailyLimit=sheetLastRow-1;
}
var createdSheet=ss.getSheetByName('SERP Rankings');
if(!createdSheet)
{
createdSheet=ss.insertSheet("SERP Rankings");
createdSheet.appendRow(["Keyword","Title","Rank","Link","DisplayLink","Language","GeoLocation","Date"]);
}
var date=formatDate();
var numberLast=Number(dailyLimit)+1;
for(var i=1;i<numberLast;i++)
{
var keyword=sheet.getRange(i+1,1).getValue();
var url = "https://www.googleapis.com/customsearch/v1?cx="+searchId+"&key="+key+"&q="+keyword+"&gl="+gl+"&hl="+hl;
var encodedUri = encodeURI(url);
var response = UrlFetchApp.fetch(encodedUri).getContentText();
var obj= JSON.parse(response);
if(obj.items)
{
for(var j=0;j<10;j++)
{
var title=obj.items[j].title;
var link=obj.items[j].link;
var displayLink=obj.items[j].displayLink;
createdSheet.appendRow([keyword,title,j+1,link,displayLink,hl,gl,date]);
}
}
}
}
function onOpen() {
var spreadsheet = SpreadsheetApp.getUi();
var menu = spreadsheet.createMenu('Check-Rankings');
menu.addItem('Run Manually', 'checkRankings');
menu.addToUi();
var allTriggers = ScriptApp.getProjectTriggers();
if(allTriggers.length>0)
{
menu.addItem('Disable Scheduler', 'disableTriger');
}
else
{
menu.addItem('Enable Scheduler', 'enableTriger');
}
menu.addToUi();
}
function enableTriger()
{
var allTriggers = ScriptApp.getProjectTriggers();
if(allTriggers.length==0)
{
ScriptApp.newTrigger('scheduledCheckRankings')
.timeBased()
.everyDays(1)
.create();
SpreadsheetApp.getUi().alert('Scheduler is enable ');
}
else{
throw new Error( "Your scheuduler is enabled,to disable refresh sheet please" );
}
}
function disableTriger()
{
var allTriggers = ScriptApp.getProjectTriggers();
if(allTriggers.length>0)
{
ScriptApp.deleteTrigger(allTriggers[0]);
SpreadsheetApp.getUi().alert('Scheduler is disable ');
var props = PropertiesService.getScriptProperties();
var processedRecordCount = props.getProperty("count");
if(processedRecordCount)
{
props.deleteProperty("count");
}
}
else{
throw new Error( "Your scheuduler is disabled,to enable refresh sheet please" );
}
}
function formatDate()
{
var today = new Date();
var dd = today.getDate();
var mm = today.getMonth()+1;
var stringMM=mm.toString();
var stringdd=dd.toString();
if(mm<=9)
{
stringMM="0"+mm.toString();
}
if(dd<=9)
{
stringdd="0"+dd.toString();
}
var yyyy = today.getFullYear();
return stringMM+"/"+stringdd+"/"+yyyy;
}
function scheduledCheckRankings()
{
var ss=SpreadsheetApp.getActiveSpreadsheet();
var logSheet=ss.getSheetByName('Logs');
if(!logSheet)
{
logSheet=ss.insertSheet("Logs");
logSheet.appendRow(["Date","Error"]);
}
var date=formatDate();
var settingsSheet=ss.getSheetByName('Settings');
var searchId=settingsSheet.getRange(2,2).getValue();
var key=settingsSheet.getRange(2,1).getValue();
var hl=settingsSheet.getRange(2,4).getValue();
var gl=settingsSheet.getRange(2,5).getValue();
if(searchId==""||key=="")
{
logSheet.appendRow([date,"Empty credentials! Please provide them in settings sheet"])
throw new Error( "Empty credentials! Please provide them in settings sheet" );
}
var stringLimit=settingsSheet.getRange(2,3).getValue();
if(stringLimit=="")
{
logSheet.appendRow([date,"Empty daily limit"])
throw new Error( "Enter daily limit" );
}
var sheet=ss.getSheetByName('Keywords');
var last=sheet.getLastRow();
if(last<=1)
{
logSheet.appendRow([date,"Please enter keywords"]);
throw new Error( "Please enter keywords" );
}
var limit=Number(stringLimit);
var props = PropertiesService.getScriptProperties();
var counter=0;
var processedRecordCount = props.getProperty("count");
if(processedRecordCount)
{
var setRuntime = {
count: Number(processedRecordCount)+limit,
};
props.setProperties(setRuntime);
counter= Number(processedRecordCount)+limit;
}
else
{
var setRuntimeZero = {
count: Number(0),
};
props.setProperties(setRuntimeZero);
}
var createdSheet=ss.getSheetByName('SERP Rankings');
if(!createdSheet)
{
createdSheet=ss.insertSheet("SERP Rankings");
createdSheet.appendRow(["Keyword","Title","Rank","Link","DisplayLink","Language","GeoLocation","Date"]);
}
var loopLimit=counter+limit;
if(loopLimit>last)
{
loopLimit=last-1;
props.deleteProperty("count");
}
for(var i=counter+1;i<loopLimit+1;i++)
{
var keyword=sheet.getRange(i+1,1).getValue();
var url = "https://www.googleapis.com/customsearch/v1?cx="+searchId+"&key="+key+"&q="+keyword+"&gl="+gl+"&hl="+hl;
var encodedUri = encodeURI(url);
try {
var response = UrlFetchApp.fetch(encodedUri).getContentText();
var obj= JSON.parse(response);
if(obj.items)
{
for(var j=0;j<10;j++)
{
var title=obj.items[j].title;
var link=obj.items[j].link;
var displayLink=obj.items[j].displayLink;
createdSheet.appendRow([keyword,title,j+1,link,displayLink,hl,gl,date]);
}
}
}
catch(err) {
logSheet.appendRow([date,err]);
throw new Error( err );
}
}
}
function reset()
{
var createdSheet=SpreadsheetApp.getActive().getSheetByName('SERP Rankings');
if(createdSheet)
{
createdSheet.clear();
createdSheet.appendRow(["Keyword","Title","Rank","Link","DisplayLink","Language","GeoLocation","Date"]);
}
var props = PropertiesService.getScriptProperties();
props.deleteProperty("count");
}
How does my Python solution look like?
I decided to go with the easy spreadsheet solution because it is practical for a lot of users out there. My own Python solution is built for bigger projects:
- Save results to Big Query. In addition to the ranking results we also scrape the ranking website and store the content as well. I recommend using data partitions in the tables – this saves costs when querying the data set.
- Add more than one Custom Search Engine – you have to add one additional Google Cloud Project per CSE. This will you give more than 100 daily free requests.
- Schedule the runs in Google Cloud by using Pub/Sub and CloudFunctions.
