Tired of manually writing SEO content for hundreds of web pages? What if you could automate the entire process using a simple Google Sheet and the power of AI? This guide will show you how to build a powerful Google Apps Script that connects to the Gemini API, generating unique, SEO-optimized content, complete with images and internal links, all with the click of a button.

This script is perfect for anyone managing large-scale websites, such as e-commerce stores, who needs to create fresh, keyword-rich content for category pages quickly and efficiently.

What You'll Need

A Google Account (to access Google Sheets and Apps Script).
A Google Cloud Project with the Gemini API enabled.
Your Gemini API key.
A Google Sheet with a specific structure.

Step 1: Set Up Your Google Sheet

First, you need to create a new Google Sheet and set up the columns. This is crucial because the script relies on specific column indexes.

Create the following columns in your spreadsheet:

ID: A unique identifier for each row.
Name: The name of the category or product (e.g., "Cordless Drills").
Description: This is where the generated HTML content will go.
H1 title: The generated H1 heading for the page.
Meta keywords: The generated meta keywords.
URLS: The URL of the category page to be used as a context for the AI.
SEO URL: An optional column for the final SEO-friendly URL.
Image: The main image for the page, added as a formula.

ID	Name	Description	H1 title	Meta keywords	URLS	SEO URL	Image

Export to Sheets

Step 2: Get Your Gemini API Key

To use the script, you need to connect to the Gemini API.

Go to the Google AI Studio.
Create a new API key.
Copy the key and save it somewhere secure. You'll need to paste this into the script later.

Important: This API key is a secret. Do not share it publicly.

Step 3: Install the Apps Script

Now for the core of the project.

In your Google Sheet, go to Extensions > Apps Script.
This will open a new tab with the Apps Script editor.
Delete any default code in the Code.gs file.
Copy the entire script from the Code.gs section below and paste it into the editor.
Create two new HTML files by going to File > New > HTML file. Name them StatusDialog.html and ProgressDialog.html.
Copy the code from the corresponding sections below and paste it into each of the new files.
In the Code.gs file, locate the API_KEY constant and replace the placeholder with your actual Gemini API key.

JavaScript

const API_KEY = 'YOUR_API_KEY_HERE'; // <-- PASTE YOUR API KEY HERE

Save the project by clicking the Save project icon.

Step 4: Authorize the Script

The first time you run the script, Google will ask for permission to access your spreadsheet and connect to external services.

In the Apps Script editor, go to Run > Run function > onOpen.
A dialog will pop up asking you to review permissions. Click Review permissions.
Choose your Google account.
Click Allow to grant the necessary permissions.

Once authorized, a new menu item called "🤖 SEO Generator" will appear in your Google Sheet.

Step 5: How to Use the Script

The new menu offers several powerful options:

Generate Content and Images: This is the main function. It processes every row that has a Name and URL but is missing a Description or Image. It calls the Gemini API to create the full HTML content and also scrapes the page to find and embed an image.
Generate Images Only: If you've already generated content and just need to fill in the missing images, this option will process all rows with a valid URL but no Image.
Add Images to Existing Content: This function is useful for older rows. It finds images on the category URL and injects them directly into the generated HTML content, typically after the <h2> tags. It also adds a main image to the Image column if it's missing.
Retry Failed Images: For rows where the image generation failed, this option will attempt to find an image again.
Update Content with Links: This powerful function scrapes a category page to find subcategories. It then updates the generated HTML content, turning plain text mentions of those subcategories into proper HTML hyperlinks (<a>tags). This is great for improving internal linking after the initial content generation.
Preview HTML from Selected Cell: Select any cell in the Description column and use this to see how the generated HTML content will look in a browser.
Clean Existing HTML Content: This option removes unwanted HTML boilerplate tags (<html>, <body>, etc.) from the Description column, which can sometimes be generated by the AI.
Clear Content Columns: Use this to quickly clear the Description, H1 title, Meta keywords, and Imagecolumns in your sheet, allowing you to re-run the generation process from a clean slate.

A dialog with a STOP button will appear during the longer processes, allowing you to stop the script at any time.

The Code

`Code.gs`

