SEO Workflows Using AppScripts

Before We Get Into It: Why This Series Exists

Something comes up a lot when working alongside other SEOs. Someone will look over at a spreadsheet mid-workflow and say some version of the same thing: “I’ve never seen SEO done like this before.”

Not because it’s complicated. Not because it requires a computer science degree. But because somewhere along the way, a gap opened up between what’s technically possible inside the tools we already use every day — and what most SEOs are actually doing with them.

This series is about closing that gap.

I should say — this series properly exists because of Chris Ridley. If you don’t know Chris, he’s a PPC legend, and a while back we got into a proper back-and-forth over Instagram DMs about something that frustrates both of us: the way nobody really appreciates when you show them a beautiful spreadsheet. You know the feeling. You’ve built something genuinely clever — a workflow you’re proud of, a sheet that does something elegant — and the response is a polite nod, at best. Chris gets it. He put into words something I’d been feeling for a while, and honestly that conversation was the nudge I needed to actually write this stuff down rather than just use it.

So if you get something out of this series — hi Chris, this one’s for you.

(Also, for what it’s worth: spreadsheets are beautiful. The most beautiful thing, actually. Anyone who disagrees can leave.)

Over the coming articles, we’re going to walk through how to use Google Sheets, Google Apps Script, and AI to build real, working SEO automation — the kind that handles the repetitive, time-consuming parts of your workflow so you can focus on the strategic stuff that actually needs a human brain.

Here’s a flavour of what we’ll be covering across the series:

• This article: What Apps Script is, why it matters for SEOs, connecting it to AI models, prompt management basics, and a no-nonsense JavaScript primer
• Coming up: Title tag and meta description generators, product page content generators, heading generators, content judges, scripts to push content directly into your CMS, content outline builders, and more
• Later in the series: We’ll also be moving into VS Code for more complex, large-scale content generation workflows where Google Sheets alone isn’t enough

Everything we build will be practical, reusable, and based on workflows that are genuinely in use — not theoretical examples cooked up to fill a tutorial.

Let’s get started.

---

What Even Is Google Apps Script?

For the uninitiated: Google Apps Script (GAS) is a cloud-based scripting platform built into Google Workspace. Think of it as a way to write code that lives inside Google Sheets (or Docs, or Drive) and can automate almost anything you’d otherwise do manually.

It runs on JavaScript — specifically a flavour of modern JS that Google hosts and executes on their servers. You don’t need to install anything. You don’t need to run a local server. You open your spreadsheet, go to Extensions > Apps Script, and you’re in.

For SEOs, this is a big deal. Here’s why.

Your Google Sheets data is already your SEO data — keyword lists, crawl exports, content audits, rank tracking, URL inventories. Apps Script lets you write logic that sits on top of that data and does things with it: calling an AI API, cleaning up messy exports, applying logic across thousands of rows, pushing output to another sheet, or even making requests to external tools.

And unlike building a Python script locally or spinning up a custom tool, Apps Script runs in the cloud. It can be scheduled. It can be triggered by a form submission, a spreadsheet edit, or run on a timer. Your team can use it without needing to touch the code. It’s genuinely accessible in a way that other automation paths often aren’t.

---

Connecting Apps Script to AI

This is where things get interesting.

Apps Script can make HTTP requests to external APIs. That means you can call AI models directly from inside your spreadsheet — pass in a row of data, get a generated output back, and write it into another cell. All without leaving Google Sheets.

For this series, we’ll primarily be working with Google AI Studio (Gemini API), as it’s the most natural pairing with Google Workspace. But the same approach works with other models, and we’ll show you how.

Getting Your API Key

Google AI Studio (Gemini)

1. Go to aistudio.google.com
2. Sign in with your Google account
3. Click Get API key in the left navigation
4. Click Create API key — select an existing Google Cloud project or create a new one
5. Copy your key and store it somewhere safe

Model to use: gemini-2.0-flash is the current recommended default — fast, cost-effective, and well-suited to repetitive SEO generation tasks at scale. For complex reasoning tasks where output quality is the priority, gemini-2.5-pro is Google’s current flagship model.

OpenAI (GPT-4o / GPT-4o mini)

1. Go to platform.openai.com
2. Navigate to API Keys in the left menu
3. Click Create new secret key
4. Copy your key immediately — you won’t be able to see it again

Model to use: gpt-4o-mini for cost-efficiency at scale, gpt-4o for quality-critical tasks.

Anthropic (Claude)

1. Go to console.anthropic.com
2. Navigate to API Keys
3. Click Create Key

