Build a Scalable Title Tag & Meta Description Generator in Google Sheets

If you worked through Article 1, you’ve got the foundations in place — an API connection, a basic loop, and an understanding of how system prompts and user messages work as separate layers. This article is where we build something you can actually use on a real site.

We’re going to build a title tag and meta description generator. Not a single-cell formula. A proper, scalable script that reads a list of URLs with their existing metadata, understands what kind of page each one is, constructs a smart prompt on the fly, calls the AI, and writes the results back into your sheet — all while tracking its own progress and staying inside Apps Script’s execution limits.

This is, specifically, the script that Chris Ridley and I fangirled over in those Instagram DMs that started this whole series. Hi again, Chris.

By the end of this article you’ll have a script you can hand to anyone on your team. They open the spreadsheet, click a menu item, and it runs. No code editor required.

We’re building this around an imaginary client called TradeLink — a UK platform connecting homeowners with vetted local tradespeople. It gives us a realistic context with real constraints: location-based service pages, a specific tone of voice, character limits that actually matter, and a business that can’t afford to sound generic.

What We’re Building

1. Reads a sheet where each row is a page — URL, old title, old meta description
2. Works out what kind of page each URL is by parsing the URL structure
3. Builds a tailored prompt for each row using that context
4. Calls the Gemini API with a persistent system prompt holding the TradeLink brand voice
5. Writes the new title and meta description back to the sheet
6. Logs a status for every row so you can see what’s processed, pending, and errored
7. Handles execution time limits gracefully — stops itself before Apps Script force-kills it so you can pick up where you left off

Setting Up Your Google Sheet

Your existing title and meta data comes from your crawl tool. If you’re using Sitebulb, export your page metadata report and paste columns D and E directly. If you’re using Screaming Frog, the Title 1 and Meta Description 1 columns map straight to D and E.

Column F is optional but worth populating where you can. Pasting in the page’s H1, a couple of body paragraphs, or a list of H2s gives the AI meaningful signal about what’s actually on the page — especially useful when the existing title and meta are thin. A plain text paste is fine; it doesn’t need to be formatted.

The Config Block: Stop Hardcoding Things

Open your spreadsheet, go to Extensions > Apps Script, clear the default code, and start with this:

/**
 * TradeLink SEO Automation — Title Tag & Meta Description Generator
 * @OnlyCurrentDoc limits permissions to the current spreadsheet only.
 */

const CONFIG = {
  sheetName:  'Sheet1',
  apiKey:     PropertiesService.getScriptProperties().getProperty('GEMINI_API_KEY'),
  modelName:  'gemini-2.0-flash',
  cols: {
    url:               1,  // A — page URL (input)
    newTitle:          2,  // B — AI-generated title (output)
    newMeta:           3,  // C — AI-generated meta (output)
    oldTitle:          4,  // D — existing title from crawl (input)
    oldMeta:           5,  // E — existing meta from crawl (input)
    additionalContext: 6,  // F — H1, body copy, H2s etc. (optional)
    status:            7   // G — processing status (output)
  },
  // Apps Script kills at 6 minutes — we stop at 4.5 to exit cleanly
  timeLimitMs: (4.5 * 60) * 1000
};

The @OnlyCurrentDoc annotation limits OAuth permissions to just this spreadsheet. Without it, the permissions prompt asks for access to all your Google Sheets. Always include it.

The Custom Menu: Making It Usable for Everyone

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('TradeLink SEO')
    .addItem('▶ Process new pages (skip completed)', 'processSheet')
    .addItem('↺ Re-run everything (overwrite all)', 'reprocessSheet')
    .addSeparator()
    .addItem('✔ Check progress', 'checkProgress')
    .addToUi();
}

onOpen() is a reserved function name — Apps Script runs it automatically every time the spreadsheet is opened. The menu appears to the right of the Help menu in the spreadsheet toolbar. Process new pages skips rows with a “Done” status in column G. Re-run everything clears all statuses first — use this when you’ve updated the prompt.

The System Prompt: Your Brand Voice in Code

This is where the TradeLink personality lives. Define it once, pass it as the system prompt to every API call. This is the [ROLE] and [CONTEXT] from the prompt framework in Article 1 — extracted into its own reusable function.

