SEO Charlie shares some of the essentials in his automation toolset. A must-read for any SEO who wants to free up time for higher-value work.
As an SEO I have often found myself doing important, yet sometimes boring, manual and repetitive tasks; think downloading data, categorising data (keyword research, competitors data, backlinks),cannibalisation, redirect mapping, reporting... The list goes on.
A large chunk of these jobs don’t require much brain power - they follow some simple rules to get to an output, which means they’re prime tasks to be automated.
In this article, I’ll demonstrate how you can extend Google Sheets functionality to assist with the boring day-to-day tasks, making them quicker to do and allowing you to get on with the more exciting aspects of SEO.
What is Apps Script?
Before we get into using Apps Script for SEO automation, what is Apps Script? In short, and in its simplest form, it’s a JavaScript cloud console, that allows you to talk to and run programs across Google's products.
To add some flesh to the bones, take a look at the screen grab from the Apps Script console below which creates a Google Doc and adds a paragraph.
Why is this useful?
Apps Script overcomes fiddly access requirements for APIs
The traditional way to talk to a service like Google Docs is via an API (Application Program Interface). APIs allow two computers / programs to talk to one another - you make a request to an API and it returns some data, often in JSON or XML. For example, take a look at the random user API which returns some user data (developers use it to populate the front end during development and switch to actual data further down the line).
Where it can get more tricky is when the API is restricted to certain users e.g. Google Drive cannot share any file with any user - a user can only access their own files and files that have been shared with them. This is where API keys and authentication comes in. Setting this up across multiple Google services can be time consuming, and this is where some of Apps Script’s value comes in; Apps Script allows you access your data on Google in one workspace without having to query an API or set up authentication - it's taken care of for you. So with relative ease, you can write a program to take some data from a Google Sheet, create a report in Google Docs and then, if you wanted to, email that document out.
Apps Script automation
Automation features are built into Apps Script. The most beneficial of which is an execution trigger. The executiontrigger is somewhat dependent upon the service you are using - for example, atrigger for Google Sheets could be when someone edits a spreadsheet or submits a form. Perhaps the most useful trigger for SEO automation is the time driventrigger - you can schedule your program to execute at regular intervals.
Additionally, the execution log gives you status of your function execution and provides information around the status. If your program fails, by default you’ll receive an email with why the program failed - a debugging godsend.
Apps Script to extend Google Sheets - 3 examples
To give a flavour of how we can use Apps Script to help with SEO tasks, we will create some simple functions to extend Google Sheets. We will write some functions and import them into our GoogleSheet. You then ‘call' the function like you would with a Google Sheet formulai.e. =SUM(A1:10). Detailed instructions and code follow at the end of the article.
For this demonstration we are going to look to do three things:
1. Web crawling
2. Pulling data from third party API’s
3. Simple SEO functions
Web crawling
Apps Scripts ‘URL Fetch Service’ allows you to make requests to resources on the web. We can give it a URL and use the method ‘getContentText()’ to return the HTML of the page. From here we justneed to parse the parts of the page we are after and return them in our function.
Data we might want to parse could include:
- Metadata - titles, descriptions, robots, canonical, schema, etc
- Content - headings, paragraphs, internal / external links, images, etc
- Custom data - products in stock, prices, number of products on page, sub headings, etc
Note - we have used an additional library to parse and query the HTML - Cheerio for Apps Script
In the example below, we have pulled the status code, title, h1, canonical, robots tax, description, outlinks and somecustom extractions using the class and id.
Formula example: = getStatusCode(A1)
In addition, you are not restricted to just getting crawl data - you can use this data to create actions. For example, extract a title and check its length or check for keyword optimisation and return an action such as ‘review length’, ‘review optimisation’ etc.
Ideal use cases
For basic auditing this can be extremely useful (for site wide audits you’ll likely need a paid-for crawler such as Screaming Frog or Botify). For example, you may want to review blog content and find outdated content that requires updating or archiving - you can extract the blog title, the content and the date of publication (given its in the source code) and begin your content audit all within a Google Sheet.
Connecting to third party APIs
To get SEO data you’ll need to pull from third party providers, such as Ahrefs or SEMrush (and plenty others). Again, using ‘URL Fetch’ in Apps Script allows us to make a request to SEO data providers (note, you’ll need an API key from the respective SEO data sources).Like the example above, we parse the response and return the desired data.
In the example below we have used the SEMrush API to pull search volume directly into Google Sheets.
Formula example: = getSearchVolume(A1)
Ideal use cases
Anytime you’ve found yourself copying, pasting and exporting data from a third party SEO tool, you could potentially migrate into an Apps Script function. To give broad idea of data this could include:
- SERP / ranking data: keyword position tracking, SERP features
- Keyword data: keyword research, search volume, competition
- Traffic / URL data: visibility, ranking keywords
- Backlink data: referring domains, backlinks, domain rank
- Crawl data: page rank
Custom SEO functions
Our final example shows how we can create custom functions to get to a required output.
In the example below we show how you can estimate clicks for a keyword, given you have the search volume and a position(an industry CTR is used based upon the position).
Formula example: = getEstimatedTraffic(A1)
Ideal use cases
Custom functions can tackle any function that isn’t built into Google Sheets by default. For example, we often use fuzzy string matching for doing keyword / URL mapping, weighted averages &estimated traffic when looking at ranking positions across subsets of keywords.
Adding Apps Script functions
To run App Script functions as seen above, you need to follow a few steps:
1. Open a Google Sheet
2. Go to ‘Extensions’ -> ‘Apps Script’ to open the Apps Script Console
3. Remove the existing function. Copy and paste the functions listed at the bottom of this article into the console
4. Go to Libraries in the side bar and click“+” to add a new library. Add the ID'1ReeQ6WO8kKNxoaA_O0XEQ589cIrRvEBA9qcWpNqdOP17i47u6N9M5Xh0' and click ‘Look up’ and then click ‘Add’.
5. Return to your Google Sheet and start using the functions e.g. =getStatusCode(A1)
Conclusion
Google Sheets is becoming more sophisticated and has growing capacities to handle larger data sets (it's currently limited at 5 million cells with a maximum of 246 rows). I find myself moving away from trusty Microsoft Excel and favouring Google Sheets with its additional features and functionality. With some simple written JavaScript in Apps Script you can enchance Google Sheets and do your SEO automation with relative ease.
This is scratching the surface of what Apps Script can do - if you want to find out more about Apps Script the best place to start is the documentation. And if you feel so inclined, drop us a line.
Code takeaway
// ID forcheerio - 1ReeQ6WO8kKNxoaA_O0XEQ589cIrRvEBA9qcWpNqdOP17i47u6N9M5Xh0
functiongetContent(url) {
returnUrlFetchApp.fetch(url).getContentText()
}
/**
* Extract metatitle from a page.
*
*@customfunction
*/
functiongetTitle(url) {
const content = getContent(url);
const $ = Cheerio.load(content);
let title = $('title').text();
return title
}
/**
* Extract the H1from a page.
*
*@customfunction
*/
functiongetHeading(url) {
const content = getContent(url);
const $ = Cheerio.load(content);
let heading = $('h1').first().text();
return heading;
}
functiongetCanonical(url) {
const content = getContent(url);
const $ = Cheerio.load(content);
let canonical =$(""link[rel='canonical']"").attr(""href"")
return canonical
}
/**
* Extract robots redirective from a page.
*
*@customfunction
*/
functiongetRobots(url) {
const content = getContent(url);
const $ = Cheerio.load(content);
let robots =$(""meta[name='robots']"").attr(""content"");
return robots
}
/**
* Extract the meta description from a page.
*
*@customfunction
*/
functiongetDescription(url) {
const content = getContent(url);
const $ = Cheerio.load(content);
let robots =$(""meta[name='description']"").attr(""content"");
return robots
}
/**
* Get the status code of a page.
*
*@customfunction
*/
functiongetStatusCode(url) {
let statusCode =UrlFetchApp.fetch(url).getResponseCode()
return statusCode;
}
/**
* Extract theoutlinks from a page.
*
*@customfunction
*/
functiongetOutlinks(url = 'https://www.meltdigital.com/') {
const content = getContent(url);
const $ = Cheerio.load(content);
let links = $('a');
links = [...links].map(i =>i['attribs']['href'])
return `${links}`;
}
/**
* Get the number of outlinks on a page.
*
*@customfunction
*/
functiongetOutlinksCount(url) {
const content = getContent(url);
const $ = Cheerio.load(content);
let links = $('a')
return links.length;
}
/**
* Estimate the number of clicks for a keyword given its search volume and position.
*
*@customfunction
*/
functiongetEstClicks(sv, pos) {
let ctr = {
1: 0.18275, 2: 0.15725, 3: 0.12325, 4:0.8075, 5: 0.425, 6: 0.425, 7: 0.2125, 8: 0.17, 9: 0.17, 10: 0.1275, 11:0.0425, 12: 0.0425, 13: 0.0425, 14: 0.0425, 15: 0.0425, 16: 0.0425, 17: 0.0425,18: 0.0425, 19: 0.0425, 20: 0.0425, 21: 0.0255, 22: 0.0255, 23: 0.0255, 24:0.0255, 25: 0.0255, 26: 0.0255, 27: 0.0255, 28: 0.0255, 29: 0.0255, 30: 0.0255
}
if (pos in ctr) {
return ctr[pos] * sv
} else {
return 0
}
}
/**
* Get the average search volume from SEMrush.
*
*@customfunction
*/
functiongetSearchVolume(keyword) {
// Add your API KEY
let APIKEY = """"
let url =`https://api.semrush.com/?type=phrase_this&key=${APIKEY}&phrase=${keyword}&export_columns=Ph,Nq,Cp,Co,Nr,Td&database=uk`
let response =UrlFetchApp.fetch(url).getContentText()
response = response.split('\n')
let data = response[1]
data = data.split(';')
let sv = data[1]
return sv
}
/**
* Extract some text from a page given the class name.
*
*@customfunction
*/
functiongetByClassName(url, className) {
const content = getContent(url);
const $ = Cheerio.load(content);
let extraction = $(`.${className}`).text()
return extraction
}
/**
* Extract some text from a page given the id.
*
*@customfunction
*/
functiongetById(url, id) {
const content = getContent(url);
const $ = Cheerio.load(content);
let extraction = $(`#${id}`).text()
return String(extraction)
}
