Thinkclicks is an advanced digital platform that blending data from diverse advertising channels into customized, real-time dashboards—enabling data-driven decision-making and optimized marketing performance.
Choose your subscription plan
Easy connect your data sources using our integrations
Choose a template from our library with the metrics you want to track
Unlocking Success with your Custom KPI Dashboard
With extensive experience in marketing services, ThinkClicks has developed an automated platform designed to monitor and enhance analytics across advertising channels, helping businesses optimize their budgets and achieve greater performance outcomes.
We unify all data through the data blending process, which connects marketing platforms and extracts data from them into a single view.
In Thinkclicks we create customized real-time reports for your business needs. This step will provide transparency into your income and expenses.
Maximize your revenue by making every advertising dollar count — let ThinkClicks analyze your budget and boost your Return on Ad Spend (ROAS) with data-driven precision.
Don’t settle for generic insights from standard marketing platforms. Gain the freedom to segment and analyze each campaign individually, ensuring maximum efficiency and precision in your advertising strategy.
Easy access and real-time view of all data from the marketing channels you use.
ThinkClicks offers industry-leading Looker Studio connectors that seamlessly integrate data from over 325 sources into Google’s visualization platform—no coding required. Enable real-time reporting and unlock powerful analytics in just minutes.
The release of GPT-5 marks a historic leap in artificial intelligence. Built by OpenAI, this new generation model is not just an incremental update over GPT-4 — it’s a significant overhaul in reasoning ability, multimodal interaction, and integration flexibility.
GPT 5 can process massive contexts (up to ~400,000 tokens), handle text + image inputs, adapt its style to match the user, and interact with tools like calendars, email, and code interpreters. Businesses now have a near-human AI collaborator capable of understanding, reasoning, and producing content at a level previously thought impossible.
Unlike earlier versions that often focused on speed or accuracy alone, GPT-5’s design philosophy centers around adaptive intelligence — it adjusts the depth of reasoning, verbosity, and even personality based on the complexity of the task.
reasoning_effort and verbosity.GPT-5’s architecture is a layered system that routes requests to the most suitable internal model based on complexity. This allows it to remain fast for simple prompts while being deeply analytical for complex tasks.
OpenAI tested GPT 5 across reasoning, coding, factual accuracy, and long-context handling benchmarks.
| Benchmark | GPT-5 Score | GPT-4o Score | Improvement |
|---|---|---|---|
| SWE-bench Verified (Coding) | 74.9% | 25% | +200% |
| Aider Polyglot (Multi-Language Coding) | 88% | 79% | +11% |
| LongFact (Fact Accuracy) | 89% | 78% | +14% |
| Long Context Retention | 400K tokens | 128K tokens | 3x |
Infographic: AI Model Performance
Example:
An e-commerce store uses GPT 5 to create localized product descriptions in 15 languages with consistent brand tone.
Example:
A medical school uses GPT 5 to simulate patient scenarios with text and diagnostic images.
GPT-5 introduces two critical API parameters:
reasoning_effort:
minimal – fastest, minimal reasoning.low, medium, high – progressively deeper reasoning.verbosity:
low, medium, high).This enables developers to fine-tune responses for speed vs depth.
OpenAI has dedicated 5,000+ hours of safety testing to GPT-5, focusing on:
While GPT-5 is edging closer to Artificial General Intelligence (AGI), it does not yet learn continuously in real-time. OpenAI plans to:
GPT 5 is not just a model — it’s a platform for innovation. Its unprecedented reasoning depth, context handling, and multimodal features make it a transformative tool for industries from marketing to medicine. For businesses, adopting GPT-5 early means gaining a competitive edge in automation, personalization, and data-driven decision-making.
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.
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 | Name | Description | H1 title | Meta keywords | URLS | SEO URL | Image |
Export to Sheets
To use the script, you need to connect to the Gemini API.
Important: This API key is a secret. Do not share it publicly.
Now for the core of the project.
Code.gs file.Code.gs section below and paste it into the editor.StatusDialog.html and ProgressDialog.html.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
The first time you run the script, Google will ask for permission to access your spreadsheet and connect to external services.
Once authorized, a new menu item called "🤖 SEO Generator" will appear in your Google Sheet.
The new menu offers several powerful options:
<h2> tags. It also adds a main image to the Image column if it's missing.<a>tags). This is great for improving internal linking after the initial content generation.Description column and use this to see how the generated HTML content will look in a browser.<html>, <body>, etc.) from the Description column, which can sometimes be generated by the AI.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.
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!