function getSystemPrompt() {
  return `You are a senior SEO copywriter for TradeLink, a premium UK platform that connects
homeowners with expert, vetted local tradespeople — plumbers, electricians, builders, and more.

AUDIENCE:
Homeowners in stressful situations: something is broken, leaking, or unsafe. Their biggest
fear is hiring an unreliable "cowboy" tradesperson. They want local, vetted, and guaranteed.

TONE:
- Reliable, expert, and reassuring. Not salesy or hype-driven.
- Never use words like "cheap," "bargain," or "affordable."
- British English throughout.

TITLE TAG RULES:
- Maximum 60 characters (Google measures pixel width not characters, but 60 chars at
  standard font sizes keeps you safe on both desktop and mobile).
- Structure: [Trade Type] in [Location]: [Benefit] | TradeLink
- Always end with "| TradeLink"
- Good: "Expert Plumbers in Manchester: Vetted & Local | TradeLink"
- Bad: "Cheap Plumbers Manchester | TradeLink"

META DESCRIPTION RULES:
- Keep between 120–155 characters (Google truncates at ~120 on mobile and ~158 on
  desktop — landing in the middle keeps you safe on both).
- Open with the homeowner's problem or the peace-of-mind benefit.
- Must include "vetted" professionals and reference the "Workmanship Guarantee."

OUTPUT FORMAT:
Respond with ONLY valid JSON. No preamble, no explanation, no markdown code fences.
Format: {"title": "your title here", "description": "your meta description here"}`;
}

The 60-character title limit is a reliable practical ceiling — technically Google measures pixel width rather than character count (~600px on desktop), but at standard font sizes 60 characters keeps you well within the display threshold on both desktop and mobile, making it a dependable proxy. The meta description range of 120–155 is a deliberate target rather than a single number, explained with the mobile/desktop truncation reasoning so the model understands why the range exists.

The good/bad examples are doing real work. Abstract instructions like “don’t be salesy” are harder to apply consistently than concrete examples of what you don’t want. The output format instruction — JSON only, no code fences — is the most operationally important line in the whole prompt. Without it, the model returns explanation around the JSON that breaks JSON.parse().

URL Context Extraction: Making Your Prompts Smarter

TradeLink’s URL structure tells us the trade type, the location, and the page type before we’ve made a single API call. We extract all of that and use it to write different, more targeted instructions per page type.

function extractPageContext(url) {
  const context = {
    isLocationPage: false, isServicePage: false,
    tradeType: null, location: null
  };

  if (url.includes('/services/')) context.isServicePage = true;

  // /services/plumbers-in-manchester → tradeType: "plumbers"
  const serviceMatch = url.match(/\/services\/([^\/]+)/);
  if (serviceMatch) {
    context.tradeType = serviceMatch[1].split('-in-')[0].replace(/-/g, ' ');
  }

  // plumbers-in-manchester → location: "manchester"
  const locationMatch = url.match(/-in-([a-z0-9-]+)/i);
  if (locationMatch) {
    context.location       = locationMatch[1].replace(/-/g, ' ');
    context.isLocationPage = true;
  }

  return context;
}