// Configuration
const API_KEY = 'AIzaSyDz79z5I5FP6sDgh30v0-ce7CgwwuioOHs'; // <-- IMPORTANT: Make sure your key is here
const MODEL_NAME = 'gemini-1.5-flash-latest';
const API_ENDPOINT = `https://generativelanguage.googleapis.com/v1beta/models/${MODEL_NAME}:generateContent?key=${API_KEY}`;
const STOP_PROPERTY = 'STOP_SCRIPT_EXECUTION';
const STATUS_PROPERTY = 'EXECUTION_STATUS';

// Column indices (starting from 1)
const COL_NAME = 2;
const COL_DESCRIPTION = 3;
const COL_H1 = 4;
const COL_KEYWORDS = 5;
const COL_URL = 6;
const COL_IMAGE = 8;

/**
 * @description Adds a custom menu to the Google Sheets interface.
 */
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('🤖 SEO Generator')
    .addItem('Generate Content and Images', 'processSheet')
    .addItem('Generate Images Only', 'processImagesOnly')
    .addSeparator()
    .addItem('Add Images to Existing Content', 'addImagesToExistingContent')
    .addItem('Retry Failed Images', 'retryFailedImages')
    .addItem('Update Content with Links', 'addLinksToExistingContent')
    .addSeparator()
    .addItem('Preview HTML from Selected Cell', 'previewHtml')
    .addItem('Clean Existing HTML Content', 'cleanExistingHtmlContent')
    .addSeparator()
    .addItem('Clear Content Columns', 'clearContentColumns')
    .addToUi();
}

/**
 * @description Clears the content in the Description, H1 title, Meta keywords, and Image columns.
 */
function clearContentColumns() {
  const ui = SpreadsheetApp.getUi();
  const response = ui.alert(
    'Confirmation to Clear',
    'Are you sure you want to clear the content in all cells of the "Description", "H1 title", "Meta keywords", and "Image" columns? This action is irreversible.',
    ui.ButtonSet.YES_NO
  );

  if (response === ui.Button.YES) {
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    const lastRow = sheet.getLastRow();
    
    if (lastRow > 1) {
      const descriptionRange = sheet.getRange(2, COL_DESCRIPTION, lastRow - 1, 1);
      const h1Range = sheet.getRange(2, COL_H1, lastRow - 1, 1);
      const keywordsRange = sheet.getRange(2, COL_KEYWORDS, lastRow - 1, 1);
      const imageRange = sheet.getRange(2, COL_IMAGE, lastRow - 1, 1);
      
      descriptionRange.clearContent();
      h1Range.clearContent();
      keywordsRange.clearContent();
      imageRange.clearContent();
      
      ui.alert('Done!', 'The content of the selected columns has been cleared.', ui.ButtonSet.OK);
    } else {
      ui.alert('No Data', 'No rows to clear.', ui.ButtonSet.OK);
    }
  } else {
    ui.alert('Canceled', 'The clear operation has been canceled.', ui.ButtonSet.OK);
  }
}

/**
 * @description Shows a dialog with a STOP button.
 */
function showStatusDialog() {
  const html = HtmlService.createHtmlOutputFromFile('StatusDialog')
      .setWidth(300)
      .setHeight(150);
  SpreadsheetApp.getUi().showModalDialog(html, 'Generation Status');
}

/**
 * @description A function called by the STOP button to halt execution.
 */
function requestStop() {
  PropertiesService.getUserProperties().setProperty(STOP_PROPERTY, 'true');
  Logger.log('Stop request received.');
}

/**
 * @description Returns the current execution status for display in the dialog.
 */
function getExecutionStatus() {
  return PropertiesService.getUserProperties().getProperty(STATUS_PROPERTY) || 'Preparing...';
}

/**
 * @description Retrieves the URL of a single image from the category page.
 * @param {string} categoryUrl - The URL of the category.
 * @returns {string|null} - The image URL or null if not found.
 */
function getImageForCategory(categoryUrl) {
  try {
    const response = UrlFetchApp.fetch(categoryUrl, { muteHttpExceptions: true });
    const htmlContent = response.getContentText();
    
    const imageRegex = /<img[^>]+src="(https:\/\/static\.mashini\.bg\/images\/products\/[^"]+\.(?:webp|jpg|jpeg|png))"/;
    const match = htmlContent.match(imageRegex);
    
    if (match && match[1]) {
      return match[1];
    }
    const fallbackRegex = /<img class="product-image-photo"[^>]+src="([^"]+)"/;
    const fallbackMatch = htmlContent.match(fallbackRegex);
     if (fallbackMatch && fallbackMatch[1]) {
      return fallbackMatch[1];
    }

    return null;
  } catch (e) {
    Logger.log(`Error retrieving image from ${categoryUrl}: ${e.message}`);
    return null;
  }
}

