Page Title Extractor for Google Sheets: Ready To Use Script

The Two-Step Process

1. Add the provided script to Google Apps Script. (What is Google Apps Script?)
2. Apply the custom formula to your data in Google Sheets.

Step 1: Injecting the Script into Google Apps Script

1. Open the Google Sheet where the functionality is needed.
2. Click on Extensions > Apps Script from the top menu. You’ll land on a blank script page ready for your code.
3. Copy and paste the script below. (Make sure to replace any existing code if necessary.)
   • For an explanation of how the script works, see the breakdown section below.
4. Save your work via the save icon. You can name your project (if you want) and exit the editor.

function unescapeHTML(str) {
  var doc = XmlService.parse('' + str + '');
  var decodedStr = doc.getRootElement().getChild('body').getText();
return decodedStr.replace(/&/g, '&')
                .replace(/</g, '<')
                .replace(/>/g, '>')
                .replace(/"/g, '"')
                .replace(/'/g, "'")
                .replace(/•/g, '•');
}
function getPageTitle(url) {
  if (!url) return "Pending URL";
  try {
    var html = UrlFetchApp.fetch(url).getContentText();
    var titleMatch = html.match(/<title>\s*(.*?)\s*</title>/i);
    return titleMatch ? unescapeHTML(titleMatch[1]) : "No title found";
  } catch (e) {
    return "Error fetching URL";
  }
}

Step 2: Deploying the Script to Extract Page Titles and Decode HTML

1. Now that you’re in the Google Sheet, simply type or paste in your URL in a cell.
   1. Example: A1 = “https://www.carricoseo.com”
2. Type =getPageTitle(A1) to get the page title.
   1. Example: B1 = “=getPageTitle(A1)”
3. The cell will display the extracted page title or the decoded HTML string.
4. With this you can now effortlessly extract data for SEO analysis or content management purposes!

Quick Tips & Notes

• The functions (formulas) can be extended to multiple cells for batch operations. Aka: Drag and drop this down a list of cells!
• Usage Limitations: Google Apps Script has daily quotas. As long as you’re not doing this in significantly large bulk batches, you should have nothing to worry about.

Lastly, for those who want to expand their Google Sheets capabilities even further, check out my previous guide on How to Check HTTP Status Codes in Google Sheets. This tutorial compliments this one by showing you another useful application of Google Apps Script + Google Sheets to enhance your SEO toolkit. Appreciate this free resource? If you think this script might save you some time, feel free to buy me a coffee – or beer. Have a great day!

Troubleshooting

Encountering errors with your script? Here’s a quick guide to identify and fix common issues:

• Error fetching URL: This may occur if the URL is incorrect, the server is not responding, or permissions are restricted. Double-check the URL and your network status.
• Dynamic Content: If the page content, including the title, is generated dynamically via JavaScript, `UrlFetchApp` cannot execute the script to retrieve this content. Inspect the page to see if the title tag is set statically in the HTML.
• Google Apps Script Quotas: You might hit daily quota limits for URL Fetch calls. Review Google’s quota limits to ensure you haven’t exceeded them.
• Script Errors: Syntax errors or incorrect range references in your script can cause it to fail. Check the code for any typos or logical errors.

Additional Information

What is Google Apps Script?

Google Apps Script is a robust scripting platform powered by JavaScript that enables automation and extension of Google Sheets, Docs, and other G Suite applications. If you’re not using it, especially with the advent of AI, you should be!

Dissecting the Script

Here’s a detailed breakdown of the script’s functionality, explaining the purpose and mechanics of each section.

function unescapeHTML(str) { … }

• This converts HTML entities into their corresponding characters.
  • Example: When page title use special characters such as the “&” sign, typically it will return the value of that sign, which is “&amp”. Instead of our return values include these values, this function returns just the actual sign (“&”).

function getPageTitle(url) { … }

• Title Retrieval Logic: var titleMatch = html.match(//i);
  • This extracts the title from a webpage’s HTML content.
• Error Handling: try { … } catch (e) { … }
  • This structure ensures graceful handling of any exceptions during URL fetching.

Dissection Glossary

• Regular Expression (Regex): A sequence of characters that forms a search pattern. In programming, regex is used for string searching and manipulation. For example, in the `getPageTitle` function, a regex is used to find and extract the content within HTML title tags.
• HTML Entities: Special characters used in HTML coding that represent characters not available on a standard keyboard, such as < for the less-than sign. These entities prevent conflicts with HTML syntax, which uses similar characters for tags and attributes.

(back to the script section)