const catMatch = url.match(/\/blog\/([^\/]+)\//);
if (catMatch) context.category = catMatch[1].replace(/-/g, ' ');
const slugMatch = url.match(/\/blog\/[^\/]+\/([^\/]+)/);
if (slugMatch) context.topic = slugMatch[1].replace(/-/g, ' ');

const prodMatch = url.match(/\/products\/([^\/]+)\/([^\/]+)/);
if (prodMatch) {
  context.category    = prodMatch[1].replace(/-/g, ' ');
  context.productName = prodMatch[2].replace(/-/g, ' ');
}

The Prompt Builder

function toTitleCase(str) {
  if (!str) return '';
  return str.split(' ').map(function(w) {
    return w.charAt(0).toUpperCase() + w.slice(1);
  }).join(' ');
}

function constructPrompt(pageData, pageContext) {
  let pageTypeInstructions = '';

  if (pageContext.isLocationPage && pageContext.tradeType && pageContext.location) {
    pageTypeInstructions = `PAGE TYPE: Location-Specific Service Page
Trade: ${toTitleCase(pageContext.tradeType)} | Location: ${toTitleCase(pageContext.location)}
Priority: Emphasise local availability. Title MUST include both trade type and city name.`;
  } else if (pageContext.isServicePage && pageContext.tradeType) {
    pageTypeInstructions = `PAGE TYPE: General Service Page
Trade: ${toTitleCase(pageContext.tradeType)}
Priority: Emphasise the vetting process, quality of work, and peace of mind.`;
  } else {
    pageTypeInstructions = `PAGE TYPE: General | Priority: Focus on TradeLink's core value proposition.`;
  }

  const contextBlock = pageData.additionalContext
    ? `\n--- PAGE CONTENT ---\n${pageData.additionalContext.substring(0, 1500)}\n---------------------`
    : '';

  return `Generate a title tag and meta description for the following TradeLink page.

${pageTypeInstructions}

URL: ${pageData.url}
Existing Title: ${pageData.oldTitle || 'Not set'}
Existing Meta: ${pageData.oldMeta || 'Not set'}
${contextBlock}

Use the existing copy as context — do not copy it.
Respond with ONLY valid JSON: {"title": "...", "description": "..."}`;
}

The API Caller: Updated for Production Use

function callGeminiApi(userPrompt, systemPrompt) {
  const url = `https://generativelanguage.googleapis.com/v1beta/models/${CONFIG.modelName}:generateContent?key=${CONFIG.apiKey}`;

  const payload = {
    system_instruction: systemPrompt ? { parts: [{ text: systemPrompt }] } : undefined,
    contents: [{ parts: [{ text: userPrompt }] }],
    generationConfig: { temperature: 0.4 }  // Lower = more consistent. Good for copy generation.
  };

  const response   = UrlFetchApp.fetch(url, {
    method: 'post', contentType: 'application/json',
    payload: JSON.stringify(payload), muteHttpExceptions: true
  });
  const statusCode = response.getResponseCode();
  if (statusCode !== 200) throw new Error(`API error ${statusCode}: ${response.getContentText().substring(0, 200)}`);

  const data = JSON.parse(response.getContentText());

  // Guard against empty candidates — happens when safety filters block a response
  if (!data.candidates || data.candidates.length === 0) {
    const blockReason = data.promptFeedback?.blockReason || 'unknown';
    throw new Error(`No candidates returned. Block reason: ${blockReason}`);
  }

  const rawText = data.candidates[0].content.parts[0].text.trim();
  // Strip markdown code fences if the model included them anyway
  const clean = rawText.replace(/^```json\s*/i, '').replace(/\s*```$/, '').trim();

  let parsed;
  try { parsed = JSON.parse(clean); }
  catch (e) { throw new Error(`JSON parse failed. Response: ${rawText.substring(0, 300)}`); }

  if (!parsed.title || !parsed.description) throw new Error(`Missing fields. Got: ${JSON.stringify(parsed)}`);
  return parsed;
}

Three things in this version that go beyond the Article 1 function:

temperature: 0.4 — controls how creative vs. predictable the output is. 0 is fully deterministic, 1 is maximum creativity. 0.4 is the right balance for on-brief copy generation: consistent enough to respect character limits reliably, varied enough not to sound templated.

The code fence strip — even with explicit instructions not to use them, models occasionally wrap JSON in triple backticks. The .replace() lines handle that before parsing.

The candidates[0] guard — before accessing the response content, we check whether candidates exists and has at least one entry. The Gemini API can return an empty candidates array when its safety filters block a response — this can happen on legitimate SEO content if certain phrases trigger filters (some legal trade types, competitor comparisons). Without the guard, you get a cryptic TypeError. With it, the block reason surfaces into the status column so you know exactly what happened.

The Row Processor and Main Loop

Two design decisions worth calling out in these functions. First, processSingleRow accepts systemPrompt as a parameter rather than calling getSystemPrompt() itself — because the system prompt is built once before the loop starts, not rebuilt 500 times. Second, the main loop reads all input columns — including the status column — in one bulk getValues() call (7 columns, A–G), so the loop never touches the sheet for reads. Writing still happens per-row, for live feedback.

function processSingleRow(sheet, rowNum, rowData, systemPrompt) {
  sheet.getRange(rowNum, CONFIG.cols.status).setValue('Processing...');
  SpreadsheetApp.flush(); // Force sheet to update now so you can watch progress live

  // Extract from pre-read array (1-indexed cols, 0-indexed array)
  const url               = rowData[CONFIG.cols.url - 1].toString().trim();
  const oldTitle          = rowData[CONFIG.cols.oldTitle - 1].toString().trim();
  const oldMeta           = rowData[CONFIG.cols.oldMeta - 1].toString().trim();
  const additionalContext = rowData[CONFIG.cols.additionalContext - 1].toString().trim();

  if (!url) { sheet.getRange(rowNum, CONFIG.cols.status).setValue('Skipped: no URL'); return false; }

  const result = callGeminiApi(
    constructPrompt({ url, oldTitle, oldMeta, additionalContext }, extractPageContext(url)),
    systemPrompt  // passed in from the loop — not rebuilt per row
  );

  sheet.getRange(rowNum, CONFIG.cols.newTitle).setValue(result.title);
  sheet.getRange(rowNum, CONFIG.cols.newMeta).setValue(result.description);
  sheet.getRange(rowNum, CONFIG.cols.status).setValue('Done');
  SpreadsheetApp.flush();
  return true;
}

function processSheet() {
  const ss    = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(CONFIG.sheetName);
  if (!sheet) { SpreadsheetApp.getUi().alert(`Sheet "${CONFIG.sheetName}" not found.`); return; }

  const startTime = Date.now(), lastRow = sheet.getLastRow();
  let processed = 0, skipped = 0, errors = 0;
  if (lastRow < 2) { SpreadsheetApp.getUi().alert('No data rows found.'); return; }

  // Read cols A–G (7 cols) in one call — includes status col, no sheet reads in the loop
  const numRows = lastRow - 1;
  const allData = sheet.getRange(2, 1, numRows, 7).getValues();

  // Build system prompt once — the same string for every row
  const systemPrompt = getSystemPrompt();

  for (let i = 0; i < numRows; i++) {
    const rowNum = i + 2, rowData = allData[i];

    if (Date.now() - startTime > CONFIG.timeLimitMs) {
      SpreadsheetApp.getUi().alert(`Time limit reached after ${processed} rows.\nRun again to continue.`);
      return;
    }

    // Read status from in-memory array — not from the sheet
    const status = (rowData[CONFIG.cols.status - 1] || '').toString();
    if (status.startsWith('Done')) { skipped++; continue; }
    if (!rowData[CONFIG.cols.url - 1].toString().trim()) { skipped++; continue; }

    try {
      processSingleRow(sheet, rowNum, rowData, systemPrompt);
      processed++; Utilities.sleep(1500);
    } catch (error) {
      sheet.getRange(rowNum, CONFIG.cols.status).setValue(`Error: ${error.message.substring(0, 100)}`);
      SpreadsheetApp.flush(); errors++;
      // Logger.log writes to the Apps Script execution log — view via Executions in the editor
      Logger.log(`Row ${rowNum}: ${error.message}`);
    }
  }

  SpreadsheetApp.getUi().alert(
    `Complete.\n\n✔ Processed: ${processed}\n↷ Skipped: ${skipped}\n✖ Errors: ${errors}` +
    (errors > 0 ? '\n\nCheck column G for error details.' : '')
  );
}

function reprocessSheet() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(CONFIG.sheetName);
  if (!sheet) { SpreadsheetApp.getUi().alert(`Sheet "${CONFIG.sheetName}" not found.`); return; }
  const lastRow = sheet.getLastRow();
  if (lastRow >= 2) sheet.getRange(2, CONFIG.cols.status, lastRow - 1, 1).clearContent();
  processSheet();
}