/**
 * @description Main function that iterates through rows and generates content and/or images.
 */
function processSheet() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
  PropertiesService.getUserProperties().deleteProperty(STOP_PROPERTY);
  SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutputFromFile('StatusDialog').setWidth(300).setHeight(150), "Processing...");

  const dataRange = sheet.getDataRange();
  const values = dataRange.getValues();
  let rowsProcessed = 0;

  for (let i = 1; i < values.length; i++) {
    if (PropertiesService.getUserProperties().getProperty(STOP_PROPERTY) === 'true') {
      SpreadsheetApp.getUi().alert('Operation stopped by user. ' + rowsProcessed + ' rows processed.');
      return;
    }
    
    const row = values[i];
    const categoryName = row[COL_NAME - 1];
    const description = row[COL_DESCRIPTION - 1];
    const categoryUrl = row[COL_URL - 1];
    const imageUrl = row[COL_IMAGE - 1]; 

    if (categoryName && categoryUrl && (!description || !imageUrl)) {
      try {
        let rowHasBeenProcessed = false;
        if (!description) {
          sheet.getRange(i + 1, COL_DESCRIPTION).setValue('Retrieving subcategories...');
          SpreadsheetApp.flush();
          const subcategories = getSubcategories(categoryUrl);

          sheet.getRange(i + 1, COL_DESCRIPTION).setValue('Generating description with links...');
          SpreadsheetApp.flush(); 

          const generatedContent = callGeminiAPI(categoryName, categoryUrl, subcategories);
          const parsedData = parseContent(generatedContent);

          if (parsedData.description) {
            sheet.getRange(i + 1, COL_DESCRIPTION).setValue('Searching for images to embed...');
            SpreadsheetApp.flush();
            const imagesToInject = getMultipleImagesForCategory(categoryUrl, 3);

            const finalHtml = injectImagesIntoHtml(parsedData.description, imagesToInject, categoryName);
            
            sheet.getRange(i + 1, COL_DESCRIPTION).setValue(finalHtml);
            
            sheet.getRange(i + 1, COL_H1).setValue(parsedData.h1);
            sheet.getRange(i + 1, COL_KEYWORDS).setValue(parsedData.keywords);
            rowHasBeenProcessed = true;
          } else {
            sheet.getRange(i + 1, COL_DESCRIPTION).setValue('Parsing error.');
          }
        }

        if (!imageUrl) {
          sheet.getRange(i + 1, COL_IMAGE).setValue('Searching for image...');
          SpreadsheetApp.flush();
          const foundImageUrl = getImageForCategory(categoryUrl);
          if (foundImageUrl) {
            sheet.getRange(i + 1, COL_IMAGE).setFormula(`=IMAGE("${foundImageUrl}")`);
            rowHasBeenProcessed = true;
          } else {
            sheet.getRange(i + 1, COL_IMAGE).setValue('Image not found.');
          }
        }
        
        if(rowHasBeenProcessed) rowsProcessed++;
        SpreadsheetApp.flush();
      } catch (e) {
        sheet.getRange(i + 1, COL_DESCRIPTION).setValue(`Error: ${e.message}`);
        Logger.log(`Error on row ${i + 1}: ${e.message}`);
      }
    }
  }
  
  PropertiesService.getUserProperties().deleteProperty(STOP_PROPERTY);
  SpreadsheetApp.getUi().alert('Done! All empty rows have been processed. Processed rows: ' + rowsProcessed);
}

