How to Build a Custom AI Email Response Generator Using Claude API and Google Sheets

This article mentions a tool I use; the link at the end is an affiliate link.

Customer support emails pile up fast. Whether you're running a small business, managing a side project, or handling client communications, responding to common questions eats hours every week. Instead of paying for expensive automation platforms, you can build your own AI-powered email response system using Claude API and Google Sheets for under $10/month.

This guide walks through creating a practical automation that generates contextual email responses based on your own knowledge base. No complex infrastructure needed—just a Google account and an Anthropic API key.

What You'll Build

A system that:

• Reads incoming email subjects and snippets from a Google Sheet
• Sends them to Claude API with your custom context
• Returns draft responses you can review and send
• Costs roughly $0.01-0.05 per email processed

Prerequisites

• Google account (free)
• Anthropic API account (pay-as-you-go, no monthly minimum)
• 30 minutes of setup time

Step 1: Create Your Knowledge Base

Open a new Google Sheet and create three tabs:

Tab 1: "Knowledge_Base"

• Column A: Question category (shipping, returns, pricing, etc.)
• Column B: Key information (your actual policies, facts, procedures)

Fill this with 10-15 common topics. Example:

Category: Shipping
Info: We ship within 2 business days via USPS. Tracking provided automatically. International orders take 7-14 days.

Tab 2: "Incoming"

• Column A: Email subject
• Column B: Email preview/body
• Column C: Sender name
• Column D: Priority (you'll fill this manually)

Tab 3: "Responses"

• Column A: Original subject
• Column B: Generated response
• Column C: Status (draft/sent/edited)

Step 2: Get Your Claude API Key

1. Go to console.anthropic.com
2. Create an account (requires credit card, but you only pay for what you use)
3. Navigate to API Keys section
4. Generate a new key
5. Copy it immediately—you won't see it again

Set a spending limit of $10/month in the settings to avoid surprises.

Step 3: Write the Apps Script

In your Google Sheet:

1. Click Extensions → Apps Script
2. Delete the placeholder code
3. Paste this script:

const ANTHROPIC_API_KEY = 'your-api-key-here';

function generateResponses() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const kbSheet = ss.getSheetByName('Knowledge_Base');
  const incomingSheet = ss.getSheetByName('Incoming');
  const responseSheet = ss.getSheetByName('Responses');

  // Get knowledge base
  const kbData = kbSheet.getDataRange().getValues();
  let context = 'Company Knowledge Base:\n';
  kbData.slice(1).forEach(row => {
    context += `${row[0]}: ${row[1]}\n`;
  });

  // Process incoming emails
  const incoming = incomingSheet.getDataRange().getValues();

  for (let i = 1; i < incoming.length; i++) {
    const subject = incoming[i][0];
    const body = incoming[i][1];
    const sender = incoming[i][2];

    if (!subject) continue;

    const prompt = `${context}\n\nEmail Subject: ${subject}\nEmail Body: ${body}\nSender: ${sender}\n\nWrite a helpful, professional email response addressing their question. Use the knowledge base above. Keep it under 150 words.`;

    const response = callClaudeAPI(prompt);

    responseSheet.appendRow([subject, response, 'draft']);
  }
}

function callClaudeAPI(prompt) {
  const url = 'https://api.anthropic.com/v1/messages';

  const payload = {
    model: 'claude-3-haiku-20240307',
    max_tokens: 500,
    messages: [{
      role: 'user',
      content: prompt
    }]
  };

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

  const result = UrlFetchApp.fetch(url, options);
  const json = JSON.parse(result.getContentText());

  return json.content[0].text;
}

1. Replace 'your-api-key-here' with your actual API key
2. Save the project (name it "Email Response Generator")

Step 4: Test It

Add a test email to your "Incoming" sheet:

• Subject: "When will my order ship?"
• Body: "I ordered yesterday and need it by Friday"
• Sender: "John"

Run the generateResponses() function from the Apps Script editor. Check the "Responses" tab—you should see a generated reply that references your shipping knowledge base.

Step 5: Automate the Workflow

Manually copying emails is tedious. Here's where you can optionally connect this to an actual email workflow. When I set up my own system, I found that Perpetual Income 365 helped streamline the email capture part by providing pre-built integrations that fed into my Google Sheet automatically, which saved me from writing additional webhook code.

Alternatively, you can:

• Use Zapier's free tier (100 tasks/month) to forward emails to Google Sheets
• Set up a Gmail filter that forwards specific emails to a Zapier webhook
• Manually paste emails weekly for low-volume needs

Real-World Tips

Cost management: Claude Haiku costs about $0.25 per million input tokens. A typical email exchange uses 500-1000 tokens total, meaning roughly $0.01-0.02 per response. Process 500 emails monthly for $5-10.

Quality control: Never send AI responses without reviewing them. Use the "Status" column to track what you've checked.

Improve over time: When you edit a response before sending, copy your edited version back to the knowledge base. The AI learns from your actual voice.

Handle edge cases: Add a "Confidence" column where Claude rates how well it could answer (modify the prompt to include this). Auto-flag low-confidence responses for manual handling.

What This Actually Saves

If you spend 5 minutes per email and handle 20 per week, that's 100 minutes weekly. This system reduces it to 30 minutes of review time. That's 70 minutes back—every week.

The setup takes 30 minutes. You break even after one week.

Next Steps

Once this works, expand it:

• Add sentiment analysis to prioritize urgent/angry emails
• Create multiple knowledge bases for different products
• Generate responses in multiple languages
• Build a simple web interface using Google Apps Script Web App

The core pattern—structured data + AI API + review workflow—applies to countless other automation opportunities: social media responses, documentation generation, content repurposing, and more.

Start with email. Master the fundamentals. Then scale the approach.

---

The tool mentioned above is an affiliate link (disclosed at top): Perpetual Income 365