function checkProgress() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(CONFIG.sheetName);
  if (!sheet) { SpreadsheetApp.getUi().alert(`Sheet "${CONFIG.sheetName}" not found.`); return; }
  const lastRow = sheet.getLastRow();
  if (lastRow < 2) { SpreadsheetApp.getUi().alert('No data rows found.'); return; }
  const statuses = sheet.getRange(2, CONFIG.cols.status, lastRow - 1, 1).getValues();
  let done = 0, errors = 0, pending = 0;
  statuses.forEach(function(r) {
    const s = r[0].toString();
    if (s.startsWith('Done')) done++;
    else if (s.startsWith('Error')) errors++;
    else pending++;
  });
  SpreadsheetApp.getUi().alert(`Progress\n\n✔ Done: ${done}\n✖ Errors: ${errors}\n○ Pending: ${pending}\nTotal: ${lastRow - 1}`);
}

The Complete Script

The annotated walkthrough above explains what each section does. Here’s the full script in one block — paste this entire thing into Apps Script without any assembly required.

/**
 * TradeLink SEO Automation — Title Tag & Meta Description Generator
 * @OnlyCurrentDoc
 */

const CONFIG = {
  sheetName:  'Sheet1',
  apiKey:     PropertiesService.getScriptProperties().getProperty('GEMINI_API_KEY'),
  modelName:  'gemini-2.0-flash',
  cols: {
    url: 1, newTitle: 2, newMeta: 3, oldTitle: 4,
    oldMeta: 5, additionalContext: 6, status: 7
  },
  timeLimitMs: (4.5 * 60) * 1000
};

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('TradeLink SEO')
    .addItem('▶ Process new pages (skip completed)', 'processSheet')
    .addItem('↺ Re-run everything (overwrite all)', 'reprocessSheet')
    .addSeparator()
    .addItem('✔ Check progress', 'checkProgress')
    .addToUi();
}