function processImagesOnly() {
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    
    PropertiesService.getUserProperties().deleteProperty(STOP_PROPERTY);
    SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutputFromFile('StatusDialog').setWidth(300).setHeight(150), "Processing...");

    const dataRange = sheet.getDataRange();
    const values = dataRange.getValues();
    let rowsProcessed = 0;

    for (let i = 1; i < values.length; i++) {
        if (PropertiesService.getUserProperties().getProperty(STOP_PROPERTY) === 'true') {
            SpreadsheetApp.getUi().alert('Operation stopped by user. ' + rowsProcessed + ' rows processed.');
            return;
        }

        const row = values[i];
        const categoryName = row[COL_NAME - 1];
        const categoryUrl = row[COL_URL - 1];
        const imageUrl = row[COL_IMAGE - 1];

        if (categoryName && categoryUrl && !imageUrl) {
            try {
                sheet.getRange(i + 1, COL_IMAGE).setValue('Searching for image...');
                SpreadsheetApp.flush();
                const foundImageUrl = getImageForCategory(categoryUrl);
                if (foundImageUrl) {
                    sheet.getRange(i + 1, COL_IMAGE).setFormula(`=IMAGE("${foundImageUrl}")`);
                } else {
                    sheet.getRange(i + 1, COL_IMAGE).setValue('Image not found.');
                }
                rowsProcessed++;
                SpreadsheetApp.flush();
            } catch (e) {
                sheet.getRange(i + 1, COL_IMAGE).setValue(`Error: ${e.message}`);
                Logger.log(`Error on row ${i + 1}: ${e.message}`);
            }
        }
    }

    PropertiesService.getUserProperties().deleteProperty(STOP_PROPERTY);
    SpreadsheetApp.getUi().alert('Done! Images have been generated. Processed rows: ' + rowsProcessed);
}


function previewHtml() {
  const ui = SpreadsheetApp.getUi();
  const cell = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getActiveCell();
  if (cell.getColumn() !== COL_DESCRIPTION) {
    ui.alert('Error', 'Please select a cell from the "Description" column that you want to preview first.', ui.ButtonSet.OK);
    return;
  }
  const htmlContent = cell.getValue();
  if (!htmlContent) {
     ui.alert('Cell is Empty', 'The selected cell does not contain any HTML code to preview.', ui.ButtonSet.OK);
     return;
  }
  const htmlOutput = HtmlService.createHtmlOutput(htmlContent).setWidth(800).setHeight(600);
  ui.showModalDialog(htmlOutput, 'HTML Preview');
}

function callGeminiAPI(categoryName, categoryUrl, subcategories) {
  let subcategoriesText = 'Subcategories should not be included in the text.';
  if (subcategories && subcategories.length > 0) {
    subcategoriesText = 'You MUST use the following list of subcategories. When you mention one of them in the text, turn it into a hyperlink with the provided URL, using the tag <a href="...">. Example: <a href="https://example.com">Subcategory Name</a>\n' +
      subcategories.map(sub => `- ${sub.name} (URL: ${sub.url})`).join('\n');
  }

  const prompt = `
Create a professional HTML description for the category "${categoryName}" in English. Minimum 400 words. The style should be clear, informative, with good visual HTML structure. Include proper SEO-friendly structure with h2, h3, p, ul, li, etc. Do not use h1, images, or CSS.
Context: This content is for a category on an online store called Mashini.bg. Use the information from the category URL (${categoryUrl}) to understand what products are included in it.

---
**INSTRUCTIONS FOR SUBCATEGORIES AND LINKS:**
${subcategoriesText}
---

Include these sections:
- Introduction (clear explanation)
- Main types and subcategories (insert the links to subcategories here if they exist)
- Advantages and applications
- Why choose Mashini.bg
- Frequently Asked Questions (FAQ)

Also output:
- H1 Title (up to 8 words)
- Meta Keywords (5 keywords and 4 phrases)

Output the result in the following strict format, without any extra text before or after it:
###DESCRIPTION_START###
... (place the HTML description with embedded links here) ...
###DESCRIPTION_END###
###H1_START###
... (place the H1 Title here) ...
###H1_END###
###KEYWORDS_START###
... (place the Meta Keywords here) ...
###KEYWORDS_END###
`;

  const payload = {
    "contents": [{"parts": [{"text": prompt}]}],
     "generationConfig": {
        "temperature": 0.7, "topK": 1, "topP": 1, "maxOutputTokens": 8192,
     }
  };
  const options = {
    'method': 'post', 'contentType': 'application/json', 'payload': JSON.stringify(payload), 'muteHttpExceptions': true
  };
  const response = UrlFetchApp.fetch(API_ENDPOINT, options);
  const responseText = response.getContentText();
  const responseJson = JSON.parse(responseText);
  if (responseJson.error) { throw new Error(responseJson.error.message); }
  if (responseJson.candidates && responseJson.candidates[0] && responseJson.candidates[0].content && responseJson.candidates[0].content.parts && responseJson.candidates[0].content.parts[0]) {
      return responseJson.candidates[0].content.parts[0].text;
  } else { throw new Error("Invalid API response format."); }
}