Model to use: claude-haiku-4-5-20251001 for speed and cost efficiency at scale, claude-sonnet-4-6 for tasks where output quality is the priority.

Storing Your API Key Safely in Apps Script

Never hardcode your API key directly in a script. Use Apps Script’s built-in Script Properties to keep it out of the code itself:

1. In the Apps Script editor, click the ⚙️ Project Settings icon in the left sidebar
2. Select the Script properties tab
3. Click Add script property
4. Set the name as GEMINI_API_KEY (or whatever model you’re using) and paste your key as the value
5. Click Save script properties

Then in your code, retrieve it like this:

const apiKey = PropertiesService.getScriptProperties().getProperty('GEMINI_API_KEY');

This keeps your key out of the code itself, which matters when sharing scripts with colleagues or clients.

---

Your First API Call: The Basic Structure

Let’s build the simplest possible working example — a function that takes a piece of text from a cell, sends it to an AI model, and returns a response.

This is the foundation. Every more complex script in this series is just a variation on this pattern.

With Gemini (Google AI Studio)

function callGemini(prompt, systemPrompt = '') {
  // Retrieve your API key from Script Properties
  const apiKey = PropertiesService.getScriptProperties().getProperty('GEMINI_API_KEY');

  // The API endpoint — swap the model name here if needed
  const url = `https://generativelanguage.googleapis.com/v1beta/models/gemini-2.0-flash:generateContent?key=${apiKey}`;

  // Build the request payload
  // system_instruction handles the role/context part of your prompt separately from the user message
  const payload = {
    system_instruction: systemPrompt ? { parts: [{ text: systemPrompt }] } : undefined,
    contents: [{ parts: [{ text: prompt }] }]
  };

  // muteHttpExceptions: true means API errors return a response object instead of throwing,
  // so we can inspect the status code and handle errors properly
  const options = {
    method: 'post',
    contentType: 'application/json',
    payload: JSON.stringify(payload),
    muteHttpExceptions: true
  };

  const response = UrlFetchApp.fetch(url, options);

  // Check the HTTP status code before trying to parse
  const statusCode = response.getResponseCode();
  if (statusCode !== 200) {
    throw new Error(`Gemini API error: ${statusCode} — ${response.getContentText()}`);
  }

  const data = JSON.parse(response.getContentText());
  return data.candidates[0].content.parts[0].text;
}

With OpenAI

function callOpenAI(prompt, systemPrompt = '') {
  const apiKey = PropertiesService.getScriptProperties().getProperty('OPENAI_API_KEY');
  const url = 'https://api.openai.com/v1/chat/completions';

  // Build the messages array — system prompt goes first if provided
  const messages = [];
  if (systemPrompt) {
    messages.push({ role: 'system', content: systemPrompt });
  }
  messages.push({ role: 'user', content: prompt });

  const payload = { model: 'gpt-4o-mini', messages: messages };

  const options = {
    method: 'post',
    contentType: 'application/json',
    headers: { 'Authorization': `Bearer ${apiKey}` },
    payload: JSON.stringify(payload),
    muteHttpExceptions: true
  };

  const response = UrlFetchApp.fetch(url, options);
  const statusCode = response.getResponseCode();
  if (statusCode !== 200) {
    throw new Error(`OpenAI API error: ${statusCode} — ${response.getContentText()}`);
  }

  const data = JSON.parse(response.getContentText());
  return data.choices[0].message.content;
}

With Anthropic (Claude)

function callClaude(prompt, systemPrompt = '') {
  const apiKey = PropertiesService.getScriptProperties().getProperty('ANTHROPIC_API_KEY');
  const url = 'https://api.anthropic.com/v1/messages';

  // Anthropic uses a top-level 'system' field for the system prompt
  const payload = {
    model: 'claude-haiku-4-5-20251001',
    max_tokens: 1024,
    system: systemPrompt || undefined,
    messages: [{ role: 'user', content: prompt }]
  };

  const options = {
    method: 'post',
    contentType: 'application/json',
    headers: {
      'x-api-key': apiKey,
      'anthropic-version': '2023-06-01'
    },
    payload: JSON.stringify(payload),
    muteHttpExceptions: true
  };

  const response = UrlFetchApp.fetch(url, options);
  const statusCode = response.getResponseCode();
  if (statusCode !== 200) {
    throw new Error(`Anthropic API error: ${statusCode} — ${response.getContentText()}`);
  }

  const data = JSON.parse(response.getContentText());
  return data.content[0].text;
}

A Word on System Prompts — and Why They Matter

