Google Sheet SEO Hacks

Google Sheets is a powerful tool that can be used for a variety of tasks, including SEO analysis and tracking. Here are some hacks to make the most of Google Sheets for SEO for accountants and individuals:

Scrape Data From Websites “IMPORTXML”

Use the “IMPORTXML” function to scrape data from websites: This function allows you to import data from any website by specifying an XPath query. For example, you can use it to extract title tags, meta descriptions, and other important on-page SEO elements.

Conditional Formatting

Conditional formatting can be used to highlight cells based on certain criteria. For example, you can set up a rule to highlight all URLs that contain a certain keyword or phrase, which can help you quickly identify potential optimization opportunities.

Create Custom Functions:

Google Sheets allows you to create custom functions using Google Apps Script. This can be a powerful way to automate repetitive SEO tasks, such as generating meta descriptions or analyzing backlink profiles.

How To extract URLs of Sitemap in Google Sheets

1) Open Google Docs

2) Go To Tools

3) Go To Script Editor

4) Add A script mentioned below

function getSitemap(url) { var results=[]; if(!url)return; var sitemap=UrlFetchApp.fetch(url,{muteHttpExceptions:true, method:"GET", followRedirects:true}); var document = sitemap.getContentText().split("<url>"); var docHead=document.splice(0,1); for(var i=0;i<document.length;i++) results.push(document[i].split("</loc>")[0].split("<loc>")[1].replace(/&amp;/g,"&")); return results; }

5) Run The Script

6) Now go back to the sheet and put sitemap url in one tab. eg abc.com/sitemap.xml

7) In another tab use the formula =getsitemap(cell number) It will automatically extract all the URLs of the sitemap.

Bulk URL checker:

Simply get the status code of the URLs of your website in an Google sheet without any help of a tool. Now you can easily use this hack to get the Status code 200 or Broken in the Google Sheets. Use these simple steps

1) Open Google Doc

2) Go-To Tools

3) Go To Script Editor

4) Add A script mentioned below

function getStatusCode(url){
 var response = UrlFetchApp.fetch(url);
 return response.getResponseCode();
}

5) Run The Script

6) Now go back to the sheet and put all the URLs. 7) In another tab use the formula =getstatuscode(cell number)

7) In another tab use the formula =getstatuscode(cell number) It will automatically tell you which URL is working and which is not working.

Note: 200 means working ERROR means it is not working properly or is broken.