function parseContent(content) {
    try {
        const description = content.split('###DESCRIPTION_START###')[1].split('###DESCRIPTION_END###')[0].trim();
        const h1 = content.split('###H1_START###')[1].split('###H1_END###')[0].trim();
        const keywords = content.split('###KEYWORDS_START###')[1].split('###KEYWORDS_END###')[0].trim();
        return { description, h1, keywords };
    } catch(e) {
        return {};
    }
}

/**
 * @description Retrieves the URLs of MULTIPLE images from the category page.
 * @param {string} categoryUrl - The URL of the category.
 * @param {number} limit - The maximum number of images to return.
 * @returns {string[]} - An array of image URLs.
 */
function getMultipleImagesForCategory(categoryUrl, limit = 3) {
  try {
    const response = UrlFetchApp.fetch(categoryUrl, { muteHttpExceptions: true });
    const htmlContent = response.getContentText();
    
    const imageRegex = /<img[^>]+src="(https:\/\/static\.mashini\.bg\/images\/products\/[^"]+\.(?:webp|jpg|jpeg|png))"/g;
    
    const matches = htmlContent.match(imageRegex);
    
    if (!matches) {
      return [];
    }

    const urls = matches.map(tag => tag.match(/src="([^"]+)"/)[1]);

    const uniqueUrls = [...new Set(urls)];

    return uniqueUrls.slice(0, limit);
  } catch (e) {
    Logger.log(`Error retrieving multiple images from ${categoryUrl}: ${e.message}`);
    return [];
  }
}

/**
 * @description Iterates through all rows with existing content and a PROGRESS BAR and STOP button.
 * 1. Adds a main image to the Image column (if missing).
 * 2. Inserts additional images into the description text (if missing).
 */
function addImagesToExistingContent() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const ui = SpreadsheetApp.getUi();
  
  const userProperties = PropertiesService.getUserProperties();
  userProperties.deleteProperty(STOP_PROPERTY);
  userProperties.deleteProperty(STATUS_PROPERTY);

  const htmlOutput = HtmlService.createHtmlOutputFromFile('ProgressDialog').setWidth(400).setHeight(150);
  ui.showModelessDialog(htmlOutput, "Processing Status");
  
  const range = sheet.getDataRange();
  const values = range.getValues();
  const totalRows = values.length - 1; 
  let rowsProcessed = 0;

  for (let i = 1; i < values.length; i++) {
    if (userProperties.getProperty(STOP_PROPERTY) === 'true') {
      ui.alert('Operation stopped by user. Processed ' + rowsProcessed + ' rows.');
      break;
    }

    const statusMessage = `Processing row ${i} of ${totalRows}...`;
    userProperties.setProperty(STATUS_PROPERTY, statusMessage);
    
    const row = values[i];
    const categoryName = row[COL_NAME - 1];
    let description = row[COL_DESCRIPTION - 1];
    const categoryUrl = row[COL_URL - 1];
    const mainImageCell = row[COL_IMAGE - 1];
    
    if (categoryUrl && description && (!mainImageCell || !description.includes('img src="https://static.mashini.bg'))) {
        let hasBeenModified = false;

        if (!mainImageCell) {
            const mainImageUrl = getImageForCategory(categoryUrl);
            if (mainImageUrl) {
                sheet.getRange(i + 1, COL_IMAGE).setFormula(`=IMAGE("${mainImageUrl}")`);
                hasBeenModified = true;
            }
        }

        const shouldInjectImages = !description.includes('img src="https://static.mashini.bg');
        if (shouldInjectImages) {
            const imagesToInject = getMultipleImagesForCategory(categoryUrl, 3);
            if (imagesToInject.length > 0) {
                const newDescription = injectImagesIntoHtml(description, imagesToInject, categoryName);
                values[i][COL_DESCRIPTION - 1] = newDescription;
                hasBeenModified = true;
            }
        }
        
        if (hasBeenModified) {
            rowsProcessed++;
        }
    }
  }

  range.setValues(values);

  userProperties.deleteProperty(STOP_PROPERTY);
  userProperties.setProperty(STATUS_PROPERTY, "COMPLETE");

  if (userProperties.getProperty(STOP_PROPERTY) !== 'true') {
     ui.alert('Done!', `Operation finished. Processed ${rowsProcessed} rows.`, ui.ButtonSet.OK);
  }
}