You’ll notice all three functions above now accept an optional second argument: systemPrompt. This isn’t just tidiness — it’s directly connected to the prompt framework we cover later in this article, and it’s worth explaining now so the pieces make sense together.

All three APIs handle prompts in two distinct layers:

The system prompt sets the persistent context — who the AI is, what it’s there to do, what rules it always follows. This is where your [ROLE] and [CONTEXT] sections live. It applies to the entire conversation and keeps the AI consistently in character across every request.

The user message is the specific task for this particular call — the [TASK], [INPUT], and [OUTPUT FORMAT] parts. This is what changes row by row as your script loops through your data.

Keeping these separate produces meaningfully more consistent output than dumping everything into a single message. Here’s what that looks like in practice:

// The system prompt: set once, applies to every call
const system = `You are an expert SEO copywriter specialising in e-commerce.
You write clear, benefit-led copy that is optimised for search without feeling robotic.
You always follow the output format exactly as specified.`;

// The user message: changes per row
const userMessage = `Write a meta description for the following product page.
Keep between 120–155 characters. Include the primary keyword naturally.
Return only the meta description text. No quotation marks. No explanation.

Product: ${productName}
Primary keyword: ${keyword}`;

// Pass both to the function
const result = callGemini(userMessage, system);

We’ll build this pattern out fully in Article 2. For now, just know the functions support it — and when you get to the prompt framework section below, you’ll see exactly which parts belong in the system prompt and which belong in the user message.

A Word on API Costs

None of these APIs are free at scale, but for most SEO automation tasks the costs are genuinely low. As a rough guide, processing thousands of short SEO tasks (meta descriptions, title tags, heading suggestions) typically costs pennies rather than pounds. For current pricing, check directly with each provider — these figures shift regularly:

• Gemini: ai.google.dev/pricing
• OpenAI: openai.com/api/pricing
• Anthropic: anthropic.com/pricing

Google AI Studio also has a free tier with usage limits, which is more than enough to get started and test your scripts before committing to paid usage.

---

Prompt Management: How to Do It Properly

This is the bit most tutorials skip. And it’s the bit that makes the difference between a script that works once and a workflow you can maintain and improve over time.

The Problem With Inline Prompts

It’s tempting to just write your prompt directly inside your function:

const prompt = "Write a meta description for: " + pageTitle;

This works fine to start. But the moment you want to tweak the prompt, test a different approach, or reuse it across multiple functions, you’re hunting through your code to find and edit a string. It’s messy, and it gets messy fast.

The Better Approach: A Prompt Config Sheet

Store your prompts in a dedicated sheet in your spreadsheet — call it Config or Prompts. Give each prompt a name in column A and the full prompt text in column B. Then write a function that retrieves a prompt by name:

function getPrompt(promptName) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const configSheet = ss.getSheetByName('Config');
  const data = configSheet.getDataRange().getValues();

  for (let i = 0; i < data.length; i++) {
    if (data[i][0] === promptName) {
      return data[i][1]; // Return the prompt text from column B
    }
  }

  throw new Error(`Prompt "${promptName}" not found in Config sheet`);
}

Now in your main functions, you call prompts by name:

const basePrompt = getPrompt('meta_description_generator');
const fullPrompt = basePrompt + '\n\nPage title: ' + pageTitle + '\nTarget keyword: ' + keyword;

Your Config sheet becomes the single source of truth for all your prompts. You can update them without touching any code. You can see them all in one place. And if something stops working, your prompt is the first place to check.

Prompt Frameworks for SEO Tasks

A well-structured prompt has a few key components. Here’s the framework we use:

[ROLE] You are an expert SEO copywriter with 10+ years of experience writing for [industry].

[CONTEXT] You are writing for [brand/site description]. The tone should be [tone description].

[TASK] Your task is to [specific instruction].

[CONSTRAINTS]
- [constraint 1]
- [constraint 2]
- [constraint 3]

[INPUT]
[variable data goes here]

[OUTPUT FORMAT]
Return only [specify exact output format]. Do not include any explanation, preamble, or additional commentary.

The [OUTPUT FORMAT] section is critical for automation. If you’re writing the output to a cell in a spreadsheet, you don’t want the AI to return a paragraph of explanation around the actual content. Tell it exactly what format you want and nothing else.

For example, for a meta description generator:

You are an expert SEO copywriter.

Your task is to write a single meta description for the page described below.

Constraints:
- Keep between 120–155 characters (Google truncates at ~120 on mobile and ~158 on desktop — landing in the middle keeps you safe on both)
- Must include the primary keyword naturally
- Must have a clear value proposition or call to action
- Do not use clickbait or misleading language
- Do not start with the brand name