function getSystemPrompt() {
  return `You are a senior SEO copywriter for TradeLink, a premium UK platform that connects
homeowners with expert, vetted local tradespeople — plumbers, electricians, builders, and more.

AUDIENCE:
Homeowners in stressful situations: something is broken, leaking, or unsafe. Their biggest
fear is hiring an unreliable "cowboy" tradesperson. They want local, vetted, and guaranteed.

TONE:
- Reliable, expert, and reassuring. Not salesy or hype-driven.
- Never use words like "cheap," "bargain," or "affordable."
- British English throughout.

TITLE TAG RULES:
- Maximum 60 characters (Google measures pixel width, but 60 chars at standard font sizes
  is safe on both desktop and mobile).
- Structure: [Trade Type] in [Location]: [Benefit] | TradeLink
- Always end with "| TradeLink"
- Good: "Expert Plumbers in Manchester: Vetted & Local | TradeLink"
- Bad: "Cheap Plumbers Manchester | TradeLink"

META DESCRIPTION RULES:
- Keep between 120–155 characters (truncates at ~120 on mobile, ~158 on desktop — middle is safest).
- Open with the homeowner's problem or peace-of-mind benefit.
- Must reference "vetted" professionals and the "Workmanship Guarantee."

OUTPUT FORMAT:
Respond with ONLY valid JSON. No preamble, no explanation, no markdown code fences.
Format: {"title": "your title here", "description": "your meta description here"}`;
}

function extractPageContext(url) {
  const context = { isLocationPage: false, isServicePage: false, tradeType: null, location: null };
  if (url.includes('/services/')) context.isServicePage = true;
  const serviceMatch = url.match(/\/services\/([^\/]+)/);
  if (serviceMatch) context.tradeType = serviceMatch[1].split('-in-')[0].replace(/-/g, ' ');
  const locationMatch = url.match(/-in-([a-z0-9-]+)/i);
  if (locationMatch) { context.location = locationMatch[1].replace(/-/g, ' '); context.isLocationPage = true; }
  return context;
}

function toTitleCase(str) {
  if (!str) return '';
  return str.split(' ').map(function(w) { return w.charAt(0).toUpperCase() + w.slice(1); }).join(' ');
}

function constructPrompt(pageData, pageContext) {
  let pageTypeInstructions = '';
  if (pageContext.isLocationPage && pageContext.tradeType && pageContext.location) {
    pageTypeInstructions = `PAGE TYPE: Location-Specific Service Page
Trade: ${toTitleCase(pageContext.tradeType)} | Location: ${toTitleCase(pageContext.location)}
Priority: Emphasise local availability. Title MUST include both trade type and city name.`;
  } else if (pageContext.isServicePage && pageContext.tradeType) {
    pageTypeInstructions = `PAGE TYPE: General Service Page
Trade: ${toTitleCase(pageContext.tradeType)}
Priority: Emphasise the vetting process, quality of work, and peace of mind.`;
  } else {
    pageTypeInstructions = `PAGE TYPE: General | Priority: Focus on TradeLink's core value proposition.`;
  }
  const contextBlock = pageData.additionalContext
    ? `\n--- PAGE CONTENT ---\n${pageData.additionalContext.substring(0, 1500)}\n---------------------` : '';
  return `Generate a title tag and meta description for the following TradeLink page.

${pageTypeInstructions}

URL: ${pageData.url}
Existing Title: ${pageData.oldTitle || 'Not set'}
Existing Meta: ${pageData.oldMeta || 'Not set'}
${contextBlock}

Use the existing copy as context — do not copy it.
Respond with ONLY valid JSON: {"title": "...", "description": "..."}`;
}