/**
 * @description Inserts images into HTML content, typically after h2 tags.
 * @param {string} html - The original HTML text.
 * @param {string[]} imageUrls - An array of image URLs.
 * @param {string} altText - Alt text for the images (important for SEO).
 * @returns {string} - The new HTML with images inserted.
 */
function injectImagesIntoHtml(html, imageUrls, altText) {
  if (!imageUrls || imageUrls.length === 0) {
    return html;
  }

  const parts = html.split('</h2>');
  let newHtml = parts[0];

  for (let i = 1; i < parts.length; i++) {
    newHtml += '</h2>';

    if (i - 1 < imageUrls.length) {
      const imageUrl = imageUrls[i - 1];
      newHtml += `\n<p style="text-align: center;"><img src="${imageUrl}" alt="${altText} - image ${i}" style="max-width: 80%; height: auto; display: inline-block; margin: 20px 0;" /></p>\n`;
    }
    
    newHtml += parts[i];
  }

  return newHtml;
}

function cleanExistingHtmlContent() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const ui = SpreadsheetApp.getUi();

  const response = ui.alert(
    'Confirmation',
    'This operation will permanently remove the H1 tag and core HTML code from all cells in the "Description" column. Do you want to continue?',
    ui.ButtonSet.YES_NO
  );

  if (response !== ui.Button.YES) {
    ui.alert('Operation canceled by user.');
    return;
  }

  const range = sheet.getRange(2, COL_DESCRIPTION, sheet.getLastRow() - 1, 1);
  const values = range.getValues();
  let cellsCleaned = 0;

  for (let i = 0; i < values.length; i++) {
    let cellContent = values[i][0];

    if (cellContent && typeof cellContent === 'string') {
      let cleanedContent = cellContent.replace(/<h1[^>]*>[\s\S]*?<\/h1>/i, '');
      cleanedContent = cleanedContent.replace(/<!DOCTYPE html>[\s\S]*?<body>/i, '');
      cleanedContent = cleanedContent.replace(/<\/body>/i, '');
      cleanedContent = cleanedContent.replace(/<\/html>/i, '');
      cleanedContent = cleanedContent.trim();

      if (cleanedContent !== cellContent) {
        values[i][0] = cleanedContent;
        cellsCleaned++;
      }
    }
  }

  range.setValues(values);
  
  ui.alert('Cleaning complete!', `Successfully cleaned ${cellsCleaned} cells.`, ui.ButtonSet.OK);
}

/**
 * Retrieves the names and URLs of subcategories from the main category page.
 * @param {string} categoryUrl The URL of the main category.
 * @returns {Array<Object>} An array of objects, each with { name: string, url: string }.
 */
function getSubcategories(categoryUrl) {
  try {
    const response = UrlFetchApp.fetch(categoryUrl, { muteHttpExceptions: true });
    const htmlContent = response.getContentText();
    const subcategories = [];

    const listRegex = /<ul class="items-subcategories">([\s\S]+?)<\/ul>/;
    const listMatch = htmlContent.match(listRegex);

    if (listMatch && listMatch[1]) {
      const listHtml = listMatch[1];
      const linkRegex = /<a class="subcat-box-item"[^>]*href="([^"]+)"[^>]*>[\s\S]*?<span class="title">([\s\S]+?)<\/span>/g;
      let linkMatch;
      
      while ((linkMatch = linkRegex.exec(listHtml)) !== null) {
        subcategories.push({
          url: linkMatch[1],
          name: linkMatch[2].trim()
        });
      }
    }
    
    Logger.log(`Found ${subcategories.length} subcategories for ${categoryUrl}`);
    return subcategories;

  } catch (e) {
    Logger.log(`Error retrieving subcategories from ${categoryUrl}: ${e.message}`);
    return [];
  }
}

/**
 * @description Iterates through existing content and turns subcategory names
 * from plain text into hyperlinks.
 */
