How I Automated My Substack Internal Linking to Get My Sundays Back

I built an AI agent to automate the boring parts of internal linking that stopped me from quitting my newsletter and drove SEO growth.

Every Sunday morning at 8 AM, I sit at my desk at home to write my Substack newsletter. This is my routine. The job is simple on paper. I write a candid post about building products, how to build AI agents, share our best prompts and even workflows.

I would finish an article in about 2 hours and hit publish. However, days later, I would realize I missed half a dozen natural places to link to my older posts. I left SEO value and reader engagement on the table.

I tried to fix it manually. I became trapped in a loop of scrolling through my own archive to hunt for the right URLs. The newsletter is growing and I wanted to scale my distribution, but I was already at my limit. I tried dumping my drafts into NotebookLM to find the link opportunities. The AI found the matches, but the chat interface stripped all my native formatting. I got a wall of plain text back. Rebuilding the headers and spacing took longer than doing it by hand. The human flow of writing was being replaced by robotic formatting tasks. The process had hit a wall made of manual admin work.

I needed an automated system to drive growth while giving me my time back. I built a background agent using Google Apps Script and the Gemini API.

Here is the complete breakdown of how the workflow is built.

---

Hey, I’m Jason Tan, a data scientist turned bootstrapped AI founder.

I write Behind the Scene, a newsletter sharing how I build and scale AI products to help founders and business owners grow.

This is the unglorified reality you won’t read in the media. It is not another story about a dropout raising $100 million in venture capital just to fail a few years later.

I write about what I have tried, what I have seen others try, and what actually works in building AI products. My goal is to share these lessons so you can skip the common mistakes and take a proven path.

---

Step 1: Building the Master Index

The first step was creating a master index of my past work. I wrote a script to parse my Substack sitemap. For every post, it extracts the meta description and asks Gemini to write a single sentence summarizing the core lesson. It saves the URL and the summary as a numbered list inside a Google Doc.

The Indexing Script:

The runMasterIndexBuilder function handles the setup, scraping, and logic. It reads my Substack sitemap to find every post URL containing /p/. It checks the current Google Doc to see if that URL is already listed. If it finds a new link, it fetches the raw HTML of that page and extracts the post title and its description from the meta tags.

The callGeminiFlash function takes over once the script gathers that metadata. It cleans up the description text, bundles it into a payload, and sends a POST request to the Google Gemini API. The prompt specifically instructs the model to return a single sentence capturing the core lesson or technical experience without any filler.

Once the API returns the summary, the main function appends a new bullet point to my Google Doc with the URL and the AI-generated summary, then pauses for three seconds to avoid hitting rate limits before moving to the next post.

function runMasterIndexBuilder() {
  const sitemapUrl = "https://jpctan.substack.com/sitemap.xml";
  const doc = DocumentApp.getActiveDocument();
  const body = doc.getBody();
  const currentText = body.getText();
  
  // Paste your AQ. key here
  const apiKey = ""; 

  const response = UrlFetchApp.fetch(sitemapUrl);
  const xml = XmlService.parse(response.getContentText());
  const root = xml.getRootElement();
  const namespace = root.getNamespace();
  const urls = root.getChildren("url", namespace);

  for (let i = 0; i < urls.length; i++) {
    const loc = urls[i].getChildText("loc", namespace);
    
    if (!loc || !loc.includes("/p/")) continue;
    
    if (currentText.includes(loc)) {
      Logger.log(`Skipped: ${loc}`);
      continue;
    }

    try {
      const pageResponse = UrlFetchApp.fetch(loc, {muteHttpExceptions: true});
      const html = pageResponse.getContentText();
      
      let title = "Substack Post";
      const titleMatch = html.match(/<title>(.*?)<\/title>/i);
      if (titleMatch) title = titleMatch[1].replace(" - Jason Tan", "").trim();
      
      let description = "";
      const descMatch = html.match(/<meta.*?name=["']description["'].*?content=["'](.*?)["']/i) || 
                          html.match(/<meta.*?property=["']og:description["'].*?content=["'](.*?)["']/i);
      if (descMatch) description = descMatch[1];
      
      const summary = callGeminiFlash(apiKey, title, description);
      body.appendParagraph(`* ${loc} covers ${summary}`);
      
      Utilities.sleep(3000);
    } catch (e) {
      Logger.log(`Failed on ${loc}: ${e.message}`);
    }
  }
}

function callGeminiFlash(apiKey, title, description) {
  const url = `https://generativelanguage.googleapis.com/v1beta/models/gemini-flash-latest:generateContent?key=${apiKey}`;
  const cleanSnippet = description.replace(/<[^>]*>/g, '').substring(0, 1200);
  
  const prompt = `Review this article title and text snippet from a technical founder's Substack newsletter. Provide a single sentence summarizing the core lesson, failure, or technical experience shared. Do not include introductory remarks, pleasantries, or meta commentary. Start directly with the description. Title: ${title}. Snippet: ${cleanSnippet}`;
  
  const payload = {
    contents: [{ parts: [{ text: prompt }] }]
  };
  
  const options = {
    method: "post",
    contentType: "application/json",
    payload: JSON.stringify(payload),
    muteHttpExceptions: true
  };
  
  const apiResponse = UrlFetchApp.fetch(url, options);
  const responseCode = apiResponse.getResponseCode();
  const responseText = apiResponse.getContentText();
  
  if (responseCode !== 200) {
    return `API Error ${responseCode}: ${responseText}`;
  }
  
  const json = JSON.parse(responseText);
  
  if (json.candidates && json.candidates[0].content.parts[0].text) {
    return json.candidates[0].content.parts[0].text.trim();
  }
  
  return `Parse Error: ${responseText}`;
}

The Summarization Prompt:

Review this article title and text snippet from a technical founder's Substack newsletter. Provide a single sentence summarizing the core lesson, failure, or technical experience shared. Do not include introductory remarks, pleasantries, or meta commentary. Start directly with the description. Title: ${title}. Snippet: ${cleanSnippet}