Make Your Metadata Not Suck – Google Sheets + GPT-4

If you’ve got a website full of pages but no time to fix all the dodgy metadata, this one’s for you. This tutorial shows you how to:

• Import all your sitemap URLs into Google Sheets
• Check each page’s title and meta description
• Rewrite them automatically using GPT-4

It’s dead simple, and it works with any site that has a sitemap.xml file. Ideal for Webflow, WordPress, static sites — or client projects where the metadata has gone rogue.

🎥 Watch the full tutorial video here:
https://www.youtube.com/watch?v=03fAFPgU3Mg

Step-by-Step: Bulk Audit & Rewrite SEO Metadata

Step 1 – Create Your Google Sheet

Make a new Google Sheet with three tabs:

• Config – put DOMAIN in cell A1 and your domain (e.g. https://yourwebsite.co.nz) in B1
• URLs – leave this empty for now; we’ll fill it
• Main – this is where you’ll paste the URLs you want to rewrite

Step 2 – Open the Script Editor

Go to Extensions → Apps Script. You’ll paste the full script later — it’s at the bottom of this article.

Step 3 – Add Your OpenAI API Key

In Apps Script, click the gear icon (Project Settings) → scroll to Script Properties → add:

• KEY: OPENAI_API_KEY
• VALUE: paste your API key from platform.openai.com

Step 4 – Load Sitemap URLs

Back in your Sheet, reload it. You’ll now see a new menu: Sitemap Tools. Click “Fetch URLs from sitemap.xml” and it’ll pull all your page URLs into the URLs tab starting at row 3.

Step 5 – Get Title + Description

In the Main tab, copy the URLs you want to fix. Then use these formulas next to each URL:

• =GET_META_TITLE_ONLY(A2)
• =GET_META_DESCRIPTION_ONLY(A2)

Step 6 – Audit the Length

Use these functions to check whether your tags are the right length:

• =AUDIT_TITLE_LENGTH(B2)
• =AUDIT_DESCRIPTION_LENGTH(C2)

Step 7 – Rewrite with GPT-4

Now for the magic. Drop these formulas next to your audits:

• =WRITE_NEW_META_TITLE(B2)
• =WRITE_NEW_META_DESCRIPTION(C2)

They’ll rewrite the metadata using OpenAI’s GPT-4o model, keeping it clean and within best-practice character counts.

Step 8 – Paste Into Your CMS

Once you're happy with the new title and description, paste it back into Webflow, WordPress, or wherever you’re managing your site. Done.

Final Tips

• This script is fast — but GPT calls can cost cents per request, so use them wisely
• You can edit the rewrite prompts in the script to suit your tone or audience
• Good for SEO audits, client fixes, or launching sites with clean metadata

🧠 Full Script (Copy & Paste)

Scroll down in the code view and copy the entire block below into your Google Sheets Apps Script editor.

function fetchSitemapUrls() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const configSheet = ss.getSheetByName('Config');
  const urlSheet = ss.getSheetByName('URLs');
  const siteUrl = getSetupValue('DOMAIN');
  if (!siteUrl) {
    SpreadsheetApp.getUi().alert("Please set DOMAIN in the Config sheet.");
    return;
  }
  const sitemapUrl = siteUrl.endsWith('/') ? siteUrl + "sitemap.xml" : siteUrl + "/sitemap.xml";
  try {
    const response = UrlFetchApp.fetch(sitemapUrl, { muteHttpExceptions: true });
    const xml = response.getContentText();
    const urls = [];
    const regex = /<loc>(.*?)<\/loc>/g;
    let match;
    while ((match = regex.exec(xml)) !== null) {
      urls.push([match[1]]);
    }
    if (urls.length === 0) {
      SpreadsheetApp.getUi().alert("No URLs found in sitemap.");
      return;
    }
    urlSheet.getRange("A3:A" + urlSheet.getMaxRows()).clearContent();
    urlSheet.getRange(3, 1, urls.length, 1).setValues(urls);
    SpreadsheetApp.getUi().alert("URLs imported successfully!");
  } catch (err) {
    SpreadsheetApp.getUi().alert("Error fetching sitemap: " + err.message);
  }
}

