Automated SERP Info Updater for Google Sheets

gistfile1.txt

// You must set SerpAPI private API Key
// https://serpapi.com/dashboard

var api_key = "YOUR_SERPAPI_KEY_HERE"; // Replace with your actual SerpAPI key
var fixedDomain = 'example.com'; // Replace with your domain
var fixedLocation = 'United States'; // Replace with your location

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Create a menu with a name (e.g., 'SERP Update')
  ui.createMenu('SERP Update')
    .addItem('Update SERP Info', 'updateSerpInfo') // Add an item with the function name
    .addToUi(); // Add the menu to the UI
}

function updateSerpInfo() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var dataRange = sheet.getDataRange(); // Get all data in the sheet
  var data = dataRange.getValues(); // Get the 2D array of values

  // Find the columns for "Query", "SERP Title", "SERP Description", and "URL"
  var headers = data[0];
  var queryCol = headers.indexOf("Top queries");
  var titleCol = headers.indexOf("SERP Title");
  var descriptionCol = headers.indexOf("SERP Description");
  var urlCol = headers.indexOf("URL"); // Ensure you have a column for URLs in your sheet

  if (queryCol == -1 || titleCol == -1 || descriptionCol == -1 || urlCol == -1) {
    throw new Error("Required columns ('Top queries', 'SERP Title', 'SERP Description', 'URL') not found.");
  }

  // Iterate over each row (excluding header row)
  for (var i = 1; i < data.length; i++) {
    var row = data[i];
    var query = row[queryCol];

    if (query) {
      var serpInfo = fetchSerpInfo(fixedDomain, query, fixedLocation);
      // Update the "SERP Title", "SERP Description", and "URL" columns
      sheet.getRange(i + 1, titleCol + 1).setValue(serpInfo.title || "Not found");
      sheet.getRange(i + 1, descriptionCol + 1).setValue(serpInfo.description || "Not found");
      sheet.getRange(i + 1, urlCol + 1).setValue(serpInfo.link || "Not found"); // Update URL
    }
  }
}

function fetchSerpInfo(domain, query, location) {
  var url = 'https://serpapi.com/search'
    + '?q=' + encodeURIComponent(query)
    + '&google_domain=google.com'
    + '&num=100' // retrieve 100 results
    + '&location=' + encodeURIComponent(location)
    + '&api_key=' + api_key;

  // Fetch the search results
  var response = UrlFetchApp.fetch(url, {'muteHttpExceptions': true});
  try {
    var json = JSON.parse(response.getContentText());
  } catch (e) {
    // Log the error and the response content for debugging
    Logger.log('Error parsing JSON: ' + e.toString());
    Logger.log('Response content: ' + response.getContentText());
    // Return a default object to avoid further errors
    return { title: null, description: null, link: null };
  }

  // Search for the domain in the results
  for (var i = 0; i < json.organic_results.length; i++) {
    var result = json.organic_results[i];
    if (result.link && result.link.indexOf(domain) !== -1) {
      return {
        title: result.title,
        description: result.snippet,
        link: result.link // Return the URL
      };
    }
  }

  return { title: null, description: null, link: null };
}

This Google Apps Script is designed to automate the process of fetching Search Engine Results Page (SERP) information using SerpApi and updating a Google Sheet with the results. It is particularly useful for SEO analysis and tracking how a specific domain ranks for various queries in Google's search results.

Key Features:

• Custom Menu Integration: Adds a custom menu item 'SERP Update' to the Google Sheets UI for easy access.
• Automated SERP Data Retrieval: Fetches the SERP Title and Description for a given domain based on a list of queries.
• Dynamic Sheet Update: Iterates through each row in the Google Sheet, reading queries and updating corresponding SERP data.

How it works:

1. Setting Up: Users need to input their SerpApi private API key, the domain they want to track, and the location for the SERP.
2. Preparing the Sheet: The Google Sheet should have columns labeled 'Query', 'SERP Title', and 'SERP Description'. The script reads queries from the 'Query' column.
3. Running the Script: Users can run the script by selecting 'Update SERP Info' from the 'SERP Update' menu in the Google Sheets UI.
4. Fetching and Updating Data: For each query, the script uses SerpApi to fetch the SERP information of the specified domain and updates the 'SERP Title' and 'SERP Description' columns with the retrieved data.

Error Handling:

• The script checks for the required columns and throws an error if they are not found.
• Handles API response errors gracefully and sets 'Not found' if the data is not available.

Use Cases:

• SEO professionals tracking the performance of specific keywords.
• Digital marketers analyzing SERP changes over time.
• Businesses monitoring online visibility and search engine presence.

Note:

• Users must ensure their SerpApi key is valid and has sufficient requests available.
• The script operates on the currently active sheet in the Google Sheets document.

This script streamlines the process of monitoring and analyzing SERP data, making it an invaluable tool for anyone involved in SEO and digital marketing.