Streamline On-page SEO Audits in Google Sheets With Apps Script

For SEO professionals and those learning SEO, understanding how to leverage Google Apps Script for automation can significantly streamline your workflow and enhance your SEO efforts. Here’s a breakdown of how to set up a script that analyzes URLs for key SEO elements:

Setting Up the Script

1. Open your Google Sheet and navigate to Extensions > Apps Script.

When the editor opens up, make sure you’re on the “Code.gs” file.

Copy and paste the script below into the editor.

Delete the default code there or paste over it.

Quick tip: If you are comfortable with keyboard shortcuts, they really come in handy when coding.

Step 1 — Control (Cmd on Mac) + A (to select all)

Step 2 — Control (Cmd on Mac) + V (to paste)

function scrapeWebsiteData() {  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();  var urls = sheet.getRange("A2:A" + sheet.getLastRow()).getValues();  for (var i = 0; i < urls.length; i++) {    var url = urls[i][0];    if (!url) continue;    try {      var response = UrlFetchApp.fetch(url, {muteHttpExceptions: true});      var content = response.getContentText();      var statusCode = response.getResponseCode();      // Extract title, meta description, and H1      var title = content.match(/<title>(.*?)<\/title>/i);      var metaDescription = content.match(/<meta\s+name="description"\s+content="(.*?)"/i);      var h1 = content.match(/<h1>(.*?)<\/h1>/i);      // Extract all text content      var textContent = content.replace(/<[^>]+>/g, ' ').replace(/\s+/g, ' ').trim();      // Calculate keyword density      var words = textContent.toLowerCase().split(/\W+/);      var wordCounts = {};      words.forEach(function(word) {        if (word.length > 3) {          wordCounts[word] = (wordCounts[word] || 0) + 1;        }      });      var sortedWords = Object.keys(wordCounts).sort(function(a, b) {        return wordCounts[b] - wordCounts[a];      });      // Extract internal links      var internalLinks = [];      var linkRegex = /<a\s+(?:[^>]*?\s+)?href="([^"]*)"[^>]*>(.*?)<\/a>/gi;      var match;      while ((match = linkRegex.exec(content)) !== null) {        if (match[1].startsWith('/') || match[1].includes(new URL(url).hostname)) {          internalLinks.push({            url: match[1],            text: match[2].replace(/<[^>]+>/g, ''),            surroundingText: getSurroundingText(content, match[0], 50)          });        }      }      // Write data to sheet      sheet.getRange(i + 2, 2).setValue(statusCode);      sheet.getRange(i + 2, 3).setValue(title ? title[1] : '');      sheet.getRange(i + 2, 4).setValue(metaDescription ? metaDescription[1] : '');      sheet.getRange(i + 2, 5).setValue(h1 ? h1[1] : '');      // Top 3 keywords and densities      for (var j = 0; j < 3 && j < sortedWords.length; j++) {        sheet.getRange(i + 2, 8 + j).setValue(sortedWords[j]);        sheet.getRange(i + 2, 11 + j).setValue((wordCounts[sortedWords[j]] / words.length * 100).toFixed(2) + '%');      }      // Check for primary keyword in meta elements      var primaryKeyword = sortedWords[0];      var keywordCheck = '';      if (title && title[1].toLowerCase().includes(primaryKeyword)) keywordCheck += 'Title ';      if (metaDescription && metaDescription[1].toLowerCase().includes(primaryKeyword)) keywordCheck += 'Description ';      if (h1 && h1[1].toLowerCase().includes(primaryKeyword)) keywordCheck += 'H1';      sheet.getRange(i + 2, 14).setValue(keywordCheck.trim());      // Export internal links      for (var k = 0; k < internalLinks.length && k < 10; k++) {        sheet.getRange(i + 2, 15 + k * 3).setValue(internalLinks[k].url);        sheet.getRange(i + 2, 16 + k * 3).setValue(internalLinks[k].text);        sheet.getRange(i + 2, 17 + k * 3).setValue(internalLinks[k].surroundingText);      }    } catch (error) {      sheet.getRange(i + 2, 2).setValue('Error: ' + error.message);    }  }}function getSurroundingText(content, anchor, charCount) {  var index = content.indexOf(anchor);  var start = Math.max(0, index - charCount);  var end = Math.min(content.length, index + anchor.length + charCount);  return content.slice(start, end).replace(/<[^>]+>/g, '').trim();}

3. Rename the project and return to your spreadsheet.

When you first run the script, you’ll need to authorize it.

Sign into your Google account and click “Advanced”

At the bottom, click go to “Untitled Project” (or whatever you named it — it’s Untitled Project by default)

Note: I know it says (unsafe), but Google is just warning you that it is unsafe to run the script because they have not verified it. If you’re building Apps Scripts, you’re going to see this message quite often.

No worries, you are the developer they’re referring to in the warning. Just don’t scare yourself. Always check code before executing to make sure there isn’t anything malfunctioning or potentially malicious result from executing the script.

Remember, these often have access to edit, create, AND delete — wait for it — ALL of your files in Google Drive, depending on if you’re interacting with Docs, Sheets, Slides, etc.

Script Functionality

This script automates several crucial SEO tasks:

• Fetches HTTP status codes
• Extracts meta titles and descriptions
• Pulls H1 tags
• Calculates keyword density
• Identifies top keywords
• Checks for keyword presence in key on-page elements
• Extracts internal links with surrounding context

Using the Script

1. List your target URLs in column A, starting from A2.
2. Run the script via the custom “Web Scraper” menu.
3. The script populates data across columns B through AK.

Key SEO Insights

• HTTP Status: Quickly identify non-200 status codes
• On-Page Elements: Analyze titles, descriptions, and H1s for optimization opportunities
• Keyword Density: While not a direct ranking factor, it helps gauge content focus
• Top Keywords: Identify the most frequently used terms on each page
• Internal Linking: Assess your site’s link structure and anchor text usage

Advanced Applications

• Integrate with third-party APIs like SEMrush or Ahrefs for additional data points
• Create custom functions for specific SEO calculations, such as estimated traffic based on rankings

Best Practices

1. Use keyword density as a general guide, not a strict rule. Focus on natural, high-quality content
2. Leverage the script for bulk analysis of large websites or competitor research.
3. Regularly audit your content using this tool to identify optimization opportunities.

Remember, while this script provides valuable insights, it’s important to interpret the data in the context of your overall SEO strategy and user experience goals.

For more about automating your SEO workflows, check out the Resources page on our website.

‍