function getSetupValue(variableName) {
  const configSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Config');
  const data = configSheet.getRange("A1:B" + configSheet.getLastRow()).getValues();
  for (let i = 0; i < data.length; i++) {
    if (data[i][0] === variableName) {
      return data[i][1];
    }
  }
  return null;
}

function GET_META_TITLE_ONLY(url) {
  if (!url) return "";
  try {
    const response = UrlFetchApp.fetch(url, { muteHttpExceptions: true });
    const html = response.getContentText();
    const match = html.match(/<title>(.*?)<\/title>/i);
    return match ? match[1].trim() : "";
  } catch (e) {
    return "";
  }
}

function GET_META_DESCRIPTION_ONLY(url) {
  if (!url) return "";
  try {
    const response = UrlFetchApp.fetch(url, { muteHttpExceptions: true });
    const html = response.getContentText();
    const patterns = [
      /<meta\s+name=["']description["']\s+content=["'](.*?)["']/i,
      /<meta\s+content=["'](.*?)["']\s+name=["']description["']/i
    ];
    for (let pattern of patterns) {
      const match = html.match(pattern);
      if (match && match[1]) {
        return match[1].trim();
      }
    }
    return "";
  } catch (e) {
    return "";
  }
}

function AUDIT_TITLE_LENGTH(titleText) {
  if (!titleText) return "❌ Missing title";
  const length = titleText.trim().length;
  if (length <= 65) {
    return `✅ ${length} chars`;
  } else {
    return `⚠️ ${length} chars (too long)`;
  }
}

function AUDIT_DESCRIPTION_LENGTH(descriptionText) {
  if (!descriptionText) return "❌ Missing description";
  const length = descriptionText.trim().length;
  if (length >= 120 && length <= 155) {
    return `✅ ${length} chars`;
  } else if (length < 120) {
    return `⚠️ ${length} chars (too short)`;
  } else {
    return `⚠️ ${length} chars (too long)`;
  }
}

const OPENAI_API_KEY = PropertiesService.getScriptProperties().getProperty('OPENAI_API_KEY');

function CALL_OPEN_AI(prompt, input, temperature=0.2, max_tokens=100) {
  const payload = {
    model: 'gpt-4o',
    messages: [
      { role: 'system', content: prompt },
      { role: 'user', content: input }
    ],
    temperature: temperature,
    max_tokens: max_tokens
  };
  const options = {
    method: 'post',
    contentType: 'application/json',
    headers: {
      Authorization: 'Bearer ' + OPENAI_API_KEY
    },
    payload: JSON.stringify(payload)
  };
  const response = UrlFetchApp.fetch('https://api.openai.com/v1/chat/completions', options);
  const json = JSON.parse(response.getContentText());
  return json.choices[0].message.content.trim();
}

function WRITE_NEW_META_TITLE(pageTitle) {
  const prompt = `
  You are an SEO assistant. Rewrite the following page title to be clear, relevant, and under 65 characters.
  Prioritise important words, avoid buzzwords, and make it suitable for a New Zealand audience if relevant.
  Return only the new title, no extra commentary.
  `;
  const input = `Original Meta Title: ${pageTitle}`;
  return CALL_OPEN_AI(prompt, input, 0.2, 35);
}

function WRITE_NEW_META_DESCRIPTION(pageDescription) {
  const prompt = `
  You are an SEO assistant. Rewrite the following meta description to be between 120 and 155 characters.
  If the original description is too short, expand on it meaningfully without adding fluff.
  Return only the new meta description. Do not output anything else.
  `;
  const input = `Original Meta Description: ${pageDescription}`;
  return CALL_OPEN_AI(prompt, input, 0.2, 140);
}

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Sitemap Tools')
    .addItem('Fetch URLs from sitemap.xml', 'fetchSitemapUrls')
    .addToUi();
}