function callGeminiApi(userPrompt, systemPrompt) {
  const url = `https://generativelanguage.googleapis.com/v1beta/models/${CONFIG.modelName}:generateContent?key=${CONFIG.apiKey}`;
  const payload = {
    system_instruction: systemPrompt ? { parts: [{ text: systemPrompt }] } : undefined,
    contents: [{ parts: [{ text: userPrompt }] }],
    generationConfig: { temperature: 0.4 }
  };
  const response = UrlFetchApp.fetch(url, {
    method: 'post', contentType: 'application/json',
    payload: JSON.stringify(payload), muteHttpExceptions: true
  });
  const statusCode = response.getResponseCode();
  if (statusCode !== 200) throw new Error(`API error ${statusCode}: ${response.getContentText().substring(0, 200)}`);

  const data = JSON.parse(response.getContentText());
  if (!data.candidates || data.candidates.length === 0) {
    throw new Error(`No candidates returned. Block reason: ${data.promptFeedback?.blockReason || 'unknown'}`);
  }
  const rawText = data.candidates[0].content.parts[0].text.trim();
  const clean = rawText.replace(/^```json\s*/i, '').replace(/\s*```$/, '').trim();
  let parsed;
  try { parsed = JSON.parse(clean); }
  catch (e) { throw new Error(`JSON parse failed. Response: ${rawText.substring(0, 300)}`); }
  if (!parsed.title || !parsed.description) throw new Error(`Missing fields. Got: ${JSON.stringify(parsed)}`);
  return parsed;
}

function processSingleRow(sheet, rowNum, rowData, systemPrompt) {
  sheet.getRange(rowNum, CONFIG.cols.status).setValue('Processing...');
  SpreadsheetApp.flush();
  const url               = rowData[CONFIG.cols.url - 1].toString().trim();
  const oldTitle          = rowData[CONFIG.cols.oldTitle - 1].toString().trim();
  const oldMeta           = rowData[CONFIG.cols.oldMeta - 1].toString().trim();
  const additionalContext = rowData[CONFIG.cols.additionalContext - 1].toString().trim();
  if (!url) { sheet.getRange(rowNum, CONFIG.cols.status).setValue('Skipped: no URL'); return false; }
  const result = callGeminiApi(
    constructPrompt({ url, oldTitle, oldMeta, additionalContext }, extractPageContext(url)),
    systemPrompt
  );
  sheet.getRange(rowNum, CONFIG.cols.newTitle).setValue(result.title);
  sheet.getRange(rowNum, CONFIG.cols.newMeta).setValue(result.description);
  sheet.getRange(rowNum, CONFIG.cols.status).setValue('Done');
  SpreadsheetApp.flush();
  return true;
}

function processSheet() {
  const ss    = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(CONFIG.sheetName);
  if (!sheet) { SpreadsheetApp.getUi().alert(`Sheet "${CONFIG.sheetName}" not found.`); return; }
  const startTime = Date.now(), lastRow = sheet.getLastRow();
  let processed = 0, skipped = 0, errors = 0;
  if (lastRow < 2) { SpreadsheetApp.getUi().alert('No data rows found.'); return; }
  const numRows = lastRow - 1;
  const allData = sheet.getRange(2, 1, numRows, 7).getValues(); // A–G in one call
  const systemPrompt = getSystemPrompt(); // built once, not per row
  for (let i = 0; i < numRows; i++) {
    const rowNum = i + 2, rowData = allData[i];
    if (Date.now() - startTime > CONFIG.timeLimitMs) {
      SpreadsheetApp.getUi().alert(`Time limit reached after ${processed} rows.\nRun again to continue.`); return;
    }
    const status = (rowData[CONFIG.cols.status - 1] || '').toString();
    if (status.startsWith('Done')) { skipped++; continue; }
    if (!rowData[CONFIG.cols.url - 1].toString().trim()) { skipped++; continue; }
    try {
      processSingleRow(sheet, rowNum, rowData, systemPrompt);
      processed++; Utilities.sleep(1500);
    } catch (error) {
      sheet.getRange(rowNum, CONFIG.cols.status).setValue(`Error: ${error.message.substring(0, 100)}`);
      SpreadsheetApp.flush(); errors++;
      Logger.log(`Row ${rowNum}: ${error.message}`);
    }
  }
  SpreadsheetApp.getUi().alert(
    `Complete.\n\n✔ Processed: ${processed}\n↷ Skipped: ${skipped}\n✖ Errors: ${errors}` +
    (errors > 0 ? '\n\nCheck column G for error details.' : '')
  );
}