function addLinksToExistingContent() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const ui = SpreadsheetApp.getUi();
  const userProperties = PropertiesService.getUserProperties();

  userProperties.deleteProperty(STOP_PROPERTY);
  userProperties.deleteProperty(STATUS_PROPERTY);

  const htmlOutput = HtmlService.createHtmlOutputFromFile('ProgressDialog').setWidth(400).setHeight(150);
  ui.showModelessDialog(htmlOutput, "Link Update Status");
  
  const range = sheet.getDataRange();
  const values = range.getValues();
  const totalRows = values.length - 1; 
  let rowsUpdated = 0;

  for (let i = 1; i < values.length; i++) {
    if (userProperties.getProperty(STOP_PROPERTY) === 'true') {
      ui.alert('Operation stopped by user. ' + rowsUpdated + ' rows updated.');
      break; 
    }

    const statusMessage = `Updating row ${i} of ${totalRows}...`;
    userProperties.setProperty(STATUS_PROPERTY, statusMessage);
    
    let description = values[i][COL_DESCRIPTION - 1];
    const categoryUrl = values[i][COL_URL - 1];
    let hasBeenModified = false;

    if (description && categoryUrl) {
      const subcategories = getSubcategories(categoryUrl);

      if (subcategories.length > 0) {
        subcategories.sort((a, b) => b.name.length - a.name.length);
        
        let modifiedHtml = description;

        for (const sub of subcategories) {
          const searchString = sub.name;
          const replacementLink = `<a href="${sub.url}">${sub.name}</a>`;
          
          const regex = new RegExp(`\\b${searchString}\\b(?!([^<]*?<\/a>))`, 'gi');

          if (regex.test(modifiedHtml) && !modifiedHtml.includes(replacementLink)) {
             modifiedHtml = modifiedHtml.replace(regex, replacementLink);
             hasBeenModified = true;
          }
        }
        
        if (hasBeenModified) {
          values[i][COL_DESCRIPTION - 1] = modifiedHtml;
          rowsUpdated++;
        }
      }
    }
  }

  range.setValues(values);

  userProperties.deleteProperty(STOP_PROPERTY);
  userProperties.setProperty(STATUS_PROPERTY, "COMPLETE");

  if (userProperties.getProperty(STOP_PROPERTY) !== 'true') {
    ui.alert('Done!', `Successfully updated ${rowsUpdated} rows with new links.`, ui.ButtonSet.OK);
  }
}

StatusDialog.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <style>
      body { font-family: Arial, sans-serif; text-align: center; padding: 20px; }
      button {
        background-color: #d9534f;
        color: white;
        border: none;
        padding: 10px 20px;
        font-size: 16px;
        cursor: pointer;
        border-radius: 5px;
      }
      button:hover {
        background-color: #c9302c;
      }
      #status {
        margin-bottom: 20px;
      }
    </style>
  </head>
  <body>
    <div id="status">
      <p><strong>Processing in progress...</strong></p>
      <p>The script will stop after the current row is processed.</p>
    </div>
    <button onclick="requestStopScript()">STOP</button>

    <script>
      function requestStopScript() {
        google.script.run
          .withSuccessHandler(function() {
            document.getElementById('status').innerHTML = '<p><strong>Stop request sent!</strong></p>';
            document.querySelector('button').disabled = true;
          })
          .requestStop();
      }
    </script>
  </body>
</html>

ProgressDialog.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <style>
      body { font-family: Arial, sans-serif; text-align: center; padding: 20px; }
      #status-message { font-size: 16px; margin-bottom: 20px; }
      button { padding: 10px 20px; font-size: 14px; cursor: pointer; }
    </style>
  </head>
  <body>
    <div id="status-message">Initializing...</div>
    <button onclick="requestStopAndClose()">STOP</button>

    <script>
      function requestStopAndClose() {
        google.script.run.requestStop();
        document.getElementById('status-message').innerText = 'Stop requested...';
        document.querySelector('button').disabled = true;
      }

      function updateStatus() {
        google.script.run
          .withSuccessHandler(function(status) {
            document.getElementById('status-message').innerText = status;
            if (status === "COMPLETE") {
              setTimeout(function() { google.script.host.close(); }, 2000);
            }
          })
          .getExecutionStatus();
      }

      setInterval(updateStatus, 1500);
    </script>
  </body>
</html>

This powerful tool not only saves you time but also helps you scale your SEO efforts in a way that would be impossible to do manually. Happy automating!

Supercharge Your SEO: How to Build a Google Sheets & Gemini AI Content Generator