In the world of search engine optimization (SEO), data is king. Whether you’re tracking keyword rankings, auditing site performance, or monitoring technical SEO metrics, you’ll likely spend a lot of time working with data. One of the best tools for SEO professionals to manage and analyze data is Google Sheets—but what really takes it to the next level is the power of Google Apps Script.
By using simple scripts, you can automate a lot of SEO tasks, saving yourself countless hours and improving your workflow. In this post, we’ll explore 5 useful Google Sheets scripts every SEO expert should know.
Jump to the scripts:
What is a Google App Script?
Google Sheets App Script is a cloud-based scripting platform that allows users to extend the functionality of Google Sheets by automating tasks, connecting to external services, and building custom workflows. Built on JavaScript, it enables users to write code to interact with Google Workspace applications, such as Google Sheets, Gmail, and Google Drive, among others. With Apps Script, users can automate repetitive processes, such as pulling data from external sources, sending automated emails, or generating reports. It’s particularly useful for creating custom functions, building macros, and scheduling tasks, turning Google Sheets into a powerful, automated tool.
How to use an app script in Google Sheets?
Here are some quick steps to get started with Google Sheets App Script:
Open Google Sheets and navigate to the spreadsheet you want to automate.
Go to the "Extensions" menu and select "Apps Script."
In the Apps Script editor that opens, write or paste your JavaScript code.
Click "Save" (give your project a name if prompted).
To run the script, click the "Run" button (the play icon).
Authorize the script if required by allowing necessary permissions.
Test your script by returning to the sheet or running it again.
Optionally, set up triggers (like time-based automation) by going to Triggers > Add Trigger.
This workflow allows you to automate tasks or add custom functions in Google Sheets.
5 Google App Scripts For SEO in Google Sheets
1. Fetch Title and Meta Description
A basic yet powerful part of any SEO audit is ensuring your pages have proper titles and meta descriptions. This script pulls the title and meta description for a list of URLs directly into your Google Sheet. It’s great for quickly auditing large websites and identifying missing or duplicate tags.
Script:
function fetchTitleAndMeta() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var urls = sheet.getRange('A2:A').getValues();
for (var i = 0; i < urls.length; i++) {
var url = urls[i][0];
if (url) {
try {
var response = UrlFetchApp.fetch(url);
var html = response.getContentText();
var title = html.match(/<title>(.*?)<\/title>/i);
var metaDesc = html.match(/<meta name="description" content="(.*?)"/i);
sheet.getRange(i + 2, 2).setValue(title ? title[1] : 'No Title Found');
sheet.getRange(i + 2, 3).setValue(metaDesc ? metaDesc[1] : 'No Meta Description Found');
} catch (error) {
sheet.getRange(i + 2, 2).setValue('Error');
sheet.getRange(i + 2, 3).setValue('Error');
}
}
}
}How it works: Paste your URLs into column A, and the script will extract the title into column B and meta description into column C.
2. Check HTTP Status Codes
Ensuring that your pages return the correct HTTP status codes is critical for SEO. Broken links (404 errors), redirects (301, 302), or server errors (500) can hurt your website’s ranking. This script checks the HTTP status code for each URL in your list.
Script:
function checkHTTPStatus() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var urls = sheet.getRange('A2:A').getValues();
for (var i = 0; i < urls.length; i++) {
var url = urls[i][0];
if (url) {
try {
var response = UrlFetchApp.fetch(url, {muteHttpExceptions: true});
var statusCode = response.getResponseCode();
sheet.getRange(i + 2, 2).setValue(statusCode);
} catch (error) {
sheet.getRange(i + 2, 2).setValue('Error');
}
}
}
}How it works: Paste your URLs into column A, and the HTTP status codes will appear in column B.
3. Pull Keyword Rankings from Google Search Console
Keyword tracking is vital for SEO, and pulling keyword rankings from Google Search Console (GSC) helps you monitor performance over time. With this script, you can extract keyword data for your website using the Google Search Console API.
Script:
function getKeywordPosition() {
var siteUrl = 'https://www.example.com'; // Your site URL
var startDate = '2024-01-01'; // Start date of the report
var endDate = '2024-09-01'; // End date of the report
var request = {
startDate: startDate,
endDate: endDate,
dimensions: ['query'],
rowLimit: 100
};
var response = SearchConsole.UrlTestingTools.searchanalytics.query(siteUrl, request);
var rows = response.rows;
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
for (var i = 0; i < rows.length; i++) {
var query = rows[i].keys[0];
var position = rows[i].position;
sheet.getRange(i + 2, 1).setValue(query);
sheet.getRange(i + 2, 2).setValue(position);
}
}How it works: Replace the site URL and date range with your data. The script will pull the keyword rankings for your site and display them in your sheet.
4. Find Duplicate Titles
Duplicate titles can lead to confusion for search engines and hurt your SEO. This script quickly scans your list of page titles and identifies duplicates.
Script:
function findDuplicateTitles() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var titles = sheet.getRange('A2:A').getValues();
var titleMap = {};
for (var i = 0; i < titles.length; i++) {
var title = titles[i][0];
if (title) {
if (titleMap[title]) {
titleMap[title].push(i + 2);
} else {
titleMap[title] = [i + 2];
}
}
}
for (var title in titleMap) {
if (titleMap[title].length > 1) {
titleMap[title].forEach(function(row) {
sheet.getRange(row, 2).setValue('Duplicate');
});
}
}
}How it works: List page titles in column A. Duplicates will be marked in column B.
5. Calculate Word Count for Page Content
Content is one of the most critical ranking factors in SEO, and word count can be a useful metric. This script extracts the word count of a webpage by stripping away the HTML tags and counting the words.
Script:
function fetchWordCount() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var urls = sheet.getRange('A2:A').getValues();
for (var i = 0; i < urls.length; i++) {
var url = urls[i][0];
if (url) {
try {
var response = UrlFetchApp.fetch(url);
var html = response.getContentText();
var text = html.replace(/<[^>]+>/g, ''); // Remove HTML tags
var wordCount = text.split(/\s+/).filter(function(word) { return word.length > 0; }).length;
sheet.getRange(i + 2, 2).setValue(wordCount);
} catch (error) {
sheet.getRange(i + 2, 2).setValue('Error');
}
}
}
}How it works: Input URLs in column A, and the word count of each page will appear in column B.
These scripts are useful not just for audits but also for continuous monitoring of your SEO performance. Incorporate them into your daily tasks to see real improvements in efficiency!
Google Sheets, paired with Google Apps Script, can be a powerful tool for automating many of your SEO tasks. By using these five scripts, you can easily audit your site, check for broken links, monitor keyword rankings, and more—all from within Google Sheets.
For any SEO professional, mastering these scripts will enhance your workflow, save time, and ensure you’re getting the most out of your data. Happy optimizing!
Comments