function reprocessSheet() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(CONFIG.sheetName);
  if (!sheet) { SpreadsheetApp.getUi().alert(`Sheet "${CONFIG.sheetName}" not found.`); return; }
  const lastRow = sheet.getLastRow();
  if (lastRow >= 2) sheet.getRange(2, CONFIG.cols.status, lastRow - 1, 1).clearContent();
  processSheet();
}

function checkProgress() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(CONFIG.sheetName);
  if (!sheet) { SpreadsheetApp.getUi().alert(`Sheet "${CONFIG.sheetName}" not found.`); return; }
  const lastRow = sheet.getLastRow();
  if (lastRow < 2) { SpreadsheetApp.getUi().alert('No data rows found.'); return; }
  const statuses = sheet.getRange(2, CONFIG.cols.status, lastRow - 1, 1).getValues();
  let done = 0, errors = 0, pending = 0;
  statuses.forEach(function(r) {
    const s = r[0].toString();
    if (s.startsWith('Done')) done++;
    else if (s.startsWith('Error')) errors++;
    else pending++;
  });
  SpreadsheetApp.getUi().alert(`Progress\n\n✔ Done: ${done}\n✖ Errors: ${errors}\n○ Pending: ${pending}\nTotal: ${lastRow - 1}`);
}

Running It: What to Expect

1. Paste the complete script into Extensions > Apps Script
2. Save (Ctrl+S / Cmd+S)
3. Make sure your GEMINI_API_KEY is in Script Properties (covered in Article 1)
4. Close the script editor and refresh your spreadsheet
5. You’ll see a new TradeLink SEO menu appear to the right of the Help menu
6. Click ▶ Process new pages
7. Accept the permissions prompt on the first run
8. Watch column G populate as rows are processed

If you hit the time limit, the script will alert you and stop cleanly. Click the same menu item again — it picks up from the first unprocessed row.

Once complete, your outputs should look something like this:

Add a character count formula in a spare column to validate outputs:

=LEN(B2)   ← should be ≤ 60
=LEN(C2)   ← should be between 120 and 155

If anything is outside those ranges, re-run those rows after tweaking the prompt constraints. We’re keeping validation manual for now because adding automated retry logic — where the script detects a title over 60 characters and re-prompts automatically — adds meaningful complexity. It needs its own section to be done properly, not bolted on here. The LEN formula gives you solid coverage in the meantime, and for most runs you’ll find the vast majority of outputs land within range first time.

A Note on Screaming Frog

If you’re using Screaming Frog, the AI Content Generation feature gets you part of the way there. Connect it to the OpenAI API via Configuration > API Access > AI Content Generation, set up a prompt template in the Prompt Configuration tab, and the generated output comes through in your crawl export.

The limitation compared to what we’ve built here is flexibility. Screaming Frog’s prompt templates are static — you can’t dynamically vary instructions by page type the way constructPrompt() does. For a straightforward batch job on a consistent page type it works well. For sites with multiple templates needing different treatment, Apps Script gives you the control.

What’s Coming in Article 3

Now that we’ve got a solid generator pattern established, the next article applies the same architecture to product page content generation — body copy, not just metadata. That brings longer outputs, more complex prompts, and the question of how much you let the AI write versus how much it’s working from structured data you provide. We’ll be building that out for TradeLink’s service detail pages, and introducing the Prompt Config Sheet pattern so prompts can be updated without touching the script.

This series is part of an ongoing set of practical AI workflow guides for working SEOs. Tools referenced: Sitebulb, Google Sheets, Google AI Studio, SE Ranking, Google Search Console, Google Analytics, SEO Testing.