How To Check Bulk Urls and Metas Using Sheet

If you’re an SEO professional or developer, you may need to check the status code and metadata of multiple URLs for analysis. Instead of manually checking URLs, you can use Google Sheets and code to automate the process. In this blog post, we will describe a step-by-step method to check bulk URLs in Google Sheets via coding for SEO analysis.

Steps to check Bulk Urls using Google sheets

Step 1: Create a Google Sheet Open Google Drive and create a new Google Sheet. Name the sheet and add a header row with the column names “URL”, “Status Code”, “Title”, “Meta Description,” and any other columns you want to include.

Step 2: Get API credentials for Google Sheets API To access the Google Sheets API from your code, you need to obtain API credentials. Follow the instructions provided by Google to create a project, enable the Sheets API, and obtain your API credentials.

Step 3: Install the required packages To interact with the Google Sheets API from your code, you’ll need to install the required packages. Install the “google-auth” and “google-api-python-client” packages by running the following commands in your terminal or command prompt:

Copy codepip install google-auth google-auth-oauthlib google-auth-httplib2 google-api-python-client

Step 4: Write the Python code Now it’s time to write the Python code to check the status code and metadata for the URLs. Here is an example code to get you started:

pythonCopy codeimport requests
import json
import os
from google.oauth2 import service_account
from googleapiclient.discovery import build

# Define your sheet and range name
sheet_name = "Your sheet name"
range_name = "Your range name"

# Define the Google Sheets API credentials
creds = service_account.Credentials.from_service_account_file("path/to/your/credentials.json")

# Define the URL column and the range of URLs
url_column = "A"
start_row = 2
end_row = 100

# Define the columns to populate with the results
status_code_column = "B"
title_column = "C"
meta_description_column = "D"

# Build the Google Sheets API service
service = build("sheets", "v4", credentials=creds)

# Get the values from the sheet
result = service.spreadsheets().values().get(
    spreadsheetId=creds._info['spreadsheet_id'], range=f"{sheet_name}!{range_name}").execute()
values = result.get("values", [])

# Loop through the URLs and get the status code, title, and meta description
for i in range(start_row-1, end_row):
    url = values[i][0]
    response = requests.get(url)
    status_code = response.status_code
    title = response.text.split('<title>')[1].split('</title>')[0]
    meta_description = response.text.split('name="description" content="')[1].split('"')[0]

    # Update the sheet with the results
    update_values = [[status_code, title, meta_description]]
    body = {
        "values": update_values
    }
    range = f"{sheet_name}!{status_code_column}{i+1}:{meta_description_column}{i+1}"
    service.spreadsheets().values().update(
        spreadsheetId=creds._info['spreadsheet_id'], range=range, valueInputOption='RAW', body=body).execute()

print("Done checking URLs!")

Step 5: Run the code and analyze the results. Save the Python code to a file and run it at your terminal or command prompt. The code will loop through the URLs in the specified range, get the status code, title, and meta description, and update the corresponding columns on the sheet

How to check the status code and metadata of multiple URLs in Google Sheets without Codes Easy

If you’re not familiar with coding, there’s an easier way to check the status code and metadata of multiple URLs in Google Sheets. Here’s a step-by-step guide:

Step 1: Install the “Check My Links” extension Go to the Chrome Web Store and install the “Check My Links” extension. This extension checks the validity of links on a web page and highlights any broken links.

Step 2: Copy the URLs to a Google Sheet Open a new Google Sheet and copy the URLs you want to check into the first column.

Step 3: Install the “ImportXML” add-on Go to the Google Sheets add-on store and install the “ImportXML” add-on. This add-on lets you extract data from an XML or HTML file using XPath queries.

Step 4: Use ImportXML to extract the status code and meta data In the second column of your sheet, use the following formula to extract the status code of each URL:

lessCopy code=IFERROR(IMPORTXML(A1,"//@status_code"), "")

In the third column, use the following formula to extract the title of each URL:

lessCopy code=IFERROR(IMPORTXML(A1,"//title"), "")

In the fourth column, use the following formula to extract the meta description of each URL:

cssCopy code=IFERROR(IMPORTXML(A1,"//meta[@name='description']/@content"), "")

Step 5: Analyze the results The status code, title, and meta description for each URL will be populated in the corresponding columns. You can analyze the results to identify any broken links, duplicate titles, or missing meta descriptions.

By following these simple steps, you can quickly check the status code and metadata of multiple URLs in Google Sheets without any coding knowledge.

Video Reference: Sheet Road Map
Service Link: SEO Services