Problem: Waking Up in Translation Hell Every Morning
Inbound messages from overseas buyers pile up in Google Sheets. English, Chinese (Simplified and Traditional), Japanese, Spanish, Portuguese. The first task every morning is to run them through an online translator to understand what each message means. Every five minutes spent clicking between tabs and wasting time.
Then one day the dream team asked: "Why are you still doing this manually?"
Solution: Build a Translation Bot with Claude Code
Step 1. Design Your Google Sheet Input Table
First, create a table in Google Sheets with this structure:
Column A: Original text
Column B: Detected language
Column C: Korean translation
Column D: Summary (Korean)
Column E: Sentiment analysis
Step 2. Write Your Claude Code Script
Open Python on your Windows device and ask Claude Code:
"Read the text in column A of the Google Sheet,
detect the language for each cell (column B),
translate it to Korean using Claude API and put it in column C,
summarize the key points to column D (3 lines),
determine positive/neutral/negative and put it in column E, okay?"
Claude Code will provide a skeleton like this:
from google.oauth2.service_account import Credentials
from google.auth.transport.requests import Request
import gspread
import anthropic
# Google Sheets authentication
creds = Credentials.from_service_account_file(
"service_account.json",
scopes=["https://www.googleapis.com/auth/spreadsheets"]
)
client = gspread.authorize(creds)
sheet = client.open("Dream Team Translation Worksheet").sheet1
# Initialize Claude API
anth_client = anthropic.Anthropic(api_key="your-api-key")
# Read original text from column A
original_texts = sheet.col_values(1)[1:] # Exclude header
for idx, text in enumerate(original_texts, start=2):
if not text.strip():
continue
# Request Claude to perform language detection, translation, summary, sentiment analysis
response = anth_client.messages.create(
model="claude-3-5-sonnet-20241022",
max_tokens=500,
messages=[
{
"role": "user",
"content": f"""Analyze the following text:
Original text: {text}
Tasks:
1. Language detection (e.g., English, Chinese, Spanish)
2. Korean translation
3. Core summary (3 lines)
4. Sentiment analysis (positive/neutral/negative)
Return as JSON:
{{
"language": "...",
"translation": "...",
"summary": "...",
"sentiment": "..."
}}"""
}
]
)
# Parse response and record in sheet
result = json.loads(response.content[0].text)
sheet.update_cell(idx, 2, result["language"])
sheet.update_cell(idx, 3, result["translation"])
sheet.update_cell(idx, 4, result["summary"])
sheet.update_cell(idx, 5, result["sentiment"])
Step 3. Set Up Automatic Execution
Open Windows Task Scheduler and set this script to run automatically at 8 AM daily. If running on Mac mini, use Launchd instead.
Real Results
• Translation time: Manual 1 hour 20 minutes → Automated 3 minutes
• Error rate: Reduced by approximately 2% (eliminates human fatigue)
• Unexpected discovery: When you visualize buyer sentiment trends, you can spot patterns in when dissatisfaction increases.
Important Notes
**Sensitive Information Protection**: Never record customer names, transaction amounts, or pricing information in the sheet. Store only message content. Keep sensitive columns in a separate sheet for security.
**Claude API Cost**: Consumes approximately 2,000 to 3,000 tokens monthly. Monthly API cost is under one dollar.
Next Steps
Once translation is complete, you can extend the pipeline to auto-reply. By creating a flow like "Translate → Summarize → Generate response template → Pending approval," you can cut buyer response time in half or more.