Input:
Page title: {PAGE_TITLE}
Primary keyword: {PRIMARY_KEYWORD}
Page topic: {PAGE_TOPIC}

Output format:
Return only the meta description text. No quotation marks. No explanation.

We’ll build this into a full working generator in the next article. For now, the principle is what matters: treat your prompt like a brief, not a casual chat message.

---

A Quick JavaScript Primer for SEOs

You don’t need to become a developer to use Apps Script effectively. But you do need to be comfortable reading and making small edits to JavaScript. Here’s the bare minimum.

Variables

Variables store data. You’ll use const for values that don’t change and let for values that might:

const keyword = 'technical SEO audit';   // Won't change
let characterCount = 0;                   // Might change as we count characters

Functions

Functions are reusable blocks of code. You define them once, call them wherever you need them:

// Define the function
function greetUser(name) {
  return 'Hello, ' + name;
}

// Call the function
const message = greetUser('Billie'); // message = 'Hello, Billie'

In Apps Script, functions are also how you create the buttons and triggers that run your automation.

Arrays

Arrays are lists. A lot of SEO data is lists — lists of URLs, keywords, titles:

const urls = ['https://example.com/page-1', 'https://example.com/page-2'];

// Access by position (counting starts at 0)
const firstUrl = urls[0]; // 'https://example.com/page-1'

// Loop through the list
for (let i = 0; i < urls.length; i++) {
  Logger.log(urls[i]); // Prints each URL in the Apps Script logs
}

Objects

Objects store data with named properties. API responses come back as objects:

const pageData = {
  title: 'Technical SEO Guide',
  keyword: 'technical SEO',
  wordCount: 2400
};

const title = pageData.title; // 'Technical SEO Guide'

Reading and Writing to Google Sheets

This is what you’ll use most:

function readAndWriteExample() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('Data');

  // Read a single cell (row 2, column 1 = A2)
  const cellValue = sheet.getRange(2, 1).getValue();

  // Read a range of data (all rows from A2 downwards)
  const lastRow = sheet.getLastRow();
  const allData = sheet.getRange(2, 1, lastRow - 1, 3).getValues();
  // Returns a 2D array: allData[0] = first row, allData[0][0] = first cell

  // Write a value to a cell (row 2, column 2 = B2)
  sheet.getRange(2, 2).setValue('Written by the script');

  // Write multiple values at once (much faster than writing one at a time)
  const outputData = [['Value 1', 'Value 2'], ['Value 3', 'Value 4']];
  sheet.getRange(2, 3, outputData.length, outputData[0].length).setValues(outputData);
}

Handling Errors Gracefully

APIs fail. Rate limits get hit. Cells contain unexpected values. Your script needs to handle this without crashing and losing all its progress:

function safeCellOperation(value) {
  try {
    const result = callGemini('Process this: ' + value);
    return result;
  } catch (error) {
    Logger.log('Error processing value: ' + value + ' — ' + error.message);
    return 'ERROR: ' + error.message;
  }
}

Writing ERROR: [message] to the output cell means you can spot failures at a glance without the whole script stopping.

Know Your Limits: Apps Script Has Ceilings Worth Understanding

Before you start building workflows that process large datasets, there are a few hard limits in Apps Script that are worth knowing about upfront. None of them are dealbreakers — but hitting them mid-run without knowing they exist is frustrating.

Execution time limits

Apps Script cuts off any script that runs longer than 6 minutes on a standard Google account, or 30 minutes on a Google Workspace (paid) account. For small datasets this won’t matter. For larger ones — say, generating meta descriptions for a 500-page site — you’ll need to process in batches and track your progress. We’ll cover a proper batching strategy in Article 2 when we build the first real generator.

UrlFetch daily quotas

Separate from API rate limits, Apps Script itself has a daily limit on UrlFetchApp calls — 20,000 per day on standard accounts, 100,000 on Workspace. For most SEO workflows this won’t be a constraint, but if you’re running multiple scripts or high-volume jobs, it’s worth being aware of. The full Apps Script quota list is in Google’s documentation.

Adding delays to avoid API rate limits

When processing rows in a loop, you’ll need to slow your script down to avoid hitting the AI provider’s rate limits. The Google AI Studio free tier allows around 15 requests per minute, which works out to roughly one request every 4 seconds. A safe default:

// 1.5s delay — increase to 4000ms if hitting rate limits on the free tier
// Paid API tiers have much higher limits and can run significantly faster
Utilities.sleep(1500);

