Backlink Tracking Template: Checking Anchor and Linking URLs in Google Sheets

Google Sheets is a powerful tool for managing and analyzing data, including website URLs and related information. In this blog post, we will explore how to use a custom script in Google Sheets to automatically check if anchor texts and linking URLs match on web pages. We will walk you through the step-by-step process of setting up and running the script, allowing you to efficiently validate your URLs without manual effort.

Prerequisites: Before proceeding, ensure that you have a Google Sheets document ready, containing the URLs to be checked in Column B, and the corresponding anchor texts and linking URLs in Columns C and D, respectively. Also, make sure that you have editing access to the Google Sheets document.

Setting up the Script: To begin, open your Google Sheets document and follow these steps:

1. Click on “Extensions” in the menu bar, and select “Apps Script”.

2. In the Apps Script editor, delete any existing code and replace it with the provided script.

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom Menu')
    .addItem('Check Anchor and Linking URL', 'checkAnchorAndLinkingURL')
    .addToUi();
}

function checkAnchorAndLinkingURL() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = sheet.getLastRow();

  // Get the values in Columns B, C, and D
  var pageValues = sheet.getRange("B2:B" + lastRow).getValues();
  var anchorValues = sheet.getRange("C2:C" + lastRow).getValues();
  var urlValues = sheet.getRange("D2:D" + lastRow).getValues();

  // Loop through each row
  for (var i = 0; i < pageValues.length; i++) {
    var page = pageValues[i][0];
    var anchor = anchorValues[i][0];
    var url = urlValues[i][0];

    // Fetch the page content
    var response = UrlFetchApp.fetch(page);
    var content = response.getContentText();

    // Check if anchor and linking URL exist in the page
    var hasAnchor = content.indexOf(anchor) !== -1;
    var hasURL = content.indexOf(url) !== -1;

    // Check if the linking URL is nofollow
    var regex = new RegExp('<a[^>]+href=["\']([^"\']+)[^>]+rel=["\'][^"\']*nofollow[^"\']*["\'][^>]*>', 'i');
    var isNoFollow = regex.test(content);

    // Apply formatting based on the check results
    var anchorCell = sheet.getRange("C" + (i + 2));
    var urlCell = sheet.getRange("D" + (i + 2));
    var noFollowCell = sheet.getRange("E" + (i + 2));

    if (hasAnchor && hasURL) {
      anchorCell.setBackground("green");
      urlCell.setBackground("green");
    } else {
      anchorCell.setBackground("red");
      urlCell.setBackground("red");
    }

    if (isNoFollow) {
      noFollowCell.setValue("NoFollow");
      noFollowCell.setBackground("yellow");
    }
  }
}

3. Save the script by clicking on the floppy disk icon or pressing Ctrl + S.
4. Save the script once again.
5. Close the script editor.

Running the Script: Now that the script is set up, follow these steps to execute it:

1. Refresh your Google Sheets document.
2. You will notice a new menu option labeled “Custom Menu” in the menu bar.
3. Click on “Custom Menu” and select “Check Anchor and Linking URL” to run the script.

The script will initiate the crawling process, sequentially checking each URL specified in Column B. It will access the respective web page, search for the anchor text in Column C and the linking URL in Column D, and compare them with the page’s content. Based on the comparison, the script will apply formatting to the corresponding cells in Columns C and D.

Result Interpretation: The script uses a color-coding system to highlight the results:

• If both the anchor text and linking URL are found on the page, the corresponding cells in Columns C and D will be highlighted in green, indicating a successful match.
• Conversely, if either the anchor text or linking URL is not found on the page, the respective cells in Columns C and D will be highlighted in red, indicating a mismatch.

Furthermore, the script also checks if the linking URL contains the “nofollow” attribute. If found, it adds the value “NoFollow” to Column E and highlights the corresponding cell in yellow.

Features of Backlink Tracking Tool

1. Automated Validation: The script eliminates the need for manual checking of each web page, as it automatically fetches the page content and compares the anchor texts and linking URLs with the page’s content.
2. Clear Visual Feedback: The script applies color formatting to the cells in Columns C and D, making it easy to visually identify whether the anchor texts and linking URLs match or not. Green highlights indicate a successful match, while red highlights indicate a mismatch.
3. “NoFollow” Attribute Detection: In addition to matching anchor texts and linking URLs, the script also checks if the linking URL contains the “nofollow” attribute. It adds the value “NoFollow” to Column E and highlights the corresponding cell in yellow, providing valuable information about the link attribute.
4. Custom Menu Integration: By adding the script to a custom menu in Google Sheets, users can conveniently access and run the script with just a few clicks, enhancing the usability and accessibility of the tool.
5. Scalability: The script supports processing a large number of URLs by utilizing the power of Google Sheets. Users can easily expand their URL list and run the script to validate hundreds or even thousands of URLs in one go.

Use Case

The custom script for checking anchor texts and linking URLs in Google Sheets has several practical use cases, including:

1. Website Maintenance: Webmasters and SEO professionals can utilize the script to verify that anchor texts and linking URLs on their websites are accurate and up-to-date.
2. Content Auditing: Content creators and editors can automate the process of validating external links in their articles or blog posts, ensuring that they correctly point to the intended web pages.
3. SEO Analysis: The script can be used to analyze the presence of specific anchor texts and linking URLs across multiple web pages, allowing SEO specialists to assess the effectiveness of their link building strategies.

Conclusion

By implementing the provided custom script in Google Sheets, you can automate the process of checking anchor texts and linking URLs, saving valuable time and effort. The script crawls the specified web pages, compares the provided data with the page’s content, and provides clear visual cues to indicate matching and mismatching results. With this tool at your disposal, you can effectively manage and validate your website URLs, ensuring accurate and up-to-date information.