A note on Clasp — for those who want to develop locally

If you’re already comfortable in a code editor, it’s worth knowing that Google has an official CLI tool called Clasp that lets you write and edit Apps Script projects locally in VS Code and push changes to your project. We’ll be covering it properly when the series moves into VS Code for more complex workflows — but if you want to explore it now, it’s a solid bridge between the two environments.

---

Putting It Together: A Working “Hello World” for SEO

Let’s build one complete, working script that ties everything above together. This takes a list of page topics from a sheet, sends each one to Gemini, asks for a one-line page description, and writes the results back.

Set up your sheet first:

Column A	Column B
Page Topic	AI Output
Technical SEO audit checklist	blank — will be filled by script
How to fix crawl errors in Google Search Console	blank
Core Web Vitals explained	blank

The script — copy this entire block into Apps Script:

// ======================================================
// FUNCTION 1: The API caller — paste this first
// ======================================================
function callGemini(prompt, systemPrompt = '') {
  const apiKey = PropertiesService.getScriptProperties().getProperty('GEMINI_API_KEY');
  const url = `https://generativelanguage.googleapis.com/v1beta/models/gemini-2.0-flash:generateContent?key=${apiKey}`;

  const payload = {
    system_instruction: systemPrompt ? { parts: [{ text: systemPrompt }] } : undefined,
    contents: [{ parts: [{ text: prompt }] }]
  };

  const options = {
    method: 'post',
    contentType: 'application/json',
    payload: JSON.stringify(payload),
    muteHttpExceptions: true
  };

  const response = UrlFetchApp.fetch(url, options);
  const statusCode = response.getResponseCode();
  if (statusCode !== 200) {
    throw new Error(`Gemini API error: ${statusCode} — ${response.getContentText()}`);
  }

  const data = JSON.parse(response.getContentText());
  return data.candidates[0].content.parts[0].text;
}

// ======================================================
// FUNCTION 2: The main script — this is the one you Run
// ======================================================
function generatePageDescriptions() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('Sheet1'); // Change to match your sheet name

  const lastRow = sheet.getLastRow();
  const topics = sheet.getRange(2, 1, lastRow - 1, 1).getValues();
  const outputs = [];

  for (let i = 0; i < topics.length; i++) {
    const topic = topics[i][0];

    if (!topic) {
      outputs.push(['']);
      continue;
    }

    // Note: for simplicity this Hello World puts everything in one message.
    // Article 2 shows the proper system/user prompt split in a production workflow.
    const prompt = `You are an SEO copywriter. Write a single sentence (maximum 20 words) that describes what a page about the following topic covers. Be clear and direct. No preamble.

Topic: ${topic}`;

    try {
      const result = callGemini(prompt);
      outputs.push([result.trim()]);

      // 1.5s delay — increase to 4000ms if hitting rate limits on the free tier
      Utilities.sleep(1500);
    } catch (error) {
      Logger.log('Failed on row ' + (i + 2) + ': ' + error.message);
      outputs.push(['ERROR: ' + error.message]);
    }
  }

  sheet.getRange(2, 2, outputs.length, 1).setValues(outputs);
  SpreadsheetApp.getUi().alert('Done! ' + outputs.length + ' rows processed.');
}

To run this:

1. Open your spreadsheet
2. Go to Extensions > Apps Script
3. Delete any placeholder code and paste the entire block above — both functions
4. Make sure your API key is stored in Script Properties (as covered earlier)
5. In the function dropdown at the top of the editor, select generatePageDescriptions
6. Click Run (the ▶️ button)
7. Accept the permissions prompt the first time you run it — Apps Script needs permission to access your spreadsheet and make external requests
8. Switch back to your spreadsheet — your outputs should be populating column B

That’s it. That’s the full loop: data in, AI processes it, results written back to your sheet.

---

What’s Coming Next

This was the foundation. In Article 2, we’re going to build our first real SEO tool on top of this: a title tag and meta description generator that takes your page data and produces on-brand, properly constrained SEO copy at scale.

We’ll be covering:

• Building a proper multi-column input structure in Google Sheets
• Writing prompts that produce consistent, usable output (not just technically correct output)
• Handling character limits and validation in the script
• Adding a simple UI so non-technical team members can run it without touching the code
• A note on how to do the same thing in Screaming Frog if that’s your tool of choice

If you want to get ahead, grab a free API key from Google AI Studio and make sure the Hello World script above is working for you. The next article picks up exactly where this one leaves off.

---

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.